mysqldump備份和恢複Mysql資料庫

標籤：mysqldump

簡單介紹：

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

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

查看二進位日誌記錄位置
[[email protected] ~]# 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使用者備份所有庫，滾動記錄檔，記錄二進位檔案位置和路徑，同時鎖定所有庫
[[email protected] ~]# mysqldump -uroot -p --lock-all-table --flush-logs --all-databases --master-data=2 > /root/alldatabases.sqlEnter password:

查看備份的資料庫發現滾動的日誌到了000007了
[[email protected] ~]# 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 |
+------------------+-----------+

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

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

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

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

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


首先還原完全備份的資料庫檔案
[[email protected] ~]# 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檔案，然後匯入第一次的增量備份和第二次的增量備份
[[email protected] ~]# mysqlbinlog mysql-bin.000007 > diyici.sql
[[email protected] ~]# mysqlbinlog mysql-bin.000008 > dierci.sql
[[email protected] ~]# mysql -uroot -p < diyici.sql
Enter password:
[[email protected] ~]# 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)

本文出自 “梅花香自苦寒來” 部落格，請務必保留此出處http://wangjunkang.blog.51cto.com/8809812/1586092

mysqldump備份和恢複Mysql資料庫