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

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

时间：2024-04-17 15:12:28浏览次数：29  
标签：主库 opt binlog 升级 SSL Master mysql mysql57

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

 

标签：主库,opt,binlog,升级,SSL,Master,mysql,mysql57
From： https://www.cnblogs.com/hxlasky/p/18140768

相关文章

  • mysql小版本升级（yg）
    mysql小版本升级两种方式：In-PlaceUpgradeLogicalUpgrade[In-PlaceUpgrade](#in-placeupgrade)​ 原地升级（In-PlaceUpgrade：直接替换二进制文件（即直接yumupdate/rpm-Uvh升级rpm包）数据库目录等都不变。LogicalUpgrade​ 逻辑升级（LogicalUpgrade）：先用mysqldump......
  • mysql小版本升级
    #rpm-ivhmysql-community-common-8.0.23-1.el8.x86_64.rpm#rpm-ivhmysql-community-client-plugins-8.0.23-1.el8.x86_64.rpm#rpm-ivhmysql-community-libs-8.0.23-1.el8.x86_64.rpm#rpm-ivhmysql-community-client-8.0.23-1.el8.x86_64.rpm#rpm-ivhmy......
  • 小版本升级（pxc）
    配置yum仓库，可以通过yum下载pxcyuminstall-yhttps://repo.percona.com/yum/percona-release-latest.noarch.rpm关闭数据库systemctlstopmysql升级：sudoyumupdatePercona-XtraDB-Cluster-57以单节点模式启动服务sudomysqld--skip-grant-tables--user=mysql--w......
  • 4-01. 升级到 URP 并创建灯光数据结构
    安装URP安装URP创建Settings修改ProjectSettings让素材支持通用渲染管线如果Convert的时候出现报错，继续点击Convert即可注意，如果报错说场景没有加载，需要把场景加载好之后再转换实现全局光照新建Lights然后创建GlobalLight2D白天的灯光效果晚上......
  • RTX 腾讯通停止服务，有哪些平滑升级迁移替代方案？
    RTX腾讯通，作为腾讯公司于2003年推出的企业即时通讯软件，曾经在政企单位中得到广泛应用。然而，自2015年后，这款软件就未曾更新，近期腾讯将RTX腾讯通官网的域名更改为跳转到企业微信官网，这意味RTX腾讯通正式停止服务。尽管如此，由于本地化部署的特点，已安装用户仍可继续使用。但面临授权文......
  • 界面组件DevExpress WinForms v23.2 - 数据展示、UI模板功能全新升级
    DevExpressWinForms拥有180+组件和UI库，能为WindowsForms平台创建具有影响力的业务解决方案。DevExpressWinForms能完美构建流畅、美观且易于使用的应用程序，无论是Office风格的界面，还是分析处理大批量的业务数据，它都能轻松胜任！DevExpressWinForms控件日前正式发布了v23.2，此版......
  • 08、M-LAG维护模式升级原理
    M-LAG维护模式升级原理M-LAG维护模式下的升级的基本原理是在维护模式下，通过OSPF/OSPFv3/BGP/BGP4+和LACP命令将流量先切换到备份链路上，再升级设备。升级完成后，依次删除LACP和OSPF/OSPFv3/BGP/BGP4+命令将流量回切，再退出维护模式。整个维护模式下的升级过程相对传统的升级方式提......
  • 阿里云消息队列升级全新品牌 ApsaraMQ丨阿里云云原生 3 月产品月报
    云原生月度动态云原生是企业数字创新的最短路径。《阿里云云原生每月动态》，从趋势热点、产品新功能、服务客户、开源与开发者动态等方面，为企业提供数字化的路径与指南。趋势热点......
  • 在Linux中，如何进行文件系统的迁移和升级？
    在Linux中，文件系统的迁移和升级是涉及数据完整性和系统稳定性的重要操作。以下是进行文件系统迁移和升级的一般步骤：1.文件系统迁移文件系统迁移通常涉及将数据从一个文件系统迁移到另一个文件系统，例如从ext3迁移到ext4，或者从一个分区迁移到另一个分区。备份数据：在进行任何......
  • jdk8 升级 jdk17 docker 部署失败
    首先，感谢 http://www.manongjc.com/detail/42-hiwfjklnbpukjgd.html & https://huaweicloud.csdn.net/654a1218525bff6100e99afd.html在jdk8升级jdk17，项目docker部署失败。报错：###SQL:SELECTid,wechat_nick_name,open_id,name,take_effect,mobile,email,user......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99