86
MySQL-5.7.18 主从复制升级至5.7.21
source link: http://www.dboracle.com/archivers/mysql-5-7-18-主从复制升级至5-7-21.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
前面写了一篇文章，搭建了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; +------------------+----------+--------------+------------------+-------------------+ | 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方式，在把新的从库进行升级，就实现了主从的全部升级。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK