mysql常用命令以及小技巧
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跳過(guò)空事務(wù)
問(wèn)題出現(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)
開(kāi)始處理:首先我們知道
Executed_Gtid_Set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3,
81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496
是slave 已經(jīng)執(zhí)行過(guò)的事務(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)
跳過(guò)空事務(wù):
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
查詢(xún)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
我們可以通過(guò)設(shè)定gtid_next
的值來(lái)改變這種行為
1 AUTOMATIC
當(dāng)設(shè)置為AUTOMATIC
時(shí)(默認(rèn)值)時(shí),系統(tǒng)會(huì)自動(dòng)分配一個(gè)GTID,如果事務(wù)回滾或者沒(méi)有寫(xiě)入到二進(jìn)制文件時(shí)則不會(huì)分配
2 具體的GTID值
我們可以設(shè)置該變量為一個(gè)具體的有效的GTID,這時(shí)服務(wù)器會(huì)將該GTID分配給下一個(gè)事務(wù),就算該事務(wù)沒(méi)有被寫(xiě)入二進(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
注釋?zhuān)?/strong>
- 1.數(shù)據(jù)庫(kù)前面不需要加任何參數(shù),如果加-d就是只導(dǎo)出表結(jié)構(gòu)
- 2.當(dāng)數(shù)據(jù)庫(kù)開(kāi)啟
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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql(5.6及以下)解析json的方法實(shí)例詳解
這篇文章主要介紹了mysql(5.6及以下)解析json的方法,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值 ,需要的朋友可以參考下2019-07-07MySQL數(shù)據(jù)庫(kù)字段超長(zhǎng)問(wèn)題的解決
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)字段超長(zhǎng)問(wèn)題的解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-07-07mysql分頁(yè)時(shí)offset過(guò)大的Sql優(yōu)化經(jīng)驗(yàn)分享
mysql分頁(yè)是我們?cè)陂_(kāi)發(fā)經(jīng)常遇到的一個(gè)功能,最近在實(shí)現(xiàn)該功能的時(shí)候遇到一個(gè)問(wèn)題,所以這篇文章主要給大家介紹了關(guān)于mysql分頁(yè)時(shí)offset過(guò)大的Sql優(yōu)化經(jīng)驗(yàn),文中介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面跟著小編來(lái)一起看看吧。2017-08-08解決Mysql多行子查詢(xún)的使用及空值問(wèn)題
所謂多行子查詢(xún),是指執(zhí)行查詢(xún)語(yǔ)句獲得的結(jié)果集中返回了多行數(shù)據(jù)的子查詢(xún),今天通過(guò)本文給大家分享Mysql多行子查詢(xún)的使用及空值問(wèn)題,感興趣的朋友一起看看吧2022-01-01mysql觸發(fā)器實(shí)現(xiàn)oracle物化視圖示例代碼
mysql觸發(fā)器實(shí)現(xiàn)oracle物化視圖即不是基于基表的虛表,而是根據(jù)表實(shí)際存在的實(shí)表,需要的朋友可以參考下2014-02-02一文詳解MySQL數(shù)據(jù)庫(kù)索引優(yōu)化的過(guò)程
在MySQL數(shù)據(jù)庫(kù)中,索引是一種關(guān)鍵的組件,它可以大大提高查詢(xún)的效率,但是,當(dāng)數(shù)據(jù)量增大或者查詢(xún)復(fù)雜度增加時(shí),索引的選擇和優(yōu)化變得至關(guān)重要,本文將記錄MySQL數(shù)據(jù)庫(kù)索引優(yōu)化的過(guò)程,以幫助開(kāi)發(fā)人員更好地理解和應(yīng)用索引優(yōu)化技巧2023-06-06