mysql主主架构搭建，删库恢复
搭建mysql主主架构
环境信息
主机名IP地址服务角色mysql1192.168.44.188mysql，keepalivedmysql-master，keepalived-mastermysql2192.168.44.190mysql，keepalivedmysql-master，keepalived-backup192.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 inset(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 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: 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 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: 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 {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
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 # 连续检测成功次数
}
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_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;thenecho"无法连接到MySQL！"return1fi# MySQL状态正常return0}if check_mysql_status;thenecho"MySQL服务正常"exit0elseecho"MySQL服务异常"# 停止Keepalived服务
systemctl stop keepalived.service
# 释放VIP（虚拟IP）ip address del "${MYSQL_VIP}"/24 dev ens33
exit1fi
[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 {
[email protected]
[email protected]
[email protected]
}
notification_email_from [email protected]
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 # 连续检测成功次数
}
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服务脚本的名称
}
}
检查脚本
[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_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;thenecho"无法连接到MySQL！"return1fi# MySQL状态正常return0}if check_mysql_status;thenecho"MySQL服务正常"exit0elseecho"MySQL服务异常"# 停止Keepalived服务
systemctl stop keepalived.service
# 释放VIP（虚拟IP）ip address del "${MYSQL_VIP}"/24 dev ens33
exit1fi
[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/bashrsync-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 -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 setauthentication_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 inset(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 inset(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'@'%'|
+--------------------------------------------------+
恢复授权信息
#即授权信息未被恢复#恢复授权信息#上传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 933441015:38 ? 00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root 1162531275015: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 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: 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 inset(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: 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 formore 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 inset(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"whiletruedo# 生成一个随机数作为 age 字段的值age=$(shuf-i1-100 -n1)# 生成一个随机字符串作为 home 和 love 字段的值，长度为 10home=$(cat /dev/urandom |tr-dc'a-zA-Z0-9'|fold-w10|head-n1)love=$(cat /dev/urandom |tr-dc'a-zA-Z0-9'|fold-w10|head-n1)# 检查是否已经存在相同的 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"-eq0];then
mysql -h${vip_my}-u test_user -pzzz-123-ZZZ -e"INSERT INTO test.table1 (home, love, age) VALUES ('$home', '$love', $age);"fisleep1# 暂停 1 秒done
#!/bin/bashvip_my="192.168.44.100"whiletruedo# 生成一个随机数作为 id 字段的值,因为table2的id未设置自增长id=$(shuf-i1-100000 -n1)# 生成一个随机字符串作为 home 和 love 字段的值，长度为 10home=$(cat /dev/urandom |tr-dc'a-zA-Z0-9'|fold-w10|head-n1)love=$(cat /dev/urandom |tr-dc'a-zA-Z0-9'|fold-w10|head-n1)# 生成一个随机数作为 age 字段的值age=$(shuf-i1-100 -n1)# 插入数据到 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);"sleep1# 暂停 1 秒done
本文转载自: https://blog.csdn.net/qq_44659804/article/details/131756033
版权归原作者 qq_44659804 所有， 如有侵权，请联系我们删除。
版权归原作者 qq_44659804 所有， 如有侵权，请联系我们删除。