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
Leave A Comment