mysql備份與恢複之mysqldump

首先搭建測試環境，資料庫名稱為test1

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| t2              |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)
           

一、備份與恢複

全庫備份到/bak/test1_bak.sql

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4! --databases test1>/bak/test1_bak.sql
           

删除t1,t2表的記錄

mysql> delete from t1;
Query OK,  rows affected ( sec)
mysql> delete from t2;
Query OK,  rows affected ( sec)
           

查詢沒有資料

mysql> select * from t1;
Empty set ( sec)
mysql> select * from t2;
Empty set ( sec)
           

恢複記錄

/usr/local/mysql/bin/mysql -hlocalhost -P3306 -uroot -pMyNewPass4! test1</bak/test1_bak.sql
           

再查詢t1,t2表又有資料了

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)
           

二、備份指定表

隻備份t1表

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4! test1 t1>/bak/test1_t1_bak.sql
           

删除資料

mysql> delete from t1;
Query OK,  rows affected ( sec)

mysql> delete from t2;
Query OK,  rows affected ( sec)
           

恢複資料

/usr/local/mysql/bin/mysql -hlocalhost -P3306 -uroot -pMyNewPass4! test1</bak/test1_t1_bak.sql
           

可以看到隻有t1表被恢複了

mysql> select * from t1;
Empty set (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)
           

三、壓縮備份

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4! --databases test1|gzip>/bak/test1_t1_bak.sql.gz
           

還原壓縮備份

gunzip</bak/test1_bak.sql.gz|/usr/local/mysql/bin/mysql -hlocalhost -P3306 -uroot -pMyNewPass4! test1
           

四、導出表結構不導出資料

加上-d選擇，導出表結構

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4! -d test1>/bak/test1_s_bak.sql
           

删除表

mysql> drop table t1;
Query OK,  rows affected ( sec)

mysql> drop table t2;
Query OK,  rows affected ( sec)

mysql> show tables;
Empty set ( sec)

           

恢複表結構

/usr/local/mysql/bin/mysql -hlocalhost -P3306 -uroot -pMyNewPass4! test1</bak/test1_s_bak.sql
           

檢視表

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t1              |
| t2              |
+-----------------+
2 rows in set (0.00 sec)
           

表中沒有資料

mysql> select * from t1;
Empty set ( sec)

mysql> select * from t2;
Empty set ( sec)
           

五、增量備份

mysql增量備份的過程是先全量備份，全量備份後得到目前binlog的檢查點，進行恢複時先進行全量恢複，然後對檢查點之後的資料進行恢複，這樣資料就全部恢複了。

首先先删除t1表的記錄

mysql> delete from t1;
           

然後強制生成新的二進制日志，插入一條記錄

mysql> flush logs;
mysql> insert into t1 value(,'a');
Query OK,  row affected ( sec)
           

然後備份，加上參數–master-data=2

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4!  --master-data= --databases test1>/bak/test1_bak.sql
cat /bak/test1_bak.sql|grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000014', MASTER_LOG_POS=;
           

表示備份之前的修改都在binlog.000014的437

在備份之後，插入一條記錄，表示備份之後資料有變動

mysql> insert into t1 value(,'b');
flush logs;
           

然後删除資料

mysql> delete from t1;
           

這個時候指定binlog.000013，在位置437之後進行恢複，恢複後資料隻有(2,’b’)這一條

結果和預期一樣

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  2 | b    |
+----+------+
1 row in set (0.00 sec)