How to create MySQL Database Backup using Shell Script

-

How to create MySQL Database Backup using Shell Script

I am going to represent a general Mysql Backup Script here.

-- You have to create appropriate directories for the backup task and make sure that file permission on configuration file, script file, lock file etc. are appropriately set so that only the intended user can access the various files and no one else. 

-- MySQL user (os user) that is used to create the database backup has a configuration file in his home directory that stores the password that is required to login into the database.

-- I have commented the uuencode command, you can test the script and mailing capability of the server and can uncomment the command to send mails if required.

-- The script is running under mysql user therefore he will have the rights over the backup and log files that will be created in the process.

I had put my script under /dba/mysql/scripts directory and give mysql appropriate privileges to run it.
You can place your script anywhere as you desire.

NOTE:
The script and backup & restore process must be discussed and tested thoroughly before applying to production level databases.


MySQL Backup Script
~~~~~~~~~~~~~~~~~~~~~~

# Script to backup MySQL databases

#!/bin/bash

# Lock file to check that only one instance of the Script run at a time
lock_file="/dba/locks/mysql_autobckp.lck"

# MySQL and Mysqldump settings
mysql_user="root"

# Create backup directory, log file and set permissions
backup_parent_dir="/mysql_backup"
backup_date=`date +%d_%m_%Y_%H_%M`
log_date=`date +%d%m%Y_%H%M`
backup_dir="${backup_parent_dir}/${backup_date}"
log_file="/dba/logs/mysqlbckp_${log_date}.log"
touch "${log_file}"
mkdir -p "${backup_dir}"
chmod -R 700 "${backup_dir}"
chmod 600 "${log_file}"

# DO NOT BACKUP these databases
#ignore_db="test mynewdb mytest"
ignore_db="mynewdb mytest"

# Check whether Log File is accessible or not
if [ ! -f "${log_file}" ] && [ ! -w "${log_file}" ]; then
# Generate a mail saying that log file inaccessible and send it to user.
log_file="/dev/null"
fi

# Check whether Lock File exists or not
if [ -f "${lock_file}" ]; then
echo " The Mysql Database(s) backup script is already running .........." >> ${log_file}
echo " DBA Investigation required .........." >> ${log_file}
# uuencode ${log_file} ${log_file} | mail someone@domain.com
exit 0
else
touch /dba/locks/mysql_autobckp.lck
chmod 600 /dba/locks/mysql_autobckp.lck
fi

# Get MySQL databases if running
mysql_alive=`mysqladmin --user=${mysql_user} -s ping|grep alive|wc -l`
if [ "$mysql_alive" == 1 ]; then
{
mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} -B | sed /^Database$/d`
}
else
{
echo "Oops Can’t Connect to Database, may be your database server is DOWN" >> ${log_file}
rm -f /dba/locks/mysql_autobckp.lck
echo "Lock File removed ....." >> ${log_file}
# uuencode ${log_file} ${log_file} | mail someone@domain.com
exit 0
}
fi

# Check whether Backup directory created and writable by user
if [ -d "${backup_dir}" ] && [ -w "${backup_dir}" ]; then
{
echo "Ready to Perform Backup ......." >> ${log_file}
echo " " >> ${log_file}
}
else
{
echo "Backup Directory either does not exists or not Writeable by User: `echo $USER|tr [a-z] [A-Z]`" >> ${log_file}
rm -f /dba/locks/mysql_autobckp.lck
echo "Lock File removed ....." >> ${log_file}
# uuencode ${log_file} ${log_file} | mail someone@domain.com
exit 0
}
fi

echo " " >> ${log_file}
echo "MySQL Database(s) Backup Started......." >> ${log_file}
echo " " >> ${log_file}
echo "MySQL Database(s) Backup Process ID: $$ " >> ${log_file}
echo " " >> ${log_file}

# Backup and compress each database
for database in $mysql_databases
do

skipdb=-1
if [ "$ignore_db" != "" ]; then
      for i in $ignore_db
      do
      [ "$database" == "$i" ] && skipdb=1 || :
      done
            if [ "$skipdb" == 1 ]; then
            echo "Skipping backup of \"${database}\" database ........." >> ${log_file}
            echo " " >> ${log_file}
            fi
fi

if [ "$skipdb" == -1 ]; then

if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ];then
      additional_mysqldump_params="--skip-lock-tables"
else
      additional_mysqldump_params=""
fi

echo "Creating backup of \"${database}\" database .........." >> ${log_file}
echo " " >> ${log_file}
echo "Start Time:" >> ${log_file}
echo `date +%d%m%Y_%H:%M:%S` >> ${log_file}
echo " " >> ${log_file}
mysqldump ${additional_mysqldump_params} --user=${mysql_user} ${database} | gzip > "${backup_dir}/${database}_bckp.sql.gz"
echo "Finish Time:" >> ${log_file}
echo `date +%d%m%Y_%H:%M:%S` >> ${log_file}
echo " " >> ${log_file}
chown mysql:mysql "${backup_dir}/${database}_bckp.sql.gz"
chmod 600 "${backup_dir}/${database}_bckp.sql.gz"
fi

done

echo "MySQL Database(s) Backup Completed ......." >> ${log_file}
echo " " >> ${log_file}
rm -f /dba/locks/mysql_autobckp.lck
echo "Lock File removed ....." >> ${log_file}
# uuencode ${log_file} ${log_file} | mail someone@domain.com

Comments

Post a Comment

Back To Top

Popular posts from this blog

How to save video from Internet Explorer

-
How to save video from Internet Explorer Here I am going to share an experience with you, where I was required to get the video file that I was playing in Internet explorer out of Temporary directory and save it to some permanent disk folder. Note: It is far better if you use video download tools with the web browser. Internet Explorer Version: 7.0.6xxx [Open Internet Explorer – Help – About Internet Explorer] Operating System: Windows Vista I was not playing file from you tube [I do not see any tmp file while watching video from youtube.] I found the file in following directory: C:\Users\<UserName>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low [ Although the directory is not visible in my computer system, so I directly put it in address bar/location bar in windows explorer. ] File name was fla3870.tmp [May be in your system It will be by different name] You have to find the video file by trick technique for example: 1.
Read more

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more