binlog常用的命令

#备份全部数据库数据
mysqldump --all-databases > dump.sql
#备份指定数据库数据
mysqldump --databases db1 db2 db3 > dump.sql
#备份数据库中的表数据
mysqldump test t1 t3 t7 > dump.sql
#恢复备份数据
mysql < dump.sql
#查看日志开启状态 
show variables like '%log_bin%';
#查看binlog存放日志文件目录
show variables like '%datadir%';
#查看所有binlog日志列表
show master logs;
#查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 
show master status;
#刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 
flush logs;
#清空所有binlog日志 
reset master;
#根据日志查询删除命令的日志点
mysqlbinlog mysql-bin.000001 | grep -5a "DROP TABLE"
#可以将二进制binlog日志文件转为可阅读的sql语句
mysqlbinlog mysql-bin.000001  > 000001.sql 
	[ERROR]  The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. 解决方法：  --skip-gtids
	[ERROR] unknown variable 'default-character-set=utf8'解决方法
	在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大
	用mysqlbinlog --no-defaults mysql-bin.000001 命令打开	mysqlbinlog --no-defaults mysql-bin.000001 > 000001.sql

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
show binlog events in 'mysql-bin.000001' limit 10
show binlog events in 'mysql-bin.000002' from 444 limit 0,5
	IN ‘log_name’ 指定要查询的binlog文件名(不指定就是第一个binlog文件)
	FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
	LIMIT [offset,] 偏移量(不指定就是0)
	row_count 查询总条数(不指定就是所有行)
	
使用.sql文件全量恢复
mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql
mysql -uroot -p123456  < /opt/backup/000003.sql
指定pos结束点恢复(部分恢复)
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
mysqlbinlog ––start-position=875 --stop-position=571 --database=codehui /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p123456 -v codehui
mysqlbinlog --start-datetime="2019-03-25 10:30:30" --stop-datetime="2019-03-25 11:10:10" --database=codehui mysql-bin.000001 | mysql -u root -p
	––start-position=875 起始pos点
	––stop-position=954 结束pos点
	––start-datetime=“2016-9-25 22:01:08” 起始时间点
	––stop-datetime=“2019-9-25 22:09:46” 结束时间点
	––database=xxx指定只恢复xxx数据库(一台主机上往往有多个数据库，只限本地log日志)

在数据库开启binlog功能

找到/etc/my.cnf并编辑（没有my.cnf的时候就找my.ini）；添加
 log-bin=mysql-bin 
 expire_logs_days=7(日志保留天数)
然后重启mysql

my.ini配置文件

[mysql] 	#客户端基本设置
# 默认编码
default-character-set=utf8
# 设置3306端口
port = 3306
[mysqld]	#服务端基本设置
#无密码登录  skip-grant-tables

# 设置mysql的安装目录
basedir=D:\\Program Files\\MySQL\\mysql-5.7.27-winx64
# 设置mysql数据库的数据的存放目录（自动生成，不然可能报错）
datadir=D:\\Program Files\\MySQL\\mysql-5.7.27-winx64\\data
# 允许最大连接数
max_connections=200
# 允许最大连接人数
max_user_connections=1000
#运行连接失败的次数。这也是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
#服务端默认编码（数据库级别）
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 慢查询sql日志的文件地址
slow_query_log_file=D:\\Program Files\\MySQL\\mysql-5.7.27-winx64\\log\\mysql-slow.log
#慢查询执行的秒数，超过这个值则会被记录到慢查询日志
long_query_time = 1
#数据库错误日志文件
log_error=D:\\Program Files\\MySQL\\mysql-5.7.27-winx64\\log\\mysqld-error.log

#开启binlog日志
#log_bin=ON
#binlog日志的基本文件名
#log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
#log_bin_index=/var/lib/mysql/mysql-bin.index

#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写,这里可以写绝对路径,也可以直接写mysql-bin(后者默认就是在/var/lib/mysql目录下) #此一行等同于上面log_bin三行,这里可以写绝对路径,也可以直接写mysql-bin(后者默认就是在/var/lib/mysql目录下)
log-bin = D:\\Program Files\\MySQL\\mysql-5.7.27-winx64\\data\\mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
#配置serverid
server-id=1


# 连接时间一年
wait_timeout=31536000
interactive_timeout=31536000

#最大执行sql文件
max_allowed_packet=20M

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


explicit_defaults_for_timestamp=on
# 解决无法写入空时间，解除严格限制模式
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#NO_ZERO_IN_DATE 在严格模式下，不允许日期和月份为零
#NO_ZERO_DATE	设置该值，在mysql数据库不允许插入零日期，插入零日期会抛出

操作日志

1、查看日志文件：
mysql> show master logs;
+------------------+--------------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
| mysql-bin.000002 | 4249 |
| mysql-bin.000003 | 1110 |
+------------------+--------------+

2、查看最后一个binlog日志的编号名称(就是最后一个操作事件的值)：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      1110 | | | |
+------------------+----------+--------------+------------------+-------------------+

3、产生一个新的日志文件
mysql> flush logs; 
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+--------------+---------------+
|  Log_name   | File_size |
+--------------+---------------+
| mysql-bin.000004 |      106 |
+-----------------+------------+
4 rows in set (0.01 sec)

4、删除所有binlog日志,重新记录
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+--------------+---------------+
|  Log_name   | File_size |
+--------------+---------------+
| mysql-bin.000001 |      106 |
+-----------------+------------+
1 rows in set (0.01 sec)

恢复数据

1、如果想全部恢复，先执行之前的备份恢复，再执行自备份后产生的二进制日志文件
    mysql localhost mysql-bin.000001 | mysql -uroot -p( 这样数据库就可以恢复到删除数据前的状态)

2、根据时间点来恢复，如果确认被删的时间点为2019-03-25 09:00:00，那么就可以如下
     mysqlbinlog --stop-date='2019-03-25 8:59:59' mysql-bin.000001 | mysql -uroot -p  然后跳过数据被删的时间点，继续执行后面的binlog  # mysqlbinlog --start-date='2019-03-25 09:01:00' mysql-bin.000001 | mysql -uroot -p  
     其中--stop-date='2019-03-25 08:59:59' 和--start-date='2019-03-25 09:01:00' 

3、如果知道两个时间点，就按如下操作
   mysqlbinlog --start-datetime="2019-03-25 10:30:30" --stop-datetime="2019-03-25 11:10:10" mysql-bin.000001 | mysql -u root -p

4、通过查看日志文件信息确认(假如操作点为6200-6300)
    mysqlbinlog --stop-position=6200 mysql-bin.000001 | mysql -uroot -p 
    mysqlbinlog --start-position=6300 mysql-bin.000001 | mysql -uroot -p

Q.E.D.