Mysql Binlog issue

1

I woke up this morning and server was down.
Upon investigation my root directory is full because of multiple binlog files.
So I deleted them.

I investigated further by checking the binlogs and says [ERROR] unknown variable ‘default-character-set=utf8mb4’

Along with random characters such as
MGZjZmIxZjIzNDZhYzJkIjtzOjMzOiIwNmQ0MjdhYzVkY2FkNjlmZjFlMjMzM2Y2ODRkYWY3ODki

So I went to mysql and put an # in default-character-set=utf8mb4

But I still have files generating and filling my diskspace.

I put log_bin = OFF under [mysqld]

Now I am getting OFF.000*** files.

Anyone want knows how to fix this?

2

If you want to disable log_bin, comment out the directive.

#log_bin                = /var/log/mysql/mysql-bin.log

After that, restart mysql:

systemctl restart mysql

Just in case, check if log_bin directive is used in another conf files:

grep -r log_bin /etc/mysql
3

Thanks for the reply.

log_bin is already commented out by default when I checked.


# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
4

And now?

grep -r log_bin /etc/mysql
5

Hi,

root@hestia:/var/lib/mysql# grep -r log_bin /etc/mysql
/etc/mysql/mysql.conf.d/mysqld.cnf:# log_bin                    = /var/log/mysql/mysql-bin.log
6

Did you restart mysql?

Check current log_bin conf:

mysql -uroot -e 'SHOW VARIABLES LIKE "log_bin%";'
7

Yes I did.

Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF

I am in the process of migrating to a new server with mariadb if that will benefit me and should resolve this issue.
Can cancel this is there is no benefit at all and if I am able to resolve this current issue.

8

Edit your main cnf conf file (usually /etc/mysql/my.cnf) and add this directive to [mysqld] section:

skip-log-bin

Once done, restart mysql.

systemctl restart mysql

And check again:

mysql -uroot -e 'SHOW VARIABLES LIKE "log_bin%";'