1114 (HY000): The table is full, It’s Time to Clear MySQL/MariaDB Log a.k.a Garbage

mysql vs mariadb
Written by @kiosmerdeka

While trying to import the database file, I get an error 1114 (HY000): The table is full. Is my disk out of space? I checked and found that the free space is low. But I’m sure I do not save any big files on my server.

By using ncdu I find out that directory /var/log/mysql/logs consume most of the disk space. Yes, it was MySQL/MariaDB Binary logs.

Is it safe to delete mysql-bin files?

Glad to know that it’s save to delete them. How to delete theme?

Log in to your mysql database and run this command :

RESET MASTER

as the document said:

RESET MASTER enables you to delete any binary log files and their related binary log index file, returning the master to its state before binary logging was started.

Auto expire mysql-bin files

We can set binlog_expire_logs_seconds as an option or as a system variable or as a command-line parameter too, but I will show how to use it as a configuration option.

Using this as an option, you have to edit configurations files. You can look for windows and linux files here. For linux, I went to /etc/mysql/my.cnf file.

For example, to keep only 3 days of logs, you have to calculate total of seconds (246060*3). Now, all we have to add binlog_expire_logs_seconds option to a mysqld section.

$sudo nano /etc/mysql/my.cnf

[mysqld]

# Three days: 24*60*60*3
binlog_expire_logs_seconds=259200

And then, restart mysql service:

sudo service mysql restart
Baca juga  Transfer Cepat File dan Direktori: Panduan Menggunakan Rsync antara Server di Linux

Leave a Comment