Ever wanted to program a complete database automated backup? There are several scripts avilable on the internet, but this is probably the best one.

The features (taken directly from the web site)

• Backup mutiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database seperately)
• Backup all databases to a single backup file or to a seperate directory and file for each database.
• Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
• Can backup remote MySQL servers to a central server.
• Runs automatically using cron or can be run manually.
• Can e-mail the backup log to any specified e-mail address instead of “root”. (Great for hosted websites and databases).
• Can email the compressed database backup files to the specified email address.
• Can specify maximun size backup to email.
• Can be set to run PRE and POST backup commands.
• Choose which day of the week to run weekly backups.

What this script does:

This is how to make it work:

1. Download the script:

Dowload link: automysqlbackupt

2. Configure the script to suit you system

open the downloaded file with a text editor and edit the first lines. The file is well commented but these are the lines you will need to configure:

# Username to access the MySQL server e.g. dbuser
USERNAME=root

# Username to access the MySQL server e.g. password
PASSWORD=yourPassword

# Host name (or IP address) of MySQL server e.g localhost
#for temote access type the IP of your server
DBHOST=172.32.22.10

# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”databaseName”

# Backup directory location e.g /backups
BACKUPDIR=”/backups”

If the database you want to backup is in a remote server, you must enable remote mysql access. Otherwise you can skip steps from 3 to 8.

3. Login to the remote server, via ssh or via freeNX (see my freeNX remote access guide)

4. Enable networking

Once connected you need edit the mysql configuration file my.cfg using text editor such as vi.

# sudo vi /etc/mysql/my.cnf

5. Locate the line that starts with “bind-address” and set the ip to:

bind-address=0.0.0.0

This will enable remote access from any location, (I also tryed to use xxx.xxx.xxx.0 but it didn’t work so if you need extra security configure access rules in your firewall)

Save and close the file

6. Restart your mysql service to take change in effect

# /etc/init.d/mysql restart

7. Grant access to remote IP address

connect via phpMyAdmin and grant the necesary permissions to the remote user, in this case the remote user was root, but you should change it to suit your needs.

CREATE USER ‘root’@'%’ IDENTIFIED BY ‘**********’;

GRANT SELECT ,
FILE ,
LOCK TABLES ON * . *
TO ‘root’@'%’
IDENTIFIED BY ‘**********’
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `root\_%` . * TO ‘root’@'%’;

8. Test it

In the remote system type this command:

$ mysql -u webadmin –h 172.20.5.2 –p

(replace 172.20.5.2 with your server’s IP)

If everyting succeded to this point, there’s only one more thing to do…

9. Run the script

This script can be executed manually from a terminal or added as a cron job to be execute periodically.

to execute it manually open a terminal, cd to the directory where your script is located, and type

sh ./mysqlbackup.sh

(replace the name of your sh file)

You should see something like this when it’s finished: