Home » articles, Linux tips, Tutorial » 14 July 2018 » 1,445 supporters » No Comment »

Bash script to backup and restore MySQL, InnoDB database for Cpanel server

14 July 2018 1,445 supporters No Comment

mysqlIf 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 Shell scripts 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 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 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 restore database:

#Script to restore database. Remove comment if not root and need to define password.
#!/bin/bash
  
 
# USER="root"
# PASSWORD="07d9ukwxbiq7"
  
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 >> Databaserestore.txt
 sleep 5
done
echo "......Complete ALL DB!"



Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.