nmcli g hostname mysql-master
nmcli g hostname mysql-slave
本地解析
yum -y install lftp (2台)
m与s:
改:UUID:vi /var/lib/mysql/auto.cnf
二:GTID主从复制
修改主机名:(所有节点)(可选操作)
[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)