How to take Full Databases backup using script
How to take Full Databases backup using script
Hello
This is a simple script to take DB backups.
Step1. Login to the server via SSH
Step2. Open the file /root/dbbackup.sh (you can use any names) using your favourite editor.
Step3. Put the below script and save the file.
#!/bin/bash mkdir -p /home/dbbackups path="/home/dbbackups" today=`date +%F` dow=`date +%a` # List all databases in server mysql -e "show databases" | egrep -vw "(Database|information_schema|performance_schema)" > $path/dblist.txt rm -fv $path/dbfailed.txt rm -fv $path/dbfailed-tmp.txt rm -fv $path/dbfailed-mail.txt mkdir -p $path/$dow /usr/bin/mysqldump --events mysql > $path/$dow/mysql.sql if [ $? -ne 0 ] ; then mysqlcheck -r mysql ; /usr/bin/mysqldump --events mysql > $path/$dow/mysql.sql if [ $? -ne 0 ] ; then echo "mysql" > $path/dbfailed.txt; fi fi for i in `cat $path/dblist.txt` ; do sleep 2; echo "Creating dbbackup of $i" ; /usr/bin/mysqldump $i > $path/$dow/$i.sql ; if [ $? -ne 0 ] ; then echo $i >> $path/dbfailed-tmp.txt; fi; done if [ -f $path/dbfailed-tmp.txt ] then for i in `cat $path/dbfailed-tmp.txt` do mysqlcheck -r $i /usr/bin/mysqldump $i > $path/$dow/$i.sql ; if [ $? -ne 0 ] ; then echo $i >> $path/dbfailed.txt; fi; done fi find /home/dbbackups/* -type f -mtime +30 -exec rm -f {} \;
Step4. The script permission should be 755
chmod 755 /root/dbbackup.sh
Step5. Run the command
# bash /root/dbbackup.sh
The DB backup will be stored on your /home/dbbackups
If you want to set it as cronjob
Step6. Open the crontab
crontab -e
Set the cronjob like this, You can set it as your wish.
30 1 * * 0,1,2,3,4,5,6 /root/dbbackup.sh
I hope this script will help you to take all DB backups,
Thanks
