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

| improve this answer | |
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.

| improve this answer | |

Your Answer

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