The ‘mysqldump’ command proves useful when backing up a MySQL database. However, when dealing with servers that have several hundred databases inside, creating a Bash script can automate the task efficiently. Two versions of scripts are typically used: one for backing up the entire MySQL database and the other for only backing up InnoDB databases.
The Bash script for backing up InnoDB databases proves useful when a database server crashes due to an InnoDB issue and the MySQL database server is unable to start. By using the InnoDB force recovery option and this script, you can later dump the SQL queries to a specified folder and use them to recreate the ibdata and ib_logfile.
For restoring backups, the included script can be used for both MySQL dump and InnoDB dump files. Ensure the script has executable permission and can also be used on servers without Cpanel installed.
Using a Bash script optimizes your MySQL backups by automating the process and ensuring InnoDB databases are properly backed up in case of a crash. The included script makes restoring backups easy, ensuring your databases are always safe and secure.
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
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 ;
rm -rfv tmp.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 ;
rm -rfv sys.sql ;
rm -rfv tmp.sql ;
date ;
Script to restore database:
#Script to restore database. Remove comment if not root and need to define password.
#!/bin/bash
echo "Removing cPanel system databases dumps..."
rm -f modsec.sql mysql.sql roundcube.sql whmxfer.sql \
leechprotect.sql eximstats.sql cphulkd.sql horde.sql \
performance_schema.sql perl5.sql sys.sql
rm -f logaholic*.sql mydns_*.sql
LOG="Databaserestore.txt"
echo "==== Restore started $(date) ====" >> "$LOG"
for SQL in *.sql; do
[ -e "$SQL" ] || continue
DB="${SQL%.sql}"
echo "Restoring $DB..."
# Create database if missing
if ! mysql -e "CREATE DATABASE IF NOT EXISTS \`$DB\`"; then
echo "FAILED creating database $DB" | tee -a "$LOG"
continue
fi
# Import
if mysql "$DB" < "$SQL"; then
echo "SUCCESS $DB" | tee -a "$LOG"
else
echo "FAILED import $DB" | tee -a "$LOG"
fi
sleep 0.2
done
echo "==== Restore finished $(date) ====" >> "$LOG"
