MySQL bin-logging

[shacker23]

I have a client who wants bin-logging enabled for mysql, and I'm not finding much info out there on setting this up for cPanel. Can anyone share a best-practices recipe for:

- Altering the mysql startup command so that binlogging is always on
- Specifying the log directory
- Automatically cleaning up so we never keep more than 30 days of binlogs
- Anything else to make cpanel and binlogging happy

TIA,
Scot

 

[Miraenda]

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:

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

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

log-bin=base_name

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

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

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

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.

 

[Infopro]

Don't forget to set an expire;
http://forums.cpanel.net/f189/mysql-logs-160110.html#post671013

 

[Miraenda]

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.

 

[shacker23]

Thanks for the fantastic responses Miraenda - just what we're looking for. No, we don't need to get into replication, but isn't binlogging the only way to do incremental database restores? They sent me this:

Well I'm not an expert, but as it was explained to me by Kelly (one of the IS&T DBAs) bin logging is also used for recovery. See <http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html>

To do incremental backups we need to have the increments.

So binlogging definitely seems to have a use outside of replication.

 

[Miraenda]

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.

 

[shacker23]

Ah, sorry, I should have clarified - The client is the only user on the server - they own all of the dbs on the server.

The MySQL docs say that the performance hit is so negligible that the tradeoff is generally worth it.

Everything's working great - thanks for the advice.

 

[Miraenda]

Certainly, you are very welcome.