How to Backup All Mysql Databases in Cpanel server in separate mysql files

How to Backup All Mysql Databases in Cpanel server in separate mysql files



mysqldump --databases db1 db2 db3 db4 > db_backup.sql

mysqldump --all-databases > db_backup.sql


That would work. Or, you can take the mysqldump databasename > backupname, and throw it in a for loop.

i.e.
******************************

SUFFIX=`date +%Y-%m-%d`
DBUSER="your database user"
DBHOST="your database host"
DBPASS="your password"
BACKUPDIR="where you want to backup to"

DBS=`mysql -u$DBUSER -h$DBHOST -p$DBPASS -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-mysql-$DATABASE.gz
mysqldump -u$DBUSER -h$DBHOST -p$DBPASS $DATABASE | gzip --best > $BACKUPDIR/$FILENAME
fi
done


If you want to backup only .sql files, the following code should work:

SUFFIX=`date +%Y-%m-%d`
DBUSER="root"
DBHOST="localhost"
DBPASS="PASSWORD HERE"
BACKUPDIR="/PATH/TO/USER/"

DBS=`mysql -u$DBUSER -h$DBHOST -p$DBPASS -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-mysql-$DATABASE.sql
mysqldump -u$DBUSER -h$DBHOST -p$DBPASS $DATABASE > $BACKUPDIR/$FILENAME
fi

done

Post a Comment

1 Comments

  1. The following script will create mysql files without any suffix in home/DB

    DBUSER="root"
    DBHOST="localhost"
    DBPASS="your-password-here"
    BACKUPDIR="/home/DB"

    DBS=`mysql -u$DBUSER -h$DBHOST -p$DBPASS -e"show databases"`

    for DATABASE in $DBS
    do
    if [ $DATABASE != "Database" ]; then
    FILENAME=$DATABASE.sql
    mysqldump -u$DBUSER -h$DBHOST -p$DBPASS $DATABASE > $BACKUPDIR/$FILENAME
    fi
    done

    ReplyDelete