标签：mysqlbinlog

结合mysqlbinlog与mysqldump进行MySQL数据备份与恢复（适用于较小的数据量）

通过mysqlbinlog工具备份二进制日志文件

mysqlbinlog  --read-from-remote-server --host=192.168.83.37 --port=55944 --user=us_yanzhaozhang --password --raw --to-last-log VMS00782-bin.000001

mysqlbinlog  --read-from-remote-server --host=192.168.83.37 --port=55944 --user=us_yanzhaozhang --password VMS00782-bin.000001  VMS00782-bin.000002  VMS00782-bin.000003 ...  VMS00782-bin.000017

上述两条命令会将mysqlbinlog连接到的主机中的由VMS00782-bin.000001开始至最后一个二进制日志文件备份到启动mysqlbinlog的目录中。其中–raw参数表明输出二进制格式的文件而非文本格式，–read-from-remote-serve参数表明要连接至该server请求其二进制日志。需要注意的是即使是在本地也需要列出–read-from-remote-server参数。

默认情况下，上述命令会在备份完成后立即断开连接，若想保持连接进行持续备份可添加–stop-never参数。

mysqlbinlog  --read-from-remote-server --host=192.168.83.37 --port=55944 --user=us_yanzhaozhang --password --raw --stop-never VMS00782-bin.000001

指定了–stop-never参数后无需再指定–to-last-log参数。

默认情况下，mysqlbinlog 备份的二进制日志文件保持原名，且存放在当前目录中。可通过–result-file参数改变这个行为，该参数指定了输出文件名的前缀，假定原名为VMS00782-bin.000009，若指定–result-file=x则输出
为xVMS00782-bin.000001，若指定–result-file=/backup/则输出为/backup/VMS00782-bin.000001,若指定–result-file=/backup/x则输出为/backup/xVMS00782-bin.000001。

使用mysqldump和mysqlbinlog进行备份和恢复

假定欲备份的主机中第一个二进制日志文件为VMS00782-bin.000010
首先使用mysqlbinlog进行二进制日志文件的持续备份

mysqlbinlog  --read-from-remote-server --host=192.168.83.37 --port=55944 --user=us_yanzhaozhang --password --raw --stop-never VMS00782-bin.000010

然后使用mysqldump创建备份文件

mysqldump --host=192.168.83.37 --port=55944 --user=us_yanzhaozhang --password --all-databases --events --routines --master-data=2 > dump.sql

若发生数据丢失则使用最近的备份来恢复数据

mysql --host=192.168.83.37 -uus_yanzhaozhang -p -P55944 < dump.sql

根据dump.sql备份文件中– CHANGE MASTER TO MASTER_LOG_FILE=’VMS00782-bin.000018′, MASTER_LOG_POS=27284语句中的二进制日志文件名和位置重新执行该坐标之后的二进制日志备份文件中的事件。

若最新的二进制日志备份文件为VMS00782-bin.000020，如下重新执行日志事件

mysqlbinlog --start-position=27284 VMS00782-bin.000018 VMS00782-bin.000019 VMS00782-bin.000020| mysql --host=192.168.83.37 -uus_yanzhaozhang -p -P55944

CentOS使用mysqlbinlog恢复MySQL数据库

如果不小心对数据库进行误操作，而又没有及时备份怎么办？这恐怕是广大的coder经常遇到的一类问题。 我今天就因为不小心删除了某个数据库，但最后的备份是1个礼拜前的，唯一能解决的办法就是通过mysqlbinlog来恢复了。解决方案如下：

1、如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始(例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。

2、要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。

3、一般可以从配置文件(一般情况，Linux下为my.cnf ，windows系统下为my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。

4、启用二进制日志的选项为–log-bin。

5、要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

SHOW BINLOG EVENTS G;
或者还可以从命令行输入下面的内容：
mysql –user=root -pmypasswd -e ‘SHOW BINLOG EVENTS G’ 将密码mypasswd替换为你的MySQL服务器的root密码。

6、比如得到的日志文件名为：

mysql-bin.000001 1. 指定恢复时间

对于MySQL5.1.54，可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间。

7、举例说明，比如在今天下午14:02(今天是2012年3月15日)，不小心执行SQL语句删除了一个数据表，但发现没有最新的备份（当然，这只是开发环境，并不是正式的生产环境，正式环境还得定时做数据备份）。要想恢复表和数据，可以通过mysqlbinlog恢复指定时间的备份，输入：

mysqlbinlog –stop-date=”2012-03-15 14:02:00″ /data1/log/mysql/mysql-bin.000001  | mysql -u root -pmypasswd

该命令将恢复截止到在–stop-date选项中以DATETIME格式给出的日期和时间的所有数据。

8、如果你没有检测到输入的错误的SQL语句，可能你想要恢复后面发生的数据库活动。 根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog –start-date=”2012-03-15 00:01:00″ /data1/log/mysql/mysql-bin.000001  | mysql -u root -pmypasswd

9、在该行中，从今天凌晨0:01登录的SQL语句将运行，组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到今天凌晨0:01前一秒钟。 你应检查日志以确保时间确切。

10、和–stop-date恢复指定时间段的数据库活动记录，如下：

mysqlbinlog –start-date=”2012-03-09 02:00:00″ –stop-date=”2012-03-15 14:00:00″ /data1/log/mysql/mysql-bin.000001 > /tmp/mysql_restore_030915.sql

通过这种方式，就能获取最后一个备份的文件时间2012-03-09 02:00:00到今天删除数据库之前2012-03-15 14:02这段时间的数据库活动事务操作