MySQL - 数据备份与恢复

备份

[options]: -h主机名 -P端口 -u用户名 -p密码

1. 备份 MySQL 数据库
$ mysqldump [options] 数据库名 > backup.sql
或
$ mysqldump [options] -database 数据库名 > backup.sql

2. 备份 MySQL 数据库 + 删除表 ( --add-drop-table )
$ mysqldump --add-drop-table [options] -database 数据库名 > backup.sql

3. 直接将 MySQL 数据库 压缩备份 ( gzip )
$ mysqldump [options] -database 数据库名 | gzip > backup.sql.gz

4. 备份 MySQL 数据库里的表
$ mysqldump [options] 数据库名 表名1 表名2 > backup.sql

5. 同时备份多个 MySQL 数据库
$ mysqldump [options] -databases 数据库名1 数据库名2 数据库名3 > multi_backup.sql

6. 备份数据库结构 ( --no-data )
$ mysqldump --no-data [options] -databases 数据库名1 数据库名2 数据库名3 > struct.sql

7. 备份所有数据库 ( --all-databases )
$ mysqldump [options] --all-databases > all_backup.sql

恢复

1. 还原 MySQL 数据库

$ mysql [options] 数据库名 < backup.sql

2. 还原压缩的 MySQL 数据库

$ gunzip < backup.sql.gz | mysql [options] 数据库名

3. 将数据库转移到新服务器

$ mysqldump [options] 数据库名 | mysql --host=*.*.*.* -C 数据库名

4. 在 mysqldump 中使用 --master-data 和 --single-transaction

--master-data=2 : 会记录 binlog 文件和 position 的信息
--single-transaction : 会将隔离级别设置成 repeatable-commited

5. 导入数据库

常用 source 命令, 用 use 进入到某个数据库, mysql> source C:\test.sql

6. 查看 binlog 日志

$ mysqlbinlog 日志名 | more

7. general_log - 记录数据库的任何操作

- 查看 general_log 的状态和位置 : show variables like "general_log%",

- 开启 general_log : set global general_log=on

增量备份

1. 首先做一次完整备份

$ mysqldump [options] --single-transaction --master-data=2 数据库名 > backup.sql

在 backup.sql 文件中可看到

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;

是指备份后所有的更改将会保存到 bin-log.000002 二进制文件中

2. 在 数据库名 库的 t_student 表中增加两条记录, 然后执行 flush logs 命令

这时将会产生一个新的二进制日志文件 bin-log.000003,
bin-log.000002 则保存了全备份过后的所有更改,
既增加的记录操作也保存于 bin-log.00002 中

3. 再 数据库名 库中的 a 表中增加 2 条记录, 然后误删除 a 和 t_student 表

以上操作也记录在 bin-log.000003 中

恢复数据 + binlog 日志

1. 首先导入全备数据

$ mysql [options] < backup.sql

2. 恢复 binlog 日志 bin-log.000002

$ mysqlbinlog bin-log.000002 | mysql [options]

或
$ mysql [options] < bin-log.000002

3. 恢复 binlog 日志 bin-log.000003

在 general_log 中找到误删除的时间点,

然后更加对应的时间点到 bin-log.000003 中找到相应的 position 点,

需要恢复到误删除的前面一个 position 点

可以用如下参数来控制 binlog 的区间

开始点 --start-position

结束点 --stop-position

开始时间 --start-date 

结束时间 --stop-date

找到恢复点后, 既可以开始恢复

$ mysqlbinlog --start-position=208 bin-log.000003 | mysql [options]