1.开启bin-log日志

[root@localhost mysql]# vim /etc/my.cnf

设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；

mysql log文件 mysql log-bin_mysql


添加后重启【请安你安装的mysql目录操作】

[root@localhost mysql]#[root@localhost mysql]# /usr/local/mysql/support-files/mysql.server restart

2.可登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启 单词：variable[ˈvɛriəbəl] 变量

[root@localhost mysql]# /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+

3.常用binlog日志操作命令
3.1查看所有binlog日志列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       524 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       853 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
4 rows in set (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志

3.4.重置(清空)所有binlog日志

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

4.查看某个binlog日志内容，常用有两种方式：

1.使用mysqlbinlog自带查看命令法：
注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)

注意： 在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项

[root@localhost mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001

查看文件内容如下

mysql log文件 mysql log-bin_mysql log文件_02

2.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：

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

例子

mysql> show binlog events in ‘mysql-bin.000006’\G

mysql log文件 mysql log-bin_mysql_03

demo:

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      3848 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.21-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 316
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
        Pos: 316
 Event_type: Query
  Server_id: 1
End_log_pos: 492
       Info: use `yl_wechat_dev`; UPDATE `yh_trade` SET `trade_code`='P156342020423598124' WHERE (`trade_id`='110917')
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
        Pos: 492
 Event_type: Xid
  Server_id: 1
End_log_pos: 523
       Info: COMMIT /* xid=39 */
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
        Pos: 523
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 588
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 8. row ***************************
   Log_name: mysql-bin.000001
        Pos: 588
 Event_type: Query
  Server_id: 1
End_log_pos: 685
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: mysql-bin.000001
        Pos: 685
 Event_type: Query
  Server_id: 1
End_log_pos: 862
       Info: use `yl_wechat_dev`; UPDATE `yh_trade` SET `trade_code`='P156342017247967277s' WHERE (`trade_id`='110916')
*************************** 10. row ***************************
   Log_name: mysql-bin.000001
        Pos: 862
 Event_type: Xid
  Server_id: 1
End_log_pos: 893
       Info: COMMIT /* xid=41 */
*************************** 11. row ***************************
   Log_name: mysql-bin.000001
        Pos: 893
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 958
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 12. row ***************************
   Log_name: mysql-bin.000001
        Pos: 958
 Event_type: Query
  Server_id: 1
End_log_pos: 1055
       Info: BEGIN

查看3条数据

mysql> show binlog events in 'mysql-bin.000001' from 316 limit 3\G;

mysql log文件 mysql log-bin_mysql log文件_04


命令demo:

A.查询第一个(最早)的binlog日志：

mysql> show binlog events\G;

B.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in ‘mysql-bin.000021’\G;

C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in ‘mysql-bin.000021’ from 8224\G;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 10\G;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 2,10\G;

5.通过bin-log日志恢复数据【恢复binlog日志实验(diaoshi是数据库)】

mysql log文件 mysql log-bin_mysql_05


(当前数据库只有一张数据表)

mysql的工具：

[root@localhost ~]# ls /usr/local/mysql/bin
innochecksum    my_print_defaults           mysql_config         mysqldump         mysqlpump                  mysql_tzinfo_to_sql  resolve_stack_dump
lz4_decompress  mysql                       mysql_config_editor  mysqldumpslow     mysql_secure_installation  mysql_upgrade        zlib_decompress
myisamchk       mysqladmin                  mysqld               mysql_embedded    mysqlshow                  mysqlxtest
myisam_ftdump   mysqlbinlog                 mysqld-debug         mysqlimport       mysqlslap                  perror
myisamlog       mysqlcheck                  mysqld_multi         mysql_install_db  mysql_ssl_rsa_setup        replace
myisampack      mysql_client_test_embedded  mysqld_safe          mysql_plugin      mysqltest_embedded         resolveip

mysql log文件 mysql log-bin_Server_06


5.1.将diaoshi数据库备份到 /root/diaoshi-0926-1038.sql （任意目录）文件中：
【利用mysqldump --help 查看命令】（主要利用mysqldump工具备份数据）

命令帮助：

[root@localhost ~]# /usr/local/mysql/bin/mysqldump --help

mysql log文件 mysql log-bin_Server_07


备份数据库的数据：

1》查看数据前的bin-log日志：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1552 |
+------------------+-----------+
1 row in set (0.00 sec)

2》执行导出数据命令：

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p123456ok diaoshi -l -F >/root/diaoshi-0926-1038.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
anaconda-ks.cfg  diaoshi-0926-1038.sql

（使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作；-l 是锁表【具体参数请自行查看命令帮助】）

3》查看导出数据后的bin-log日志状态：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1599 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

（已经产生新的bin-log日志）

5.2假如diaoshi数据库的数据没有了，需要进行恢复数据操作【利用bin下的工具】【利用mysql --help 查看命令】

mysql log文件 mysql log-bin_mysql_08


执行恢复数据库操作：

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p123456ok diaoshi -v -f </root/diaoshi-0926-1038.sql

恢复数据后：

mysql log文件 mysql log-bin_mysql log文件_09

5.3通过bin-log日志来恢复数据【利用mysqlbinlog --help 查看命令】

截图说明：

备份的数据 diaoshi-0926-1038.sql，users只有三条数据

mysql log文件 mysql log-bin_mysql_10


现在往数据表里面添加数据：

mysql log文件 mysql log-bin_mysql log文件_11


5.3.1当前模拟数据误操作删除（可以手动的方式删除4,5数据【模式4,5误操作丢失或删除】），需要恢复数据：

此时数据截图：（4,5数据已经被误删除）

mysql log文件 mysql log-bin_数据_12


mysql log文件 mysql log-bin_数据_13

1》首先查看mysql-bin.000002日志，查看4,5数据位置

(查看日志方式

1.mysqlbinlog查看二进制日志

2. show binlog查看日志【推荐】)

使用命令：

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

查看到4,5数据所在位置：

mysql log文件 mysql log-bin_Server_14


mysql log文件 mysql log-bin_mysql_15


推断：我们恢复的数据是pos:2081到2774

利用mysqlbinlog工具恢复数据：（查看命令帮助）

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --help

常用参数选项解释：
–start-position=2081起始pos点
–stop-position=2774结束pos点
–start-datetime="2019-9-26 22:58:40 " 起始时间点
–stop-datetime=“2019-9-26 23:03:41” 结束时间点

时间点截图：

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

mysql log文件 mysql log-bin_mysql_16

通过pos位置恢复数据：

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=2081 --stop-position=2774 --database=diaoshi /usr/local/mysql/data/mysql-bin.000002 | /usr/local/mysql/bin/mysql -uroot -p123456ok -v -f

mysql log文件 mysql log-bin_Server_17

误删除的数据恢复：

mysql log文件 mysql log-bin_Server_18


5.3.2假如在备份数据之后有操作数据，那个时候没有把数据备份到diaoshi-0926-1355.sql,这个时候通过bin-log恢复数据

1》查看当前bin-log

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1599 |
| mysql-bin.000002 |      7539 |
+------------------+-----------+
2 rows in set (0.00 sec)

2》执行数据备份

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p123456ok diaoshi -l -F >/root/diaoshi-0926-1355.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
anaconda-ks.cfg   diaoshi-0926-1038.sql diaoshi-0926-1355.sql

3》查看bin-log日志

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1599 |
| mysql-bin.000002 |      7586 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

（重新创建一个bin-log日志）

4》添加数据

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1599 |
| mysql-bin.000002 |      7586 |
| mysql-bin.000003 |       984 |
| mysql-bin.000004 |       154 |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql log文件 mysql log-bin_mysql_19


5》刷新log日志，模拟数据库数据数据丢失了

mysql log文件 mysql log-bin_Server_20


（把数据库的数据表都删除）–》模拟数据库数据都丢失

6》查看数据表

mysql> show tables;
Empty set (0.00 sec)

7》恢复数据【由于数据备份是数据没有记录到8,9的数据】所以都先恢复备份的数，然后通过bin-log日志恢复数据

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p123456ok diaoshi -v -f </root/diaoshi-0926-1355.sql

mysql log文件 mysql log-bin_mysql log文件_21


此时恢复的数据是【备份节点的数据】

mysql log文件 mysql log-bin_数据_22


8》通过mysqlbinlog恢复之后操作的数据

[root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -p123456ok -v -f

mysql log文件 mysql log-bin_mysql_23


恢复后的数据：

mysql log文件 mysql log-bin_mysql log文件_24


6.总结

1.备份数据使用mysqldump工具
 demo：
 [root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p123456ok diaoshi -l -F >/root/diaoshi-0926-1355.sql
2.查看bin-log日志：
 2.1使用mysqlbinlog工具
 demo:[root@localhost mysql]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001
 2.2使用 show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];
 demo:
 mysql>show binlog events in ‘mysql-bin.000006’\G3.导入备份数据
 demo:
 [root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p123456ok diaoshi -v -f </root/diaoshi-0926-1355.sql4.通过mysqlbinlog工具恢复数据
 demo1：
 [root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=2081 --stop-position=2774 --database=diaoshi /usr/local/mysql/data/mysql-bin.000002 | /usr/local/mysql/bin/mysql -uroot -p123456ok -v -fdemo2:
 [root@localhost ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -p123456ok -v -f