- 开启二进制文件:my.cnf
[mysqld]
# 必选
# [必须] 主服务唯一ID 主从下不能重复
server-id=1
# [必须] 启用二进制日志,比如:自己本地路径/log/mysqlbin
log-bin=mysql-bin
# 可选
# [可选] (默认) 0表示读写 (主机) ，1表示只读 (从机) (具有super权限的用户除外)
read-only=0
# 设置日志文件保留的时长，单位是秒
#binlog_expire_logs_seconds=6000
# 设置日志文件保留的时长，单位是天
expire_logs_days = 10
# 控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=500M
# [可选]设置不要复制的数据库 可不设置
#binlog-ignore-db=test
# [可选]设置需要复制的数据库,默认全部记录。比如: binlog-do-db=atguigu_master_slave 可不设置
# binlog-do-db=需要复制的主数据库名字
# 设置二进制日志使用内存大小(事务）
binlog_cache_size=1M
# 跳过主从复制中遇到的所有错误或指定类型的错误﹐避免slave端复制中断。
# 如∶1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
# [可选]设置binlog格式(mixed(混合模式,有系统函数自动采用row,普通数据采用statement),statement(只记录执行语句,数据量小,系统函数会导致数据不一致),row(记录每条数据,数据量大,导致并发日志))
binlog_format=mixed
- 查看日志开启状态
登录Mysql
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)
- 查看日志文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 5889540 |
| mysql-bin.000003 | 2750 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
- 刷新日志，开启新二进制文件，主要用于恢复，截断旧二进制文件，进行恢复使用。防止新数据混淆。
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 5889540 |
| mysql-bin.000003 | 2750 |
| mysql-bin.000004 | 507 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
5 rows in set (0.00 sec)
- 查看二进制文件内容，目前为空
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)
恢复数据演示（mysqlbinlog）：
定位点恢复方式
- 1.先进行写入数据 查看bin日志
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 1 | 298 | BEGIN |
| mysql-bin.000005 | 298 | Query | 1 | 429 | use `test`; INSERT INTO `test`.`name` (`id`, `name`) VALUES (7, '77') |
| mysql-bin.000005 | 429 | Xid | 1 | 460 | COMMIT /* xid=144 */ |
| mysql-bin.000005 | 460 | Anonymous_Gtid | 1 | 525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 525 | Query | 1 | 604 | BEGIN |
| mysql-bin.000005 | 604 | Query | 1 | 735 | use `test`; INSERT INTO `test`.`name` (`id`, `name`) VALUES (8, '88') |
| mysql-bin.000005 | 735 | Xid | 1 | 766 | COMMIT /* xid=147 */ |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)
- 2.删除数据 查看bin日志
mysql> show binlog events in 'mysql-bin.000005';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 1 | 298 | BEGIN |
| mysql-bin.000005 | 298 | Query | 1 | 429 | use `test`; INSERT INTO `test`.`name` (`id`, `name`) VALUES (7, '77') |
| mysql-bin.000005 | 429 | Xid | 1 | 460 | COMMIT /* xid=144 */ |
| mysql-bin.000005 | 460 | Anonymous_Gtid | 1 | 525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 525 | Query | 1 | 604 | BEGIN |
| mysql-bin.000005 | 604 | Query | 1 | 735 | use `test`; INSERT INTO `test`.`name` (`id`, `name`) VALUES (8, '88') |
| mysql-bin.000005 | 735 | Xid | 1 | 766 | COMMIT /* xid=147 */ |
| mysql-bin.000005 | 766 | Anonymous_Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 831 | Query | 1 | 910 | BEGIN |
| mysql-bin.000005 | 910 | Query | 1 | 1024 | use `test`; DELETE FROM `test`.`name` WHERE `id` = 7 |
| mysql-bin.000005 | 1024 | Xid | 1 | 1055 | COMMIT /* xid=151 */ |
| mysql-bin.000005 | 1055 | Anonymous_Gtid | 1 | 1120 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 1120 | Query | 1 | 1199 | BEGIN |
| mysql-bin.000005 | 1199 | Query | 1 | 1313 | use `test`; DELETE FROM `test`.`name` WHERE `id` = 8 |
| mysql-bin.000005 | 1313 | Xid | 1 | 1344 | COMMIT /* xid=152 */ |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
18 rows in set (0.00 sec)
- 3.刷新日志，防止有新数据进行混淆，难以恢复
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 5889540 |
| mysql-bin.000003 | 2750 |
| mysql-bin.000004 | 507 |
| mysql-bin.000005 | 1391 |
| mysql-bin.000006 | 154 |
+------------------+-----------+
6 rows in set (0.00 sec)
- 4.使用mysql-bin.000005进行恢复
查看需要恢复起始点和结束点
二进制详情.jpg
执行：
root@4eaea03da154:/# /usr/bin/mysqlbinlog --start-position=219 --stop-position=766 --database=test /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p123456 -v test
查看数据表，发现已经恢复了指定数据
时间范围恢复方式
查看日志文件
root@4eaea03da154:/# mysqlbinlog /var/lib/mysql/mysql-bin.000005
找出需要恢复的时间范围
执行恢复语句
/usr/bin/mysqlbinlog --start-datetime='2099-12-30 20:00:00' --stop-datetime='2099-12-30 21:00:00' --database=test /var/lib/mysql/mysql-bin.000005 | mysql -uroot -p123456 -v test
二进制文件删除
- 删除指定名称之前的
purge master logs to "mysql-bin.000005";
- 删除指定时间之前的
purge master logs before "20300101";
- 删除所有二进制 - 慎用
reset master;
