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

搭建mysql主主架构

环境信息

主机名 IP地址 服务 角色
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服务

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)

设置mysql2同步mysql1

#配置mysql2为mysql1的从服务器,填写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 statusG
*************************** 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: 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: 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和 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 statusG
*************************** 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: 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: 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 keepalived

global_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 0
else
    echo "MySQL服务异常"
    # 停止Keepalived服务
    systemctl stop keepalived.service
    # 释放VIP(虚拟IP),停止keepalived服务后,vip不释放,配置此项,ens33为vip所在网卡名
	ip address del "${MYSQL_VIP}"/24 dev ens33
    exit 1
fi
vim /data/usr/shell/my.cnf
[client]
user=root
password=zzz-123-ZZZ
host=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 keepalived

global_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 0
else
    echo "MySQL服务异常"
    # 停止Keepalived服务
    systemctl stop keepalived.service
    # 释放VIP(虚拟IP),停止keepalived服务后,vip不释放,配置此项,ens33为vip所在网卡名
	ip address del "${MYSQL_VIP}"/24 dev ens33
    exit 1
fi
vim /data/usr/shell/my.cnf
[client]
user=root
password=zzz-123-ZZZ
host=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.sql


TIME=$(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}

数据库目录全量备份

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

删除mysql1数据库目录,恢复数据

删除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 -1
2023-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 -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


#重启数据库
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-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

导入备份数据

[root@mysql1 ~]# mysql -uroot -pzzz-123-ZZZ < /root/20230716allback.sql
#查看数据
[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 -A

Database changed
mysql> 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 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
#再次查看授权信息,此时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-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 statusG
*************************** 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: 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: 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 statusG
*************************** 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: 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: 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/bash

vip_my="192.168.44.100"

while true
do
    # 生成一个随机数作为 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/bash

vip_my="192.168.44.100"

while true
do
    # 生成一个随机数作为 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