MySQL全量备份与增量备份-mysqldump&mysqlbinlog+binlog2sql

上周对测试环境的MySQL进行了一次迁移，学习了一些MySQL数据备份相关的知识。主要的操作步骤有4步，使用到了mysqldump、mysqlbinlog和binlog2sql这三个工具。

全量备份

mysqldump

1、全量备份数据库

mysqldump -uroot -proot --lock-all-tables --flush-logs --databases test > /mysqlbackup/test_`date`.sql
 --lock-all-tables：备份时为所有的表加锁
 --flush-logs：备份前刷新日志

以下是mysqldump的常用操作

#导出所有数据库
mysqldump -uroot -proot --all-databases >/tmp/all.sql

#导出指定数据库
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

#导出一个数据库的指定表
mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

#条件导出，如果多个表条件相同可以一次导出多个表
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql

#只导出表结构， --no-data 
mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

#导出其他服务器上的数据
mysqldump --host=h1 -uroot -proot --databases db1 >/tmp/db1.sql
#将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

增量备份

mysqlbinlog

2、保证bin-log已开启

修改my.cnf并且将以下参数加入其中,重启mysql实例

server-id=1 #由于bug,所以需要设置该参数.否则无法启动mysql实例 
log-bin = mysql-bin #其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径

查看binlog相关系统参数值

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /opt/mysql/data/mysql-bin       |
| log_bin_index                   | /opt/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | ON                              |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+
log_bin：binlog开启
log_bin_basename：binlog文件的前缀
log_bin_index：该文件记录所有的binlog文件的地址
log_bin_trust_function_creators：默认值为False，只有置为true时，用户才可以创建或修改存储函数
log_bin_use_v1_row_events：控制binlog中Rows_log_event的格式，默认是OFF，即使用v2版本的格式
sql_log_bin：动态变量，可以全局设置也可以针对当前的会话，置为OFF时会关闭binlog


mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 1048576              |
| binlog_checksum                         | CRC32                |
| bin