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 Backup

Discussion in 'General Discussion' started by Garry-UK, Jul 22, 2008.

  1. Garry-UK

    Garry-UK Member

    Joined:
    Jul 18, 2004
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    I would like to backup some MySQL databases on my VPS every hour or 2, then rsync it to a backup server, how would I go about doing this. I think I read somewhere you shouldn't do a direct rsync as the tables would not be locked or something like that. So I have done some looking around and come up with:

    mysqlhotcopy - with this I noticed it creates: frm , MYI and MYD
    How would I restore these. Would I stop mysql and copy them into username_database and start mysql, would I need to do any repair as the backup be either hourly or every 2 hours

    mysqldump - with this I noticed it create an sql file with all the info in it, the only problem I see with this, if I want to restore just one table I would need to remove it from the database then replace it.

    What is the best way ?

    phpMyAdmin says: MyISAM is the default storage engine on this MySQL server.

    The databases are small at the moment but hopefully the site will go well, which mean the database will get bigger.
     
  2. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    MysqlHotCopy,
    Yes you would restore the files just as they are. I would suggest stopping mysql first so you retain the current backup integrity while restoring.

    MysqlDump,
    This is probably the most preferred method. But for dumping tables and restoring tables, this could be a daunting task if you have many files. So here is a very nice script which will dump and restore and works like a dream.

    Dump script
    Restore script

    Of course you may want to add some option such as
    --databases --lock-tables

    Hope this gets you started.
     
  3. Garry-UK

    Garry-UK Member

    Joined:
    Jul 18, 2004
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    Thanks for those links :D
    Doesn't mysqldump use lock tables or lock database as default ?
     
    #3 Garry-UK, Jul 22, 2008
    Last edited: Jul 22, 2008
  4. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    Hmm.. I don't think it uses --lock-tables as default but regardless, I like to be sure of it. My common dump line would be something like this:

    mysqldump --opt --lock-tables --databases database_name > dumpfile.sql

    Without --databases you would need to create the empty database first if it doesn't exist.

    For database table dumps, I simply use the script.
     
  5. Garry-UK

    Garry-UK Member

    Joined:
    Jul 18, 2004
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Hi,

    Thanks for the info.
    One more questions should I be using: --add-drop-table

    o --add-drop-table
    Add a DROP TABLE statement before each CREATE TABLE statement.

    As these backups I am taking, should only be used in an emergency.
     
  6. jayh38

    jayh38 Well-Known Member

    Joined:
    Mar 3, 2006
    Messages:
    1,215
    Likes Received:
    0
    Trophy Points:
    36
    No, theres no need for that. If you use --databases the entire database is dropped and recreated if it exists.

    I just confirmed that these statements:
    DROP TABLE IF EXISTS and
    LOCK TABLES statements are by default included in the mysqldump with no options.

    Now the LOCK TABLES is for restoring, not for dumping. So to keep your data in sync, you would want to cut off apache and cronjobs during your restore since it would lock only the current table that its restoring.

    Or if you have many accounts that you don't want to interrupt, I simply rename their virtual container until the restore of their database is complete (if restoring manually) otherwise a cpanel restore would take care of its self.
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    One should always LOCK InnoDB tables when dumping and restoring. For such, the use of --single-transaction is better than --lock-tables. The Locks applied are READ locks, which allow data to be SELECTed but not modified (e.g. INSERT, DELETE).

    There is some excellent information on the subject in the MySQL manual:

    http://dev.mysql.com/doc/refman/5.1/en/backup.html
     
Loading...

Share This Page