前面写了一篇文章，搭建了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)
Post a Comment