mysql5.7 主从切换,MySQL-5.7.18 主从复制升级至5.7.21

最新推荐文章于 2021-11-27 22:34:47 发布

weixin_39637151

最新推荐文章于 2021-11-27 22:34:47 发布

阅读量201

点赞数

文章标签： mysql5.7 主从切换

前面写了一篇文章，搭建了MySQL5.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;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

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方式，在把新的从库进行升级，就实现了主从的全部升级。

分享到：

weixin_39637151

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7 主从切换,MySQL-5.7.18 主从复制升级至5.7.21

前面写了一篇文章，搭建了MySQL5.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.tarlrwxrwxrwx...
复制链接

扫一扫

mysql5.7 主从切换,MySQL-5.7.18 主从复制升级至5.7.21

“相关推荐”对你有帮助么？