环境:
OS:Centos 7
旧mysql版本:5.7.29
新版本mysql:5.7.39
主库:192.168.1.134
从库:192.168.1.135
1.从库机器上安装好新版本的mysql
注意端口和socket不能与现有的实例相同,比如:
port=23306
socket=/data/middle/mysql57/data/mysql.sock
2.停掉从库和新实例数据库
从库:
/home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p shutdown
新实例:
/data/middle/mysql57/bin/mysqladmin -h localhost -uroot -P23306 -p -S /data/middle/mysql57/data/mysql.sock shutdown
3.主库上模拟写入数据
主要是验证下升级后这些数据是否自动同步到从库
4.将从库的数据库目录拷贝到新实例的数据目录
先备份新实例的数据目录
[root@host135 mysql57]#cd /data/middle/mysql57
[root@host135 mysql57]#mv data bakdata
将从库旧实例的data目录拷贝到新实例的目录下(旧实例已经停掉的,可以直接拷贝文件)
[root@host135 mysql57]# cd /home/middle/mysql57
[root@host135 mysql57]# cp -r data /data/middle/mysql57/
修改权限
[root@host135 mysql57]# cd /data/middle
[root@host135 middle]# chown -R mysql:mysql ./mysql57
5.启动新版本实例
/data/middle/mysql57/bin/mysqld_safe --defaults-file=/data/middle/mysql57/conf/my.cnf --user=mysql &
登录查看
/data/middle/mysql57/bin/mysql -h localhost -uroot -P23306 -p -S /data/middle/mysql57/data/mysql.sock
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)
6.运行升级脚本进行升级
[root@host135 middle]# /data/middle/mysql57/bin/mysql_upgrade -s -h localhost -pmysql -P23307 -S /data/middle/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.2).
Upgrade process completed successfully.
Checking if update is needed.
7.启动主从复制
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决办法:
reset slave;
start slave;
8.验证主库新增的数据是否同步过来
mysql> select count(1) from tb_test02;
+----------+
| count(1) |
+----------+
| 3000000 |
+----------+
1 row in set (1.05 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.134
Master_User: repl
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: binlog.000007
Read_Master_Log_Pos: 37803620
Relay_Log_File: relaylog-binlog.000008
Relay_Log_Pos: 37803827
Relay_Master_Log_File: binlog.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,sys
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: 37803620
Relay_Log_Space: 37804118
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: 5dd21189-3fea-11ee-a965-525400c8dc1f
Master_Info_File: /data/middle/mysql57/data/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: 5dd21189-3fea-11ee-a965-525400c8dc1f:208-307
Executed_Gtid_Set: 5dd21189-3fea-11ee-a965-525400c8dc1f:1-307,
8de3c6c5-3ff8-11ee-9ae7-52540051cd25:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
#################这个时候从库已经升级完成,可以进行主从切换,升级原主库.#####################