ITPub博客

首页 > 数据库 > MySQL > MySQL二进制日志删除与恢复

MySQL二进制日志删除与恢复

原创 MySQL 作者:luashin 时间:2016-03-13 12:27:58 0 删除 编辑

# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# old_passwords=1
table_cache = 300
default-character-set = utf8
log = /var/lib/mysqllog/mysql.log
log-bin = /var/lib/mysqllog/log-bin
log-slow-queries = /var/lib/mysqllog/slowquery.log
long_query_time=2

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
其中黑体的内容为增加的,斜体的log-error=/var/log/mysqld.log原有的
mysql有以下几种日志: 
错误日志:  -log-err  
查询日志:  -log 
慢查询日志: -log-slow-queries 
更新日志:   -log-update    这个版本已经不用了,update的操作也记入查询日志中。设置上后错误日志中会有一条错误信息,不过不影响使用
二进制日志: -log-bin 


附录:

//显示所有本机上的二进制日志
mysql> SHOW MASTER LOGS;

//删除所有本机上的二进制日志
mysql> RESET MASTER;

//删除所有创建时间在binary-log.xxx之前的二进制日志
mysql> PURGE MASTER LOGS TO 'binary-log.xxx';

//只保留最近6天的日志,之前的都删掉
find /var/intra -type f -mtime +6 -name "*.log" -exec rm -f {} \;

//用键盘左上角(也就是Esc下面)那个键包围起来,说明是命令。-1d是昨天,以此类推-1m是上个月等等
day=`/bin/date -v -1d +%Y%m%d`;

//给文件改名
mv xxx.log xxx-${day}.log;

//这里还要加上数据库的用户名密码,作用是更新日志(包括二进制日志和查询日志等等)
mysqladmin flush-logs

 

打开MySQL的慢查询日志记录

   MySQL慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢?
其实打开mysql的慢查询日志很简单,只需要在mysql的配置文件里(windows系统是my.ini,linux系统是my.cnf)的[mysqld]下面加上如下代码:

log-slow-queries=/var/lib/mysql/slowquery.log
long_query_time=2
注:
log-slow-queries设置把日志写在那里,为空的时候,系统会给慢查询日志赋予主机名,并被附加slow.log。
/var/lib/mysql/slowquery.log为日志存放的文件的位置,一般这个目录要有mysql的运行帐号的可写权限,一般都将这个目录设置为 mysql的数据存放目录

long_query_time=2中的2表示查询超过两秒才记录.

如果设置了参数log-long-format,那么所有没有使用索引的查询也将被记录。在文件my.cnf或my.ini中加入下面这一行可以记录这些查询

这是一个有用的日志。它对于性能的影响不大(假设所有查询都很快),并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用)

# Time: 070927  8:08:52

# User@Host: root[root] @  [192.168.0.20]

# Query_time: 372  Lock_time: 136  Rows_sent: 152  Rows_examined: 263630
select id, name from manager where id in (66,10135);
这是慢查询日志中的一条,用了372秒,锁了136秒,返回152行,一共查了263630行

   如果日志内容很多,用眼睛一条一条去看会累死,mysql自带了分析的工具,使用方法如下:
命令行下,进入mysql/bin目录,输入mysqldumpslow –help或--help可以看到这个工具的参数


MySQL的log-bin的日志功能
   安装mysql,运行一段时间后,在mysql目录下出现一堆类似mysql-bin.000***,从mysql-bin.000001开始一直排列下来,而且占用了大量硬盘空间,高达几十个G。对于这些超大空间占用量的文件应该怎么办呢?

那么mysql数据库文件夹中的mysql-bin.00001是什么文件?
mysql-bin.000001、mysql- bin.000002等文件是数据库的操作日志,例如UPDATE一个表,或者DELETE一些数据,即使该语句没有匹配的数据,这个命令也会存储到日志文件中,还包括每个语句执行的时间,也会记录进去。

这些形如mysql-bin.00001的文件主要是用来做什么的呢?
1:数据恢复
如果你的数据库出问题了,而你之前有过备份,那么可以看日志文件,找出是哪个命令导致你的数据库出问题了,想办法挽回损失。

2:主从服务器之间同步数据
主服务器上所有的操作都在记录日志中,从服务器可以根据该日志来进行,以确保两个同步。

如果不想要这些文件应该怎么做呢?
1:只有一个mysql服务器,那么可以简单的注释掉这个选项就行了。
vim /etc/my.cnf把里面的log-bin这一行注释掉,重启mysql服务即可。

2:如果你的环境是主从服务器,那么就需要做以下操作了。
A:在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
B:使用SHOW MASTER LOGS获得主服务器上的一系列日志。
C:在所有的从属服务器中判定最早的日志,这个是目标日志,如果所有的从属服务器是更新的,就是清单上的最后一个日志。
D:清理所有的日志,但是不包括目标日志,因为从服务器还要与它同步。


简单地说,这些MySQL目录下的形如mysql-bin.000***的文件时MySQL的事务日志。

删除复制服务器已经拿走的binlog是安全的,一般来说网络状况好的时候,保留最新的那一个足以。

mysql二进制日志维护

先登陆上去, 看看这台机器有没有做replication, 如果有在做的话, 清除bin-log的时候要小心了, 要确保要删除的bin-log都已经送到slave了.

mysql> show processlist;
+----------+------+-----------+------+---------+------+-------+------------------+
| Id       | User | Host      | db   | Command | Time | State | Info             |
+----------+------+-----------+------+---------+------+-------+------------------+
| 55467761 | root | localhost | NULL | Query   | 0    | NULL  | show processlist |
+----------+------+-----------+------+---------+------+-------+------------------+
没有binlog dump的线程, 说明这台机器没有在做replication, 直接用以下命令清除所有的bin-log, 可以删除列于索引文件中的所有二进制日志,把二进制日志索引文件重新设置为空,并创建一个新的二进制日志文件。

mysql> reset master;
mysql> show master status;
+----------+----------+--------------+------------------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------+----------+--------------+------------------+
|xxx.000001| 180      |              |                  |
+----------+----------+--------------+------------------+
假设我只想删除xxx.000018以前的bin-log, 可以用以下的命令.

mysql> purge master logs to 'xxx.000018'
有条件的机器最好保留一份bin-log在另外一 个磁盘, 就算数据库挂了, 我们也可以根据二进制日志来恢复, 怎么保留二进制日志呢? 编辑你的my.cnf, 指定如下配置log-bin=/diskb/bin-logs/xxx_db-bin, 重新启动数据库的时候, 你就会发现/diskb/bin-logs哪里多了两个文件xxx_db-bin.000001, xxx_db-bin.index, 一个是二进制日志文件, 它将更改数据的所有查询记入该文件, 另外一个是二进制日志文件名的索引文件.

下面讲一下怎么从二进制文件恢复数据, 假如不小心执行了drop table xxx_db, 假如你保留了完整的二进制日志的话, 先不要冒汗, 这是可以恢复的.

先看看日志

mysql> mysqlbinlog /diskb/bin-logs/xxx_db-bin.000001
找到执行create table xxx_db之后和drop table xxx_db之前的position, 假如是20, 1000.

mysql> mysqlbinlog --start-position="4" --stop-position="1000" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root
伴 随着一大堆的ERROR 1062 (23000) at line 12355: Duplicate entry '139' for key 1, 数据库就这样恢复了, 不过--start-position="20"是不行的, 必须从--start-position="4"开始, 为什么要强制从4开始, 这个问题我也暂时没有搞清楚.

还有一种办法是根据日期来恢复

mysql> mysqlbinlog --start-datetime="2009-09-14 0:20:00" --stop-datetim="2009-09-15 01:25:00" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root
如果create table xxx_db和drop table xxx_db之间的时间相距是一年, 或者在不同的二进制日志中, 且位置相距好远, 就等着失眠吧! 做好备份, 小心操作才是正路啊..


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9034054/viewspace-2056444/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-02-18

  • 博文量
    290
  • 访问量
    1056955