mysql/主从复制.md at main

wxin/mysql

Fork 0

wxin a93a50ae65 更新主从复制.md

2025-03-13 13:42:37 +08:00

38 KiB

Raw Permalink Blame History

主从复制

一：主从复制

1. 简介

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库

主从复制的作用：

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失
架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足，多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能
读写分离，使数据库能支撑更大的并发

主从复制的原理：

主服务器（Master）：主服务器负责处理所有的写操作和事务。所有的更改（如 INSERT、UPDATE 和 DELETE）首先在主服务器上执行。
从服务器（Slave）：从服务器通过读取主服务器的二进制日志（binlog）来获取数据更改信息，并将这些更改应用到自身的数据库中。
二进制日志（binlog）：binlog 是 MySQL 中记录所有更改数据的日志文件。主服务器在执行每个更改操作时，会将该操作以事件的形式记录到 binlog 中。
复制线程：从服务器通过一个 I/O 线程连接到主服务器，读取 binlog 并将其存储到本地的中继日志（relay log）中。然后，由一个 SQL 线程从中继日志中读取事件并应用到从服务器的数据库中。

注意：

I/O进程：负责通信
SQL进程：负责写数据，根据log日志写数据

2. 主从复制部署

环境准备

节点	IP地址
Master	192.168.159.131
Slave	192.168.159.132

注意：

所有节点关闭防火墙和selinux
保证yum仓库可用
保证网络畅通
如果是克隆的服务器需要修改每台数据库的server-uuid

修改主机名：（所有节点）（可选操作）

[root@test ~]# hostnamectl set-hostname Master
[root@test ~]# hostnamectl set-hostname Slave

添加本地解析：（所有节点）（可选操作）

[root@master ~]# vim /etc/hosts
192.168.159.131 Master
192.168.159.132 Slave

[root@slave ~]# vim /etc/hosts
192.168.159.131 Master
192.168.159.132 Slave

Master部署

安装数据库：

[root@master ~]# rpm -e --nodeps mariadb-libs
[root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
[root@master ~]# mkdir mysql
[root@master ~]# tar xvf mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar -C mysql && cd mysql
[root@master mysql]# rpm -i mysql-community-common-5.7.30-1.el7.x86_64.rpm
[root@master mysql]# rpm -i mysql-community-libs-5.7.30-1.el7.x86_64.rpm 
[root@master mysql]# rpm -i mysql-community-client-5.7.30-1.el7.x86_64.rpm 
[root@master mysql]# rpm -i mysql-community-server-5.7.30-1.el7.x86_64.rpm 
[root@master mysql]# rpm -ivh mysql-community-libs-compat-5.7.30-1.el7.x86_64.rpm

启动数据库：

[root@master mysql]# systemctl start mysqld
[root@master mysql]# systemctl enable mysqld

修改数据库初始密码：

[root@master mysql]# grep password /var/log/mysqld.log
[root@master mysql]# mysqladmin -uroot -p'K*6i%5e&nh.d' password 'Wxin@123456'

主服务器部署：

[root@master mysql]# vim /etc/my.cnf
log-bin = mylog
server-id = 1

[root@master mysql]# systemctl restart mysqld

创建授权账户：

[root@master mysql]# mysql -uroot -p'Wxin@123456'
mysql> grant all on *.* to 'Slave'@'%' identified by 'Slave@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

重启服务：

[root@master mysql]# systemctl restart mysqld

注意：

replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志
super权限：允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：必须拥有reload权限，才可以执行flush [tables | logs | privileges]

Slave部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

从服务器部署：

[root@slave ~]# vim /etc/my.cnf
log-bin = mylog
server-id = 2

重启服务：

[root@slave ~]# systemctl restart mysqld

获取主服务器信息：（主服务器操作）

[root@slave ~]# mysql -uroot -p'Wxin@123456'
mysql> show binlog events;
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name     | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| mylog.000001 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mylog.000001 | 123 | Previous_gtids |         2 |         154 |                                       |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

指定主服务器信息：（从服务器操作）

mysql> change master to 
    -> master_host='Master',
    -> master_user='Slave',
    -> master_password='Slave@123456',
    -> master_port=3306,
    -> master_log_file='mylog.000001',
    -> master_log_pos=4,
    -> master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

参数解释：
CHANGE MASTER TO
  MASTER_HOST='mysql-master-1.blackmed.cn/ip',
  MASTER_USER='slave',  //主服务器用户
  MASTER_PASSWORD='big',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001', //日志文件
  MASTER_LOG_POS=4, //日志位置
  MASTER_CONNECT_RETRY=10;  //默认尝试次数
获取参数：
mysql> help change master to

启动slave：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

注意：

stop slave：停止slave
reset master：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件；用于第一次进行搭建主从库时，进行主库binlog初始化工作
reset slave：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

查看主从状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mylog.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 359
        Relay_Master_Log_File: mylog.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: 154
              Relay_Log_Space: 1204
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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 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: 
1 row in set (0.00 sec)

注意：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证：

主服务器创建数据：

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

从服务器查看数据：

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

二：GTID主从复制

1. GTID 简介

GTID基于事务ID复制
GTID全局事务标识（global transaction identifiers）
是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制

2. GTID 组成

GTID = source_id:transaction_id

source_id源id，用于鉴别原服务器，即mysql服务器唯一的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID

transaction_id事务id，为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务

示例：

3E11FA47-71CA-11E1-9E33-C80AA9429562:23
前面的一串为服务器的server_uuid
后面的23为transaction_id

3. GTID 工作原理

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中
slave端的i/o 线程将变更的binlog，写入到本地的relay log中
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录
如果有记录，说明该GTID的事务已经执行，slave会忽略
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog

4. 主从复制

注意：

实验之前环境初始化，不要有残留的数据

环境准备

节点	IP地址
Master	192.168.159.131
Slave	192.168.159.132

注意：

所有节点关闭防火墙和selinux
保证yum仓库可用
保证网络畅通
如果是克隆的服务器需要修改每台数据库的server-uuid

修改主机名：（所有节点）（可选操作）（略）

Master部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

主服务器部署：

[root@master ~]# vim /etc/my.cnf
log-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

创建授权用户：

[root@master ~]# mysql -uroot -p'Wxin@123456'
mysql> grant all on *.* to 'slave'@'%' identified by 'Slave@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

重启服务：

[root@master ~]# systemctl restart mysqld

Slave部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

从服务器部署：

[root@slave ~]# vim /etc/my.cnf
log-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_recovery=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
//这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用

重启服务：

[root@slave ~]# systemctl restart mysqld

配置连接主服务器：

[root@slave ~]# mysql -uroot -p'Wxin@123456'
mysql> change master to 
    -> master_host='master',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动Slave：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

主从状态验证：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 409
        Relay_Master_Log_File: master-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: 194
              Relay_Log_Space: 1264
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2
            Executed_Gtid_Set: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

数据验证：

主服务器创建数据：

[root@master ~]# mysql -uroot -p'Wxin@123456'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

从服务器查验数据：

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

三：GTID 双主双从

1. 环境准备

注意：

实验之前环境初始化，不要有残留的数据
先做双主，M-M互为主从，从是双主的从

节点	IP地址
Master-1	192.168.159.131
Master-2	192.168.159.132
Slave-1	192.168.159.133
Slave-2	192.168.159.134

注意：

所有节点关闭防火墙和selinux
保证yum仓库可用
保证网络畅通
如果是克隆的服务器需要修改每台数据库的server-uuid

2. Master-1 部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

主服务器一部署：

[root@master1 ~]# vim /etc/my.cnf
log-bin=my1log
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1

创建授权账户：

[root@master1 ~]# mysql -uroot -p'Wxin@123456'
mysql> grant all on *.* to 'slave'@'%' identified by 'Slave@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

重启服务：

[root@master1 ~]# systemctl restart mysqld

3. Master-2 部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

主服务器二部署：

[root@master2 ~]# vim /etc/my.cnf
log-bin=my2log
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

创建授权账户：

[root@master2 ~]# mysql -u root -p'Wxin@123456'
mysql> grant all on *.* to 'slave'@'%' identified by 'Slave@123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

重启服务：

[root@master2 ~]# systemctl restart mysqld

4. 双主互为主从

Master-1：

[root@master1 ~]# mysql -uroot -p'Wxin@123456'
mysql> change master to
    -> master_host='master2',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: master1-relay-bin.000003
                Relay_Log_Pos: 401
        Relay_Master_Log_File: my2log.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: 194
              Relay_Log_Space: 1250
              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: 2
                  Master_UUID: fe4582a5-ffc2-11ef-a5a1-000c29d5ef63
             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 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: fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
            Executed_Gtid_Set: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> exit
Bye

Master-2：

[root@master2 ~]# mysql -uroot -p'Wxin@123456'
mysql> change master to 
    -> master_host='master1',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: master2-relay-bin.000003
                Relay_Log_Pos: 401
        Relay_Master_Log_File: my1log.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: 194
              Relay_Log_Space: 1250
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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 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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2
            Executed_Gtid_Set: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> exit
Bye

5. Slave-1 部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

从服务器一部署：

[root@slave1 ~]# vim /etc/my.cnf
log-bin=my3log
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on

当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性

重启服务：

[root@slave1 ~]# systemctl restart mysqld

从连接主服务器：

主服务器：
[root@master1 ~]# mysql -uroot -p'Wxin@123456'
mysql> show master status\G
*************************** 1. row ***************************
             File: my1log.000002
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
1 row in set (0.00 sec)

[root@slave1 ~]# mysql -uroot -p'Wxin@123456'
mysql> set global gtid_purged='2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2';
Query OK, 0 rows affected (0.00 sec)

mysql> change master to 
    -> master_host='master1',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1 for channel 'master1';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave1-relay-bin-master1.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my1log.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: 194
              Relay_Log_Space: 577
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master1
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> change master to
    -> master_host='master2',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1 for channel 'master2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave1-relay-bin-master1.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my1log.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: 194
              Relay_Log_Space: 577
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave1-relay-bin-master2.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my2log.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: 194
              Relay_Log_Space: 577
              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: 2
                  Master_UUID: fe4582a5-ffc2-11ef-a5a1-000c29d5ef63
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

6. Slave-2 部署

安装数据库：（略）

启动数据库：（略）

修改数据库初始密码：（略）

从服务器二部署：

root@slave2 ~]# vim /etc/my.cnf
log-bin=my4log
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on

重启服务：

[root@slave2 ~]# systemctl restart mysqld

从连接主服务器：

主服务器：
[root@master1 ~]# mysql -uroot -p'Wxin@123456'
mysql> show master status\G
*************************** 1. row ***************************
             File: my1log.000002
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
1 row in set (0.00 sec)

[root@slave2 ~]# mysql -uroot -p'Wxin@123456'
mysql> set global gtid_purged='2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2';
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='master1',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1 for channel 'master1';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='master2',
    -> master_user='slave',
    -> master_password='Slave@123456',
    -> master_auto_position=1 for channel 'master2';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave2-relay-bin-master1.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my1log.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: 194
              Relay_Log_Space: 577
              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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave2-relay-bin-master2.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my2log.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: 194
              Relay_Log_Space: 577
              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: 2
                  Master_UUID: fe4582a5-ffc2-11ef-a5a1-000c29d5ef63
             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: 2d8d2ad9-ff2f-11ef-84bc-000c29ba8f62:1-2,
fe4582a5-ffc2-11ef-a5a1-000c29d5ef63:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

7. 验证

主服务器创建数据：

[root@master1 ~]# mysql -uroot -p'Wxin@123456'
mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

其他服务器验证：

[root@master2 ~]# mysql -uroot -p'Wxin@123456'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

[root@slave1 ~]# mysql -uroot -p'Wxin@123456'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

[root@slave2 ~]# mysql -uroot -p'Wxin@123456'
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

38 KiB Raw Permalink Blame History Unescape Escape

主从复制

一：主从复制

1. 简介

2. 主从复制部署

环境准备

Master部署

Slave部署

二：GTID主从复制

1. GTID 简介

2. GTID 组成

3. GTID 工作原理

4. 主从复制

环境准备

Master部署

Slave部署

三：GTID 双主双从

1. 环境准备

2. Master-1 部署

3. Master-2 部署

4. 双主互为主从

5. Slave-1 部署

6. Slave-2 部署

7. 验证

38 KiB

Raw Permalink Blame History