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)