mysql主从复制

如果开始是单机mysql需要先同步数据到slave
mysqldump -h主数据库ip  -uroot  -p123456 --default-character-set=utf8 --databases test --single-transaction --master-data=2 > test.sql

从库导入
mysql -uroot -123456 --default-character-set=utf8 < test.sql

主从复制配置

####################主服务器配置(192.168.56.149)###########################

1、修改主数据库mysql配置，找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行

[mysqld]
character_set_server=utf8
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

 

2、重启mysql，创建用于同步的用户账号

CREATE USER 'test'@'192.168.56.150' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.56.150';
flush privileges;

 

3、查看master装填，记录二进制文件名(mysql-bin.000005)和位置(977)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      977 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

#########################从服务器(ip:192.168.56.150)##################################

1、修改mysql配置，同样找到my.cnf配置文件，添加server-id

[mysqld]

character_set_server=utf8
server-id=2 #设置server-id，必须唯一

2、重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

CHANGE MASTER TO
MASTER_HOST='192.168.56.149',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=516;

3、启动slave同步进程

start slave;
注意：关闭是stop slave;

4、查看slave状态

show slave status\G;

 5、如果在线增加

　　change master to master_host='192.168.31.33',master_user='admin',master_password='admin123', master_log_file='mysql-bin.000001',master_log_pos=106;

FAQ

1、如果Slave_IO_Running: Connecting，同时Last_IO_Errno: 2003，说明和主节点服务器无法通信

 Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'test@192.168.56.149:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

解决方案：

　　1、ping主机是否通

　　2、从节点的设置账号和密码以及主节点IP是否正确 ，即MASTER_USER、MASTER_PASSWORD、MASTER_HOST、MASTER_LOG_FILE、MASTER_LOG_POS是否一致

　　3、关闭防火墙  即systemctl disable firewalld

　　4、关闭selinux 即selinux=disabled

 

2、如果搭建失败，重置slave

reset slave;

 3、指定复制主库表

在slave实例的my.cnf添加
replicate-do-db=指定的库