mysql常用命令以及小技巧

2022-02-17 13:01:30

1. 清理二進位制紀錄檔

purge master logs to 'log-bin.004193';   #表示直接清理到4193位置

2. mysqldump不鎖表

在使用mysqldump備份mysql資料時，要儘量去從庫拿，如果有需求去主庫，可以加 --single-transaction 引數不鎖表，不加此引數有可能會把主庫的表全鎖了！！導致業務出現故障

mysqldump --single-transaction  --compact -uroot -p（password） -h（dbip） -d （databasesname） > /tmp/test.sql

3. mysql跳過空事務

問題出現：

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.187.97.219
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000047
          Read_Master_Log_Pos: 61907358
               Relay_Log_File: relay.000114
                Relay_Log_Pos: 61906291
        Relay_Master_Log_File: log-bin.000047
             Slave_IO_Running: Yes
            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: 1062
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '81a58913-993d-11eb-94c7-00e0ed7ae706:37497' at master log log-bin.000047, end_log_pos 61906370. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 61906082
              Relay_Log_Space: 61907849
              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: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '81a58913-993d-11eb-94c7-00e0ed7ae706:37497' at master log log-bin.000047, end_log_pos 61906370. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5345323
                  Master_UUID: 81a58913-993d-11eb-94c7-00e0ed7ae706
             Master_Info_File: mysql.slave_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: 210413 05:25:50
               Master_SSL_Crl:
           Master_SSL_Crlpath:
#master# Retrieved_Gtid_Set: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500
#slave#    Executed_Gtid_Set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3,
#81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

開始處理：首先我們知道

Executed_Gtid_Set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3,
81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496

是slave 已經執行過的事務。

其中：

81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496 是已經回放了的從master 同步的事務，
119cf71e-993c-11eb-94bd-00e0ed93753c:1-3, 是在 slave 上執行的事務

而下面這個是從master 同步的事務，等待slave

Retrieved_Gtid_Set: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500

執行停止slave

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

檢查當前 gtid 相關資訊

mysql> show variables like '%gtid%';
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery      | ON                                     |
| enforce_gtid_consistency         | ON                                     |
| gtid_executed                    |                                        |
| gtid_executed_compression_period | 1000                                   |
| gtid_mode                        | ON                                     |
| gtid_next                        | AUTOMATIC                              |
| gtid_owned                       |                                        |
| gtid_purged                      | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 |
| session_track_gtids              | OFF                                    |
+----------------------------------+----------------------------------------+
9 rows in set (0.00 sec)

將事務指向37496 的下一個事務，即 37497，注意規範，把 :1-37396 的 1 去掉

mysql> set gtid_next='81a58913-993d-11eb-94c7-00e0ed7ae706:37497';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%gtid%';
+----------------------------------+--------------------------------------------+
| Variable_name                    | Value                                      |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                         |
| enforce_gtid_consistency         | ON                                         |
| gtid_executed                    |                                            |
| gtid_executed_compression_period | 1000                                       |
| gtid_mode                        | ON                                         |
| gtid_next                        | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 |
| gtid_owned                       | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 |
| gtid_purged                      | 81a58913-993d-11eb-94c7-00e0ed7ae706:1     |
| session_track_gtids              | OFF                                        |
+----------------------------------+--------------------------------------------+
9 rows in set (0.01 sec)

跳過空事務：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查詢gtid 資訊：

mysql> show variables like '%gtid%';
+----------------------------------+--------------------------------------------+
| Variable_name                    | Value                                      |
+----------------------------------+--------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                         |
| enforce_gtid_consistency         | ON                                         |
| gtid_executed                    |                                            |
| gtid_executed_compression_period | 1000                                       |
| gtid_mode                        | ON                                         |
| gtid_next                        | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 |
| gtid_owned                       |                                            |
| gtid_purged                      | 81a58913-993d-11eb-94c7-00e0ed7ae706:1     |
| session_track_gtids              | OFF                                        |
+----------------------------------+--------------------------------------------+
9 rows in set (0.00 sec)

設定自動分配 GTID：

ysql> set gtid_next='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

4. 番外

我們知道一個新的事務在提交後會被分配一個新的GTID,當該事務在從庫上被應用時會保留主庫上的GTID
我們可以通過設定gtid_next 的值來改變這種行為

1 AUTOMATIC

當設定為AUTOMATIC時(預設值)時，系統會自動分配一個GTID，如果事務回滾或者沒有寫入到二進位制檔案時則不會分配

2 具體的GTID值

我們可以設定該變數為一個具體的有效的GTID，這時伺服器會將該GTID分配給下一個事務，就算該事務沒有被寫入二進位制紀錄檔或者為空事務，該GTID也會被分配並加入到gtid_executed變數中
這裡需要注意的是，如果該變數值不為AUTOMATIC，我們需要手動的為每個事務指定GTID，否則該事務會失敗，你可以將其改為AUTOMATIC，讓伺服器自動分配

啟動 slave：

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show variables like '%gtid%';
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| binlog_gtid_simple_recovery      | ON                                     |
| enforce_gtid_consistency         | ON                                     |
| gtid_executed                    |                                        |
| gtid_executed_compression_period | 1000                                   |
| gtid_mode                        | ON                                     |
| gtid_next                        | AUTOMATIC                              |
| gtid_owned                       |                                        |
| gtid_purged                      | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 |
| session_track_gtids              | OFF                                    |
+----------------------------------+----------------------------------------+
9 rows in set (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.187.97.219
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000047
          Read_Master_Log_Pos: 61907358
               Relay_Log_File: relay.000115
                Relay_Log_Pos: 448
        Relay_Master_Log_File: log-bin.000047
             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: 61907358
              Relay_Log_Space: 61908058
              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: 5345323
                  Master_UUID: 81a58913-993d-11eb-94c7-00e0ed7ae706
             Master_Info_File: mysql.slave_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: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500
            Executed_Gtid_Set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3,
81a58913-993d-11eb-94c7-00e0ed7ae706:1-37500
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

5. mysql8.0使用mysqldump匯出資料

匯出指定庫的所有資料：

./bin/mysqldump -uroot -padmin123 -S status/mysql3306.sock  aaa5 --set-gtid-purged=OFF --single-transaction  > /root/aaa5.sql

註釋：

1.資料庫前面不需要加任何引數，如果加-d就是隻匯出表結構
2.當資料庫開啟gtid後需要在匯出資料時把gtid關掉，即加--set-gtid-purged=OFF引數，因為gtid是唯一的，再插入時會報錯
3.加引數--single-transaction不鎖表

到此這篇關於mysql常用命令以及小技巧的文章就介紹到這了,更多相關mysql常用命令和小技巧內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！