mysql主从升级(直接先升级主库,旧binlog拷贝到新版本目录,需要停业务)
环境:
OS:Centos 7
旧版本:mysql 5.6
新版本:mysql 5.7
1.停掉旧版本数据库
确保主从当前没有延迟再停主库,不过有延迟也可以停掉
查看从库的状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000024
Read_Master_Log_Pos: 27280191
Relay_Log_File: host135-relay-bin.000002
Relay_Log_Pos: 2595024
Relay_Master_Log_File: binlog.000024
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: 2594860
Relay_Log_Space: 27280561
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: 309
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: Reading event from the relay log
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:550005-630004 ##接收到的gtid与主库的一致,说明日志都传到从库了
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-557613 ##从库的sql进程在应用日志
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
主库状态:
mysql> show master status \G;
*************************** 1. row ***************************
File: binlog.000024
Position: 27280191
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-630004
1 row in set (0.00 sec)
ERROR:
No query specified
停掉主库
/opt/mysql56/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql56/data/mysql.sock shutdown
2.停掉从库的接收日志进程
mysql> stop slave io_thread;
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 -P3306 -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 -P3306 -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.拷贝旧版本的binlog到新版本的目录下
删除新库的binlog
先停掉数据库
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P3306 -p -S /opt/mysql57/data/mysql.sock shutdown
删除binlog
[root@host134 binlog]# pwd
/opt/mysql57/mysqllog/binlog
[root@host134 binlog]# ls
bak_binlog.000001 binlog.000019 binlog.000021 binlog.000023 binlog.index
bak_binlog.index binlog.000020 binlog.000022 binlog.000024
[root@host134 binlog]# rm -rf *
将原来5.6版本的binlog拷贝到新版本数据库的binlog目录
[root@host134 binlog]# pwd
/opt/mysql56/mysqllog/binlog
[root@host134 binlog]# cp * /opt/mysql57/mysqllog/binlog/
修改权限
[root@host134 binlog]# chown -R mysql:mysql /opt/mysql57/mysqllog/binlog/
拷贝过去后需要修改binlog.index里面内容的文件
[root@host134 binlog]# more binlog.index
/opt/mysql56/mysqllog/binlog/binlog.000019
/opt/mysql56/mysqllog/binlog/binlog.000020
/opt/mysql56/mysqllog/binlog/binlog.000021
/opt/mysql56/mysqllog/binlog/binlog.000022
/opt/mysql56/mysqllog/binlog/binlog.000023
修改为:
[root@host134 binlog]# more binlog.index
/opt/mysql57/mysqllog/binlog/binlog.000019
/opt/mysql57/mysqllog/binlog/binlog.000020
/opt/mysql57/mysqllog/binlog/binlog.000021
/opt/mysql57/mysqllog/binlog/binlog.000022
/opt/mysql57/mysqllog/binlog/binlog.000023
9.启动数据库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &
10.启动从库的io进程
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
11.查看从库复制情况
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000025
Read_Master_Log_Pos: 194
Relay_Log_File: host135-relay-bin.000002
Relay_Log_Pos: 24219198
Relay_Master_Log_File: binlog.000024
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: 24219034
Relay_Log_Space: 27281849
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: 2923
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: 134
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: Reading event from the relay log
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:550005-630004
Executed_Gtid_Set: f58f88cb-f478-11ed-b257-525400c8dc1f:1-621027
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
