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, 2 rows affected (0.03 sec)
mysql> delete from t2;
Query OK, 2 rows affected (0.03 sec)

查询没有数据

mysql> select * from t1;
Empty set (0.00 sec)
mysql> select * from t2;
Empty set (0.00 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, 2 rows affected (0.04 sec)

mysql> delete from t2;
Query OK, 2 rows affected (0.06 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, 0 rows affected (0.02 sec)

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

mysql> show tables;
Empty set (0.01 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 (0.00 sec)

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

五、增量备份
mysql增量备份的过程是先全量备份，全量备份后得到当前binlog的检查点，进行恢复时先进行全量恢复，然后对检查点之后的数据进行恢复，这样数据就全部恢复了。
首先先删除t1表的记录

mysql> delete from t1;

然后强制生成新的二进制日志，插入一条记录

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

然后备份，加上参数–master-data=2

/usr/local/mysql/bin/mysqldump -hlocalhost -P3306 -uroot -pMyNewPass4!  --master-data=2 --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=437;

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

在备份之后，插入一条记录，表示备份之后数据有变动

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

然后删除数据

mysql> delete from t1;

这个时候指定binlog.000013，在位置437之后进行恢复，恢复后数据只有(2,’b’)这一条

/usr/local/mysql/bin/mysqlbinlog --start-position=437 /data/mysql/binlog.000013|/usr/local/mysql/bin/mysql -hlocalhost -uroot -pMyNewPass4! -P3306

结果和预期一样

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