主從環境下升級(先升級從庫,不做切換,需要停業務,auto_position=1)

slnngk發表於2024-04-16

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

相關文章