LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Blogs > jkzfixme
User Name
Password

Notices

Rating: 4 votes, 3.00 average.

Automated MySQL backups

Posted 09-15-2009 at 01:50 PM by jkzfixme
Updated 09-15-2009 at 04:00 PM by jkzfixme

Lately I had two SQL servers crash and thankfully have implemented a pretty good backup solution using a script called AutoMySQLBackup, which uses mysqldump to create SQL dumps of your databases. Here is the play by play so you to can get back up and running with expedience. I am assuming the use of ubuntu in this tutorial as that is what I typically use, although the only real difference here is in the sudo command. If you are using a different distro just disregard any sudo's and login as root.

The first thing you are going to need to do is download the script

Quote:
next you will need to edit the file for your particular setup

Quote:
sudo vi automysqlbackup.sh.2.5
If you are farmiliar with the basics on how to use vi then you can skip right by this and edit, if not here is a crash course.

In order to change to append mode you need to press the
Quote:
escape key
and then
Quote:
a
This will make it so you can actually edit the text. The following in bold is what you need to edit

Quote:
USERNAME=root
[...]
PASSWORD=yoursqlpassword
[...]
DBHOST=localhost
[...]
List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
# Backup directory location e.g /backups
BACKUPDIR="/backups"
[...]
# Email Address to send mail to? (user@domain.com)
MAILADDR="youremail@domain.com"
Now you need to save it and exit vi which is accomplished by doing the following.

Quote:
escape key
then
Quote:
:wq
whenever you use a colon command in vi you need to execute it using the enter key.

If you are unsure as to the exact names of databases you can easily list them via the command prompt by doing the following.

Quote:
mysql -u root -p
whereas "root" is the username and the -p option will prompt for the userís password

you can now use the command
Quote:
show databases;
and it will show you the full list. You can get out of this by using the command
Quote:
exit
The next step is to make the script executable using the command
Quote:
chmod 755 automysqlbackup.sh.2.5
Ok lets go ahead and make sure everything works by executing the script
Quote:
sh ./automysqlbackup.sh.2.5
If everything went well we want to make this automated via a cron job with the following commands

Quote:
cd /etc/cron.daily/
Quote:
sudo ln -s /usr/local/bin/automysqlbackup.sh.2.5 automysqlbackup
Any questions trials or tribulations feel free to msg me or leave a comment and I will do my best to help you out

Regards,
JKZfixme
Posted in Uncategorized
Views 1165 Comments 0
« Prev     Main     Next »
Total Comments 0

Comments

 

  



All times are GMT -5. The time now is 08:34 PM.

Main Menu
Advertisement

My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration