Mysql主从复制

Mysql主从复制

复制功能

• 数据分布
• 负载均衡（读）
• 备份
• 高可用和故障切换
• MySQL升级测试

复制方式

1. 主–从复制
2. 主–主复制
3. 半同步复制

复制原理

Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

复制过程

1. 主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
2. 从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件
3. 主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
4. 从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个，在后面详细讲解）。
5. 从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

Mysql复制特点

1. 异步复制：主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主节点，可以使用（很多软件来检查是否落后）
2. 主从数据不一致。

主从复制配置过程

主节点：

1. 启用二进制日志。
2. 为当前节点设置一个全局唯一的server_id。
3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

1. 启动中继日志。
2. 为当前节点设置一个全局唯一的server_id。
3. 使用有复制权限的用户账号连接至主节点，并启动复制线程。

配置演示

主节点：192.168.138.141

从节点：192.168.138.142

#主从机均添加
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
server-id=141
log-bin=mysql-bin
log-slave=updates
slave-skip-errors=all
binlog-do-db=test #复制的具体数据库，如果不添加，则复制整个mysql数据库

#重启MySQL服务生效
[root@localhost ~]# systemctl restart mysqld.service

#进入mysql 检查配置生效
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 141   |
+---------------+-------+
1 row in set (0.02 sec)


#主
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)


#从
mysql> CHANGE MASTER TO MASTER_HOST='192.168.138.141',MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=156;

#开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#关闭主从复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
#重设主从复制CHANGE MASTER TO 信息
mysql> reset slave;
Query OK, 0 rows affected (0.02 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.138.141
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 437
               Relay_Log_File: localhost-relay-bin.000010
                Relay_Log_Pos: 605
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes   ##两个都为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: 437
              Relay_Log_Space: 986
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0

常见异常处理

Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.

mysql> stopt slave;
mysql> set global enforce_gtid_consistency=warn; 
Query OK, 0 rows affected (0.00 sec)

mysql> set global enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL GTID_MODE = OFF_PERMISSIVE;
mysql> set GLOBAL GTID_MODE = ON_PERMISSIVE;
mysql> set GLOBAL GTID_MODE = ON;
mysql> start slave;


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/data文件夹下的auto.cnf文件，重启mysql服务