mysqldump備份和恢複MySQL資料庫

mysqldump備份和恢複MySQL資料庫

簡單介紹：

通過mysqldump命令可以將指定的庫和表全部匯出為sql指令碼，可以在不同版塊的Mysql上面使用。例如需要升級Mysql資料庫可以使用mysqldump先備份所有資料庫，然後直接在升級後的Mysql資料庫匯入即可。

基本操作：
備份單個資料庫，或者庫中的特定表（庫名後面加表名）
mysqldump備份jiaowu庫
[root@localhost ~]# mysqldump -uroot -p jiaowu > /root/jiaowu.sql
刪除jiaowu的資料庫
mysql> DROP DATABASE jiaowu;   
刪除之後匯入備份檔案說是沒有jiaowu資料庫
注意：mysqldump備份出來的資料庫都是插入語句，還原的時候沒有辦法建立資料庫需要手動建立資料庫
[root@localhost ~]# mysql < jiaowu.sql
ERROR 1046 (3D000) at line 22: No database selected
手動建立jiaowu的資料庫
mysql> CREATE DATABASE jiaowu;
還原jiaowu資料庫
[root@localhost ~]# mysql jiaowu < jiaowu.sql

如果是生產環境備份時需要鎖定所有表，不然在備份的時候有使用者寫入資料，會造成時間點不一樣
鎖定所有表
mysql> FLUSH TABLES WITH READ LOCK;
釋放鎖
mysql> UNLOCK TABLES;

參數說明：
--master-data={0|1|2}
    0: 不記錄二進位記錄檔記錄位置；
    1：以CHNAGE MASTER TO的方式記錄位置，可用於恢複後直接啟動從伺服器；
    2：以CHANGE MASTER TO的方式記錄位置，但預設為被注釋；

備份jiaowu資料庫
[root@localhost ~]# mysqldump -uroot -p --master-data=2 jiaowu > /root/jiaowu-`date +%F-%H-%M-%S`.sql

查看二進位日誌記錄位置
[root@localhost ~]# vim jiaowu-2014-11-27-17-02-38.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=9749;

--lock-all-tables：鎖定所有表

--flush-logs: 執行日誌滾動

如果指定庫中的表類型均為InnoDB，可使用--single-transaction啟動熱備，不要和--lock-all-tables一塊使用

備份多個庫： 備份的時候自動建立庫名，還原的時候不需要手動建立庫
    --all-databases: 備份所有庫
    --databases DB_NAME,DB_NAME,...: 備份指定庫

    --events 備份事件調度器的
    --routines 備份預存程序和儲存函數的
    --triggers 備份觸發器

類比實驗：備份所有庫，並且所有庫壞掉了如何還原
使用root使用者備份所有庫，滾動記錄檔，記錄二進位檔案位置和路徑，同時鎖定所有庫
[root@localhost ~]# mysqldump -uroot -p --lock-all-table --flush-logs --all-databases --master-data=2 > /root/alldatabases.sqlEnter password:

查看備份的資料庫發現滾動的日誌到了000007了
[root@localhost ~]# less alldatabases.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

刪除以前的滾動記錄檔（生產環境建議先複製然後再刪除）
mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000007 |      107 |
+------------------+-----------+

查看tutors表中資料
mysql> USE jiaowu;
Database changed
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|  1 | HongQigong  | M      |  93 |
|  2 | HuangYaoshi  | M      |  63 |
|  3 | Miejueshitai | F      |  72 |
|  4 | OuYangfeng  | M      |  76 |
|  5 | YiDeng      | M      |  90 |
|  6 | YuCanghai    | M      |  56 |
|  7 | Jinlunfawang | M      |  67 |
|  8 | HuYidao      | M      |  42 |
|  9 | NingZhongze  | F      |  49 |
+-----+--------------+--------+------+

刪除年齡大於80的行
mysql> DELETE FROM tutors WHERE Age>80;
Query OK, 2 rows affected (0.00 sec)

然後一天過去了，要做增量備份

滾動日誌
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000007 |      343 |
| mysql-bin.000008 |      107 |
+------------------+-----------+

備份記錄檔
[root@localhost ~]# cd /mydate/date/
[root@localhost date]# cp mysql-bin.000007 /root/

第二天往表中插入一行資料
mysql> INSERT INTO tutors (Tname) VALUES ('zhangsan');

刪除資料庫，但是把二進位記錄檔複製出去，假設記錄檔和資料不在同一目錄存放，不然如果連二進位記錄檔也刪除了就沒有辦法做時間點恢複了
[root@localhost date]# cp mysql-bin.000008 /root/
[root@localhost date]# rm -rf ./*

這時就會發現MySQL停止不了了，所以只要關閉進程
[root@localhost date]# service mysqld stop
MySQL server PID file could not be found!                  [失敗]
[root@localhost date]# killall mysqld

初始化MySQL資料庫
[root@localhost date]# cd /usr/local/mysql/
[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydate/date/

首先還原完全備份的資料庫檔案
[root@localhost ~]# mysql -uroot -p < alldatabases.sql
這時資料就恢複到了完全備份之前
被刪除的大於80歲的兩個使用者還存在
mysql> USE jiaowu;
Database changed
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|  1 | HongQigong  | M      |  93 |
|  2 | HuangYaoshi  | M      |  63 |
|  3 | Miejueshitai | F      |  72 |
|  4 | OuYangfeng  | M      |  76 |
|  5 | YiDeng      | M      |  90 |
|  6 | YuCanghai    | M      |  56 |
|  7 | Jinlunfawang | M      |  67 |
|  8 | HuYidao      | M      |  42 |
|  9 | NingZhongze  | F      |  49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)

將第一次和第二次備份的二進位檔案轉化為sql檔案，然後匯入第一次的增量備份和第二次的增量備份
[root@localhost ~]# mysqlbinlog mysql-bin.000007 > diyici.sql
[root@localhost ~]# mysqlbinlog mysql-bin.000008 > dierci.sql
[root@localhost ~]# mysql -uroot -p < diyici.sql
Enter password:
[root@localhost ~]# mysql -uroot -p < dierci.sql
Enter password:

這時資料就恢複了
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|  2 | HuangYaoshi  | M      |  63 |
|  3 | Miejueshitai | F      |  72 |
|  4 | OuYangfeng  | M      |  76 |
|  6 | YuCanghai    | M      |  56 |
|  7 | Jinlunfawang | M      |  67 |
|  8 | HuYidao      | M      |  42 |
|  9 | NingZhongze  | F      |  49 |
|  10 | zhangsan    | M      | NULL |
+-----+--------------+--------+------+

注意：生產環境：在恢複資料庫的時候關閉二進位記錄檔，不然就產生很多沒有用的記錄，資料恢複完成後開啟記錄二進位記錄檔
臨時性關閉二進位日誌記錄
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
開啟二進位記錄檔記錄
mysql> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

Linux下通過mysqldump備份MySQL資料庫成sql檔案

Linux中使用mysqldump對MySQL資料庫進行定時備份

mysqldump缺失-q參數導致MySQL被oom幹掉

mysqldump和LVM邏輯卷快照

MySQL備份方案-->(利用mysqldump以及binlog二進位日誌)

[MySQL] 用mysqldump製作文本備份

本文永久更新連結地址：