環境:
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.