MySQL is a relational database management system (RDMS), and one of the most powerful DBMS. Most of the websites including this blog using MySQL technology behind it. Since it free and it always bundled with the PHP and Linux, often it is called LAMP (Linux, Apache, MySQL, and PHP). If you know a little about programming in PHP, MySQL skill is a must to you to learn.
Okay i am not explaining what it MySQL, or what is SQL syntax. Now i just want to share with you a script to backup all the databases into compressed file. So you can backup your databases regularly easily to avoid data loss.
How to run/install it:
- First download the script below.
- Open the file and change the configuration, like username, password and target folder
- Make it executable with chmod +x scriptname.sh
- Run it with ./scriptname.sh
Here is the script:
#!/bin/bash PATH=/usr/sbin:/sbin:/bin:/usr/bin MyUSER="Your Username Here" MyPASS="Your Password Here" MyHOST="localhost" #Usually 'localhost', but if you know what you are doing, please change it MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" GZIP="$(which gzip)" DEST="/backup" #destination folder, /backup is default folder, you need to create it first MDB="$DEST/mysql" #i put all database backup under mysql folder, you can change it to anyname HOST="$(hostname)" NOW="$(date +"%d-%m-%Y")" FILE="" DBS="" IGGY="test mysql" DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" for db in $DBS do skipdb=-1 if [ "$IGGY" != "" ]; then for i in $IGGY do [ "$db" == "$i" ] && skipdb==1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$MDB/$db.$HOST.gz" $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --complete-insert $db | $GZIP -9 > $FILE echo "Backup $FILE.....DONE" fi done
Or download it here.
And also you can automatically send it to email or another backup server, so you don’t have to worry if something happen with your server, your data will be safe. And last you can run this script with cron, so you can have your database backup regularly.
I will write how to run it with cron and how to automatically send it to another server. So stay tune 🙂