mysql常用命令以及小技巧

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

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

1. 清理二進(jìn)制日志

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

2. mysqldump不鎖表

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

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

3. mysql跳過空事務(wù)

問題出現(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í)行過的事務(wù)。

其中：

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

而下面這個(gè)是從master 同步的事務(wù)，等待slave

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

執(zhí)行停止slave

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

檢查當(dāng)前 gtid 相關(guān)信息

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)

將事務(wù)指向37496 的下一個(gè)事務(wù)，即 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)

跳過空事務(wù)：

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)

設(shè)置自動(dòng)分配 GTID：

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

4. 番外

我們知道一個(gè)新的事務(wù)在提交后會(huì)被分配一個(gè)新的GTID,當(dāng)該事務(wù)在從庫(kù)上被應(yīng)用時(shí)會(huì)保留主庫(kù)上的GTID
我們可以通過設(shè)定gtid_next 的值來(lái)改變這種行為

1 AUTOMATIC

當(dāng)設(shè)置為AUTOMATIC時(shí)(默認(rèn)值)時(shí)，系統(tǒng)會(huì)自動(dòng)分配一個(gè)GTID，如果事務(wù)回滾或者沒有寫入到二進(jìn)制文件時(shí)則不會(huì)分配

2 具體的GTID值

我們可以設(shè)置該變量為一個(gè)具體的有效的GTID，這時(shí)服務(wù)器會(huì)將該GTID分配給下一個(gè)事務(wù)，就算該事務(wù)沒有被寫入二進(jìn)制日志或者為空事務(wù)，該GTID也會(huì)被分配并加入到gtid_executed變量中
這里需要注意的是，如果該變量值不為AUTOMATIC，我們需要手動(dòng)的為每個(gè)事務(wù)指定GTID，否則該事務(wù)會(huì)失敗，你可以將其改為AUTOMATIC，讓服務(wù)器自動(dòng)分配

啟動(dòng) 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導(dǎo)出數(shù)據(jù)

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

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

注釋：

1.數(shù)據(jù)庫(kù)前面不需要加任何參數(shù)，如果加-d就是只導(dǎo)出表結(jié)構(gòu)
2.當(dāng)數(shù)據(jù)庫(kù)開啟gtid后需要在導(dǎo)出數(shù)據(jù)時(shí)把gtid關(guān)掉，即加--set-gtid-purged=OFF參數(shù)，因?yàn)間tid是唯一的，再插入時(shí)會(huì)報(bào)錯(cuò)
3.加參數(shù)--single-transaction不鎖表

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

您可能感興趣的文章: