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.

how to mysql dump large databases

Discussion in 'Data Protection' started by bhanuprasad1981, Oct 8, 2011.

  1. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    i run cpanel backups weekly , but issue is mysql backups run really long nearly 4 hours causing lot of slowness to the sites is there any way we can do fast mysql dumps my database size is around 20gb :( i am looking for some script which can run on cronjobs.


    thnaks.
     
  2. chrismfz

    chrismfz Well-Known Member

    Joined:
    Jul 4, 2007
    Messages:
    109
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Greece
    cPanel Access Level:
    DataCenter Provider
    You should consider to exclude mysql from backup and run mysqlhotcopy or just rsync the database from /var/lib/mysql
    to another place.

    Easier, less load and same results in case of disaster (just copy them back in mysql folder).
     
  3. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    yes i heard of mysql hotcopy , but i want to know wether there is any custom bash script which can run auto like cpanel backups
     
  4. milind

    milind Well-Known Member

    Joined:
    Jan 15, 2006
    Messages:
    113
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Nasik (Maharashtra), India
    cPanel Access Level:
    Root Administrator
    As far as I know you have to write a bash script for MySQL database backups.
     
  5. ModServ

    ModServ Well-Known Member

    Joined:
    Oct 17, 2006
    Messages:
    332
    Likes Received:
    5
    Trophy Points:
    18
    Location:
    Egypt
    cPanel Access Level:
    Root Administrator
    That's the mysqlhotcopy syntax:

    PHP:
    /usr/bin/mysqlhotcopy -u root -p YOURMYSQLROOTPASSWORD DB_NAME /PATH/TO/FOLDER --allowold --keepold
    –allowold: This options renames the old backup to {datbase-name}_old before taking a new backup.

    –keepold: This option instructs the mysqlhotcopy to keep the old backup (i.e the renamed _old) after the backup is completed.

    Source:
    Backup and Restore MySQL Database using mysqlhotcopy

    May that help.
     
  6. nobodyk

    nobodyk Well-Known Member

    Joined:
    Aug 1, 2010
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Note that msqlhotcopy doesn't work with InnoDB, but it's definitively the preferred option to mysqldump. Alternatively, you can run another instance of your db on a local vps. Every time there's a write operation it will write twice however, once on the real MySQL db and again on the backup MySQL db. The load may be a little higher, but this virtually eliminates any table locking during the whole db backup. Note that over 95% of queries are read and not write.
     
  7. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
  8. luiznaninho

    luiznaninho Registered

    Joined:
    Nov 25, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
Loading...

Share This Page