mysql常用命令以及小技巧

更新時間：2022年02月17日 08:51:10 作者：礁之

這篇文章主要分享的是mysql常用命令以及小技巧，概述清理二進制日志、mysqldump不鎖表、mysql跳過空事務等相關資料展開主題，需要的小伙伴可以參考一下，希望對你有所幫助

1. 清理二進制日志

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

2. mysqldump不鎖表

在使用mysqldump備份mysql數(shù)據(jù)時，要盡量去從庫拿，如果有需求去主庫，可以加 --single-transaction 參數(shù)不鎖表，不加此參數(shù)有可能會把主庫的表全鎖了??！導致業(yè)務出現(xiàn)故障

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

3. mysql跳過空事務

問題出現(xiàn)：

? ? ? ? ? ? ? ?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 已經(jīng)執(zhí)行過的事務。

其中：

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

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

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

執(zhí)行停止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，注意規(guī)范，把 :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時(默認值)時，系統(tǒng)會自動分配一個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 status\G
*************************** 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導出數(shù)據(jù)

導出指定庫的所有數(shù)據(jù)：

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

注釋：

1.數(shù)據(jù)庫前面不需要加任何參數(shù)，如果加-d就是只導出表結(jié)構(gòu)
2.當數(shù)據(jù)庫開啟gtid后需要在導出數(shù)據(jù)時把gtid關掉，即加--set-gtid-purged=OFF參數(shù)，因為gtid是唯一的，再插入時會報錯
3.加參數(shù)--single-transaction不鎖表

到此這篇關于mysql常用命令以及小技巧的文章就介紹到這了,更多相關mysql常用命令和小技巧內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

CentOS系統(tǒng)中安裝MySQL和開啟MySQL遠程訪問的方法
這篇文章主要介紹了CentOS系統(tǒng)中安裝MySQL和開啟MySQL遠程訪問的方法,包括MySQL的隨機啟動等操作的介紹,需要的朋友可以參考下
2016-02-02
MySQL去重該使用distinct還是group by？
這篇文章主要介紹了MySQL去重該使用distinct還是group by,本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2020-05-05
專業(yè)級的MySQL開發(fā)設計規(guī)范及SQL編寫規(guī)范
這篇文章主要介紹了專業(yè)級的MySQL開發(fā)設計規(guī)范及SQL編寫規(guī)范,需要的朋友可以參考下
2020-11-11
MySQL幾種更新操作的案例分析
本文將通過一個用戶賬戶金額更新的案例分析幾種數(shù)據(jù)更新的操作的優(yōu)劣，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2021-10-10
Mysql系列SQL查詢語句書寫順序及執(zhí)行順序詳解
這篇文章主要為大家介紹了Mysql系列SQL查詢語句的書寫順序及執(zhí)行順序示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步
2021-10-10
安裝的mysql中沒有my.ini文件的解決方法
本文主要介紹了安裝的mysql中沒有my.ini文件的解決方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-03-03
Linux下Mysql5.6 二進制安裝過程
這篇文章主要介紹了Linux下Mysql5.6 二進制安裝過程，本文通過實例代碼給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下
2018-06-06
MySQL通配符與正則表達式搜過濾數(shù)據(jù)詳解
簡單來說,正則表達式就是用來匹配文本的特殊字符串,下面這篇文章主要給大家介紹了關于MySQL通配符與正則表達式搜過濾數(shù)據(jù)的相關資料,文中通過實例代碼以及圖文介紹的非常詳細,需要的朋友可以參考下
2022-09-09
MySQL雙主配置的項目實踐
本文詳細介紹了配置兩臺MySQL服務器之間的主從復制,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-12-12
深入理解Mysql的四種隔離級別
開發(fā)工作中我們會使用到事務，那你們知道事務又分哪幾種嗎？MYSQL標準定義了4類隔離級別，用來限定事務內(nèi)外的哪些改變是可見的，哪些是不可見的。低的隔離級一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開銷。下面通過這篇文章我們來一起深入理解Mysql中的四種隔離級別。
2016-11-11