mysql主主架构搭建，删库恢复

环境信息

安装msql服务

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=0001log_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=0002log_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)

设置mysql2同步mysql1

#配置mysql2为mysql1的从服务器，填写mysql1查询master status，中的file和 Positionmysql> 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 event                  Master_Host: 192.168.44.188                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-0001.000002          Read_Master_Log_Pos: 685               Relay_Log_File: mysql2-relay-bin.000002                Relay_Log_Pos: 321        Relay_Master_Log_File: mysql-0001.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: 685              Relay_Log_Space: 529              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: 0Master_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: 196aed76-23c3-11ee-970c-000c29919b39             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)mysql> exit

设置mysql1同步mysql2

#配置mysql1为mysql2的从服务器，填写mysql1查询master status，中的file和 Positionmysql> 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 event                  Master_Host: 192.168.44.190                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-0002.000002          Read_Master_Log_Pos: 929               Relay_Log_File: mysql1-relay-bin.000002                Relay_Log_Pos: 321        Relay_Master_Log_File: mysql-0002.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: 929              Relay_Log_Space: 529              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: 0Master_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: 2f686140-23c3-11ee-98f2-000c29fe7242             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)

授权测试用账户

#对测试mysql运行状态的用户授权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.loc     failover@firewall.loc     sysadmin@firewall.loc   }   notification_email_from Alexandre.Cassen@firewall.loc   smtp_server 192.168.200.1   smtp_connect_timeout 30   router_id mysql1#集群唯一标识   vrrp_iptables#防火墙放行   vrrp_skip_check_adv_addr   vrrp_strict   vrrp_garp_interval 0   vrrp_gna_interval 0}vrrp_script check_mysql {    script "/usr/local/bin/check_mysql.sh"    # 检测MySQL服务的脚本路径    interval 3                   # 检测频率（单位：秒）#    weight -4                    # 检测失败时扣除的权重 通过脚本停止了keepalived服务，此处不再配置#    fall 2                       # 连续检测失败次数#    rise 2                       # 连续检测成功次数#    user keepalived_scripts #运行脚本的用户，若配置此项，此用户需在系统中创建，不配置时root身份运行}vrrp_instance VI_1 {    state MASTER#节点为master    interface ens33#网卡名    virtual_router_id 51    priority 100#节点权重，越大越重    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.44.100/24#虚拟ip    }    track_script {        check_mysql                # 监测MySQL服务脚本的名称    }}

检查脚本

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

/usr/local/bin/check_mysql.sh

#!/bin/bash# 定义MySQL相关配置MYSQL_USER="test_user"MYSQL_PASS="zzz-123-ZZZ"#也可使用读用户登录mysql#MYSQL_AUTH="/data/usr/shell/my.cnf"MYSQL_HOST="192.168.44.188"MYSQL_PORT="3306"MYSQL_VIP="192.168.44.100"# 检测MySQL状态check_mysql_status() {    # 尝试连接MySQL并执行查询    if ! mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p"${MYSQL_PASS}" -e "SELECT 1" >/dev/null; then#    if ! mysql "$MYSQL_AUTH" -e "SELECT 1" >/dev/null; then        echo "无法连接到MySQL！"        return 1    fi    # MySQL状态正常    return 0}if check_mysql_status; then   echo "MySQL服务正常"   exit 0else    echo "MySQL服务异常"    # 停止Keepalived服务    systemctl stop keepalived.service    # 释放VIP（虚拟IP），停止keepalived服务后，vip不释放，配置此项，ens33为vip所在网卡名ip address del "${MYSQL_VIP}"/24 dev ens33    exit 1fi

vim /data/usr/shell/my.cnf[client]user=rootpassword=zzz-123-ZZZhost=localhost

[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.loc     failover@firewall.loc     sysadmin@firewall.loc   }   notification_email_from Alexandre.Cassen@firewall.loc   smtp_server 192.168.200.1   smtp_connect_timeout 30   router_id mysql2#集群唯一标识   vrrp_iptables#防火墙放行   vrrp_skip_check_adv_addr   vrrp_strict   vrrp_garp_interval 0   vrrp_gna_interval 0}vrrp_script check_mysql {    script "/usr/local/bin/check_mysql.sh"    # 检测MySQL服务的脚本路径    interval 3                   # 检测频率（单位：秒）#    weight -4                    # 检测失败时扣除的权重 通过脚本停止了keepalived服务，此处不再配置#    fall 2                       # 连续检测失败次数#    rise 2                       # 连续检测成功次数#    user keepalived_scripts #运行脚本的用户，若配置此项，此用户需在系统中创建，不配置时root身份运行}vrrp_instance VI_1 {    state BACKUP#节点为BACKUP    interface ens33#网卡名    virtual_router_id 51    priority 98#节点权重，比master要小    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.44.100/24#虚拟ip    }    track_script {        check_mysql                # 监测MySQL服务脚本的名称    }}

keepalived闹裂问题解决

keepalived 备节点抢占vip，主备同时拥有vip
所有keepalived节点增加配置

vrrp_instance VI_1 {    state MASTER    interface ens33    virtual_router_id 51    # 设置unicast通信的本地IP地址    unicast_src_ip          192.168.44.188            # 设置unicast通信的对端IP地址    unicast_peer {        192.168.44.190    }    priority 100    advert_int 1    .....}

vrrp_instance VI_1 {    state BACKUP    interface ens33    virtual_router_id 51    # 设置unicast通信的本地IP地址    unicast_src_ip          192.168.44.190            # 设置unicast通信的对端IP地址    unicast_peer {        192.168.44.188    }    priority 100    advert_int 1    .....

重启keepalived服务，解决脑裂

检查脚本

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

/usr/local/bin/check_mysql.sh

#!/bin/bash# 定义MySQL相关配置MYSQL_USER="test_user"MYSQL_PASS="zzz-123-ZZZ"#也可使用读用户登录mysql#MYSQL_AUTH="/data/usr/shell/my.cnf"MYSQL_HOST="192.168.44.190"MYSQL_PORT="3306"MYSQL_VIP="192.168.44.100"# 检测MySQL状态check_mysql_status() {    # 尝试连接MySQL并执行查询    if ! mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p"${MYSQL_PASS}" -e "SELECT 1" >/dev/null; then#    if ! mysql "$MYSQL_AUTH" -e "SELECT 1" >/dev/null; then        echo "无法连接到MySQL！"        return 1    fi    # MySQL状态正常    return 0}if check_mysql_status; then   echo "MySQL服务正常"   exit 0else    echo "MySQL服务异常"    # 停止Keepalived服务    systemctl stop keepalived.service    # 释放VIP（虚拟IP），停止keepalived服务后，vip不释放，配置此项，ens33为vip所在网卡名ip address del "${MYSQL_VIP}"/24 dev ens33    exit 1fi

vim /data/usr/shell/my.cnf[client]user=rootpassword=zzz-123-ZZZhost=localhost

[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#先手动全量备份，后执行脚本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=snarrsync ${BACKUP_DIR}back-${TIME}.sql ${LAST_BACKUP}

数据库目录全量备份

#/bin/bashrsync -av /var/lib/mysql /mysqlback/var-lib-mysql

删除mysql1的数据库目录

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

查看keepalived停止，VIP漂移到mysql2

停止mysql1的数据库

[root@mysql1 ~]# systemctl stop mysqld

全备份mysql2的数据

[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;

备份数据上传mysql1

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

mysql1启动数据库服务

[root@mysql1 ~]# systemctl start mysqld#生成初始密码，初始密码登录，改密码#数据库没有生成初始密码[root@mysql1 ~]# grep "password" /var/log/mysqld.log | tail -12023-07-16T07:28:38.032091Z 882 [Note] Access denied for user 'test_user'@'localhost' (using password: YES)#停止mysql服务，删除数据库目录，再次启动数据库[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 -12023-07-16T07:30:43.170590Z 15 [Note] Access denied for user 'test_user'@'localhost' (using password: YES)#依旧没有生成初始密码#重置root密码vim /etc/mysql#增加免密配置skip-grant-tables#重启数据库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#重启数据库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-ZZZmysql: [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 337Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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

导入备份数据

[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ < /root/20230716allback.sql#查看数据[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZmysql: [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 631Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-----------------+| Tables_in_test1 |+-----------------+| table1          || table2          |+-----------------+2 rows in set (0.00 sec)#查看授权信息mysql> show grants for repluser;ERROR 1141 (42000): There is no such grant defined for user 'repluser' on host '%'

查看mysql2授权信息

[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'@'%' |+--------------------------------------------------+

恢复授权信息

#即授权信息未被恢复#恢复授权信息#全量备份，刷新授权就可以看到同步过来的授权信息FLUSH PRIVILEGES;#可以通过查询mysql.user表的，查看用户信息#刷新授权后，可以通过show grants for 'username';查看到

#下述复制mysql目录的方法暂不使用#上传mysql2数据库目录的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的数据库服务重新加载配置[root@mysql1 ~]# ps -ef | grep mysqlmysql     93344      1  0 15:38 ?        00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidroot     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

#再次查看授权信息，此时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'@'%' |+--------------------------------------------------+

设置mysql1同步mysql2

设置mysql1同步mysql2的数据,使用备份数据里的binlog数据

[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-ZZZmysql: [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 2388Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 event                  Master_Host: 192.168.44.187                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-0002.000003          Read_Master_Log_Pos: 154               Relay_Log_File: mysql1-relay-bin.000003                Relay_Log_Pos: 369        Relay_Master_Log_File: mysql-0002.000003             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: 744              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: 0Master_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: 88729250-22fc-11ee-af60-000c29fe7242             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)

设置mysql2同步mysql1的数据

#查看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的数据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 event                  Master_Host: 192.168.44.186                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-0001.000004          Read_Master_Log_Pos: 707915               Relay_Log_File: mysql2-relay-bin.000002                Relay_Log_Pos: 158982        Relay_Master_Log_File: mysql-0001.000004             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: 707915              Relay_Log_Space: 159190              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: 0Master_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: 0f1f81d7-23b1-11ee-b1e8-000c29919b39             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)

数据恢复完成,主主同步恢复

启动mysql1上的keepalived

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

期间创建test库，和表table1，表table2测试数据

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);

创建两个脚本测试插入随机数据

#!/bin/bashvip_my="192.168.44.100"while truedo    # 生成一个随机数作为 age 字段的值    age=$(shuf -i 1-100 -n 1)    # 生成一个随机字符串作为 home 和 love 字段的值，长度为 10    home=$(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)    # 检查是否已经存在相同的 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 ]; then        mysql -h${vip_my} -u test_user -pzzz-123-ZZZ -e "INSERT INTO test.table1 (home, love, age) VALUES ('$home', '$love', $age);"    fi    sleep 1  # 暂停 1 秒done

#!/bin/bashvip_my="192.168.44.100"while truedo    # 生成一个随机数作为 id 字段的值,因为table2的id未设置自增长    id=$(shuf -i 1-100000 -n 1)    # 生成一个随机字符串作为 home 和 love 字段的值，长度为 10    home=$(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)    # 生成一个随机数作为 age 字段的值    age=$(shuf -i 1-100 -n 1)    # 插入数据到 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

来源地址：https://blog.csdn.net/qq_44659804/article/details/131756033