3

I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be stored on another partition and also want to schedule binary logging to run at regular intervals in a day.

I am using MySQL 5.5.

7

You should be able to set the binary logs to a new partition by providing the absolute path:

log-bin=/my/other/dir/mysql-bin

You might need to copy the old binary logs to the new directory and update the .index file with the new path information (Do this only when mysql is not running).

Why would you only want to binary log during certain intervals of the day? Doing this could really mess with your ability to restore at any given time.

  • 1
    This is a thorough +2 (I can only give +1) especially on the part of editing of the mysql-bin.index file. @RPK, instead of binary logging at certain parts of the day, you may want to do FLUSH LOGS every hour and set expire_logs_days to 7 in /etc/my.cnf to control growing binary logs in the face of a heavy write environment. – RolandoMySQLDBA Aug 4 '11 at 18:32
  • At present I am not using Binary Logging. As suggested by you, I am going to add line: MySQLd log-bin in my.ini file (I am using Windows 7). How to update the index? – RPK Aug 5 '11 at 5:51
  • @RPK if you weren't using binary logging previously you can skip that. – Derek Downey Aug 5 '11 at 13:22
1

Step 1: Check current log-bin path and naming format

mysql> show variables like '%log_bin%'; enter image description here

Step 2: Change the log-bin path in my.cnf file as below log_bin = new_path/naming_format

Step 3: Shutdown your mysql server.

Step 4: Move your bin log files from previous location to new location.

Step 5: Star you mysql server.

Step 6 : Check again that, bin-log moved in new location using below command

mysql> show variables like '%log_bin%'; enter image description here

Above I made change log-bin location from "/u01/mysql/mysql/data/" to "/u02/mysql/mysql_binlog/"

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.