mysql主从升级(直接先升级主库,旧binlog拷贝到新版本目录,需要停业务)

环境:
OS:Centos 7
旧版本:mysql 5.6
新版本:mysql 5.7

1.停掉旧版本数据库
确保主从当前没有延迟再停主库,不过有延迟也可以停掉

查看从库的状态

复制代码
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: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000024
          Read_Master_Log_Pos: 27280191
               Relay_Log_File: host135-relay-bin.000002
                Relay_Log_Pos: 2595024
        Relay_Master_Log_File: binlog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 2594860
              Relay_Log_Space: 27280561
              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: 309
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: 23
                  Master_UUID: f58f88cb-f478-11ed-b257-525400c8dc1f
             Master_Info_File: /opt/mysql56/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:550005-630004 ##接收到的gtid与主库的一致,说明日志都传到从库了
            Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-557613      ##从库的sql进程在应用日志
                Auto_Position: 1
1 row in set (0.00 sec)

ERROR: 
No query specified
复制代码

 

主库状态:

复制代码
mysql> show master status \G;
*************************** 1. row ***************************
             File: binlog.000024
         Position: 27280191
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-630004
1 row in set (0.00 sec)

ERROR: 
No query specified
复制代码

 

停掉主库
/opt/mysql56/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql56/data/mysql.sock shutdown

 

2.停掉从库的接收日志进程
mysql> stop slave io_thread;

 

3.停掉主库的新版数据库实例
新实例:
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown

 

4.将从库的数据库目录拷贝到新实例的数据目录
先备份新实例的数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata

将从库旧实例的data目录拷贝到新实例的目录下(旧实例已经停掉的,可以直接拷贝文件)


[root@host135 mysql57]# cd /opt/mysql56 ##旧版本数据库目录
[root@host135 mysql57]# cp -r data /opt/mysql57/

 

5.修改权限
[root@host135 mysql57]# cd /opt/mysql57/
[root@host135 middle]#chown -R mysql:mysql /opt/mysql57/


6.启动新实例,端口与旧实例的一致,这样应用程序已经从库之前的同步就不需要修改端口了
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
当前的uuid
[root@host134 data]# more auto.cnf
[auto]
server-uuid=f58f88cb-f478-11ed-b257-525400c8dc1f

登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -P3306 -p -S /opt/mysql57/data/mysql.sock
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)

 

7.升级

复制代码
[root@host134 binlog]# /opt/mysql57/bin/mysql_upgrade -s -h localhost -pmysql -P3306 -S /opt/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.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.

升级后的uuid
[root@host134 data]# more auto.cnf 
[auto]
server-uuid=f58f88cb-f478-11ed-b257-525400c8dc1f
复制代码

 

8.拷贝旧版本的binlog到新版本的目录下
删除新库的binlog
先停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P3306 -p -S /opt/mysql57/data/mysql.sock shutdown

删除binlog
[root@host134 binlog]# pwd
/opt/mysql57/mysqllog/binlog
[root@host134 binlog]# ls
bak_binlog.000001 binlog.000019 binlog.000021 binlog.000023 binlog.index
bak_binlog.index binlog.000020 binlog.000022 binlog.000024
[root@host134 binlog]# rm -rf *

将原来5.6版本的binlog拷贝到新版本数据库的binlog目录
[root@host134 binlog]# pwd
/opt/mysql56/mysqllog/binlog
[root@host134 binlog]# cp * /opt/mysql57/mysqllog/binlog/

修改权限
[root@host134 binlog]# chown -R mysql:mysql /opt/mysql57/mysqllog/binlog/
拷贝过去后需要修改binlog.index里面内容的文件
[root@host134 binlog]# more binlog.index
/opt/mysql56/mysqllog/binlog/binlog.000019
/opt/mysql56/mysqllog/binlog/binlog.000020
/opt/mysql56/mysqllog/binlog/binlog.000021
/opt/mysql56/mysqllog/binlog/binlog.000022
/opt/mysql56/mysqllog/binlog/binlog.000023

修改为:
[root@host134 binlog]# more binlog.index
/opt/mysql57/mysqllog/binlog/binlog.000019
/opt/mysql57/mysqllog/binlog/binlog.000020
/opt/mysql57/mysqllog/binlog/binlog.000021
/opt/mysql57/mysqllog/binlog/binlog.000022
/opt/mysql57/mysqllog/binlog/binlog.000023

 

9.启动数据库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &

 

10.启动从库的io进程
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

 

11.查看从库复制情况

复制代码
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: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000025
          Read_Master_Log_Pos: 194
               Relay_Log_File: host135-relay-bin.000002
                Relay_Log_Pos: 24219198
        Relay_Master_Log_File: binlog.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 24219034
              Relay_Log_Space: 27281849
              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: 2923
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: 134
                  Master_UUID: f58f88cb-f478-11ed-b257-525400c8dc1f
             Master_Info_File: /opt/mysql56/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:550005-630004
            Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-621027
                Auto_Position: 1
1 row in set (0.00 sec)

ERROR: 
No query specified
复制代码

 

posted @   slnngk  阅读(62)  评论(0)  编辑  收藏  举报
相关博文：
· 主从环境下升级(先升级从库,不做切换,需要停业务,auto_position=1)
· 主从环境下升级(先升级从库)
· centos7 mysql5.6 主从复制
· mysql循序渐进
· mysql主从复制
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
历史上的今天：
2023-04-17 dataguard重新恢复某个pdb
2020-04-17 带wallet的pdb 拔出和接入
2019-04-17 php升级
2019-04-17 zentao安装升级
2019-04-17 mysqldiff差异比较
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛