MySQL mysqldump 全量备份数据库和binlog增量备份

前言

数据库的主要作用就是对数据进行保存和维护，所以备份数据是数据库管理中最常用的操作。为了防止数据库意外崩溃或硬件损伤而导致的数据丢失，数据库系统提供了备份和恢复策略。

保证数据安全的最重要的一个措施就是定期的对数据库进行备份。这样即使发生了意外，也会把损失降到最低。

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

MySQL 中提供了两种备份方式，即 mysqldump 命令以及 mysqlhotcopy 脚本。由于 mysqlhotcopy 只能用于
MyISAM 表，所以 MySQL 5.7 移除了 mysqlhotcopy 脚本。

本节主要介绍如何使用 mysqldump 命令备份数据库。

mysql 全量备份(mysqldump)

文件存放位置

在mysql安装目录的bin下

备份数据库

备份一个数据库

使用 mysqldump 命令备份一个数据库的语法格式如下：

mysqldump -u username -p dbname [tbname ...]> filename.sql

对上述语法参数说明如下：

username：表示用户名称；
dbname：表示需要备份的数据库名称；
tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
右箭头“>”：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

注意:mysqldump 命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的。例如，后缀名为.txt的文件。通常情况下，建议备份成后缀名为.sql 的文件。因为，后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

例 1
下面使用 root 用户备份 test 数据库下的 student 表。打开命令行窗口，输入备份命令和密码，运行过程如下：

mysqldump -uroot -p test student>/data/mysql/student.sql

**注意：**mysqldump 命令必须在 窗口下执行，不能登录到 MySQL 服务中执行。

备份多个数据库

如果要使用 mysqldump 命令备份多个数据库，需要使用 --databases 参数。备份多个数据库的语法格式如下：

mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql

加上“–databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开。

例 2
下面使用 root 用户备份 test 数据库和 mysql 数据库。命令如下:

mysqldump -u root -p --databases test mysql>C:\testandmysql.sql

执行完后，可以在C:\下面看到名为 testandmysql.sql 的文件，这个文件中存储着这两个数据库的信息。

备份所有数据库

mysqldump 命令备份所有数据库的语法格式如下：

mysqldump -u username -P --all-databases>filename.sql

使用“–all-databases”参数时，不需要指定数据库名称。

例 3
下面使用 root 用户备份所有数据库。命令如下：

mysqldump -u root -p --all-databases >/data/mysql/all.sql

执行完后，可以在/data/mysql下面看到名为 all.sql 的文件，这个文件中存储着所有数据库的信息。

恢复数据

mysql 命令语法格式如下：

mysql -u username -P [dbname] < filename.sql

username 表示用户名称；
dbname 表示数据库名称，该参数是可选参数。如果 filename.sql 文件为 mysqldump 命令创建的包含创建数据库语句的文件，则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错；
右箭头“<”：备份数据表的定义和数据的备份文件写入 mysql中；
filename.sql 表示备份文件的名称。

注意：mysql 命令和 mysqldump 命令一样，都直接在命令行窗口下执行。
例 1
下面使用 root 用户恢复所有数据库，命令如下：

mysql -u root -p < /data/mysql/all.sql

执行完后，MySQL 数据库就已经恢复了 all.sql 文件中的所有数据库。

---

binlog 增量备份

文件相关配置

可以利用binlog进行增量备份，参数可以加在my.cnf（linux）或者my.ini(window版本)中。

参数说明

查看mysql下的 my.cnf 并设置

vi /etc/my.cnf，修改好后重启mysql。
server-id=111
log-bin=mysql-bin # 开启binlog功能
binlog-do-db=test #只对test数据库有效
说明：
server-id 此参数唯一

log_bin 设置此参数表示启用binlog功能，并指定路径名称

log_bin_index 设置此参数是指定二进制索引文件的路径与名称

binlog_do_db 此参数表示只记录指定数据库的二进制日志

可以使用show variables like’%log_bin%’;查看binlog开启的状态

可以使用**show master status，**可以查看binlog文件名，同步开始的位置。

可以用 show binlog events in ‘mysql_bin.000013’; 查看日志内容。

备份数据库

往test 数据库的userinfo表插入两条数据

insert into userinfo values('3','王三',25,'男');

insert into userinfo values('4','王小',25,'女');

日志文件存放在/var/lib/mysql，文件名为“mysql-bin.000001”，备份这个文件。

恢复数据库

常用还原命令

根据文件还原

mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -uroot -pxxxx

导入没有报错，说明导入成功。这时候我们查询userinfo表，发现后面插入的数据已经导入进去。

根据时间还原

–start-date,–stop-date

如：mysqlbinlog –start-date=”2010-09-29 18:00:00″ –stop-date=”2010-09-29 23:00:00″ /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

根据起始位置来还原

–start-position,–stop-position

如：mysqlbinlog –start-position=370 –stop-position=440 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

位置可以根据 show binlog events in 'mysql-bin.000001’去确定

根据数据库名来进行还原

-d

在这里是小写的d，请不要把它和mysqldump中的－D搞混了

如 ： mysqlbinlog -d test /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

根据数据库所占用的端口来分

－P

mysql用的不一定是3306端口，注意是大写的P

如： mysqlbinlog -P 13306 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

根据数据库serverid来还原

-serverid

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的

如 mysqlbinlog –server-id=1 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p

binlog删除

binlog 每次重启的时候都会产生一个新的binlog，或者手动执行 flush logs也会产生一个新的文件，这样产期可能会让磁盘爆掉，下面几种方法可以清除logs：

• 在my.cnf加入参数，加入参数需要重启mysql

expire_logs_days = x //二进制日志自动删除的天数。默认值为0,表示“没有自动删除”

• 如果不想重启mysql,可以进入mysql执行命令：

set global expire_logs_days = 10;

• 进入mysql，手动执行命令删除：

PURGE MASTER LOGS TO ‘mysql-bin.0001’; //清除mysql-bin.0001日志
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00’; //清除2008-06-22 13:00:00前binlog日志
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

• 进入mysql, 重置master，删除所有binlog文件

reset master;

重置master log对从服务器有影响，如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。