1

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.

  • What is the hostname of your server? – Craig Efrein Jun 10 '15 at 11:40
  • acct-db1.example.com but, i have already specified log-bin in mycustom.cnf – Nandan A Jun 10 '15 at 12:21
  • if you could include more of the mycustom.cnf that would be helpful. There may be another parameter in mycustom.cnf that is incorrectly configured. – Craig Efrein Jun 10 '15 at 13:01
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.