mysql日志恢复数据_mysql利用binlog日志恢复数据操作记录

1，binlog日志：mysql的二进制日志，它记录了所有的DDL 和DML 语句，以事件形式记录，还包括了语句所执行的时间，mysql的二进制日志是事务安全性的。

DDL--Data Definition Language 数据库定义语言

主要命令有CREATE、ALTER、DROP等，主要是用在定义或者改变表的结构，数据类型，

表之间的链接和约束等初始化工作上

DML--Data Manipulation Language 数据库操纵语言

主要命令有SELECT，UPDATE， INSERT，DELETE，用来对数据库里的数据进行操作

mysqlbinlog常见的选项有以下几个：

--start-datetime：从二进制日志读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime:从二进制日志读取指定小于时间戳或者等于本地计算机的时间

--start-position:从二进制日志中读取指定position事件位置作为开始

--stop-position:从二进制日志中读取指定position时间位置作为事件截止位置

一般来说开启binlog日志会有大概1%的性能损耗

binlog日志有两个重要使用场景

1，mysql主从复制：

Mysql Replication在Maste端开启binlog， Master把它的二进制日志传递给Slaves来达到

master-slaves数据一致的目的

2，数据恢复，使用mysqlbinlog工具来使数据恢复

binlog日志包括两类文件：

a,二进制日志索引文件，用于记录所有的二进制文件(后缀名为.index)

b,二进制日志文件，记录数据库所有的DDL和DML(除select)语句事件(后缀为.0000*)

开启binlog日志

1)打开编辑 mysql配置文件 /etc/my.cnf

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# vim /etc/my.cnf

在mysqld区块添加

log-bin=mysql-bin

2)重启mysql服务使之生效

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# /etc/init.d/mysqld stop

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

3)查看binlog日志是否开启

mysql> show variables like 'log_%';

+----------------------------------------+---------------------------------------+

| Variable_name | Value |

+----------------------------------------+---------------------------------------+

| log_bin | ON |

| log_bin_basename | /mnt/data/mysql-bin |

| log_bin_index | /mnt/data/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_error | /mnt/data/iZ2zeim71fvtr79a4hpx1lZ.err |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| log_throttle_queries_not_using_indexes | 0 |

| log_warnings | 1 |

+----------------------------------------+---------------------------------------+

13 rows in set (0.00 sec)

常用binlog日志操作命令

1)查看所有binlog日志

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 128921 |

| mysql-bin.000002 | 14661 |

| mysql-bin.000003 | 143 |

| mysql-bin.000004 | 66209 |

| mysql-bin.000005 | 52771 |

| mysql-bin.000006 | 143 |

| mysql-bin.000007 | 5670 |

| mysql-bin.000008 | 53782912 |

+------------------+-----------+

8 rows in set (0.00 sec)

2)查看master状态，即最后(最新)一个binlog日志的编号名称及其最后一个操作时间pos结束点值

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000008 | 53784272 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

3)刷新log日志，自此刻起开始产生一个新编号的binlog日志

mysql> flush logs;

Query OK, 0 rows affected (0.09 sec)

注意：当mysql服务器重启时，会自动执行此命令，刷新binlog日志

在mysqldump备份时加 -F 选项也会刷新binlog日志

4)重置清空所有日志

mysql> reset master;

Query OK, 0 rows affected (0.12 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 106 |

+------------------+-----------+

1 row in set (0.00 sec)

查看binlog日志内容，常用有两种方法：

1)使用mysqlbinlog自带命令查看

注意：a)binlog日志是二进制文件，普通的文件查看命令cat、vim、more等都无法打开，

必须使用自带的mysqlbinlog命令查看

b)binlog日志文件与数据库文件在同目录

c)mysql5.5以下版本使用mysqlbinlog出错时添加 --no-defaults 选项

查看mysql数据日志存放目录 --data-dir

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# ps -ef | grep mysql

root 1513 1 0 Aug22 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/mnt/data --pid-file=/mnt/data/iZ2zeim71fvtr79a4hpx1lZ.pid

mysql 1684 1513 0 Aug22 ? 00:25:49 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mnt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mnt/data/iZ2zeim71fvtr79a4hpx1lZ.err --pid-file=/mnt/data/iZ2zeim71fvtr79a4hpx1lZ.pid

root 10671 9436 0 10:42 pts/0 00:00:00 grep mysql

使用mysqlbinlog查看日志文件内容

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# mysqlbinlog mysql-bin.000002

# at 67353

#170816 17:13:10 server id 1 end_log_pos 67436 CRC32 0xbe1d0ec9

Querythread_id=3631exec_time=0error_code=0

SET TIMESTAMP=1502874790/*!*/;

BEGIN

/*!*/;

# at 67436

#170816 17:13:10 server id 1 end_log_pos 67579 CRC32 0x6a4a8e76

Querythread_id=3631exec_time=0error_code=0

SET TIMESTAMP=1502874790/*!*/;

UPDATE `users_ext` SET `longitude`='116.417526' WHERE (`uid`='4')

/*!*/;

# at 67579

#170816 17:13:10 server id 1 end_log_pos 67610 CRC32 0x6299412b

Xid = 15199

COMMIT/*!*/;

解释：

server id 1 ： 数据库主机的服务号；

end_log_pos 796： sql结束时的pos节点

thread_id=11： 线程号

2)上面这种方式读取的binlog日志内容比较多，不容易分辨查看pos点信息

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] ：偏移量(不指定就是0)

row_count ：查询总条数(不指定就是所有行)

mysql> show binlog events in 'mysql-bin.000009'\G;

*************************** 186. row ***************************

Log_name: mysql-bin.000009

Pos: 19401

Event_type: Query

Server_id: 1

End_log_pos: 19711

Info: use `line_test`; INSERT INTO `services_data`

(`source_id` , `comment_count` , `avg_star` , `hot`)

SELECT '65','1','5.0000','1' UNION ALL SELECT '66','3','5.0000','3' UNION ALL

SELECT '71','2','5.0000','2' UNION ALL SELECT '72','1','5.0000','1'

*************************** 187. row ***************************

Log_name: mysql-bin.000009

Pos: 19711

Event_type: Xid

Server_id: 1

End_log_pos: 19742

Info: COMMIT /* xid=2257658 */

187 rows in set (0.00 sec)

ERROR:

No query specified

面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，

并可使用limit指定pos点的起始偏移，查询条数！

重点来啦！！！利用binlog日志回复数据

下面进行场景模拟:

line_test 会在每天凌晨4点进行一次完全备份的定时计划任务

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# crontab -l

0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 line_test |gzip

\n >/mnt/backup/line_test_$(date +%F).sql.gz

手动执行以下

[root@iZ2zeim71fvtr79a4hpx1lZ mnt]# /usr/local/mysql/bin/mysqldump -uroot -p -B -F

\n -R -x --master-data=2 line_test |gzip >/mnt/backup/line_test_$(date +%F).sql.gz

Enter password:

[root@iZ2zeim71fvtr79a4hpx1lZ mnt]# ls /mnt/backup/

line_test_2017-11-08.sql.gz

参数说明：

-B：指定数据库

-F：刷新日志

-R：备份存储过程等

-x：锁表

--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

出问题时，先备份最后一个binlog

[root@iZ2zeim71fvtr79a4hpx1lZ mnt]# cp -v /mnt/data/mysql-bin.000010 /mnt/backup/

`/mnt/data/mysql-bin.000010' -> `/mnt/backup/mysql-bin.000010'

刷新日志信息生成新的日志

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

查看新日志信息

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000011 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

恢复已备份好的数据：

解压缩备份文件

[root@iZ2zeim71fvtr79a4hpx1lZ backup]# ls

line_test_2017-11-08.sql.gz mysql-bin.000010

You have mail in /var/spool/mail/root

[root@iZ2zeim71fvtr79a4hpx1lZ backup]# gzip -d line_test_2017-11-08.sql.gz

[root@iZ2zeim71fvtr79a4hpx1lZ backup]# ls

line_test_2017-11-08.sql mysql-bin.000010

恢复备份好的数据

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# mysql -uroot -p -v < line_test_2017-11-08.sql

读取binlog信息，分析日志回复出现问题的数据

1)使用mysqlbinlog读取binlog日志：

[root@iZ2zeim71fvtr79a4hpx1lZ mnt]# mysqlbinlog --no-defaults backup/mysql-bin.000010

2)登录服务器并查看(推荐)

mysql> show binlog events in 'mysql-bin.000010';

mysql> show binlog events in 'mysql-bin.000010'\G;

查看日志发现

[root@iZ2zeim71fvtr79a4hpx1lZ backup]# /usr/local/mysql/bin/mysqlbinlog

\n --no-defaults --stop-position=6628 --database=line_test

\n /mnt/data/mysql-bin.000013 | /usr/local/mysql/bin/mysql -u root -p -v line_test

按照时间回复数据

[root@iZ2zeim71fvtr79a4hpx1lZ ~]# /usr/local/mysql/bin/mysqlbinlog

\n --no-defaults --start-datetime='2017-11-08 21:58:56'

\n --stop-datetime='2017-11-08 23:58:56' --database=line_test

\n /mnt/data/mysql-bin.000013 | /usr/local/mysql/bin/mysql -u root -p -v line_test

Xtrabackup ：一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)

一个菜鸟的操作日志记录，仅供参考，欢迎指正！！！