mysql 数据备份和恢复

如果你需要热备份，参考:
https://www.percona.com/doc/percona-xtrabackup/LATEST/intro.html

环境及要求:
ubuntu 16.04
mysql 5.6.4
mysql 开启 binlog （我的binlog 格式是 mixed, row 格式没有测试。）

方案描述:
如果你打算在生产环境实现这篇文章内容，请先磁盘快照后者备份原数据，并谨慎操作。
通过备份脚本每日备份数据库，在需要恢复数据时, 通过最新的备份数据恢复数据到备份时刻, 然后在基于该版本，回放binlog 来完成数据库恢复。

1、mysqldump + crontab 实现每日数据备份脚本

#!/bin/bash

user='数据库用户名'
password='数据库密码'
dbname='数据库名'
date=`date +%Y%m%d`
year=`date +%Y`
month=`date +%m`
day=`date +%d`

# 创建备份文件目录和备份文件名
backup_dir=/archive/data/$year/$month/$day
filename=$dbname-${date}.sql.gz

# mkdir directory recursely
if [ ! -d $backup_dir ]; then
	mkdir -p $backup_dir
fi

# single-transaction quick master-data 三个参数的含义详见文底摘录的官方描述 -- 注意 master-data后面的"="两端不能有空格，master-data=1会在备份文件头部添加一条语句，标明备份文件的坐标，而这个坐标可以作为二进制日志恢复的起点。(后文详述)
/www/server/mysql/bin/mysqldump --single-transaction --quick --master-data=1 -u"$user" -p"$password" "$dbname" | gzip > ${backup_dir}/$filename

2、mysql 备份数据库的恢复

mysql -uroot -p backup<backup.sql

3、其他

可能使用达到的命令:

查看 MySQL 的位置:
SELECT @@datadir;

查看  mysql 是否开启 binglog

show variables like 'log_bin';

windows 开启 binglog 在 [mysqld] 下添加如下内容:
# Binary Logging.
# log-bin
log_bin = mysql-bin
# log_bin = C:\ProgramData\MySQL\MySQL Server 5.7\Data\log\bin_log
binlog_format = mixed


查看 binlog 格式
show variables like 'binlog_format'	

查看 Master 数据库当前正在使用的二进制日志和坐标
show master status;

一个完整的数据库备份和恢复的例子

1、创建测试数据库 test、创建测试表 t1、并插入几条数据

create database test;

use test;

create table t1(
	id int primary key auto_increment,
	name varchar(100)
);

insert into t1 (name) values ('Jack Ma'), ('Poly Liu'), ('Uncle Wang');

2、备份数据

# 如果你的 mysqldump 不在环境变量中，这里写上完整的命令路径 /usr/local/mysql/bin/mysqldump (根据自己实际情况写)
mysqldump -uroot -p test |gzip > test.sql.gz # 这里备份文件压缩里以节省磁盘空间

3、业务需要又新增和修改了一些数据

update t1 set name = "Mr Liu" where id = 2;
delete from t1 where id = 1;

修改后的数据:

4、模拟意外的失误删除数据

delete from t1;

5、使用备份数据恢复数据
a、新建一个临时实例，在该实例新建同名数据库 test（后面用"测试数据库"简称。）， 用于恢复数据.

create database test;

b、解压备份最新的数据备份文件（真实的备份文件是日期命令的）

gzip -d test.sql.gz

c、将解压的数据文件恢复到上面创建的 test数据库

mysql -uroot -p test<test.sql

d、检查恢复的数据库是否是最新的，且完好正常的数据

6、回放binlog, 恢复从备份文件和数据被意外删除前一刻的差异数据。
a、将binlog转成文本文件（如果binlog 比较大，也可以在恢复的时候用两次恢复跳过问题语句，–start-position 和 --stop-position可以很好的完成辅助）, （因为binlog有一我们误操作的语句，所以我们需要先编辑(编辑并注释我们的误操作语句)一下，再回放。）
(1) 查看 binlog 文件前缀 (也可以在 my.cnf 配置中查看)

(2) 查看 mysql 数据位置

# 将 binlog 转成文本。( mysqlbinlog 没配置环境变量，这里写绝对路径.)
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.[0-9]* > /archive/binlog_text

b、编辑 binlog_text, 注释掉我们误操作的语句

c、在测试数据库 test 播放 binlog_text 文件.

通过分析，我们在 binlog_text 中找出播放起始的位置, 开始增量恢复:

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.[0-9]* --start-position=648 --stop-position=1033 | mysql -uroot -p test 

补充:
–single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

To dump large tables, combine the --single-transaction option with the --quick option.

–quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

–master-data[=value]

Use this option to dump a source replication server to produce a dump file that can be used to set up another server as a replica of the source. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the source server coordinates from which the replica should start replicating after you load the dump file into the replica.

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

–master-data sends a SHOW MASTER STATUS statement to the server to obtain information, so it requires privileges sufficient to execute that statement. This option also requires the RELOAD privilege and the binary log must be enabled.

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

It is also possible to set up a replica by dumping an existing replica of the source, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

更多参考MySQL 官方文档: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_master-data