4.1、MySQL主从同步小版本升级

MySQL主从同步小版本升级

https://www.osyunwei.com/archives/12982.html

操作系统：CentOS 7.x

MySQL二进制版本：5.7.x 小版本升级

现有版本：MySQL 5.7.38

升级后版本：MySQL 5.7.39

下载地址：https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

上传mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz到/usr/local/src

升级前先查看MySQL 5.7.x 中添加、弃用或删除的变量和选项

https://dev.mysql.com/doc/refman/5.7/en/added-deprecated-removed.html

############################################

MySQL 8.0.x版本可以使用mysql-shell进行升级前的检查

mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz

https://cdn.mysql.com/archives/mysql-shell/mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz

tar zxvf /usr/local/src/mysql-shell-8.0.29-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/

cd /usr/local

mv mysql-shell-8.0.29-linux-glibc2.12-x86-64bit mysql-shell

cd /usr/local/mysql-shell/bin

./mysqlsh – util checkForServerUpgrade root@localhost:3306 –target-version=5.7.39 –output-format=JSON –config-path=/data/server/mysql/my.cnf

############################################

使用离线方式进行升级

一、主库操作

1、进入mysql控制台

set global innodb_fast_shutdown=0; #保障数据的完整性

flush tables with read lock; #数据库只读锁定命令，防止导出数据库的时候有数据写入（命令执行期间控制台不能关闭，控制台关闭命令就自动失效了，unlock tables;#解除锁定）

set global read_only=on; #设置数据库为只读（数据库重启之后命令失效，set global read_only=off;#解除锁定）

2、备份数据

#重启打开一个终端进行操作

/data/server/mysql/bin/mysqldump -h 127.0.0.1 -u root -p lts –default-character-set=utf8mb4 –opt -Q -R –skip-lock-tables> /tmp/lts-bak.sql

3、停止mysql

/data/server/mysql/mysql.sh stop

4、安装新版mysql

#备份mysql安装目录和数据库目录（mysql数据库目录在/data/server/mysql/data下）

mv /data/server/mysql /data/server/mysql.bak

#创建新版数据库安装目录

mkdir -p /data/server/mysql

#解压新版软件到安装目录

cd /usr/local/src/

tar zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /data/server/mysql –strip-components 1

#拷贝旧版数据存放目录、my.cnf配置文件、mysql启动脚本到新版数据库安装目录

cp -r /data/server/mysql.bak/data /data/server/mysql/

cp -r /data/server/mysql.bak/my.cnf /data/server/mysql/

cp -r /data/server/mysql.bak/mysql.sh /data/server/mysql/

#注意权限保持一致

chown mysql.mysql /data/server/mysql -R

#编辑配置文件，注释掉主从同步的配置参数

vi /data/server/mysql/my.cnf

#log-bin=mysql-bin

#binlog-do-db=lts

#binlog-ignore-db=mysql

:wq! #保存退出

#启动mysql

/data/server/mysql/bin/mysqld_safe –defaults-file=/data/server/mysql/my.cnf –user=mysql –port=3306 –basedir=/data/server/mysql –datadir=/data/server/mysql/data –socket=/data/server/mysql/data/mysql.sock –pid-file=/data/server/mysql/data/mysql.pid –skip-grant-tables –skip-networking &

注：–skip-grant-tables和–skip-networking参数，来保证没有任何的应用连接，让升级过程更加安全。

#查看数据库是否正常启动

ps -ef |grep mysqld

5、升级系统表数据字典信息

/data/server/mysql/bin/mysql_upgrade

[root@mysql-master mysql]# /data/server/mysql/bin/mysql_upgrade

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv OK

mysql.db OK

mysql.engine_cost OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.gtid_executed OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.innodb_index_stats OK

mysql.innodb_table_stats OK

mysql.ndb_binlog_index OK

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.server_cost OK

mysql.servers OK

mysql.slave_master_info OK

mysql.slave_relay_log_info OK

mysql.slave_worker_info OK

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

The sys schema is already up to date (version 1.5.2).

Checking databases.

lts.lts_cron_job_queue OK

lts.lts_executing_job_queue OK

lts.lts_fjq_test_jobclient OK

lts.lts_job_log_po OK

lts.lts_node_group_store OK

lts.lts_repeat_job_queue OK

lts.lts_suspend_job_queue OK

lts.lts_wjq_test_trade_tasktracker OK

sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

6、登录到数据库查看版本

[root@mysql-master mysql]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select version();

+———–+

| version() |

+———–+

| 5.7.39 |

+———–+

1 row in set (0.00 sec)

exit;

#关闭mysql

/data/server/mysql/bin/mysqladmin -u root -p shutdown

二、从库操作

1、进入mysql控制台

stop slave io_thread;

stop slave sql_thread;

start slave until sql_after_mts_gaps;

show slave status\G #查看是否已经停掉了sql_thread、io_thread

stop slave;

exit;

2、备份数据

/data/server/mysql/bin/mysqldump -h 127.0.0.1 -u root -p lts –default-character-set=utf8mb4 –opt -Q -R –skip-lock-tables> /tmp/lts-bak.sql

3、停止mysql

/data/server/mysql/mysql.sh stop

4、安装新版mysql

#备份mysql安装目录和数据库目录（mysql数据库目录在/data/server/mysql/data下）

mv /data/server/mysql /data/server/mysql.bak

#创建新版数据库安装目录

mkdir -p /data/server/mysql

#解压新版软件到安装目录

cd /usr/local/src/

tar zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /data/server/mysql –strip-components 1

#拷贝旧版数据存放目录、my.cnf配置文件、mysql启动脚本到新版数据库安装目录

cp -r /data/server/mysql.bak/data /data/server/mysql/

cp -r /data/server/mysql.bak/my.cnf /data/server/mysql/

cp -r /data/server/mysql.bak/mysql.sh /data/server/mysql/

#注意权限保持一致

chown mysql.mysql /data/server/mysql -R

#编辑配置文件，注释掉主从同步的配置参数

vi /data/server/mysql/my.cnf

#log-bin=mysql-bin

#replicate-do-db=lts

#replicate-ignore-db=mysql

#read_only

:wq! #保存退出

#启动mysql

/data/server/mysql/bin/mysqld_safe –defaults-file=/data/server/mysql/my.cnf –user=mysql –port=3306 –basedir=/data/server/mysql –datadir=/data/server/mysql/data –socket=/data/server/mysql/data/mysql.sock –pid-file=/data/server/mysql/data/mysql.pid –skip-grant-tables –skip-networking &

注：–skip-grant-tables和–skip-networking参数，来保证没有任何的应用连接，让升级过程更加安全。

#查看数据库是否正常启动

ps -ef |grep mysqld

5、升级系统表数据字典信息

/data/server/mysql/bin/mysql_upgrade

[root@mysql-slave src]# /data/server/mysql/bin/mysql_upgrade

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

mysql.columns_priv OK

mysql.db OK

mysql.engine_cost OK

mysql.event OK

mysql.func OK

mysql.general_log OK

mysql.gtid_executed OK

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.innodb_index_stats OK

mysql.innodb_table_stats OK

mysql.ndb_binlog_index OK

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.server_cost OK

mysql.servers OK

mysql.slave_master_info OK

mysql.slave_relay_log_info OK

mysql.slave_worker_info OK

mysql.slow_log OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.user OK

The sys schema is already up to date (version 1.5.2).

Checking databases.

lts.lts_cron_job_queue OK

lts.lts_executing_job_queue OK

lts.lts_fjq_test_jobclient OK

lts.lts_job_log_po OK

lts.lts_node_group_store OK

lts.lts_repeat_job_queue OK

lts.lts_suspend_job_queue OK

lts.lts_wjq_test_trade_tasktracker OK

sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

6、登录到数据库查看版本

[root@mysql-slave src]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.39-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select version();

+————+

| version() |

+————+

| 5.7.39-log |

+————+

1 row in set (0.00 sec)

exit;

#关闭mysql

/data/server/mysql/bin/mysqladmin -u root -p shutdown

三、配置主从同步

1、登录到主库

#编辑配置文件，开启主从同步的配置参数

vi /data/server/mysql/my.cnf

#log-bin=mysql-bin

#binlog-do-db=lts

#binlog-ignore-db=mysql

:wq! #保存退出

#启动主库

/data/server/mysql/mysql.sh start

#进入mysql控制台

show variables like ‘server_id’; #查看server-id的值是否为1

show master status; #查看主服务器，出现以下类似信息

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000008 | 154 | lts | mysql | |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

mysql>

注意：这里记住File的值：mysql-bin.000008和Position的值：154

2、登录到从库

#编辑配置文件，开启主从同步的配置参数

vi /data/server/mysql.bak/my.cnf

#log-bin=mysql-bin

#replicate-do-db=lts

#replicate-ignore-db=mysql

#read_only

:wq! #保存退出

#启动从库

/data/server/mysql/mysql.sh start

#进入mysql控制台

start slave; #开启slave同步进程

SHOW SLAVE STATUS\G #查看slave同步信息，出现以下内容

mysql> SHOW SLAVE STATUS\G

*** 1. row ***

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.21.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 154

Relay_Log_File: mysql-slave-relay-bin.000010

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: lts

Replicate_Ignore_DB: mysql

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: 154

Relay_Log_Space: 793

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: 797f979a-2849-11ed-90ac-000c292ccd74

Master_Info_File: /data/server/mysql/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:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

注意查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

以上这两个参数的值为Yes，即说明同步成功！

备注：主从同步命令

stop slave; #停止slave同步进程

#执行主从同步操作

change master to master_host=’192.168.21.128’,master_user=’repl’,master_password=’12345678Abc++’,master_log_file=’mysql-bin.000008’ ,master_log_pos=154;

start slave; #开启slave同步进程

至此，Linux下MySQL主从同步小版本升级完成。