mysqldump對mysql資料庫的影響

2020-09-05 架構師技術之棧


對於想入門或者初級，中級mysql資料庫運維人員，了解mysqldump對mysql資料庫的影響，是非常必要的，當執行mysqldump命令之後，mysql後臺執行了什麼，下面就帶大家看看，在這裡使用general_log進行分析

1.首先的開啟資料庫的general_log，如下所示

[root@localhost] 17:30:41 [(none)]>show variables like &39;;+------------------+---------------------------------+| Variable_name | Value |+------------------+---------------------------------+| general_log | OFF || general_log_file | /data/mysql/data/3306/mysql.log |+------------------+---------------------------------+2 rows in set (0.00 sec)開啟general_log[root@localhost] 17:41:40 [(none)]>set global general_log=on;Query OK, 0 rows affected (0.42 sec)[root@localhost] 17:41:50 [(none)]>show variables like &39;;+------------------+---------------------------------+| Variable_name | Value |+------------------+---------------------------------+| general_log | ON || general_log_file | /data/mysql/data/3306/mysql.log |+------------------+---------------------------------+2 rows in set (0.00 sec)

2.執行mysqldump備份命令

mysqldump --socket=/data/mysql/run/3306/mysql.sock --skip-tz-utc --all-databases --single-transaction -ER -u root -proot> db_script.sql

執行之後，看看general_log的內容

2020-08-20T17:44:58.473668+08:00 6 Connect root@localhost on using Socket2020-08-20T17:44:58.473911+08:00 6 Query /*!40100 SET @@SQL_MODE=&39; */2020-08-20T17:44:58.474234+08:00 6 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2020-08-20T17:44:58.474325+08:00 6 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2020-08-20T17:44:58.474514+08:00 6 Query SHOW VARIABLES LIKE &39;2020-08-20T17:44:58.478784+08:00 6 Query SELECT @@GLOBAL.GTID_EXECUTED2020-08-20T17:44:58.478972+08:00 6 Query UNLOCK TABLES

備份命令首先設置會話的隔離級別為RR，然後開始設置事物一致性快照，看到這裡，有同學會問，為什麼後臺沒有執行FTWRL（flush table with read lock），這是因為備份的時候沒有添加--master-data參數，那看看添加這個參數之後，會有什麼不同呢

mysqldump --socket=/data/mysql/run/3306/mysql.sock --master-data=2 --skip-tz-utc --all-databases --single-transaction -ER -u root -proot> db_script.sql

再次看看general_log的內容

2020-08-20T17:52:33.612990+08:00 7 Connect root@localhost on using Socket2020-08-20T17:52:33.613504+08:00 7 Query /*!40100 SET @@SQL_MODE=&39; */2020-08-20T17:52:33.614493+08:00 7 Query FLUSH /*!40101 LOCAL */ TABLES2020-08-20T17:52:33.617383+08:00 7 Query FLUSH TABLES WITH READ LOCK2020-08-20T17:52:33.617559+08:00 7 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2020-08-20T17:52:33.617628+08:00 7 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2020-08-20T17:52:33.617804+08:00 7 Query SHOW VARIABLES LIKE &39;2020-08-20T17:52:33.625696+08:00 7 Query SELECT @@GLOBAL.GTID_EXECUTED2020-08-20T17:52:33.625876+08:00 7 Query SHOW MASTER STATUS2020-08-20T17:52:33.626051+08:00 7 Query UNLOCK TABLES

這裡就會發現，多了2個操作
FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK

這是因為添加--master-data參數，是表示從複製的主庫上導出備份，可以利用此 備份文件直接建立另一個複製從庫，FLUSH TABLES WITH READ LOCK主要用於備份工具獲取一致性備份(數據與binlog位點匹配 )。

在這裡非常有必要了解一下FLUSH TABLES WITH READ LOCK，因為操作不好，所有DML，DDL，for Update都會被堵塞，造成業務無法做任何操作

FTWRL主要包括3個步驟：
1.上全局讀鎖(lock_global_read_lock)
2.清理表緩存(close_cached_tables)
3.上全局COMMIT鎖(make_global_read_lock_block_commit)

在第一個步驟中，由於上的是全局讀鎖，他會堵塞所有的DML，DDL，for Update操作，但是這一步非常快，業務感覺不到

在第二個步驟中，清理表緩存，在這裡需要注意了，如果有一個慢查詢，FTWRL就會被堵塞了，而且，涉及的表上所有的操作，是所有的操作，包括select，都會被堵塞，而且現在就算終止mysqldump備份命令，堵塞也不會解除，除非你kill掉慢查詢語句，所以在凌晨做備份的同學，可以要注意了

在第三個操作中，會申請全局commit鎖，如果這個時候有大事物，長時間沒有提交，FTWRL就會被堵塞了

綜上所述，在執行mysqldump命令之前，先檢查一下，資料庫中是否有慢sql，長時間未提交的大事物。


相關焦點

  • mysqldump對mysql資料庫的影響
    對於想入門或者初級，中級mysql資料庫運維人員，了解mysqldump對mysql資料庫的影響，是非常必要的，當執行mysqldump命令之後，mysql後臺執行了什麼，下面就帶大家看看，在這裡使用general_log進行分析1.首先的開啟資料庫的general_log，如下所示[root@localhost] 17:30:41 [(none
  • MySQL-mysqldump備份資料庫
    mysqldump備份1、備份命令格式：mysqldump -h主機名 -P埠 -u用戶名 -p密碼 --database 資料庫名 > 文件名.sql 例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database
  • MySQL資料庫的mysqldump完全備份、binlog的增量備份與還原
    備份的資料庫大小不超過50G三、 按照備份時對資料庫的影響範圍分類：hot backup ：指在資料庫運行中直接備份，對正在運行的資料庫沒有任何影響；稱為 在線備份(OnlineBackup） （備份的同時，業務不受影響）
  • mysqldump簡單備份
    對於同一款資料庫來講，它的遷移方式還是比較簡單的mysql -> mysql比較麻煩的是異構資料庫之間的遷移。此處還包括了作業系統不同的遷移其他 -> mysqlmysql -> 其他備份的介紹備份類型：熱備：對於業務影響小 InnoDB溫備：長時間鎖表備份 MyISAM冷備：業務關閉情況下備份備份基本參數mysqldump命令將資料庫中的數據備份成一個文本文件。
  • MySQL - mysqldump常用命令 - linux運維菜
    導出數據1、備份全部資料庫的數據和表結構mysqldump -uroot -ppassword -A >all.sql2、只備份表結構，不備份數據mysqldump -uroot -ppassword -A -d > database.sql
  • 常用的mysql備份、mysqldump的shell應用
    【本文閱讀需要shell編程和mysql基礎】作為一個運維經常收到開發同學導出線上數據到測試資料庫的需求。工作情景：開發同學【小A】收到測試【小B】提的線上的bug。【小A】同學，火速check了master分支，在本地調試。但發現測試環境的數據並沒有類似的錯誤。【小A】跑到運維那裡。
  • MySQL - mysqldump 命令參數
    mysqldump是MySQL自帶的用於轉存儲資料庫的程序。mysqldump有很多參數，但是常用的就那麼幾個。--all-databases , -A導出所有的庫裡面的數據。(默認為打開狀態)--databases, -B導出幾個資料庫。參數後面所有名字參量都被看作資料庫名。--debug輸出debug信息，用於調試。默認值為：d:t:o,/tmp/mysqldump.trace--debug-check檢查內存和打開文件使用說明並退出。
  • MySQL5.7 使用 mysqldump 重要避坑事項
    擅長資料庫故障處理。對資料庫技術和 python 有著濃厚的興趣。mysqldump：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.htmlmysqldump --databases --routines sys > sys_dump.sqlmysqlpump sys > sys_dump.sql
  • MySQL教程之MySQL定時備份資料庫
    一、MySQL數據備份1.1、 mysqldump命令備份數據在MySQL中提供了命令行導出資料庫數據以及文件的一種方便的工具mysqldump,我們可以通過命令行直接實現資料庫內容的導出dump,首先我們簡單了解一下mysqldump命令用法:#MySQLdump常用mysqldump -u root -p --databases
  • 改寫 mysqldump 解決 DEFINER 問題
    使用 mysqldump 導出備份，然後將 SQL 文件中的對象所有者替換為導入帳號。只能看下是否可以從 mysqldump 源碼中去掉 DEFINER 定義。本次 mysqldump 改寫主要有 2 個目的：1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義；2.
  • MySQL資料庫怎麼升級 MySQL資料庫升級教程
    邏輯方式升級邏輯方式升級其實就是通過邏輯備份工具(例如mysqldump工具)將資料庫、表、其他相關對象及數據邏輯備份成SQL腳本，再將其還原至MySQL5. 7 的實例中。詳細步驟如下：1.1  備份資料庫當前資料庫的版本為MySQL5.6.27，現在準備備份
  • ​定時備份mysql資料庫
    定時備份mysql資料庫源碼https://gitee.com/pingfanrenbiji/docker-mysql-auto-backup=ry-vue -e MYSQL_DB_PORT=PORT registry.cn-shanghai.aliyuncs.com/mengfanxiao/docker-mysql-auto-backup:v1.0.0原理簡介使用的mysql的導出工具 mysqldump
  • 改寫mysqldump 解決 DEFINER 問題-愛可生
    使用 mysqldump 導出備份，然後將 SQL 文件中的對象所有者替換為導入帳號。只能看下是否可以從mysqldump 源碼中去掉 DEFINER 定義。 本次mysqldump 改寫主要有 2 個目的： 1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義； 2.
  • 改寫 mysqldump 解決 DEFINER 問題-愛可生
    使用 mysqldump 導出備份，然後將 SQL 文件中的對象所有者替換為導入帳號。只能看下是否可以從 mysqldump 源碼中去掉 DEFINER 定義。本次 mysqldump 改寫主要有 2 個目的：1. 摘取備份中視圖、函數、存儲過程、觸發器等對象的 DEFINER 定義；2.
  • mysqldump備份時的數據一致性問題
    原標題：mysqldump備份時的數據一致性問題作者：魏新平，知數堂優秀校友。在日常運維當中，經常會用到mysqldump。使用mysqldump導出數據的時候，我們最關心的問題之一就是表的一致性。簡單的說就是所有表是不是同一時間的數據和結構。
  • linux基礎應用(MySQL資料庫)
    'mysql的密碼' 設置mysql密碼註：my.cnf所有參數詳解請看官方文檔MySQL升級1、備份之前的mysql數據備份方式一：linux-szge:~ #mysqldump -uroot -p 資料庫名 > /home/資料庫名.sql備份方式二：linux-szge:~ #mv /home/mysqldatas /home/mysqldatas.bak
  • Mysql資料庫備份和還原常用的命令
    備份MySQL資料庫的命令mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql備份MySQL資料庫為帶刪除表的格式備份MySQL資料庫為帶刪除表的格式，能夠讓該備份覆蓋已有資料庫而不需要手動刪除原有資料庫
  • 使用docker備份mysql資料庫
    所以，資料庫備份在生產上是必不可少的，下面我們來了解下在docker環境下mysql資料庫的備份是如何做的。docker mysql容器實例二、定時備份資料庫為sql腳本我們每日進行定時備份資料庫，生成sql腳本。
  • mysql資料庫導入和導出
    步驟如下：一.mysql的命令行模式的設置：首先，安裝mysql資料庫，若是手動安裝，請將路徑添加進環境變量，如下桌面->我的電腦->屬性->環境變量->新建->PATH=「;path\MySQL\bin;」其中path為mysql的安裝路徑。
  • 關於mysqldump，這個參數你可能還不知道
    比如我們資料庫採用北京時間東八區，mysqldump 導出的文件當中顯示的 timestamp 時間值相對於通過資料庫查詢顯示的時間倒退了8個小時。2.實驗參數具體作用為了更清楚了解這對參數的作用，下面我們來具體測試下，我們知道 mysqldump 後可以跟 where 條件來備份部分數據，若根據 timestamp 欄位來備份部分數據，這對參數是否有影響呢？