MySQL双主架构升级实施测试方案

#systemctl stop keepalived

mysql> set global innodb_fast_shutdown = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.04 sec)

[root@mgrmaster my3306]# tar -xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@mgrmaster my3306]# unlink mysql
[root@mgrmaster my3306]# ln -s mysql-5.7.37-linux-glibc2.12-x86_64 mysql

[root@mgrmaster my3306]# ./mysql/bin/mysqld_safe --defaults-file=/home/my3306/my.cnf &

[root@mgrmaster my3306]# mysql -uroot -p -S /home/my3306/run/mysql.sock
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 4
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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.

[root@mgrmaster my3306]# ./mysql/bin/mysql_upgrade -uroot -p123456
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
huayu.a                                            OK
huayu.t1 OK
huayu.t2 OK
percona.checksums OK
sys.sys_config OK
test.checksums OK
Upgrade process completed successfully.
Checking if update is needed.

[root@mgrmaster my3306]# ./mysql/bin/mysqladmin -uroot -p123456 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2022-06-30T03:07:13.300940Z mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
[root@mgrmaster my3306]# ./mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 194
Relay_Log_File: mgrmaster-relay-bin.000011
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:

Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.XX.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000042
Read_Master_Log_Pos: 194
Relay_Log_File: mgrslave1-relay-bin.000010
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000042
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Seconds_Behind_Master: 0
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

#rm -f /home/my3306/data/ ib_logfile{0,1,2,3}

#unlink mysql
# ln –s /opt/mysql-5.7.29-linux-glibc2.12-x86_64 mysql
  #mysqld_safe --defaults-file=/home/my3306/my.cnf &