主从环境升级(mysql57-->mysql8)

环境:
1主1从环境
原数据库版本:5.7.29
新数据库版本:8.0.28

 

说明:

主               从                        是否支持

mysql5.7 -->mysql 8.0           支持

mysql8.0-->mysql57              不支持

 

##################先升级从库###########################
1.参考如下方法先安装好mysql8.0并正常启动
https://www.cnblogs.com/hxlasky/p/15960873.html

 

2.停掉新旧的数据库
旧数据库:
停掉主从同步:
mysql> stop slave;
停掉旧数据库:
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/mysql.sock shutdown

新数据库:
/opt/mysql8/bin/mysqladmin -h localhost -uroot -P23306 -p -S /opt/mysql8/mysql.sock shutdown

 

3.备份新版本的data目录
[root@localhost mysql8]# cd /opt/mysql8
[root@localhost mysql8]# mv data bakdata
[root@localhost mysql8]# mkdir data

 

4.将旧版本目录下的data目录拷贝到新版本(新版本已经停掉)的相应目录下
[root@localhost mysql8]# cd /opt/mysql57 ##进入到旧数据库的安装目录
[root@localhost mysql57]# cp -r data /opt/mysql8/ ##将data文件夹拷贝到新的安装目录

 

5.修改目录目录权限
[root@localhost binlog]# cd /opt
[root@localhost opt]# chown -R mysql:mysql ./mysql8

 

6.启动新版本数据库
[root@localhost opt]# /opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &

我这里的参数文件配置了跳过从库同步自启动的,所以从库启动数据库后需要手工启动主从同步
skip_replica_start

这里8.0.28版本的启动了会自动升级,不需要手动升级的.

升级后查看版本信息:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec

 

7.启动主从复制
/opt/mysql8/bin/mysql -h localhost -uroot -p -P23306 --socket=/opt/mysql8/mysql.sock

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解决办法:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.104
                  Master_User: repl
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: binlog.000008
          Read_Master_Log_Pos: 99968971
               Relay_Log_File: relaylog-binlog.000017
                Relay_Log_Pos: 99969178
        Relay_Master_Log_File: binlog.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 13124
                   Last_Error: Slave failed to initialize relay log info structure from the repository
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 99968971
              Relay_Log_Space: 0
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 13124
               Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 9ecf61e2-eed1-11ed-a4b0-080027e81195
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 230510 22:57:28
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 636915ca-ef98-11ed-a854-080027962264:1-37,
949fe304-efa3-11ed-8814-080027962264:1,
9ecf61e2-eed1-11ed-a4b0-080027e81195:32-325
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

 

mysql>reset slave;

修改同步信息
mysql>change master to master_host='192.168.1.104',
master_port=13306,
master_user='repl',
master_password='mysql',
master_log_file='binlog.000008',
master_log_pos=99968971;

mysql>start slave;

到这里从库升级完成

 

说明：

若主从同步是使用master_auto_position=1的方式的，那么直接启动复制即可

 

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql>reset slave;
mysql>start slave;