Piotr Zalewa

It's a bash program to backup all needed databases on my system. It uses thy MySQL database to get information what and when to backup. It is calling the mysqlbackup command so it's not very good for large databases.

Post is split this post in two parts: Installation in 4 steps and Description.

Installation in 4 steps

Step 1

1. Create database to maintain the database backup. In this example – „manage“
2. Grant all needed privileges to the desired user. In this example – „manage_user“, „manage_pass“

Step 2

Download and unpack the installation files

tar xvz backub_db.tgz

Configure the ini file (BackupDB/config/config.ini)

Step 3

Load backup table definition

mysql -u manage_user -pmanage_pass manage < BackupDB/sql/backup.sql

Insert rows with databases you want to backup (user has to have privileges sufficent to provide mysqldump action)

INSERT INTO `backup` (`frequency`,`name`,`user`,`password`) VALUES ('daily','backup_db_name','backup_db_user','backup_db_pass');

Step 4

Configure cron. Just enter crontab -e and add following lines:

10 1 * * *    /path/to/the/directory/BackubDB/bin/mysql_backup.sh daily
10 2 1 * *    /path/to/the/directory/BackubDB/bin/mysql_backup.sh weekly
10 3 1 1 *    /path/to/the/directory/BackubDB/bin/mysql_backup.sh monthly

Your backups will be stored in the BackupDB/backup/mysql/ directory as backup_db_name_YYYY-DD-MM_HH-SS.sql

Upgrade

There may be many ways to make this code better.

One may use the backup table as a part of bigger project. It's just important to have these fields in the backup table:

`frequency`     ENUM('daily','weekly','monthly') DEFAULT 'daily',
`name`          VARCHAR(255),
`user`          VARCHAR(255),
`password`      VARCHAR(255),
`last_backup`   DATE DEFAULT '0000-00-00'

The other solution is to use external backup. There will be a need to add server field to backup table and change the lib/backup.sh and bin/mysql_backup.sh to use it.

Links

Ini parser code