主从环境下升级(先升级从库)

环境:
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

或是修改配置文件my.cnf,将实例的数据目录指向旧实例的数据目录
datadir=/home/middle/mysql57/data
这种方式的话风险就是若升级不成功,原有的实例可能无法启动使用

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

解决办法1:
reset slave;
start slave;

解决办法2:

a.拷贝旧实例的binlog和relaylog到新实例对应的目录下

b.修改binlog.index和relaylog.index里面的文件路径

b.修改relay-log.info文件里面的路径

a.停掉新实例的mysql
/data/middle/mysql57/bin/mysqladmin -h localhost -uroot -P23306 -p -S /data/middle/mysql57/data/mysql.sock shutdown

b.修改relay-log.info文件
vi /home/middle/mysql57/data/relay-log.info


[root@host134 data]# more /home/middle/mysql57/data/relay-log.info
7
/home/middle/mysql57/mysqllog/relaylog/relaylog-binlog.000011
60485879
binlog.000006
60485672
0
0
1

修改relaylog路径,修改为
/data/middle/mysql57/mysqllog/relaylog/relaylog-binlog.000011


c.拷贝relaylog到新目录(覆盖现有的)
cp /home/middle/mysql57/mysqllog/relaylog/*  /data/middle/mysql57/mysqllog/relaylog/


修改slave-relay-bin.index,指向正确的路径
[root@host134 relaylog]# more slave-relay-bin.index 
/home/middle/mysql57/mysqllog/relaylog/relaylog-binlog.000010
/home/middle/mysql57/mysqllog/relaylog/relaylog-binlog.000011


d.将原实例的binlog目录下的所有文件拷贝到新实例的目录下
[root@host134 binlog]# pwd
/home/middle/mysql57/mysqllog/binlog
[root@host134 binlog]# cp * /data/middle/mysql57/mysqllog/binlog/
修改路径

[root@host134 binlog]# vi binlog.index 
/data/middle/mysql57/mysqllog/binlog/binlog.000001
/data/middle/mysql57/mysqllog/binlog/binlog.000002
/data/middle/mysql57/mysqllog/binlog/binlog.000003
/data/middle/mysql57/mysqllog/binlog/binlog.000004
/data/middle/mysql57/mysqllog/binlog/binlog.000005
/data/middle/mysql57/mysqllog/binlog/binlog.000006
/data/middle/mysql57/mysqllog/binlog/binlog.000007
/data/middle/mysql57/mysqllog/binlog/binlog.000008
/data/middle/mysql57/mysqllog/binlog/binlog.000009
/data/middle/mysql57/mysqllog/binlog/binlog.000010
/data/middle/mysql57/mysqllog/binlog/binlog.000011
/data/middle/mysql57/mysqllog/binlog/binlog.000012
/data/middle/mysql57/mysqllog/binlog/binlog.000013
/data/middle/mysql57/mysqllog/binlog/binlog.000014

修改权限
[root@host134 mysqllog]# chown -R mysql:mysql ./binlog/

启动
/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> 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

#################这个时候从库已经升级完成,可以进行主从切换,升级原主库.#####################
1.当前的从库升级为新主库
mysql> stop slave;
mysql> reset master;
mysql> reset slave all;

2.新主库模拟写入数据

3.原主库作为新主库的从库
change master to master_host='192.168.1.135',
       master_user='repl',
       master_password='mysql',
       master_port=23306,
       master_auto_position=1;

start slave;

发现从库也会同步主库写入的新数据

4.那么现在的拓扑结构就是
主               从
mysql 5.7.39 --> mysql 5.7.29

5.6、5.7小版本之间,高版本可以同步到低版本
但是8.0好像不能同步到5.7,5.6