主从环境下升级(先升级从库,不做切换,需要停业务,auto_position=1)
环境:
OS:Centos 7
旧mysql版本:5.6.40
新版本mysql:5.7.39
主库:192.168.1.134
从库:192.168.1.135
##################################升级从库#################################
注意端口和socket不能与现有的实例相同,等升级后没有问题再修改回来原来的,这样应用程序就不需要修改链接:
port=13306
socket=/opt/mysql57/data/mysql.sock
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | d6696ebd-fb91-11ee-b632-52540051cd25 |
+---------------+--------------------------------------+
1 row in set (0.07 sec)
data目录下的auto.cnf文件也保存着该server_uuid
[root@host135 data]# more auto.cnf
[auto]
server-uuid=d6696ebd-fb91-11ee-b632-52540051cd25
2.停掉从库的复制进程
[root@host135 data]# /opt/mysql56/bin/mysql -h localhost -uroot -pmysql
mysql> stop slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000019
Read_Master_Log_Pos: 101634643
Relay_Log_File: host135-relay-bin.000002
Relay_Log_Pos: 68200399
Relay_Master_Log_File: binlog.000019
Slave_IO_Running: No
Slave_SQL_Running: No
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: 101634643
Relay_Log_Space: 68200605
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: NULL
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: 23
Master_UUID: f58f88cb-f478-11ed-b257-525400c8dc1f
Master_Info_File: /opt/mysql56/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:100005-300004
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-300004
Auto_Position: 1
1 row in set (0.00 sec)
这里停掉复制进程的目的是记录下从库已经执行了的GTID
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-300004
下面步骤升级到5.7后启动复制进行需要跳过该uuid进行重新同步.
3.停掉从库和新版本实例数据库
从库:
/opt/mysql56/bin/mysqladmin -h localhost -uroot -pmysql shutdown
新实例:
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown
4.这个时候模拟主库继续写入数据
主要是验证下升级后这些数据是否自动同步到从库
5.将从库的数据库目录拷贝到新实例的数据目录
先备份新实例的数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata
将从库旧实例的data目录拷贝到新实例的目录下(旧实例已经停掉的,可以直接拷贝文件)
[root@host135 mysql57]# cd /opt/mysql56
[root@host135 mysql57]# cp -r data /opt/mysql57/
修改权限
[root@host135 mysql57]# cd /opt
[root@host135 middle]# chown -R mysql:mysql ./mysql57
6.启动新版本实例
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)
7.升级
[root@host135 mysql57]# /opt/mysql57/bin/mysql_upgrade -s -h localhost -pmysql -P13306 -S /opt/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
[root@host135 data]# more auto.cnf
[auto]
server-uuid=f4ea4856-fb03-11ee-a25a-52540051cd25
发现升级后的server-uuid改变了的
mysql> show variables like '%server_uuid%';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure
mysql> show variables like '%gtid%';
ERROR 1682 (HY000): Native table 'performance_schema'.'session_variables' has the wrong structure
解决办法:
重启数据库
[root@host135 data]#/opt/mysql57/bin/mysqladmin -s -h localhost -pmysql -P13306 -S /opt/mysql57/data/mysql.sock shutdown
[root@host135 data]#/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | f4ea4856-fb03-11ee-a25a-52540051cd25 |
+---------------+--------------------------------------+
1 row in set (0.25 sec)
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)
8.重新应用从库复制
尝试启动主从复制
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决办法:
mysql> stop slave;
mysql> reset slave all;
mysql> reset master;
mysql> set @@global.gtid_purged ='f58f88cb-f478-11ed-b257-525400c8dc1f:1-300004'; ##必须执行reset master,否则报错误:ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql>change master to master_host='192.168.1.134',
master_port=3306,
master_user='repl',
master_password='repl',
master_auto_position=1;
这里的gtid就是从库停掉时记录的gtid，这个时候的复制信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: relaylog-binlog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: information_schema,performance_schema,sys
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: 0
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /opt/mysql57/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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: f58f88cb-f478-11ed-b257-525400c8dc1f:1-300004
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
启动复制进程:
mysql> start slave;
这个时候从库完成升级
###########################升级主库#############################
正常的升级流程是到这里可以做主从切换了,然后再升级原来的主库,但是这里需要修改应用程序,下面我直接升级主库
1.停掉旧版本数据库
确保主从当前没有延迟再停主库
/opt/mysql56/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql56/data/mysql.sock shutdown
2.停掉从库的日志应用
这里确保从库没有延迟,确保没有延迟的目的是从库需要重新应用主库升级后的日志
mysql> stop slave;
3.停掉主库的新版数据库实例
新实例:
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown
4.将从库的数据库目录拷贝到新实例的数据目录
先备份新实例的数据目录
[root@host135 mysql57]#cd /opt/mysql57
[root@host135 mysql57]#mv data bakdata
将从库旧实例的data目录拷贝到新实例的目录下(旧实例已经停掉的,可以直接拷贝文件)
[root@host135 mysql57]# cd /opt/mysql56
[root@host135 mysql57]# cp -r data /opt/mysql57/
5.修改权限
[root@host135 mysql57]# cd /opt/mysql57/
[root@host135 middle]#chown -R mysql:mysql /opt/mysql57/
6.启动新实例
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
当前的uuid
[root@host134 data]# more auto.cnf
[auto]
server-uuid=f58f88cb-f478-11ed-b257-525400c8dc1f
登录查看
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.39-log |
+------------+
1 row in set (0.00 sec)
7.升级
[root@host134 binlog]# /opt/mysql57/bin/mysql_upgrade -s -h localhost -pmysql -P13306 -S /opt/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
升级后的uuid
[root@host134 data]# more auto.cnf
[auto]
server-uuid=f58f88cb-f478-11ed-b257-525400c8dc1f
8.重启
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
9.重置主库
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> reset master;
Query OK, 0 rows affected (0.52 sec)
这个时候会生成新的binlog,从1开始
[root@host134 binlog]# ls -al
total 8
drwxrwxr-x 2 mysql mysql 47 Apr 16 14:57 .
drwxrwxr-x 5 mysql mysql 51 Apr 16 13:53 ..
-rw-r----- 1 mysql mysql 154 Apr 16 14:57 binlog.000001
-rw-r----- 1 mysql mysql 43 Apr 16 14:57 binlog.index
10.从库重新应用日志
mysql> stop slave;
mysql> reset master;
mysql> reset slave all;
change master to master_host='192.168.1.134',
master_user='repl',
master_password='mysql',
master_port=13306,
master_auto_position=1;
start slave;
发现密码错误,修改正确的
mysql> stop slave;
mysql> change master to master_password='repl';
mysql> start slave;
#############################修改端口,提供外部访问##########################
1.主库修改端口重启
port=13306修改为3306
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
2.从库修改端口重启同时修改复制端口
port=13306修改为3306
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock shutdown
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
/opt/mysql57/bin/mysql -h localhost -uroot -P13306 -p -S /opt/mysql57/data/mysql.sock
mysql> stop slave;
mysql> change master to master_port=3306;
mysql> start slave;
