Day 29 MySQL的主从复制集群

月之菈妮

已于 2024-05-21 22:11:38 修改

阅读量1k

点赞数 22

文章标签： mysql 数据库

于 2024-05-10 17:14:08 首次发布

本文链接：https://blog.csdn.net/xlv133922/article/details/138673223

版权

一：主从复制

1.主从复制概念

什么是主从复制：

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

主从复制的作用：

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失

架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足，多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能

读写分离，使数据库能支撑更大的并发

主从复制的原理：

数据库有个bin-log二进制文件，记录了所有sql语句

我们的目标就是把主数据库的bin-log文件的sql语句复制到从库

让其在从数据的relay-log(中继日志)重做日志文件中再执行一次这些sql语句即可

总结：

从库slave生成两个线程，i/o线程和sql线程，i/o将变更记录写到二进制日志文件中，再写到中继日志中，sql线程读取中继日志，解析操作，最终数据统一

注意：

I/O进程：负责通信

SQL进程：负责写数据，根据log日志写数据

2.主从复制部署

环境准备

节点	IP地址
Master	10.0.0.128
Slave	10.0.0.42

注意：

所有节点关闭防火墙和selinux

保证yum仓库可用

保证网络畅通

如果是克隆的服务器需要修改每台数据库的server-uuid：vim /var/lib/mysql/auto.cnf

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

[root@xingdian ~]# hostnamectl set-hostname master
[root@xingdian ~]# hostnamectl set-hostname slave

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

[root@master ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave

[root@slave ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave

Master部署

安装数据库：（略）

启动数据库：（略）

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

主服务器部署：

[root@master ~]# vi /etc/my.cnf
log-bin = my1log
server-id = 1

创建授权账户：

[root@master ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to 'slave'@'%' identified by 'QianFeng@123';
mysql> flush privileges;
mysql> exit
Bye

重启服务：

[root@master ~]# systemctl restart mysqld

注意：

replication slave：

拥有此权限可以查看从服务器，从主服务器读取二进制日志

super权限：

允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句

reload权限：

必须拥有reload权限，才可以执行flush [tables | logs | privileges]

Slave部署

安装数据库：（略）

启动数据库：（略）

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

从服务器部署：

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

重启服务：

[root@slave ~]# systemctl restart mysqld

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

[root@master ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| my1log.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| my1log.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

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

[root@slave ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> edit
    -> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

注意：edit中添加的内容
CHANGE MASTER TO
  MASTER_HOST='master',
  MASTER_USER='slave',  
  MASTER_PASSWORD='QianFeng@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='my1log.000001',
  MASTER_LOG_POS=4, 
  MASTER_CONNECT_RETRY=10;  
  
参数解释：
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: my1log.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my1log.000001
             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: 568
              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: 85c6acc4-3db0-11ed-b302-000c29311164
             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 t1;
Query OK, 1 row affected (0.00 sec)

从服务器查看数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| t1                 |
+--------------------+
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	10.0.0.128
Slave	10.0.0.42

注意：

所有节点关闭防火墙和selinux

保证yum仓库可用

保证网络畅通

如果是克隆的服务器需要修改每台数据库的server-uuid

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

[root@xingdian ~]# hostnamectl set-hostname master
[root@xingdian ~]# hostnamectl set-hostname slave

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

[root@master ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave

[root@slave ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.128 master
10.0.0.42 slave

Master部署

安装数据库：（略）

启动数据库：（略）

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

主服务器部署：

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

创建授权用户：

[root@master ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to slave@'%' identified by 'QianFeng@123';
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 ~]# 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 -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='master',
    -> master_user='slave',
    -> master_password='QianFeng@123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 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.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000001
             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: 576
              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: 00813e87-4321-11ed-a33c-000c29311164
             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: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

数据验证：

主服务器创建数据：

[root@master ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> exit
Bye

从服务器查验数据：

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

三：GTID双主双从

1.环境准备

注意：

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

先做双主，M-M互为主从，从是双主的从

节点	IP地址
Master-1	10.0.0.128
Master-2	10.0.0.46
Slave-1	10.0.0.42
Slave-2	10.0.0.45

注意：

所有节点关闭防火墙和selinux

保证yum仓库可用

保证网络畅通

如果是克隆的服务器需要修改每台数据库的server-uuid
在这里插入图片描述

2.Master-1部署

安装数据库：（略）

启动数据库：（略）

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

主服务器一部署：

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

创建授权账户：

[root@master-1 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'slave'@'%' identified by 'QianFeng@123';
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-1 ~]# systemctl restart mysqld

3.Master-2部署

安装数据库：（略）

启动数据库：（略）

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

主服务器二部署：

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

创建授权账户：

[root@master-2 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on *.* to 'slave'@'%' identified by 'QianFeng@123';
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-2 ~]# systemctl restart mysqld

4.双主互为主从

Master-1：

[root@master-1 ~]# mysql  -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='master-2',
    -> master_user='slave',
    -> master_password='QianFeng@123',
    -> 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: master-2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: master-1-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my2log.000001
             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: 571
              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: 1b453d94-452e-11ed-b744-000c2936b606
             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: 146f4cae-452e-11ed-b87f-000c29311164: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@master-2 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='master-1',
    -> master_user='slave',
    -> master_password='QianFeng@123',
    -> 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: master-1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000001
          Read_Master_Log_Pos: 587
               Relay_Log_File: master-2-relay-bin.000002
                Relay_Log_Pos: 794
        Relay_Master_Log_File: my1log.000001
             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: 587
              Relay_Log_Space: 1004
              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: 146f4cae-452e-11ed-b87f-000c29311164
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
            Executed_Gtid_Set: 146f4cae-452e-11ed-b87f-000c29311164: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@slave-1 ~]# 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@slave-1 ~]# systemctl restart mysqld

从连接主服务器：

[root@slave-1 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master-1',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='QianFeng@123',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-1';
Query OK, 0 rows affected, 2 warnings (0.00 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: master-1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000001
          Read_Master_Log_Pos: 587
               Relay_Log_File: slave-1-relay-bin-master@002d1.000002
                Relay_Log_Pos: 794
        Relay_Master_Log_File: my1log.000001
             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: 587
              Relay_Log_Space: 1016
              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: 146f4cae-452e-11ed-b87f-000c29311164
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
            Executed_Gtid_Set: 146f4cae-452e-11ed-b87f-000c29311164:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
1 row in set (0.00 sec)


mysql> CHANGE MASTER TO
    -> MASTER_HOST='master-2',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='QianFeng@123',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-2';
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: master-1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000001
          Read_Master_Log_Pos: 587
               Relay_Log_File: slave-1-relay-bin-master@002d1.000002
                Relay_Log_Pos: 794
        Relay_Master_Log_File: my1log.000001
             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: 587
              Relay_Log_Space: 1016
              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: 146f4cae-452e-11ed-b87f-000c29311164
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
            Executed_Gtid_Set: 146f4cae-452e-11ed-b87f-000c29311164:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-1-relay-bin-master@002d2.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my2log.000001
             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: 583
              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: 1b453d94-452e-11ed-b744-000c2936b606
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

6.Slave-2部署

安装数据库：（略）

启动数据库：（略）

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

从服务器二部署：

[root@slave-2 ~]# 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@slave-2 ~]# systemctl restart mysqld

从连接主服务器：

[root@slave-2 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master-1',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='QianFeng@123',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-1';
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='master-2',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='QianFeng@123',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'master-2';
Query OK, 0 rows affected, 2 warnings (0.00 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: master-1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my1log.000001
          Read_Master_Log_Pos: 587
               Relay_Log_File: slave-2-relay-bin-master@002d1.000002
                Relay_Log_Pos: 794
        Relay_Master_Log_File: my1log.000001
             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: 587
              Relay_Log_Space: 1016
              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: 146f4cae-452e-11ed-b87f-000c29311164
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
            Executed_Gtid_Set: 146f4cae-452e-11ed-b87f-000c29311164:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my2log.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-2-relay-bin-master@002d2.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: my2log.000001
             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: 583
              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: 1b453d94-452e-11ed-b744-000c2936b606
             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: 146f4cae-452e-11ed-b87f-000c29311164:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

7.验证

主服务器创建数据：

[root@master-1 ~]# mysql  -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> exit
Bye

其他服务器验证：

[root@master-2 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

[root@slave-1 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

[root@slave-2 ~]# mysql -u root -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

月之菈妮

关注

22
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Day 29 MySQL的主从复制集群

用于第一次进行搭建主从库时，进行主库binlog初始化工作。从库slave生成两个线程，i/o线程和sql线程，i/o将变更记录写到二进制日志文件中，再写到中继日志中，sql线程读取中继日志，解析操作，最终数据统一。 source_id源id，用于鉴别原服务器，即mysql服务器唯一的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID。架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足，多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
复制链接

扫一扫