Backup MySql Database Linux Using Shell Scripts
The night will discuss about MySql again which previously was discussed Reset MySql [MariaDB] Password. If you have an important data you can create the backup using shell script and if using linux.
So i write the script for backup all database in local disk, then added gzip formats to compress sql file to storing disk. If have a server you can try this script for backup mysql data.
Script Backup MySql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/bin/bash
### Create Directory with Date where Database backup will be stored. ####
month=$(date | awk '{print $2}')
day=$(date | awk '{print $3}' )
year=$(date | awk '{print $6}')
foldername=$(echo $day$month$year"_backups")
### List all the databases in /usr/local/dblist file. ####
mysql -u root -p'mysqlpassword' -e 'show databases' >/usr/local/dblist
list=$(cat /usr/local/dblist)
echo $foldername
### Create Backup Directory in /Backup/mysqlbackup … ####
mkdir -p /Backup/mysqlbackup/$foldername
for i in $list
do
echo $i
mysqldump -u root -p'mysqlpassword' $i | gzip > /Backup/mysqlbackup/$foldername/$i.sql.gz
echo " "$i".sql.gz file saved.."
done
Replace this text with marked blue (mysqlpassword) with your password mysql, the script will create a file dblist. In /usr/local that will list all database in MySql Server.
This script will execute for store database /Backup/mysqlbackup/ directories, or you can put this Shell script in crontab and run everyday. In this way you will have daily backups of all database.
Sample Output
1
2
3
4
5
6
7
8
9
10
11
./mysql.sh
28June2016_backups
pminew
pminew.sql.gz file saved...
logindb
logindb.sql.gz file saved...
Conclusion
This post only use shell script for backup mysql database, the result output just is sample and may be will different in your machine. If this post there are wrong you can fill below comment for suggest me, so correct me if i wrong. Happy coding, Thanks!