Backup MySQL Database Using Shell Script

Regular backup of mysql databases are also as important as backing up your code. So I wrote this script to backup all of my databases on local disk. I then added gzip to compress the sql file to save disk space.  You can always copy them over network so as to retain multiple copies of your database.
Please replace the “mysqlpassword”  mentioned in the script with your mysql root password. This script will create a file as dblist  in /usr/local that will list all the databases in MySQL Server.
The db’s are backed up in /Backup/mysqlbackup directory with the directory name as the date of the day. Path of the dblist and backup destination can be modified by users.

#!/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

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf

 Regular backup of mysql databases are also as important as backing up your code. So I wrote this script to backup all of my databases on local disk. I then added gzip to compress the sql file to save disk space.  You can always copy them over network so as to retain multiple copies of your database.

Please replace the “mysqlpassword”  mentioned in the script with your mysql root password. This script will create a file as dblist  in /usr/local that will list all the databases in MySQL Server.

The db’s are backed up in /Backup/mysqlbackup directory with the directory name as the date of the day. Path of the dblist and backup destination can be modified by users.

    #!/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

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output
======================================================
    ./mysql.sh

    17Sep2013_backups
    database1
    database1.sql.gz file saved…
    hello_db
    hello_db.sql.gz file saved…
    site2
    site2.sql.gz file saved…
    test
    test.sql.gz file saved…

Regular backup of mysql databases are also as important as backing up your code. So I wrote this script to backup all of my databases on local disk. I then added gzip to compress the sql file to save disk space.  You can always copy them over network so as to retain multiple copies of your database.
Please replace the “mysqlpassword”  mentioned in the script with your mysql root password. This script will create a file as dblist  in /usr/local that will list all the databases in MySQL Server.
The db’s are backed up in /Backup/mysqlbackup directory with the directory name as the date of the day. Path of the dblist and backup destination can be modified by users.

#!/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

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf

Regular backup of mysql databases are also as important as backing up your code. So I wrote this script to backup all of my databases on local disk. I then added gzip to compress the sql file to save disk space.  You can always copy them over network so as to retain multiple copies of your database.
Please replace the “mysqlpassword”  mentioned in the script with your mysql root password. This script will create a file as dblist  in /usr/local that will list all the databases in MySQL Server.
The db’s are backed up in /Backup/mysqlbackup directory with the directory name as the date of the day. Path of the dblist and backup destination can be modified by users.

#!/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

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh
17Sep2013_backups
database1
database1.sql.gz file saved…
hello_db
hello_db.sql.gz file saved…
site2
site2.sql.gz file saved…
test
test.sql.gz file saved…

- See more at: http://linoxide.com/linux-shell-script/backup-mysql-database/#sthash.YnjWsjRi.dpuf