MySQL-5.7.18 主从复制升级至5.7.21

前面写了一篇文章,搭建了MySQL 5.7主从复制。今天要把这套主从环境从5.7.18升级到5.7.21。

一.上传安装包并解压

首先我们把安装介质传到salve服务器上,我们要先对slave服务器进行升级。

-rw-r--r--.  1 root root  2611024896 Jul  5 09:40 mysql-5.7.21-linux-glibc2.12-x86_64.tar
lrwxrwxrwx.  1 root root          12 Jul  5 16:09 mysql -> mysql-5.7.18
drwxr-xr-x. 11 root mysql       4096 Jul  5 16:44 mysql-5.7.18
drwxr-xr-x.  9 root root        4096 Jul  9 14:42 mysql-5.7.21

2.进入到slave记录下状态,然后停止slave进行备份

进入到数据库查看下slave的状态,然后停止复制。

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.161
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 409
Relay_Log_File: mysql-relay-bin.000014
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 409
Relay_Log_Space: 527
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: 942b8e95-8019-11e8-a4d6-0800272f786e
Master_Info_File: /usr/local/mysql-5.7.18/data/mysql-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)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

3.停止MySQL前设置innodb_fast_shutdown参数

mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0" --必须设置该参数。

关于innodb_fast_shutdown参数说明如下:
Innodb_fast_shutdown告诉InnoDB在它关闭的时候该做什么工作。有三个值可以选择:
1.0表示在innodb关闭的时候,需要purge all, merge insert buffer,flush dirty pages。这是最慢的一种关闭方式,但是restart的时候也是最快的。
2.1表示在innodb关闭的时候,它不需要purge all,merge insert buffer,只需要flush dirty page。
3.2表示在innodb关闭的时候,它不需要purge all,merge insert buffer,也不进行flush dirty page,只将log buffer里面的日志flush到log files,因此在进行崩溃恢复操作时它是最耗时的。

4.停止MySQL服务

service mysql.server stop
Shutting down MySQL.. [ OK ]

5.进行冷备份

主要备份数据字典,包括(SYS、mysql、information_schema、performance_schem)

[root@Mysql-Slave data]# cp -rp performance_schema/ /bakmysql/
[root@Mysql-Slave data]# cp -rp sys/ /bakmysql/
[root@Mysql-Slave data]# cp -rp mysql/ /bakmysql/
[root@Mysql-Slave data]# cp -rp mysql/ /information_schema/

6.将原有的目录改名。并将软链接指定到新的目录下

[root@Mysql-Slave local]# mv mysql-5.7.18/ mysql_bak
[root@Mysql-Slave local]# rm mysql
rm: remove symbolic link `mysql'? y
[root@Mysql-Slave local]# ln -s mysql-5.7.21/ mysql
[root@Mysql-Slave local]# ls -lrt
drwxr-xr-x. 11 root mysql 4096 Jul 5 16:44 mysql_bak
drwxr-xr-x. 9 root mysql 4096 Jul 9 15:25 mysql-5.7.21
lrwxrwxrwx. 1 root root 13 Jul 9 15:26 mysql -> mysql-5.7.21/

7.启动数据库,使用–skip-networking参数

[root@Mysql-Slave bin]# mysqld_safe --defaults-file=/etc/my.cnf --skip-networking &
[1] 5366
[root@Mysql-Slave bin]# 2018-07-09T07:44:29.485458Z mysqld_safe Logging to '/usr/local/mysql/data/mysql.err'.
2018-07-09T07:44:29.504810Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

8.升级数据字典

[root@Mysql-Slave bin]# ./mysql_upgrade -uroot -p -S /tmp/mysql.sock
Enter password:
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.1).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
sys.sys_config OK
test1.a1 OK
Upgrade process completed successfully.
Checking if update is needed.

9.检查数据库状态

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 4 min 10 sec

Threads: 3 Questions: 3161 Slow queries: 0 Opens: 321 Flush tables: 1 Open tables: 82 Queries per second avg: 12.644

10.启动slave,检查slave状态。经过测试,直接启动数据库后slave自动带起来了。

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.161
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 409
Relay_Log_File: mysql-relay-bin.000022
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1
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: 409
Relay_Log_Space: 527
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: 942b8e95-8019-11e8-a4d6-0800272f786e
Master_Info_File: /usr/local/mysql-5.7.21/data/mysql-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)

11.做主从切换,主变成从,从变成主,确认主从都是否开启了log_bin

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

12.将主库设置成read_only模式

mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+
4 rows in set (0.00 sec)

13.将所有日志刷新到DB,以获得一个带有bin-logs新起点的干净数据库

mysql> flush tables; flush logs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 154 | test1 | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

14.停止老的slave,设置slave进程read_only为off,打开写功能.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set global read_only=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)

15.在老的msater上修改配置

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.162', MASTER_USER='slave_user', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.162
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000009
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: 154
Relay_Log_Space: 740
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: 2
Master_UUID: 03c253a1-8030-11e8-96a4-080027c4681e
Master_Info_File: /usr/local/mysql/data/mysql-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)

16.切换后验证

新主库

mysql> use test1;
Database changed
mysql> insert into a1 values(4);
Query OK, 1 row affected (0.02 sec)

新从库

mysql> use test1;
Database changed
mysql> select * from a1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)

17.按照上述升级slave方式,在把新的从库进行升级,就实现了主从的全部升级。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569