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.

backup databases

Discussion in 'Data Protection' started by hossein, Aug 17, 2014.

  1. hossein

    hossein Member

    Joined:
    Mar 23, 2014
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,
    i want to backup all databases in my server every 4 hour,
    any body can help me to solve this problm ?
    thank you :)
     
  2. mageshm

    mageshm Well-Known Member

    Joined:
    Apr 17, 2014
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Chennai, INDIA
    cPanel Access Level:
    DataCenter Provider
    Hi hossein,

    For your requirement you need to done this by shell script.

    Create a file “mysql_backup.sh” on your server and put the below code. Assign the executable permission on your shell file. like 755

    All database on cpanel server.
    ===============================
    Code:
    #!/bin/bash
    
    # Add your backup dir location, password, mysql location and mysqldump location
    DATE=$(date +%d-%m-%Y)
    BACKUP_DIR="/backup/test-backup"
    MYSQL_USER="root"
    MYSQL_PASSWORD="***"
    MYSQL=/usr/bin/mysql
    MYSQLDUMP=/usr/bin/mysqldump
    
    # To create a new directory into backup directory location
    mkdir -p $BACKUP_DIR/$DATE
    
    # get a list of databases
    databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
    
    # dump each database in separate name
    for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
    done

    single database on cpanel server.
    ==================================

    Code:
    #!/bin/bash
    password="***"
    date_format=`date +%d-%m-%Y`
    mysqldump -u root -p [password] 2daygeek | gzip -9 > /backup/db/yourdbname$date_format.sql.gz
    find /backup/db/test* -mtime +5 -exec rm {} \;
    
    cron job for every 4 hour.
    ==========================
    To run the shell file every 4 hour you need to set the cron job like below.

    Code:
    0 */4 * * * /bin/mysql_backup.sh
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    There are no native options to backup all databases every four hours. The previous post should help you setup the cron job.

    Thank you.
     
  4. hossein

    hossein Member

    Joined:
    Mar 23, 2014
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello mageshmn , :)
    backups save to wich folder ?
    may i use remote to move backups to ftp server ?
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    The example provided would backup all databases to /backup/test-backup, but you could modify the script to change that. You would have to add an additional step to transfer your backups to a remote server using a utility such as SFTP or Rsync.

    Thank you.
     
  6. YasIT

    YasIT Active Member

    Joined:
    Jan 3, 2014
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello

    It is also simple to back up all of the databases on a server with command :

    mysqldump --all-databases > all_databases.sql

    But when restore full backups(files,logs and ...) cpanel and restore sql with "mysql -u root -p passwordroot < all_databases.sql" on another server all websites cannot connect to database but size databases in MySQL Databases is correct and login in to phpmyadmin

    so, If I use my this shell This problem will be repeated?

    Please help , thank you.
     
  7. hossein

    hossein Member

    Joined:
    Mar 23, 2014
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello Yas It
    din't care about it ,
    when you are using above shell script all databases exported from my sql ,
    i'm check all thing good and database worked on any server...
    could you test 1 database on another server ? give us out put of logs...
    thank you
     
  8. YasIT

    YasIT Active Member

    Joined:
    Jan 3, 2014
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Hello

    you testing with single database , i dump all databases!
     
  9. hossein

    hossein Member

    Joined:
    Mar 23, 2014
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    i was test with all databases.
     
  10. hossein

    hossein Member

    Joined:
    Mar 23, 2014
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    thanks every body to help me :)
     
    #10 hossein, Aug 19, 2014
    Last edited: Aug 19, 2014
Loading...

Share This Page