MySQL binary logging with just one log file

binlogMySQLmysqld

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.

Best Answer

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 !!!

Related Question