MySQL运维之二进制日志


本文摘自PHP中文网,作者齐天大圣,侵删。

MySQL二进制日志里保存会造成或可能造成数据变化的SQL语句。通过二进制日志可以完成如实时异地容灾备份、读写分离、数据恢复等功能。下面,我们一起来看看Mysql二进制日志。

开启bin-log日志

Mysql默认是没有开启bin-log日志的,需要我们自己去添加配置。

1

2

3

4

log-bin=mysql-bin

binlog_format=mixed

server-id   = 1

expire_logs_days = 10

log-bin 配置了该项后,即表示开启二进制日志功能。mysql-bin是bin-log日志文件名。

expire_logs_days = 10 表明只存放最近10天的bin-log日志。

一般bin-log日志存放在 mysql安装路径/var/下面

运维提示:二进制日志文件与数据库数据文件最好不要放在同一块硬盘上,如果存放数据文件的硬盘坏了,可以用另一块硬盘的二进制日志来恢复数据

几个有用的命令

  • flush logs:生成新的bin-log日志

  • show master status:查看最后一个bin-log日志状态。

  • reset master:清空所有bin-log文件

mysql > show master status

QQ截图20200601085151.png

Mysql日志的查看

因为该日志是二进制日志,所以用一般的命令cat或vim查看会是一推乱码。Mysql为我们提供了工具mysqlbinlog。用它就可以来查看了。

1

2

3

4

5

6

7

8

9

10

11

./mysqlbinlog ../var/mysql-bin.000015

……

# at 123

#200601  8:35:19 server id 1  end_log_pos 154 CRC32 0xd25b404e  Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

……

  • at:sql开始时pos节点

  • server_id:数据库主机的服务号;

  • end_log_pos 154:sql结束时的pos节点

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

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

  • --stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

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

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

  • -d,--database=name:只查看指定数据库的日志操作

使用bin-log日志来恢复数据

  • 导出sql文件命令:mysqldump 数据库名字 [数据表名字1[ 数据表名字2...]] > 外部文件目录(建议使用.sql)

  • sql文件导入数据库:mysql -u** -p** 数据库名字 < 备份文件目录

现在模拟一种场景:一数据库每晚3点定时备份,第二天网站正常运行了半天,突然在下午5点钟的时候,程序员小A不小心,DELETE 时候没有加WHERE 条件,然后其中某张表数据全没了。然后小A找到技术总监大圣,让大圣帮忙恢复数据。

binlog_test 数据库只有一张user表

凌晨三点时没备份前的数据如下:

1

2

3

4

5

6

7

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

| user_id | username | add_time            |

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

|       1 | gwx      | 2018-07-05 13:00:31 |

|       2 | snn      | 2018-07-05 14:00:00 |

|       3 | zy       | 2018-07-05 15:00:00 |

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

凌晨3点到了,备份数据

1

2

3

4

5

mysqldump binlog_test -l -F > /root/sql_backup/20180706.sql

ll /root/sql_backup/

总用量 4

-rw-r--r-- 1 root root 2149 7月   6 13:42 20180706.sql

=======数据备份完成=========

网站正常运行一段时间,有许多用户注册

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

INSERT INTO `user` (username) values('user1'),('user2'),('user3');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

select * from user;

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

| user_id | username | add_time            |

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

|       1 | gwx      | 2018-07-05 13:00:31 |

|       2 | snn      | 2018-07-05 14:00:00 |

|       3 | zy       | 2018-07-05 15:00:00 |

|       4 | user1    | 2018-07-06 15:01:18 |

|       5 | user2    | 2018-07-06 15:01:18 |

|       6 | user3    | 2018-07-06 15:01:18 |

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

==============新增了3个用户user1 user2 及user3==============

到了下午5点钟,小A开始犯傻了

1

2

3

DELETE FROM user;

Query OK, 6 rows affected (0.00 sec)

=========没where条件,数据全没了===========

小A找到大圣帮忙恢复数据,大圣先把昨晚凌晨三点数据给恢复了

1

2

3

4

5

6

7

8

9

10

service nginx stop;  # 大圣先关闭了nginx,使网站用户暂时访问不了数据库

Stoping nginx...  done

MariaDB [binlog_test]> flush logs;  #生成新的binlog日志

MariaDB [binlog_test]> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000003 |     1536 |              |                  |

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

mysql -v -f binlog_test < /root/sql_backup/20180706.sql

这时候大圣已经把昨晚凌晨3点时候数据恢复了

1

2

3

4

5

6

7

8

9

MariaDB [binlog_test]> select * from user;

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

| user_id | username | add_time            |

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

|       1 | gwx      | 2018-07-05 13:00:31 |

|       2 | snn      | 2018-07-05 14:00:00 |

|       3 | zy       | 2018-07-05 15:00:00 |

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

=============昨晚凌晨三点数据恢复完成===============

接下来恢复凌晨三点到DELETE间段数据

首先找到delete的pos点,备份之后log是000002 删除之后也flush logs为000003,所以只要找000002 delete之前的pos即可

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

# /usr/local/mariadb/bin/mysqlbinlog --stop-position=629  >

'mysql-bin.000002' >

| mysql binlog_test;

 

MariaDB [binlog_test]> select * from user;

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

| user_id | username | add_time            |

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

|       1 | gwx      | 2018-07-05 13:00:31 |

|       2 | snn      | 2018-07-05 14:00:00 |

|       3 | zy       | 2018-07-05 15:00:00 |

|       4 | user1    | 2018-07-06 15:01:18 |

|       5 | user2    | 2018-07-06 15:01:18 |

|       6 | user3    | 2018-07-06 15:01:18 |

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

==============数据都回来了========================

以上就是MySQL运维之二进制日志的详细内容,更多文章请关注木庄网络博客

相关阅读 >>

mysql自动记录慢查询日志实例

mysql日志文件在哪

详细解读mysql中的权限

分享一些mysql数据库的优化技巧

mysql如何查询navicat中的blob类型内容

mysql怎么使用delete

mysq详细讲解如何解决库存并发问题

mysql数据库超时设置配置的方法实例

mysql数据库增量备份的实现思路方法介绍

mysql 和 sql server 的区别?

更多相关阅读请进入《mysql》频道 >>


数据库系统概念 第6版
书籍

数据库系统概念 第6版

机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。



打赏

取消

感谢您的支持,我会继续努力的!

扫码支持
扫码打赏,您说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

评论

管理员已关闭评论功能...