MySQL中怎么删除与恢复二进制日志

这篇文章将为大家详细讲解有关MySQL中怎么删除与恢复二进制日志，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

# 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之间的时间相距是一年, 或者在不同的二进制日志中, 且位置相距好远, 就等着失眠吧! 做好备份, 小心操作才是正路啊..

关于MySQL中怎么删除与恢复二进制日志就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。