搭建mysql主主架構(gòu)

環(huán)境信息

主機名	IP地址	服務(wù)	角色
mysql1	192.168.44.188	mysql，keepalived	mysql-master，keepalived-master
mysql2	192.168.44.190	mysql，keepalived	mysql-master，keepalived-backup
	192.168.44.100（vip）

安裝msql服務(wù)

mysql1

[root@mysql1 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql1 ~]# yum -y install *.rpm[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id=0001
log_bin=mysql-0001[root@mysql1 ~]# systemctl enable mysqld --now
#查看初始密碼
[root@mysql1 ~]# grep password /var/log/mysqld.log | tail -1
#使用初始密碼登錄
[root@mysql1 ~]# mysql -uroot -p'AFKMqF?Kd2ul'mysql> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to repluser@"%" identified by "zzz-123-ZZZ";
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-0001.000002 |      685 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql2

[root@mysql2 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@mysql2 ~]# yum -y install *.rpm[root@mysql2 ~]# vim /etc/my.cnf
[mysqld]
server_id=0002
log_bin=mysql-0002[root@mysql2 ~]# systemctl enable mysqld --now
#查看初始密碼
[root@mysql2 ~]# grep password /var/log/mysqld.log | tail -1
#使用初始密碼登錄
[root@mysql2 ~]# mysql -uroot -p'(se1aYk;r3:g'mysql> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)mysql> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to repluser@"%" identified by "zzz-123-ZZZ";
Query OK, 0 rows affected, 1 warning (0.00 sec)

設(shè)置mysql2同步mysql1

#配置mysql2為mysql1的從服務(wù)器，填寫mysql1查詢master status，中的file和 Position
mysql> change master to master_host="192.168.44.188",master_user="repluser",master_password="zzz-123-ZZZ",master_log_file="mysql-0001.000002",master_log_pos=685;
Query OK, 0 rows affected, 2 warnings (0.18 sec)#配置后查看master信息
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-0002.000002 |      929 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)#查看同步信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.44.188Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-0001.000002Read_Master_Log_Pos: 685Relay_Log_File: mysql2-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: mysql-0001.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 685Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 196aed76-23c3-11ee-970c-000c29919b39Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)mysql> exit

設(shè)置mysql1同步mysql2

#配置mysql1為mysql2的從服務(wù)器，填寫mysql1查詢master status，中的file和 Position
mysql> change master to master_host="192.168.44.190",master_user="repluser",master_password="zzz-123-ZZZ",master_log_file="mysql-0001.000002",master_log_pos=929;
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 eventMaster_Host: 192.168.44.190Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-0002.000002Read_Master_Log_Pos: 929Relay_Log_File: mysql1-relay-bin.000002Relay_Log_Pos: 321Relay_Master_Log_File: mysql-0002.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 929Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 2f686140-23c3-11ee-98f2-000c29fe7242Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)

授權(quán)測試用賬戶

#對測試mysql運行狀態(tài)的用戶授權(quán)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' IDENTIFIED BY 'zzz-123-ZZZ' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

安裝配置keepalived

mysql1

[root@mysql1 ~]# yum -y install keepalived.x86_64
[root@mysql1 ~]# vim /etc/keepalived/keepalived.conf

/etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id mysql1		#集群唯一標(biāo)識vrrp_iptables		#防火墻放行vrrp_skip_check_adv_addrvrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}
vrrp_script check_mysql {script "/usr/local/bin/check_mysql.sh"    # 檢測MySQL服務(wù)的腳本路徑interval 3                   # 檢測頻率（單位：秒）
#    weight -4                    # 檢測失敗時扣除的權(quán)重 通過腳本停止了keepalived服務(wù)，此處不再配置
#    fall 2                       # 連續(xù)檢測失敗次數(shù)
#    rise 2                       # 連續(xù)檢測成功次數(shù)
}vrrp_instance VI_1 {state MASTER			#節(jié)點為masterinterface ens33			#網(wǎng)卡名virtual_router_id 51priority 100			#節(jié)點權(quán)重，越大越重advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.44.100/24	#虛擬ip}track_script {check_mysql                # 監(jiān)測MySQL服務(wù)腳本的名稱}
}

檢查腳本

[root@mysql1 ~]# vim /usr/local/bin/check_mysql.sh

/usr/local/bin/check_mysql.sh

#!/bin/bash# 定義MySQL相關(guān)配置
MYSQL_USER="test_user"
MYSQL_PASS="zzz-123-ZZZ"
MYSQL_HOST="192.168.44.188"
MYSQL_PORT="3306"
MYSQL_VIP="192.168.44.100"# 檢測MySQL狀態(tài)
check_mysql_status() {# 嘗試連接MySQL并執(zhí)行查詢if ! mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p"${MYSQL_PASS}" -e "SELECT 1" >/dev/null; thenecho "無法連接到MySQL！"return 1fi# MySQL狀態(tài)正常return 0
}if check_mysql_status; thenecho "MySQL服務(wù)正常"exit 0
elseecho "MySQL服務(wù)異常"# 停止Keepalived服務(wù)systemctl stop keepalived.service# 釋放VIP（虛擬IP）ip address del "${MYSQL_VIP}"/24 dev ens33exit 1
fi

[root@mysql1 ~]# chmod a+x /usr/local/bin/check_mysql.sh
[root@mysql1 ~]# systemctl enable keepalived.service --now
[root@mysql1 ~]# systemctl status keepalived.service

mysql2

[root@mysql2 ~]# yum -y install keepalived.x86_64
[root@mysql2 ~]# vim /etc/keepalived/keepalived.conf

/etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {notification_email {acassen@firewall.locfailover@firewall.locsysadmin@firewall.loc}notification_email_from Alexandre.Cassen@firewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id mysql2	#集群唯一標(biāo)識vrrp_iptables		#防火墻放行vrrp_skip_check_adv_addrvrrp_strictvrrp_garp_interval 0vrrp_gna_interval 0
}
vrrp_script check_mysql {script "/usr/local/bin/check_mysql.sh"    # 檢測MySQL服務(wù)的腳本路徑interval 3                   # 檢測頻率（單位：秒）
#    weight -4                    # 檢測失敗時扣除的權(quán)重 通過腳本停止了keepalived服務(wù)，此處不再配置
#    fall 2                       # 連續(xù)檢測失敗次數(shù)
#    rise 2                       # 連續(xù)檢測成功次數(shù)
}vrrp_instance VI_1 {state BACKUP			#節(jié)點為BACKUPinterface ens33			#網(wǎng)卡名virtual_router_id 51priority 98			#節(jié)點權(quán)重，比master要小advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.44.100/24	#虛擬ip}track_script {check_mysql                # 監(jiān)測MySQL服務(wù)腳本的名稱}
}

檢查腳本

[root@mysql2 ~]# vim /usr/local/bin/check_mysql.sh

/usr/local/bin/check_mysql.sh

#!/bin/bash# 定義MySQL相關(guān)配置
MYSQL_USER="test_user"
MYSQL_PASS="zzz-123-ZZZ"
MYSQL_HOST="192.168.44.190"
MYSQL_PORT="3306"
MYSQL_VIP="192.168.44.100"# 檢測MySQL狀態(tài)
check_mysql_status() {# 嘗試連接MySQL并執(zhí)行查詢if ! mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p"${MYSQL_PASS}" -e "SELECT 1" >/dev/null; thenecho "無法連接到MySQL！"return 1fi# MySQL狀態(tài)正常return 0
}if check_mysql_status; thenecho "MySQL服務(wù)正常"exit 0
elseecho "MySQL服務(wù)異常"# 停止Keepalived服務(wù)systemctl stop keepalived.service# 釋放VIP（虛擬IP）ip address del "${MYSQL_VIP}"/24 dev ens33exit 1
fi

[root@mysql1 ~]# chmod a+x /usr/local/bin/check_mysql.sh
[root@mysql1 ~]# systemctl enable keepalived.service --now
[root@mysql1 ~]# systemctl status keepalived.service

備份策略

mysqldump全量備份

#!/bin/bash#全量備份TIME=$(date +%Y-%m-%d)
BACKUP_DIR="/mysqldump_back/"mysqldump -u 用戶名 -p --master-data=2 --all-databases --result-file=${BACKUP_DIR}back-${TIME}.sql

mysqldump增量備份

#此腳本尚未親測
#!/bin/bash#先手動全量備份，后執(zhí)行腳本
mysqldump -u 用戶名 -p --master-data=2 --all-databases --result-file=${BACKUP_DIR}last_backup.sqlTIME=$(date +%Y-%m-%d)
BACKUP_DIR="/路徑/"
LAST_BACKUP="${BACKUP_DIR}last_backup.sql"mysqldump -u 用戶名 -p --master-data=2 --databases --result-file=${BACKUP_DIR}back-${TIME}.sql --incremental=snar
rsync ${BACKUP_DIR}back-${TIME}.sql ${LAST_BACKUP}

數(shù)據(jù)庫目錄全量備份

#/bin/bash
rsync -av /var/lib/mysql /mysqlback/var-lib-mysql

刪除mysql1數(shù)據(jù)庫目錄，恢復(fù)數(shù)據(jù)

刪除mysql1的數(shù)據(jù)庫目錄

[root@mysql1 ~]# rm -rf /var/lib/mysql

查看keepalived停止，VIP漂移到mysql2

停止mysql1的數(shù)據(jù)庫

[root@mysql1 ~]# systemctl stop mysqld

全備份mysql2的數(shù)據(jù)

[root@mysql2 ~]# mysqldump -uroot -pzzz-123-ZZZ --all-databases --master-data=2 > /root/20230716allback.sql
[root@mysql2 zzz]# grep mysql-0002 /root/20230716allback.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-0002.000002', MASTER_LOG_POS=1493;

備份數(shù)據(jù)上傳mysql1

[root@mysql2 ~]# scp /root/20230716allback.sql 192.168.44.188:/root

mysql1啟動數(shù)據(jù)庫服務(wù)

[root@mysql1 ~]# systemctl start mysqld
#生成初始密碼，初始密碼登錄，改密碼#數(shù)據(jù)庫沒有生成初始密碼
[root@mysql1 ~]# grep "password" /var/log/mysqld.log | tail -1
2023-07-16T07:28:38.032091Z 882 [Note] Access denied for user 'test_user'@'localhost' (using password: YES)
#停止mysql服務(wù)，刪除數(shù)據(jù)庫目錄，再次啟動數(shù)據(jù)庫
[root@mysql1 ~]# systemctl stop mysqld.service
[root@mysql1 ~]# rm -rf /var/lib/mysql
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# grep "password" /var/log/mysqld.log | tail -1
2023-07-16T07:30:43.170590Z 15 [Note] Access denied for user 'test_user'@'localhost' (using password: YES)
#依舊沒有生成初始密碼#重置root密碼
vim /etc/mysql
#增加免密配置
skip-grant-tables#重啟數(shù)據(jù)庫
systemctl restart mysqld#免密登錄
mysql#修改root密碼
mysql> update  mysql.user set authentication_string=password("123qqq...A") 
where user="root" and host="localhost"; 
#確保修改生效
mysql> flush privileges; 
mysql> exit; 斷開連接#注釋免密登錄
vim /etc/mysql
#增加免密配置
#skip-grant-tables#重啟數(shù)據(jù)庫
systemctl restart mysqld#使用密碼登錄
[root@mysql1 ~]# mysql -uroot -p123qqq...A#重置密碼
mysql> alter user root@localhost identified by 'zzz-123-ZZZ';
Query OK, 0 rows affected (0.00 sec)mysql> exit

#修改好密碼后
#登錄查看為空庫
[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ
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 337
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 |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
mysql> exit

導(dǎo)入備份數(shù)據(jù)

[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ < /root/20230716allback.sql
#查看數(shù)據(jù)
[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ
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 631
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 |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| table1          |
| table2          |
+-----------------+
2 rows in set (0.00 sec)#查看授權(quán)信息
mysql> show grants for repluser;
ERROR 1141 (42000): There is no such grant defined for user 'repluser' on host '%'

查看mysql2授權(quán)信息

[root@mysql2 zzz]# mysql -uroot -pzzz-123-ZZZ -e "show grants for repluser"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------------------+
| Grants for repluser@%                            |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+

恢復(fù)授權(quán)信息

#即授權(quán)信息未被恢復(fù)#恢復(fù)授權(quán)信息
#上傳mysql2數(shù)據(jù)庫目錄的mysql目錄到mysql1
[root@mysql1 ~]# scp -r 192.168.44.190:/var/lib/mysql/mysql /var/lib/mysql/
#查看 上傳來的mysql目錄的屬性信息
[root@mysql1 ~]# ll var/lib/mysql/mysql
[root@mysql1 ~]# chown -R mysql:mysql /var/lib/mysql#mysql1的數(shù)據(jù)庫服務(wù)重新加載配置
[root@mysql1 ~]# ps -ef | grep mysql
mysql     93344      1  0 15:38 ?        00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root     116253   1275  0 15:52 pts/0    00:00:00 grep --color=auto mysql
[root@mysql1 ~]#
[root@mysql1 ~]#
#kill -1 或 kill -SIGHUP
[root@mysql1 ~]# kill -1 93344#再次查看授權(quán)信息，此時MySQL1的root密碼也和mysql2同步
[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ -e "show grants for repluser"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------------------------------------+
| Grants for repluser@%                            |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+

設(shè)置mysql1同步mysql2

設(shè)置mysql1同步mysql2的數(shù)據(jù),使用備份數(shù)據(jù)里的binlog數(shù)據(jù)

[root@mysql2 zzz]# grep mysql-0002 /root/20230716allback.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-0002.000002', MASTER_LOG_POS=1493;

[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ
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 2388
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="192.168.44.190",master_user="repluser",master_password="zzz-123-ZZZ",master_log_file="mysql-0002.000002",master_log_pos=1493;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>
mysql>
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 eventMaster_Host: 192.168.44.187Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-0002.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql1-relay-bin.000003Relay_Log_Pos: 369Relay_Master_Log_File: mysql-0002.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 744Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 88729250-22fc-11ee-af60-000c29fe7242Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)

設(shè)置mysql2同步mysql1的數(shù)據(jù)

#查看mysql1的master信息
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-0001.000004 |   549254 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#配置mysql2同步mysql1的數(shù)據(jù)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host="192.168.44.186",master_user="repluser",master_password="zzz-123-ZZZ",master_log_file="mysql-0001.000004",master_log_pos=549254;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql>
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.44.186Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-0001.000004Read_Master_Log_Pos: 707915Relay_Log_File: mysql2-relay-bin.000002Relay_Log_Pos: 158982Relay_Master_Log_File: mysql-0001.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 707915Relay_Log_Space: 159190Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 0f1f81d7-23b1-11ee-b1e8-000c29919b39Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
1 row in set (0.00 sec)

數(shù)據(jù)恢復(fù)完成,主主同步恢復(fù)

啟動mysql1上的keepalived

[root@mysql1 ~]# systemctl start keepalived.service
#此時vip會回到mysql1 

測試用庫表

期間創(chuàng)建test庫，和表table1，表table2測試數(shù)據(jù)

CREATE DATABASE test CHARACTER SET utf8;CREATE TABLE table1 (id INT AUTO_INCREMENT PRIMARY KEY,home VARCHAR(255),love VARCHAR(255),age INT
);CREATE TABLE table2 (id INT ,home VARCHAR(255),love VARCHAR(255),age INT
);

創(chuàng)建兩個腳本測試插入隨機數(shù)據(jù)

#!/bin/bashvip_my="192.168.44.100"while true
do# 生成一個隨機數(shù)作為 age 字段的值age=$(shuf -i 1-100 -n 1)# 生成一個隨機字符串作為 home 和 love 字段的值，長度為 10home=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 10 | head -n 1)love=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 10 | head -n 1)# 檢查是否已經(jīng)存在相同的 home 和 love 值result=$(mysql -h ${vip_my} -u test_user -pzzz-123-ZZZ -e "SELECT COUNT(*) FROM test.table1 WHERE home='$home' AND love='$love';" -s)# 如果不存在相同的值，則插入新的記錄if [ "$result" -eq 0 ]; thenmysql -h${vip_my} -u test_user -pzzz-123-ZZZ -e "INSERT INTO test.table1 (home, love, age) VALUES ('$home', '$love', $age);"fisleep 1  # 暫停 1 秒
done

#!/bin/bashvip_my="192.168.44.100"while true
do# 生成一個隨機數(shù)作為 id 字段的值,因為table2的id未設(shè)置自增長id=$(shuf -i 1-100000 -n 1)# 生成一個隨機字符串作為 home 和 love 字段的值，長度為 10home=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 10 | head -n 1)love=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w 10 | head -n 1)# 生成一個隨機數(shù)作為 age 字段的值age=$(shuf -i 1-100 -n 1)# 插入數(shù)據(jù)到 table2 表mysql -h${vip_my} -u test_user -pzzz-123-ZZZ -e "INSERT INTO test.table2 (id, home, love, age) VALUES ($id, '$home', '$love', $age);"sleep 1  # 暫停 1 秒
done