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 Backups options in whm

Discussion in 'Data Protection' started by bhanuprasad1981, Feb 6, 2011.

  1. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    i see "Backup SQL Databases (at least "Per Account" is needed to use the restore feature)" has three options :-

    1. per account
    2. entire mysql directory
    3. peraccount and mysql directory

    can some one shed light on the difference in between all three ? , i am having issue swith daily backups because of large mysql db size (around 50gb) backups run almost 7-8 hours a day , all that time server is either completely slow or mysql goes down :(
     
  2. LinuxTechie

    LinuxTechie Well-Known Member

    Joined:
    Jan 22, 2011
    Messages:
    502
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello Bhanuprasad,

    The below given URL will provide the difference between the three.

    Configure Backup

    Also make sure to change your backup time to off peak hours which will prevent load issues in the server.
     
  3. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    hmm so i can use first option per account and it will make less server load , without causing me trouble while restoring backups in case of hdd fails ?
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Backing up only the accounts will mean that the backup spends less time on the MySQL directory, since less data will be backed up. Of note, you aren't going to get a full copy of the MySQL table (/var/lib/mysql/mysql) for any non-cPanel accounts, so if you have the root MySQL data become corrupted, you won't have a backup of that data.

    Rather than using the cPanel backup, is there any reason you don't simply take a full MySQL backup periodically instead? It might be less load intensive that way. You could then not make a backup of MySQL for the cPanel accounts on a daily basis (and possibly then change your backup times to once a week instead for cPanel backups to cut down on load).

    You can simply cron run the following to get a full backup of MySQL:

    Code:
    /usr/bin/mysqldump --all-databases | /bin/gzip > /home/alldatabases.sql.gz
    A few notes:

    1. I've given the command with the paths to /usr/bin/mysqldump and /bin/gzip due to the fact it would likely not work in cron if you don't have the full paths. If you are just issuing the command in root SSH directly, you can simply issue it as:

    Code:
    mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
    2. The command is using gzip so that it will be compressed to take up less space

    3. I have the saved file going to /home in order to have it take up less space, since /var normally doesn't have as much space as /home might. If you want to use some other partition, feel free to do so.

    You may also want to scp the file to an off site server instead. You are welcome to revise the command to have it scp the file after saving it and then remove the file. Alternatively, you could create a bash script to have it backup the databases, scp the file and then remove it after scp.

    If you ever need to restore from the backup, you'll need the MySQL root password:

    Code:
    cat /root/.my.cnf
    Once you have the password, then you can use this command to restore from that file:

    Code:
    gunzip < /home/alldatabases.sql.gz | mysql -u root -p
    You'll be asked for the password here, so simply enter the one noted in /root/.my.cnf location.
     
  5. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    thanks for reply , can i know what you mean by non cpanel accounts ?
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Hello,

    As I indicated, the root user information in /var/lib/mysql/mysql will not be backed up:

    If you have the root MySQL data (anything root user for MySQL has in /var/lib/mysql/mysql table) get corrupted, then it isn't going to be backed up.

    Thanks.
     
  7. bhanuprasad1981

    bhanuprasad1981 Well-Known Member

    Joined:
    Aug 5, 2008
    Messages:
    222
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    i offer shared hosting on server i need cpanel backup of clients in case of hdd failures ,so is it advised for me to backup root mysql data ?
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    It is advised for you to backup the root MySQL data, which is why I brought it up as a consideration.
     
Loading...

Share This Page