MySQL bin-logging

Hello,

Is this client running the server itself and planning to use it for replication? If not, I do not suggest enabling this option for any shared account, since the main point of bin logging is for replication purposes. It is not generalized logging for troubleshooting purposes.

Next, here's a good discussion on it on the MySQL 5.1 documentation area:

MySQL :: MySQL 5.1 Reference Manual :: 5.2.4 The Binary Log

Take note of this part:

Given that caveat, you can enable it this way in /etc/my.cnf (not via command line):

Code:

log-bin=base_name

For example, I added that to my machine using this:

Code:

log-bin=/var/lib/mysql/binlog

Then I restarted MySQL and it created these files in /var/lib/mysql:

Code:

binlog.000001
binlog.index

Each time the machine restarts, flushes the logs or reaches the max_binlog_size, it will increment a new binlog number (000001, 000002, etc.). If you want to have the log increment, simply set the max_binlog_size also in /etc/my.cnf file. I would suggest moving them by size rather than date stamp as you are suggesting, since size is a more meaningful value here.

Finally, since you haven't provided the MySQL version, so this is for MySQL 5.1. The 5.0 documentation and 4.1 documention pages can be reached by changing the number on the previously provided link to get details for those versions.

 

Just to clarify the setting, 0 means no expiration and is the default value for that expire_logs_days variable:

MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables

I normally like to link to the documentation as MySQL actually has good docs explaining their values.

 

They can be used for restore operations as indicated in the quote I posted about "for restore operations", but this logging slows down MySQL and I wouldn't ever enable it just for 1 account in a shared situation. There are other backup methods that will not slow down MySQL for everyone such as simply getting cPanel backups. Restoring from the bin logs is not as straight forward as restoring from the cPanel MySQL backups either.

You can certainly enable it, but I wanted to point out earlier it's really only ideal for replication purposes. It isn't the preferred method to create simple backups, although it is possible to use for restores. Basically, when you enable it, you are enabling it for the entire server for all databases. If it is only being added for 1 user's databases, the offset in slowdown isn't beneficial to warrant it. There are faster backup methods that are easier to perform for individual databases.

 

Certainly, you are very welcome.