实现MySQL主从复制

实验准备:

在Pc上安装两个MySQL,分别监听3307和3308端口,在centos7上也安装一个MySQL,这样我们就有三个slave!首先我们要保证4个MySQL上的数据一致,并且配置好各个的日志,这样即使出错了,也容易找出错误。

  • Slave1:MySQL3307
  • Slave2:MySQL3308
  • Slave3:Centos7上的MySQL
  • Master: 运行在3306端口的MySQL

修改Master的配置

 [mysqld]
 server-id=1      //[必须]服务器唯一ID,默认是1,可以随便配置,只要不重复即可
 log-bin=mysql-bin   //[必须]启用二进制日志 
 binlog-format =mixed #bin的格式
 relay-log=mysql-relay #中继日志文件名
 slave-skip-errors=all #跳过所有错误

修改Slave的配置

#MySQL3307
 [mysqld]
 log-bin=mysql-bin   //[不是必须]启用二进制日志
 server-id=3307      //[必须]服务器唯一ID,默认是1,可以随便配置,只要不重复即可

#MySQL3308
[mysqld]
 log-bin=mysql-bin   //[不是必须]启用二进制日志
 server-id=3308      //[必须]服务器唯一ID,默认是1,可以随便配置,只要不重复即可

#Centos7
[mysqld]
 log-bin=mysql-bin   //[不是必须]启用二进制日志
 server-id=3      //[必须]服务器唯一ID,默认是1,可以随便配置,只要不重复即可

保存修改后重启4个MySQL服务

在Master创建3个MySQL用户

grant replication client,replication slave on *.* to slave3307@'%' identified by 'slave3307';

grant replication client,replication slave on *.* to slave3308@'%' identified by 'slave3308';

grant replication client,replication slave on *.* to centos7@'%' identified by 'centos7'; 

flush privileges; 

登录Master执行命令查看log_pos和log_file

show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000062 |      8233|              |                  |
   +------------------+----------+--------------+------------------+

注意 执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化

配置从服务器Slave,下面的命令分别在3个MySQL上执行:

change master to master_host='192.168.19.254',master_port=3306,master_user='slave3307',
master_password='slave3307',master_log_file='mysql-bin.000062',master_log_pos=8233;

start slave #启动从服务器复制功能

change master to master_host='192.168.19.254',master_port=3306,master_user='slave3308',
master_password='slave3308',master_log_file='mysql-bin.000062',master_log_pos=8233;

start slave #启动从服务器复制功能

change master to master_host='192.168.19.254',master_port=3306,master_user='centos7',
master_password='centos7',master_log_file='mysql-bin.000087',master_log_pos=8233;

start slave #启动从服务器复制功能

检查Slave复制功能的状态

show slave status\G

注意 Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO或者出现Connection)。有时候是server-id配置错误、防火墙拦截或账户没有权限,仔细检查各个的错误日志,就能很快找到问题所在。

END 以上操作过程,主从服务器配置完成

#在Windows上注册MySQL添加服务

C:\phpStudy\MySQL3307\bin\mysqld install mysql3307 --defaults-file="C:\phpStudy\MySQL3307\my.ini"
C:\phpStudy\MySQL3308\bin\mysqld install mysql3308 --defaults-file="C:\phpStudy\MySQL3308\my.ini"

MySQL主从复制跳过错误两种方式:

1.跳过指定数量的事务:

mysql>stop slave ;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  #跳过一个事务
mysql>start slave;

2.修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
slave-skip-errors=all #跳过所有错误

2018年10月3号更新:
注意:
如果你是直接复制MySQL的安装目录,而你的MySQL的版本又是5.6及以后的版本,那你有可能出现下面的问题:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

mysql 5.6的复制引入了uuid的概念,各个复制结构中的server_uuid得保证不一样,但是查看到直接复制data文件夹后server_uuid是相同的

show variables like '%server_uuid%';

解决方法:
找到data文件夹下的auto.cnf文件,修改里面的uuid值,保证各个db的uuid不一样,重启db即可

附一个正确slave status的信息


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: slave3307
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 602
               Relay_Log_File: mysql-relay.000005
                Relay_Log_Pos: 765
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            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: 602
              Relay_Log_Space: 1384
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: fe1b230d-6ee9-11e8-b0c6-9c5c8e103115
             Master_Info_File: C:\phpStudy\MySQL3307\data\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
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

此处评论已关闭