If you want to dump a few database the command ‘mysqldump’ is sufficient. How about the server that have several hundred databases inside. It is easier if we create Bash script to do the task. There are 2 version of scripts I normally use. One is to backup whole MySQL database, the other is to backup InnoDB database only.
The databases will be dumped in the folder that we created first and dumped as *.sql.
The Bash script to backup InnoDB is useful when database server crash because of InnoDB and cannot start the MySQL database server. By using InnoDB force recovery and this script, we can later dump the sql query to the required folder and later can be use to recreate ibdata and ib_logfile.
For restoring, the included script can be use both for MySQL dump and InnoDB dump. Make sure the script have executable permission and the script can also be use in the server that does not use Cpanel.
Script to backup whole MySQL databases:
#Script to backup whole MySQL database. Remove comment if not root and need to define password. #!/bin/bash #USER="root" #PASSWORD="n8=?JBw*oJqlG,DH" OUTPUT="/mysqlbackup/" rm "$OUTPUT/*sql" > /dev/null 2>&1 #databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" # mysqldump --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$db.sql mysqldump --databases $db > $OUTPUT/$db.sql echo "Sleep for 10 seconds" sleep 10 echo "wakeup and run again" fi done
Script to backup the whole MySQL with time and remove the backup after 10 days.
#Script to backup whole MySQL database. Remove comment if not root and need to define password. #!/usr/bin/bash #USER="testuser" #PASSWORD="m2ViCRl2osE4P" #Set date and time for the database creation today=`date +%b-%d-%H-` OUTPUT="/mysqlbackup/" rm "$OUTPUT/*sql" > /dev/null 2>&1 #databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" #mysqldump --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$today$db.sql #Set single transaction for InnoDB on production server to prevent locking #mysqldump --single-transaction=TRUE --databases $db > $OUTPUT/$today$db.sql #gzip the database to reduce space. May consume resource. #mysqldump --single-transaction=TRUE --databases $db | gzip -9 > $OUTPUT/$today$db.sql.gz mysqldump --databases $db > $OUTPUT/$today$db.sql echo "Sleep for 10 seconds" sleep 1 echo "wakeup and run again" fi done rm -rfv mysql.sql ; rm -rfv performance_schema.sql ; rm -rfv sys.sql ; find /mysqlbackup/*.sql -mtime +10 -exec rm -rfv {} \;
Script to backup Mysql databases from Cpanel server to another Cpanel server. Useful for migration.
#Script to backup whole MySQL database. Remove comment if not root and need to define password. #!/bin/bash #USER="root" #PASSWORD="n8=?JBw*oJqlG,DH" date ; OUTPUT="/mysqlbackup/" rm "$OUTPUT/*sql" > /dev/null 2>&1 #databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` databases=`mysql -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" # mysqldump --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$db.sql mysqldump --databases $db > $OUTPUT/$db.sql echo "Sleep for 10 seconds" sleep 1 echo "wakeup and run again" fi done rm -rfv modsec.sql ; rm -rfv mysql.sql ; rm -rfv roundcube.sql ; rm -rfv whmxfer.sql ; rm -rfv leechprotect.sql ; rm -rfv eximstats.sql ; rm -rfv cphulkd.sql ; rm -rfv horde.sql ; rm -rfv logaholic* ; rm -rfv performance_schema.sql ; rm -rfv mydns_* ; rm -rfv perl5.sql ; rm -rfv sys.sql ; date ;
Script to backup InnoDB database only:
#Script to backup InnoDB database. Remove comment if not root and need to define password. #!/bin/bash #USER="root" #PASSWORD="07d9ukwxbiq7" OUTPUT="/mysqlinnodb/" rm "$OUTPUT/*sql" > /dev/null 2>&1 databases=`mysql -N mysql -e "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" | awk '{print $1}' | sort | uniq` for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" # mysqldump --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$db.sql mysqldump --databases $db > $OUTPUT/$db.sql echo "Sleep for 10 seconds" sleep 10 echo "wakeup and run again" fi done
Script to backup InnoDB databases from Cpanel server to another Cpanel server. Useful for migration.
#Script to backup InnoDB database. Remove comment if not root and need to define password. #!/bin/bash #USER="root" #PASSWORD="07d9ukwxbiq7" OUTPUT="/mysqlinnodb/" rm "$OUTPUT/*sql" > /dev/null 2>&1 databases=`mysql -N mysql -e "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';" | awk '{print $1}' | sort | uniq` for db in $databases; do if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then echo "Dumping database: $db" # mysqldump --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$db.sql mysqldump --databases $db > $OUTPUT/$db.sql echo "Sleep for 10 seconds" sleep 10 echo "wakeup and run again" fi done rm -rfv modsec.sql ; rm -rfv mysql.sql ; rm -rfv roundcube.sql ; rm -rfv whmxfer.sql ; rm -rfv leechprotect.sql ; rm -rfv eximstats.sql ; rm -rfv cphulkd.sql ; rm -rfv horde.sql ; rm -rfv logaholic* ; rm -rfv performance_schema.sql ; rm -rfv mydns_* ; rm -rfv perl5.sql ; date ;
Script to restore database:
#Script to restore database. Remove comment if not root and need to define password. #!/bin/bash # USER="root" # PASSWORD="07d9ukwxbiq7" #In case forgot to remove cpanel/system database rm -rfv modsec.sql ; rm -rfv mysql.sql ; rm -rfv roundcube.sql ; rm -rfv whmxfer.sql ; rm -rfv leechprotect.sql ; rm -rfv eximstats.sql ; rm -rfv cphulkd.sql ; rm -rfv horde.sql ; rm -rfv logaholic* ; rm -rfv performance_schema.sql ; rm -rfv mydns_* ; rm -rfv perl5.sql ; #In case forgot to remove cpanel/system database echo "Restoring Databases...." for SQL in *.sql do DB=${SQL/\.sql/} echo restoring $DB # mysql -u $USER -p$PASSWORD $DB < $SQL mysql $DB < $SQL echo Done $DB echo Done $DB >> Databaserestore.txt sleep 5 done echo "......Complete ALL DB!"