MySQL: Incorrect binary log format - OFF.000

Question

I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file.

 
mysql> show binary logs;
+------------+------------+
| Log_name   | File_size  |
+------------+------------+
| OFF.000084 | 1073744171 |
| OFF.000085 | 1073741909 |
| OFF.000086 | 1073742904 |
| OFF.000087 | 1073745627 |
| OFF.000088 | 1073742910 |
| OFF.000089 | 1073742081 |
| OFF.000090 | 1075694984 |

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

bash-4.1# grep log-bin /etc/mysql/conf.d/mycustom.cnf
log-bin = /var/log/mysql/acct-db1-bin.log

Haven't restarted or done anything on MySQL service, but wondering how the format of binary logs got changed.

I have a setup a new slave to this master, and don't have any issue with the replication. But I am thinking whether it will cause any issue in future.

Answer 1

I have seen your problem before

Problem #1

You have /etc/mysql/conf.d/mycustom.cnf and the log-bin line is not being read

Problem #2

Chances are /etc/mysql/conf.d/my.cnf exists and has the following line

[mysqld]
log-bin=OFF

That's why the binlogs start with a filename of OFF

Some feel log-bin=OFF disables binlogging. The MySQL Documentation on log-bin says the permitted value is a filename. Thus, it is not boolean. Simply removing the line or commenting it out disables binlogging.

Suggestion #1

You may want to start mysqld manually using

service mysql stop
mysqld --defaults-file="/etc/mysql/conf.d/mycustom.cnf"

Suggestion #2

Just change log-bin in /etc/mysql/conf.d/my.cnf

Then service mysql restart

Epilogue

Don't restart mysql just yet. This problem is one the Master.

STEP 01 : Stop all writes to the Master

STEP 02 : Go to the Slave and run STOP SLAVE;

STEP 03 : Restart mysql on the Master

STEP 04 : On the Slave, run this

CHANGE MASTER TO master_log_file='acct-db1-bin.000001',master_log_pos = 4;
START SLAVE;

STEP 05 : Start up all writes to the Master

This should complete your maintenance