返回
基础
分类

但我还是倾向于使用标准的操作方法,在保持MySQL主从复制的功能下有两种解决方法

日期: 2020-01-02 08:12 浏览次数 : 103

清除并禁用MySQL的Binary logs

必赢手机登录网址 1

必赢手机登录网址 ,下午跑程序,在插入mysql时突然报错:

 

mysql

”The table‘xxxx’is full“

最近 MySQL 一下子飞黄腾达了,Binary Log 吃掉 800 MiB 多的硬盘空间,干扰了系统的正常运行。解决的方法有很多,包括直接删除文件,逐个使用 SQL 语句清除日志等。但我还是倾向于使用标准的操作方法。

在MySQL主从复制的时候会开启bin日志:
#/etc/my.conf
log-bin=mysql-bin

而之前一直没问题的。

 

当该功能打开时会产生大量如mysql-bin.00000*的log文件,这会消耗大量的硬盘空间。在保持MySQL主从复制的功能下有两种解决方法:1. 设置日志expire_logs_days 2. 手动清楚bin日志文件。

 

关掉它

实现

上网查了一下,都说临时表的问题,需要设置”tmp_table_size“:

 

设置日志expire_logs_days

修改MySQL配置文件,设置expire_logs_days,重启MySQL。
# vim /etc/my.cnf
expire_logs_days = x //日志自动删除的天数。一般讲x设置的短点,如10

直接在MySQL里设置expire_logs_days,无需重启MySQL。

# mysql -u root -p
> show binary logs;
> show variables like '%log%';
> set global expire_logs_days = 10;

tmp_table_size 

如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表。如果你执行许多高级GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值。

编辑 /etc/mysql/my.cnf,找到 log-bin 选项,注释掉;如果有 expire_logs_days,同样注释掉。

手动清理bin日志文件

# mysql -u root -p
> purge master logs before date_sub(current_date, interval 10 day);  //删除10天前的mysql-bin日志
> show master logs;

也可以重置master,删除所有bin文件
# mysql -u root -p
> reset master;

 

延伸

1.expire_logs_days英文说明

Where X is the number of days you’d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.
Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data.

2.PURGE MASTER LOGS手动删除用法示例
# mysql -u root -p
> purge master logs to 'mysql-bin.010’; //清除mysql-bin.010日志
> purge master logs before '2016-02-28 13:00:00'; //清除2016-02-28 13:00:00前的日志
> purge master logs before date_sub(now(), interval 3 day); //清除3天前的bin日志

感觉与我们服务器情况不太相符:我们没用到临时表,而且tmp_table_size已经非常大了。

...
# log-bin
# expire_logs_days = 10
...

参考链接

  1. My MySQL Binary Log files are taking up all my disk space!

 

 

再查到:

 

mysql出现"the table is full"的问题,一般有两个原因:

一 .You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Section 5.1.3, “Server System Variables”.


于是就修改Mysql的配置文件/etc/my.cnf,在[mysqld]下添加/修改两行:
tmp_table_size = 256M
max_heap_table_size = 256M

系统默认是16M,修改完后重启mysql



二.硬盘空间满了,清理硬盘即可.

清除已有日志

 

 

首先使用 mysql -u root -p 登录服务器。

 

 

在服务器df了一下,果然硬盘空间不够了,已经使用了100%。

SHOW BINARY LOGS;

追查下来,发现是mysql的日志文件将硬盘撑爆了,有大量的mysql-bin.000XXX之类的日志文件。

 

如何处理?很简单:

 

(1)清除这些日志文件

首先看看我们有多少日志,占用了多少空间。真是不少啊。

(2)如果不需要的话,可以关闭mysql的bin-log功能。

 

具体操作:

+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.000001 |       264 |
| mysqld-bin.000002 |      1546 |
| mysqld-bin.000003 |     11728 |
| mysqld-bin.000004 | 122291346 |
| mysqld-bin.000005 |       264 |
| mysqld-bin.000006 |       958 |
| mysqld-bin.000007 |     85263 |
| mysqld-bin.000008 |    467392 |
| mysqld-bin.000009 |    271736 |
| mysqld-bin.000010 |       264 |
| mysqld-bin.000011 |      2670 |
| mysqld-bin.000012 |   5258576 |
| mysqld-bin.000013 |     25406 |
| mysqld-bin.000014 |    430000 |
| mysqld-bin.000015 |    560172 |
| mysqld-bin.000016 |  45971600 |
| mysqld-bin.000017 |  62227475 |
| mysqld-bin.000018 | 223992121 |
| mysqld-bin.000019 |  91294894 |
| mysqld-bin.000020 |   4796916 |
+-------------------+-----------+

FLUSH LOGS;

RESET MASTER;

这是数据库的操作日志,例如UPDATE一个表,或者DELETE一些数据,即使该语句没有匹配的数据,这个命令也会存储到日志文件中,还包括每个语句执行的时间,也会记录进去的

这样做主要有以下两个目的:
1:数据恢复
如果你的数据库出问题了,而你之前有过备份,那么可以看日志文件,找出是哪个命令导致你的数据库出问题了,想办法挽回损失。
2:主从服务器之间同步数据
主服务器上所有的操作都在记录日志中,从服务器可以根据该日志来进行,以确保两个同步。

处理方法分两种情况:
1:只有一个mysql服务器,那么可以简单的注释掉这个选项就行了。
vi /etc/my.cnf把里面的log-bin这一行注释掉,重启mysql服务即可。
2:如果你的环境是主从服务器,那么就需要做以下操作了。
A:在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
B:使用SHOW MASTER LOGS获得主服务器上的一系列日志。
C:在所有的从属服务器中判定最早的日志,这个是目标日志,如果所有的从属服务器是更新的,就是清单上的最后一个日志。
D:清理所有的日志,但是不包括目标日志,因为从服务器还要跟它同步。
清理日志方法为:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2008-12-19 21:00:00';

如果你确定从服务器已经同步过了,跟主服务器一样了,那么可以直接RESET MASTER将这些文件删除。


如何删除mysql-bin.0000X 日志文件呢?

mysql> reset master; (清除日志文件)
Query OK, 0 rows affected (8.51 sec)

mysql>

好了,我们再来查看下mysql文件夹占用多少空间?

[root@jiucool var]# du -h –max-depth=1 /usr/local/mysql/
37M /usr/local/mysql/var
70M /usr/local/mysql/mysql-test
15M /usr/local/mysql/lib
448K /usr/local/mysql/include
2.9M /usr/local/mysql/share
7.6M /usr/local/mysql/libexec
17M /usr/local/mysql/bin
11M /usr/local/mysql/docs
2.9M /usr/local/mysql/sql-bench
163M /usr/local/mysql/

好了,看一下,整个mysql 目录才占用163M大小!OK,没问题,既然mysql-bin.0000X日志文件占用这么大空间,存在的意义又不是特别大,那么我们就不让它生成吧.

[root@jiucool var]# find / -name my.cnf 

找到了my.cnf 即mysql配置文件,我们将log-bin=mysql-bin 这条注释掉即可.

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

重启下mysql吧.

OK,至此,操作完成. 以后再不会因为就几十M的数据库大小生成N个G的日志文件啦.

 

 

至此,Binary Logs 被彻底禁用。

 

 

logs 最近 MySQL 一下子飞黄腾达了,Binary Log 吃掉 800 MiB 多的硬盘空间,干扰了系统的正常运行。解决的方法有很多,...

mysql bin-log引起的故障实例及处理过程,参见:

自从我博客转过来Linode之后,一直其实我很少去理会服务器的运行情况,反正博客能上,我就准时交款给Linode。可是近来突然发现好几次博客无法登录,即使重新启动了服务器也解决不了。当我重新启动MySQL服务器的时候,就会有下面的失败语句:

MySQL manager or server PID file could not be found!! failed

意思就是MySQL管理器或者无法找到服务器PID文件,所以我开始向Linode的客服询问情况和解决方法,我在这里先说明一下,因为Linode销售的是自己管理的VPS服务器,所以理论上来说你的VPS上面所有东西应该都是自己管理的,Linode的客服没有任何责任需要去帮你,但是我不得不赞扬一下Linode的客服(特别是Peter,虽然他未必看得到这句话),他们并没有用“去Google”或者“上Linode的知识库找”等等来让我自己去受苦。

因为我当时发现问题的时候,已经是凌晨1点多了,给客服发了电子邮件,15分钟后,Peter开始联络我,当一轮的指引后,我还是无法解决问题──也因为我对Linux不太熟悉,当时已经是凌晨3点了,而第二天我需要开车去美国。所以我询问Peter,看看他能否帮我管理,他说“一般我们不应该这样子做,但是为了让你可以睡个好觉,你让我take the wheel(让我来帮你开车,也就是说他帮我找问题所在和尝试解决)吧。

第二天,我睡醒的时候,看到一封来自Peter的信,他说一个好消息,一个坏消息。好消息就是找到问题的所在──我的空间不够了,坏消息就是,我需要清理一些文件或者购买多一些空间。

这个不是问题,我马上清理了大概1GB的垃圾文件,这对于博客来说,应该足够了。做了之后,我的MySQL服务器马上可以运行,而博客也正常。

可惜,这个维持不到一个星期,两天前,我的博客再次发生同样问题,因为这次我有了经验,就先购买了1GB硬盘空间(才$2一个月,很便宜),先让博客重新运作。但是这个不是办法,我必须找出究竟是什么占了这么大的空间。

我想了想,我的Linode放了三个博客,分别是rockia.com、rockia.net还有ipanda.ca,三个都是Wordpress,基本上可以占用到空间绝对不到2GB,而我的Linode安装了Debian5 Lenny的Linux版本,应该也不会超过3GB(加上Nginx服务器和附加软件),那么也就是说我的Linode 16GB空间,应该还有至少11GB的空间。按照这个算法,肯定是有什么垃圾文件在我的服务器上面,所以我就从根目录开始找这些文件。

因为用root来ssh连接到我的服务器,没有任何GUI界面,所以只可以用命令行来查看我的文件,这里我首先要介绍一个Linux下面很好用的命令 “du”,而更加好用的就是加上参数”-h”,这个参数可以用人类可读的方法来标出查询的文件夹的大小,例如 kb, mb,gb等。

我查看到在 /usr/local/mysql/var下面,一共占用了我13GB的空间,天啊!13GB!而看到一大堆文件都是这样的格式:

mysql-bin.000011 mysql-bin.000026 mysql-bin.000041 mysql-bin.000056 mysql-bin.000071 mysql-bin.000086 mysql-bin.000101 mysql-bin.000116
li158-206.err mysql-bin.000012 mysql-bin.000027 mysql-bin.000042 mysql-bin.000057 mysql-bin.000072 mysql-bin.000087 mysql-bin.000102 mysql-bin.000117
li158-206.err-old mysql-bin.000013 mysql-bin.000028 mysql-bin.000043 mysql-bin.000058 mysql-bin.000073 mysql-bin.000088 mysql-bin.000103 mysql-bin.000118
li158-206.pid mysql-bin.000014 mysql-bin.000029 mysql-bin.000044 mysql-bin.000059 mysql-bin.000074 mysql-bin.000089 mysql-bin.000104 mysql-bin.000119
mysql mysql-bin.000015 mysql-bin.000030 mysql-bin.000045 mysql-bin.000060 mysql-bin.000075 mysql-bin.000090 mysql-bin.000105 mysql-bin.000120
mysql-bin.000001 mysql-bin.000016 mysql-bin.000031 mysql-bin.000046 mysql-bin.000061 mysql-bin.000076 mysql-bin.000091 mysql-bin.000106 mysql-bin.000121
mysql-bin.000002 mysql-bin.000017 mysql-bin.000032 mysql-bin.000047 mysql-bin.000062 mysql-bin.000077 mysql-bin.000092 mysql-bin.000107 mysql-bin.000122
mysql-bin.000003 mysql-bin.000018 mysql-bin.000033 mysql-bin.000048 mysql-bin.000063 mysql-bin.000078 mysql-bin.000093 mysql-bin.000108 mysql-bin.000123
mysql-bin.000004 mysql-bin.000019 mysql-bin.000034 mysql-bin.000049 mysql-bin.000064 mysql-bin.000079 mysql-bin.000094 mysql-bin.000109 mysql-bin.000124
mysql-bin.000005 mysql-bin.000020 mysql-bin.000035 mysql-bin.000050 mysql-bin.000065 mysql-bin.000080 mysql-bin.000095 mysql-bin.000110 mysql-bin.000125
mysql-bin.000006 mysql-bin.000021 mysql-bin.000036 mysql-bin.000051 mysql-bin.000066 mysql-bin.000081 mysql-bin.000096 mysql-bin.000111 mysql-bin.000126
mysql-bin.000007 mysql-bin.000022 mysql-bin.000037 mysql-bin.000052 mysql-bin.000067 mysql-bin.000082 mysql-bin.000097 mysql-bin.000112 mysql-bin.index
mysql-bin.000008 mysql-bin.000023 mysql-bin.000038 mysql-bin.000053 mysql-bin.000068 mysql-bin.000083 mysql-bin.000098 mysql-bin.000113
mysql-bin.000009 mysql-bin.000024 mysql-bin.000039 mysql-bin.000054 mysql-bin.000069 mysql-bin.000084 mysql-bin.000099 mysql-bin.000114
mysql-bin.000010 mysql-bin.000025 mysql-bin.000040 mysql-bin.000055 mysql-bin.000070 mysql-bin.000085 mysql-bin.000100 mysql-bin.000115

其中有好几个都是占用了1.1GB。就是这些垃圾,让我好好的博客无法访问,也是这些文件,让我连1KB的文件也无法写进去MySQL数据库,当然也无法启动。
究竟这些是什么文件?

这些文件是叫做MySQL Binary Log,主要有下面两个作用:
数据恢复。
在主从服务器上提高复制的可靠性。这个其实是主要的作用,但是我根本没有主从服务器,我只有一个,所以用不着,对不?
如何解决?

既然知道问题所在,我就知道怎么去做,首先当然是要杜绝问题重复发生,ssh 登录然后运行 /usr/local/mysql/bin/mysql -u root -p 登录执行:

reset master;

在/etc/ 下面找到my.cnf把
#log-bin=mysql-bin
#binlog_format=mixed
这两行注释掉,然后将这些文件全部删除,13GB啊,删除后那个畅快的感觉难以形容。

重新启动我的服务器,现在健步如飞,爽就一个字。
一些建议