浅析MySql二进制日志的应用
来源:互联网 发布:投票软件宣传语 编辑:程序博客网 时间:2024/05/29 10:00
mysql有4种不同的日志,分别是二进制日志,查询日志,慢查询日志和错误日志,这些日记记录着数据库工作的方方面面,可以帮助我们了解数据库的不同方面的踪迹,下面先介绍二进制日志的作用和使用方法,并利用二进制日志对数据库进行各种维护和优化,其他日志也会在后面陆续会做详细的介绍。
二进制日志(bin-log日志)
在上一篇介绍mysql主从配置的blog中,已经提过bin-log日志的作用和使用,bin-log日志记录了所有的DDL和DML的语句,但不包括查询的语句,语句以事件的方式保存,描述了数据的更改过程,此日志对发生灾难时数据恢复起到了极为重要的作用。
开启
mysql默认是没有开发bin-log日志,首先我们需要开启bin-log日志,在my.cnf中修改
指定了bin-log日志的路径,开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件,这就是我们需要的二进制日志
由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下:
现在我们尝试向test1表插入数据
然后使用mysqlbinlog工具进行日志查看
清理
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1)RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志
查看删除后的日志
可以看到,以前的日志全部被清空,新的日志从00001开始
2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)-EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
这里我们设定保存日志为3天,3天之后过期的日志将被自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
基于时间点的恢复,由于误操作,比如说删除了一张表,这时使用上面讲的完全恢复是没有用的,因为日志里面还存在误操作的语句,,我们需要的是恢复到误操作前的状态,然后跳过误操作的语句,再恢复后面操作的语句,假定我们删除了一张表的误操作发生在10:00这个时间点,我们可以使用下面的语句用备份和binlog将数据恢复到故障前
然后跳过误操作的时间点,继续执行后面的binlog
其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的时间是你误操作的时间点,当然了,这个时间点你需要你自己计算的,而且这个时间点还可以涉及到的不只是误操作,还可以有正确的操作也被跳过去了。
基于位置恢复,由于上面提到的,使用基于时间点的恢复可能出现,在一个时间点里面可能存在误操作和其他正确的操作,所以我们需要一种更为精确的恢复方式
使用mysqlbinlog查看二进制,可看到
其中drop tables test1这个误操作的end_log_pos为8879917,几下这个id,得出它前后操作的id分别为8879916,8879918
我们将进行位置恢复操作
第一行是恢复到停止位置位置的所以事务,第二性是恢复从给定的起始位置知道二进制日志结束所有事物。
主从复制
mysql的复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器上,然后在从服务器上对这些日志做重新执行的操作,从而使得从服务器和主服务器保持数据的同步,通过二进制日志进行主从复制的可以看前一篇《MySQL主从复制配置》
二进制日志(bin-log日志)
在上一篇介绍mysql主从配置的blog中,已经提过bin-log日志的作用和使用,bin-log日志记录了所有的DDL和DML的语句,但不包括查询的语句,语句以事件的方式保存,描述了数据的更改过程,此日志对发生灾难时数据恢复起到了极为重要的作用。
开启
mysql默认是没有开发bin-log日志,首先我们需要开启bin-log日志,在my.cnf中修改
指定了bin-log日志的路径,开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件,这就是我们需要的二进制日志
由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下:
#mysqlbinlog mysql-bin.000002
现在我们尝试向test1表插入数据
然后使用mysqlbinlog工具进行日志查看
#mysqlbinlog mysql-bin.000002 -d test
清理
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1)RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志
查看删除后的日志
可以看到,以前的日志全部被清空,新的日志从00001开始
2)PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)-EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
这里我们设定保存日志为3天,3天之后过期的日志将被自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
基于时间点的恢复,由于误操作,比如说删除了一张表,这时使用上面讲的完全恢复是没有用的,因为日志里面还存在误操作的语句,,我们需要的是恢复到误操作前的状态,然后跳过误操作的语句,再恢复后面操作的语句,假定我们删除了一张表的误操作发生在10:00这个时间点,我们可以使用下面的语句用备份和binlog将数据恢复到故障前
mysqlbinlog --stop-date='2010-09-04 9:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p
然后跳过误操作的时间点,继续执行后面的binlog
mysqlbinlog --start-date='2010-09-04 10:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p
其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的时间是你误操作的时间点,当然了,这个时间点你需要你自己计算的,而且这个时间点还可以涉及到的不只是误操作,还可以有正确的操作也被跳过去了。
基于位置恢复,由于上面提到的,使用基于时间点的恢复可能出现,在一个时间点里面可能存在误操作和其他正确的操作,所以我们需要一种更为精确的恢复方式
使用mysqlbinlog查看二进制,可看到
其中drop tables test1这个误操作的end_log_pos为8879917,几下这个id,得出它前后操作的id分别为8879916,8879918
我们将进行位置恢复操作
mysqlbinlog --stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p
第一行是恢复到停止位置位置的所以事务,第二性是恢复从给定的起始位置知道二进制日志结束所有事物。
主从复制
mysql的复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器上,然后在从服务器上对这些日志做重新执行的操作,从而使得从服务器和主服务器保持数据的同步,通过二进制日志进行主从复制的可以看前一篇《MySQL主从复制配置》
阅读全文
0 0
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySql二进制日志的应用
- 浅析MySQL二进制日志
- mysql的二进制日志
- mysql-二进制日志-理解与应用
- mysql日志-二进制日志
- Mysql日志的应用
- Mysql的二进制日志binlog的模式
- 关于MySQL二进制日志Binlog的认识
- Mysql清理二进制日志的技巧
- 删除MySQL二进制日志的方法
- 基于mysql二进制日志的数据库恢复
- Mysql开二进制日志的风险
- Vue-router cannot read property _c of undefined
- idea某些模块启动失败(spring cloud )
- ShareSDK Android 微信分享 绕过审核与不绕过审核
- java----抽象类和数据类型转换
- 光棍节的快乐
- 浅析MySql二进制日志的应用
- Github菜鸟教程/简明教程
- python中的chr()函数
- Javascript对象
- 自定义组件时 Binary XML file line Error inflating class 异常
- jquery
- matlab中double和im2double
- MSSQL按年龄段统计销售业绩
- 做事原则