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

37次阅读

共计 4739 个字符，预计需要花费 12 分钟才能阅读完成。

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

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

正文完