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