Automated MySQL backups
Tags auto, automysqlbackup, backups, lamp, mysql
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
next you will need to edit the file for your particular setup
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
and then
This will make it so you can actually edit the text. The following in bold is what you need to edit
Now you need to save it and exit vi which is accomplished by doing the following.
then
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.
whereas "root" is the username and the -p option will prompt for the user’s password
you can now use the command
and it will show you the full list. You can get out of this by using the command
The next step is to make the script executable using the command
Ok lets go ahead and make sure everything works by executing the script
If everything went well we want to make this automated via a cron job with the following commands
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
The first thing you are going to need to do is download the script
Quote:
cd /usr/local/bin
sudo wget http://mesh.dl.sourceforge.net/sourc...lbackup.sh.2.5
sudo wget http://mesh.dl.sourceforge.net/sourc...lbackup.sh.2.5
Quote:
sudo vi automysqlbackup.sh.2.5
In order to change to append mode you need to press the
Quote:
escape key
Quote:
a
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"
[...]
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"
Quote:
escape key
Quote:
:wq
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
you can now use the command
Quote:
show databases;
Quote:
exit
Quote:
chmod 755 automysqlbackup.sh.2.5
Quote:
sh ./automysqlbackup.sh.2.5
Quote:
cd /etc/cron.daily/
Quote:
sudo ln -s /usr/local/bin/automysqlbackup.sh.2.5 automysqlbackup
Regards,
JKZfixme
Total Comments 0