The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL bin-logging

Discussion in 'Workarounds and Optimization' started by shacker23, Aug 17, 2010.

  1. shacker23

    shacker23 Well-Known Member

    Joined:
    Feb 20, 2005
    Messages:
    263
    Likes Received:
    1
    Trophy Points:
    16
    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
     
  2. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    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.
     
  3. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,456
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  4. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
  5. shacker23

    shacker23 Well-Known Member

    Joined:
    Feb 20, 2005
    Messages:
    263
    Likes Received:
    1
    Trophy Points:
    16
    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:

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

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    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.
     
    #6 Miraenda, Aug 18, 2010
    Last edited: Aug 18, 2010
  7. shacker23

    shacker23 Well-Known Member

    Joined:
    Feb 20, 2005
    Messages:
    263
    Likes Received:
    1
    Trophy Points:
    16
    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.
     
  8. Miraenda

    Miraenda Well-Known Member

    Joined:
    Jul 28, 2004
    Messages:
    242
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Coralville, Iowa USA
    Certainly, you are very welcome. :)
     
Loading...

Share This Page