网站建设与设计的心得体会,哈尔滨网站建设网站,哈尔滨网站建设优化公司,黄骅港贴吧百度贴吧mysql主主架构搭建#xff0c;删库恢复 搭建mysql主主架构环境信息安装msql服务mysql1mysql2设置mysql2同步mysql1设置mysql1同步mysql2授权测试用账户 安装配置keepalivedmysql1检查脚本mysql2检查脚本 备份策略mysqldump全量备份mysqldump增量备份数据库目录全量备份 删除my… mysql主主架构搭建删库恢复 搭建mysql主主架构环境信息安装msql服务mysql1mysql2设置mysql2同步mysql1设置mysql1同步mysql2授权测试用账户 安装配置keepalivedmysql1检查脚本mysql2检查脚本 备份策略mysqldump全量备份mysqldump增量备份数据库目录全量备份 删除mysql1数据库目录恢复数据删除mysql1的数据库目录停止mysql1的数据库全备份mysql2的数据备份数据上传mysql1mysql1启动数据库服务导入备份数据恢复授权信息设置mysql1同步mysql2设置mysql2同步mysql1的数据启动mysql1上的keepalived 测试用库表 搭建mysql主主架构
环境信息
主机名IP地址服务角色mysql1192.168.44.188mysqlkeepalivedmysql-masterkeepalived-mastermysql2192.168.44.190mysqlkeepalivedmysql-masterkeepalived-backup192.168.44.100vip
安装msql服务
mysql1
[rootmysql1 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[rootmysql1 ~]# yum -y install *.rpm[rootmysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id0001
log_binmysql-0001[rootmysql1 ~]# systemctl enable mysqld --now
#查看初始密码
[rootmysql1 ~]# grep password /var/log/mysqld.log | tail -1
#使用初始密码登录
[rootmysql1 ~]# mysql -uroot -pAFKMqF?Kd2ulmysql alter user rootlocalhost 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
[rootmysql2 ~]# tar xf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[rootmysql2 ~]# yum -y install *.rpm[rootmysql2 ~]# vim /etc/my.cnf
[mysqld]
server_id0002
log_binmysql-0002[rootmysql2 ~]# systemctl enable mysqld --now
#查看初始密码
[rootmysql2 ~]# grep password /var/log/mysqld.log | tail -1
#使用初始密码登录
[rootmysql2 ~]# mysql -uroot -p(se1aYk;r3:gmysql alter user rootlocalhost identified by zzz-123-ZZZ;
Query OK, 0 rows affected (0.00 sec)mysql alter user rootlocalhost 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_host192.168.44.188,master_userrepluser,master_passwordzzz-123-ZZZ,master_log_filemysql-0001.000002,master_log_pos685;
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设置mysql1同步mysql2
#配置mysql1为mysql2的从服务器填写mysql1查询master status中的file和 Position
mysql change master to master_host192.168.44.190,master_userrepluser,master_passwordzzz-123-ZZZ,master_log_filemysql-0001.000002,master_log_pos929;
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)授权测试用账户
#对测试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
[rootmysql1 ~]# yum -y install keepalived.x86_64
[rootmysql1 ~]# vim /etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id mysql1 #集群唯一标识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服务的脚本路径interval 3 # 检测频率单位秒
# weight -4 # 检测失败时扣除的权重 通过脚本停止了keepalived服务此处不再配置
# fall 2 # 连续检测失败次数
# rise 2 # 连续检测成功次数
}vrrp_instance VI_1 {state MASTER #节点为masterinterface ens33 #网卡名virtual_router_id 51priority 100 #节点权重越大越重advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.44.100/24 #虚拟ip}track_script {check_mysql # 监测MySQL服务脚本的名称}
}检查脚本
[rootmysql1 ~]# vim /usr/local/bin/check_mysql.sh/usr/local/bin/check_mysql.sh
#!/bin/bash# 定义MySQL相关配置
MYSQL_USERtest_user
MYSQL_PASSzzz-123-ZZZ
MYSQL_HOST192.168.44.188
MYSQL_PORT3306
MYSQL_VIP192.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 无法连接到MySQLreturn 1fi# MySQL状态正常return 0
}if check_mysql_status; thenecho MySQL服务正常exit 0
elseecho MySQL服务异常# 停止Keepalived服务systemctl stop keepalived.service# 释放VIP虚拟IPip address del ${MYSQL_VIP}/24 dev ens33exit 1
fi[rootmysql1 ~]# chmod ax /usr/local/bin/check_mysql.sh
[rootmysql1 ~]# systemctl enable keepalived.service --now
[rootmysql1 ~]# systemctl status keepalived.servicemysql2
[rootmysql2 ~]# yum -y install keepalived.x86_64
[rootmysql2 ~]# vim /etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf
! Configuration File for keepalivedglobal_defs {notification_email {acassenfirewall.locfailoverfirewall.locsysadminfirewall.loc}notification_email_from Alexandre.Cassenfirewall.locsmtp_server 192.168.200.1smtp_connect_timeout 30router_id mysql2 #集群唯一标识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服务的脚本路径interval 3 # 检测频率单位秒
# weight -4 # 检测失败时扣除的权重 通过脚本停止了keepalived服务此处不再配置
# fall 2 # 连续检测失败次数
# rise 2 # 连续检测成功次数
}vrrp_instance VI_1 {state BACKUP #节点为BACKUPinterface ens33 #网卡名virtual_router_id 51priority 98 #节点权重比master要小advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.44.100/24 #虚拟ip}track_script {check_mysql # 监测MySQL服务脚本的名称}
}检查脚本
[rootmysql2 ~]# vim /usr/local/bin/check_mysql.sh/usr/local/bin/check_mysql.sh
#!/bin/bash# 定义MySQL相关配置
MYSQL_USERtest_user
MYSQL_PASSzzz-123-ZZZ
MYSQL_HOST192.168.44.190
MYSQL_PORT3306
MYSQL_VIP192.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 无法连接到MySQLreturn 1fi# MySQL状态正常return 0
}if check_mysql_status; thenecho MySQL服务正常exit 0
elseecho MySQL服务异常# 停止Keepalived服务systemctl stop keepalived.service# 释放VIP虚拟IPip address del ${MYSQL_VIP}/24 dev ens33exit 1
fi[rootmysql1 ~]# chmod ax /usr/local/bin/check_mysql.sh
[rootmysql1 ~]# systemctl enable keepalived.service --now
[rootmysql1 ~]# systemctl status keepalived.service备份策略
mysqldump全量备份
#!/bin/bash#全量备份TIME$(date %Y-%m-%d)
BACKUP_DIR/mysqldump_back/mysqldump -u 用户名 -p --master-data2 --all-databases --result-file${BACKUP_DIR}back-${TIME}.sqlmysqldump增量备份
#此脚本尚未亲测
#!/bin/bash#先手动全量备份后执行脚本
mysqldump -u 用户名 -p --master-data2 --all-databases --result-file${BACKUP_DIR}last_backup.sqlTIME$(date %Y-%m-%d)
BACKUP_DIR/路径/
LAST_BACKUP${BACKUP_DIR}last_backup.sqlmysqldump -u 用户名 -p --master-data2 --databases --result-file${BACKUP_DIR}back-${TIME}.sql --incrementalsnar
rsync ${BACKUP_DIR}back-${TIME}.sql ${LAST_BACKUP}
数据库目录全量备份
#/bin/bash
rsync -av /var/lib/mysql /mysqlback/var-lib-mysql删除mysql1数据库目录恢复数据
删除mysql1的数据库目录
[rootmysql1 ~]# rm -rf /var/lib/mysql查看keepalived停止VIP漂移到mysql2 停止mysql1的数据库
[rootmysql1 ~]# systemctl stop mysqld全备份mysql2的数据
[rootmysql2 ~]# mysqldump -uroot -pzzz-123-ZZZ --all-databases --master-data2 /root/20230716allback.sql
[rootmysql2 zzz]# grep mysql-0002 /root/20230716allback.sql
-- CHANGE MASTER TO MASTER_LOG_FILEmysql-0002.000002, MASTER_LOG_POS1493;备份数据上传mysql1
[rootmysql2 ~]# scp /root/20230716allback.sql 192.168.44.188:/rootmysql1启动数据库服务
[rootmysql1 ~]# systemctl start mysqld
#生成初始密码初始密码登录改密码#数据库没有生成初始密码
[rootmysql1 ~]# grep password /var/log/mysqld.log | tail -1
2023-07-16T07:28:38.032091Z 882 [Note] Access denied for user test_userlocalhost (using password: YES)
#停止mysql服务删除数据库目录再次启动数据库
[rootmysql1 ~]# systemctl stop mysqld.service
[rootmysql1 ~]# rm -rf /var/lib/mysql
[rootmysql1 ~]# systemctl start mysqld
[rootmysql1 ~]# grep password /var/log/mysqld.log | tail -1
2023-07-16T07:30:43.170590Z 15 [Note] Access denied for user test_userlocalhost (using password: YES)
#依旧没有生成初始密码#重置root密码
vim /etc/mysql
#增加免密配置
skip-grant-tables#重启数据库
systemctl restart mysqld#免密登录
mysql#修改root密码
mysql update mysql.user set authentication_stringpassword(123qqq...A)
where userroot and hostlocalhost;
#确保修改生效
mysql flush privileges;
mysql exit; 断开连接#注释免密登录
vim /etc/mysql
#增加免密配置
#skip-grant-tables#重启数据库
systemctl restart mysqld#使用密码登录
[rootmysql1 ~]# mysql -uroot -p123qqq...A#重置密码
mysql alter user rootlocalhost identified by zzz-123-ZZZ;
Query OK, 0 rows affected (0.00 sec)mysql exit#修改好密码后
#登录查看为空库
[rootmysql1 ~]# 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导入备份数据
[rootmysql1 ~]# mysql -uroot -pzzz-123-ZZZ /root/20230716allback.sql
#查看数据
[rootmysql1 ~]# 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)#查看授权信息
mysql show grants for repluser;
ERROR 1141 (42000): There is no such grant defined for user repluser on host %
查看mysql2授权信息
[rootmysql2 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
[rootmysql1 ~]# scp -r 192.168.44.190:/var/lib/mysql/mysql /var/lib/mysql/
#查看 上传来的mysql目录的属性信息
[rootmysql1 ~]# ll var/lib/mysql/mysql
[rootmysql1 ~]# chown -R mysql:mysql /var/lib/mysql#mysql1的数据库服务重新加载配置
[rootmysql1 ~]# 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 --colorauto mysql
[rootmysql1 ~]#
[rootmysql1 ~]#
#kill -1 或 kill -SIGHUP
[rootmysql1 ~]# kill -1 93344#再次查看授权信息此时MySQL1的root密码也和mysql2同步
[rootmysql1 ~]# 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数据
[rootmysql2 zzz]# grep mysql-0002 /root/20230716allback.sql
-- CHANGE MASTER TO MASTER_LOG_FILEmysql-0002.000002, MASTER_LOG_POS1493;[rootmysql1 ~]# 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_host192.168.44.190,master_userrepluser,master_passwordzzz-123-ZZZ,master_log_filemysql-0002.000002,master_log_pos1493;
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)
设置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_host192.168.44.186,master_userrepluser,master_passwordzzz-123-ZZZ,master_log_filemysql-0001.000004,master_log_pos549254;
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)数据恢复完成,主主同步恢复
启动mysql1上的keepalived
[rootmysql1 ~]# 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_my192.168.44.100while true
do# 生成一个随机数作为 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)# 检查是否已经存在相同的 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_my192.168.44.100while true
do# 生成一个随机数作为 id 字段的值,因为table2的id未设置自增长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)# 生成一个随机数作为 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
