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.
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