2

I have a MySQL database running with binary logging turned on. Each time the database is restarted a new binary log file is created in the format :

mysql-bin.00001
mysql-bin.00002
...
mysql-bin.0000n

My question is, is there a way to have the binary log file stay the same after each database restart.

So for example it's always mysql-bin.00001

Any help would be greatly appreciated.

2

There are five(5) methods you can do

METHOD #1

Don't run restart. Delete the binlogs between shutdown and startup.

Let's say mysql-bin.* is in /var/lib/mysql. Run the following:

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

After startup, login to mysql and run

SHOW MASTER STATUS;

an you should see mysql-bin.000001

METHOD #2

Create a script to execute RESET MASTER; and have it executed

Step 01 : Add this line to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init.sql

Step 02 : Run the following

echo "RESET MASTER;" > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 03 : service mysql restart

METHOD #3

Do it by hand. Login to mysql and run

mysql> RESET MASTER;

METHOD #4

Erase binary logs immediately after restart

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
service mysql restart
mysql ${MYSQL_CONN} -e"RESET MASTER"

METHOD #5 (Permanent)

Step 01 : Go to my.cnf and comment out the log-bin option

[mysqld]
#log-bin=mysql-bin

or delete the line that says log-bin=mysql-bin

Step 02 : Run the following

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

Step 03 : Login to mysql and run

mysql> SHOW BINARY LOGS;

The output should be

ERROR 1381 (HY000): You are not using binary logging    

GIVE IT A TRY !!!

1

What is the real question?

If you are bothered by more and more binlogs piling up, then configure

expire_logs_days = 7

That will keep binlogs for only 7 days. That might be 1 binlog (if you stay up all the time); it might be a hundred (if you restart with ridiculous frequency). But they won't pile up forever.

I assume you have binlog turned on for some reason -- recovery? backup? etc? Each of those cannot have you blindly throwing away binlogs as Rolando suggests in some of his methods.

Your Answer

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