How To Back Up & Restore MySQL Databases automatically

The mysqldump is a command-line utility that is used to generate the backup of the MySQL database.

Step1

Login to the server as the root user. We need to run below command as the root user for taking backup.

===

# mkdir -p /home/mysqlbackup/dbbackup/
# mkdir -p /home/mysqlbackup/logs/
# echo ‘show databases;’ | mysql | grep -v ^Database | grep -v cphulk | grep -v eximstats | grep -v information_schema | grep -v performance_schema > /home/mysqlbackup/backuplist.txt

# for i in `cat /home/mysqlbackup/backuplist.txt`; do mysqldump $i > /home/mysqlbackup/dbbackup/$i.sql; echo “$i” >> /home/mysqlbackup/logs/`date +%Y-%m-%d`_completed_dump.txt; done

====

We have the list of databases on the file /home/mysqlbackup/backuplist.txt.

Restoring the MySQL backup.

Run the below script in the terminal as root user for restoring the databases automatically.

# for i in `/home/mysqlbackup/backuplist.txt`;mysql $i < $i.sql; echo “$i” >>/home/mysqlbackup/restored.txt ; done