MySQL之主从复制

MySQL的主从复制

读写分离

复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制

复制的功用

负载均衡读操作

备份

高可用和故障切换

数据分布

MySQL升级

主从复制原理

主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关
系
mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志

主从复制特点

异步复制: 客户端性能良好
主从数据不一致比较常见
一Master/一Slave
一主多从
从服务器还可以再有从服务器
Master/Master
一从多主:适用于多个不同数据库
环状复制

复制需要考虑二进制日志事件记录格式

案例：一主一从复制

环境准备；

主服务器：172.31.0.28
从服务器：172.31.0.38

两台安装相同版本的mysql软件

[root@centos8 ~]# yum install mysql-server -y
[root@centos8 ~]# yum install mysql-server -y

主改配置

[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=28
log-bin=/data/mysql/mysql-bin

创建目录存放二进制日志

[root@centos8 ~]# mkdir /data/mysql -p

改所属组

[root@centos8 ~]# chown -R mysql.mysql /data/mysql/
[root@centos8 ~]# chown -R mysql.mysql  /var/lib/mysql

重启服务

[root@centos8 ~]# systemctl enable --now mysqld

进入mysql

[root@centos8 ~]# mysql
# 查看日志，记录下来后面从同步要用
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       156 | No        |
+------------------+-----------+-----------+

# 创建一个数据库
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 创建用户管理主从复制
mysql> create user 'repluser'@'172.31.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 授权
mysql> grant replication slave on *.* to repluser@'172.31.0.%';
Query OK, 0 rows affected (0.00 sec)

从改配置

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=38

# 重启
[root@centos8 ~]# systemctl enable --now mysqld

# 登陆mysql，这里是没有设置密码
[root@localhost ~]# mysql

# 查看当前所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 查看change格式
mysql> help change master to

# 添加如下设置
mysql> CHANGE MASTER TO
 MASTER_HOST='172.31.0.28',
 MASTER_USER='repluser',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=156;

# 启动slave
mysql> start slave;

# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.28
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1295
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 1463
        Relay_Master_Log_File: mysql-bin.000002
             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: 1295
              Relay_Log_Space: 1676
              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: 28
                  Master_UUID: 761c8309-b8e1-11eb-bb76-000c29acf5a4
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

# 再次查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

案例：在上面的基础上实现一主多从

# 新增一台服务器：172.31.0.48
安装相同版本mysql
[root@centos8 ~]# yum install mysql-server -y

# 改配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=48
read_only=ON

# 启动
[root@centos8 ~]# systemctl enable --now mysqld

# 登陆mysql，这里是没有设置密码
[root@localhost ~]# mysql

# 查看当前所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

# 添加如下设置
mysql> CHANGE MASTER TO
 MASTER_HOST='172.31.0.28',
 MASTER_USER='repluser',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=156;
 
# 启动slave
mysql> start slave;

# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.28
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1295
               Relay_Log_File: centos8-relay-bin.000002
                Relay_Log_Pos: 1463
        Relay_Master_Log_File: mysql-bin.000002
             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: 1295
              Relay_Log_Space: 1674
              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: 28
                  Master_UUID: 761c8309-b8e1-11eb-bb76-000c29acf5a4
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

# 再次查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

在从节点清除信息

# 注意：以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD等

# 系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N

# 服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

实现主从级联复制

需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

环境准备：
# 在172.31.0.28充当master
# 在172.31.0.38充当级联slave
# 在172.31.0.48充当slave

## 在172.31.0.28充当master改配置文件
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=28
log-bin=/data/mysql/mysql-bin

# 重启
[root@centos8 ~]# systemctl enable --now mysqld

# 登录mysql，没有密码
[root@centos8 ~]# mysql
# 创建用户管理主从复制
mysql> create user 'repluser'@'172.31.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 授权
mysql> grant replication slave on *.* to repluser@'172.31.0.%';
Query OK, 0 rows affected (0.00 sec)

## 在172.31.0.38充当级联slave
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=38
log_slave_updates
log-bin=/data/mysql/mysqld-bin

# 重启
[root@centos8 ~]# systemctl enable --now mysqld

# 登录mysql，没有密码
[root@centos8 ~]# mysql
# 添加
CHANGE MASTER TO
 MASTER_HOST='172.31.0.28',
 MASTER_USER='repluser',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql-bin.000002',
 MASTER_LOG_POS=156;
 
# 启动slave
mysql> start slave;

# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.28
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 867
               Relay_Log_File: centos8-relay-bin.000002
                Relay_Log_Pos: 1035
        Relay_Master_Log_File: mysql-bin.000002
             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: 867
              Relay_Log_Space: 1246
              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: 28
                  Master_UUID: ecd17bd7-b954-11eb-8928-000c29acf5a4
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

# 这里的刷新授权就不用再级联数据库创建管理slave用户
mysql> flush privileges;

# 查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tesedb             |
+--------------------+
6 rows in set (0.00 sec)

## 在172.31.0.48充当slave
[root@centos8 ~]# cat /etc/my.cnf
[mysqld]
server-id=48
read-only

# 重启
[root@centos8 ~]# systemctl enable --now mysqld

# 登录mysql，没有密码
[root@centos8 ~]# mysql

# 添加172.31.0.38的二进制日志
CHANGE MASTER TO
 MASTER_HOST='172.31.0.38',
 MASTER_USER='repluser',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysqld-bin.000002',
 MASTER_LOG_POS=156;
 
# 查看sql和io状态 看到Slave_IO_Running: Yes 和Slave_SQL_Running: Yes 即是主从复制成功
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.38
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1055
               Relay_Log_File: centos8-relay-bin.000002
                Relay_Log_Pos: 1035
        Relay_Master_Log_File: mysql-bin.000002
             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: 1055
              Relay_Log_Space: 1243
              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: 38
                  Master_UUID: 53470252-b955-11eb-a77c-000c29169a81
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

# 查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tesedb             |
+--------------------+
6 rows in set (0.00 sec)