MYSQL Back On Unix

MYSQL Back On Unix

Shell script is a script where we are writing different types of commands and executing those commands from a single file. We can execute that command manually, by entering command one by one. But if we use shell script we have to write those commands into a text file for the first time and then we can run those commands as many times as required.

In this article first I will show you, Complete Script. Later on, you will get a description of that script. I assumed that you know about shell scripting, mysqldump and crontab.

Operating System: Any Linux or UNIX.

Main Script (for backup your mysql database):

This shell script will make the backup process of a database automatic. Just copy and paste this script in a text editor, put database user name, password, and database name. I will use mysqlump command to backup the database. Later on you will get description of each line.
1. Make a directory name “backup” and “oldbackup”
1
2

mkdir /backup
mkdir /oldbackup
2. Now make file name “backup.sh” and edit with any editor you like

I’m using vi here-
1

# vi /backup/backup.sh

Now write these lines into backup.sh file:
#!bin/bash
cd /backup
echo “You are In Backup Directory”
mv backup* /oldbackup
echo “Old Databases are Moved to oldbackup folder”
Now=$(date +”%d-%m-%Y–%H:%M:%S”)
File=backup-$Now.sql
mysqldump –u user-name –p ‘password’ database-name > $File
echo “Your Database Backup Successfully Completed”

Script Description:

Remember, in line number 8: Put your Database username, Password, database name after mysqldump command.

When we run this script, first it goes to a /backup directory. Then it will move old database backup files to /oldbackup folder. Next it generates a file name from system date and time. And finally mysqldump command will generate a database backup file with “.sql” format
3. Set permission to backup.sh file executable
1

# chmod +x /backup/backup.sh
4. Running the Script
1

#./backup.sh

You will get following output after executing the script.
root@Server1:/download#./backup.sh
You are in Download Directory
Old Backup Database is Moved to oldbackup folder
database backup successful completed
root@Server1:/download#

NB: first time when you run this script you will get a message “no such file”, because you don’t have old backup file. No problem just runs again this script, problem will be solved.
5. Schedule the Backup using cron

Using Cron job you can execute this script in a certain time, and all works will be done automatically. Use crontab command to edit editing cron schedules.

#crontab –e

Just add below line in the editor and save it.
0 13 * * * * /backup/backup.sh

In this way every day 1PM your database will back up in your desired folder. Please visit crontab manuals for more details about setting the cron jobs.

This is a very basic script for the beginners. Hope you can use the same idea for more complex backup. We will try to come up with new scripts to automate further. Please ask any question you have. We will try our best to address your questions. Thanks for staying with us.

No related posts.