Home
MySQL Maint is written as a bash script, and uses mysql and mysqldump binaries (debian users : apt-get install mysql-client).
Is has been successfully tested on [http://www.debian.org debian] (etch, lenny and squeeze), [http://www.centos.org centos] 4.3, [http://www.freebsd.org FreeBSD] and [http://www.apple.com/server/macosx Mac OS X Server].
Please report if you successfully (or unsuccessfully) used it on another platform.
Note that MySQL Maint may not be suitable for any situation : mysqldump needs to acquire locks to create a consistent backup, and this may raise issues on highly loaded servers.
Simply extract mysql_maint.sh anywhere on your system (for example ~/scripts).
Don’t forget to allow execution.
Note that the script itself may contain important information, such as a login/password for your MySQL server. You should really take care to the permissions
chmod 700 mysql_maint.sh
There are two ways to configure MySQL Maint :
- By editing the script, and modifying the script variables
- pros : the variables (login, password…) don’t appear in the
pscommand - cons : the script can only backup one server (which is configured inside the script)
- pros : the variables (login, password…) don’t appear in the
- By passing command-line arguments
- pros : you can backup as many servers as you like, by passing parameters on the command-line
- cons : any parameter passed to the command line will be seen in a
ps
Edit mysql_maint.sh, and change DB_HOST, DB_USER, DB_PASS, DB_PORT to suit your MySQL server configuration. Then, add a cron job to run mysql_maint.sh -b to automatically backup your database server.
If you did not change the BACKUP_DIR variable, your backups will be stored in ~/backups/mysql.
When you need to recover a database, run bzcat <db-name>_<date>.sql.bz2|mysql -u <myuser> -p <mypassword> <db-name>
- `-b` : Ask MySQL Maint to perform a backup
- `-m` : Ask MySQL Maint to perform maintenance
- `-H ` : The host that runs mysql
- `-u ` : The user login used to connect to the mysql server
- `-p ` : The password used to connect to the mysql server
- `-P ` : The port mysql runs on
- `-d ` : The folder that will be used to store the backups
- `-n ` : A friendly name for your server. Will default to the hostname
- `-l` : Ask MySQL Maint to keep a copy of the latest backup in the folder
If you want to backup server sql.mycompany.com, which runs MySQL on port 33306, using login backupmanager and password b4ckupP455_, here is the command line you should use :
maint -b -H sql.mycompany.com -P 33306 -u backupmanager -p b4ckupP455
mysql
If you don’t want to pass command line arguments (they can be seen in a ps), you can just edit the script, and change the variables default values. Then, you will just need to run mysql_maint.sh -b (to perform a backup), or mysql_maint.sh -m (to perform maintenance)
Running MySQL Maint with cron is a good way to ensure your backups will be done regularly.
Here is an example if you want MySQL Maint to run every 3 hours on your server :
crontab -e0 */3 * * * /path/to/mysql_maint.sh -H myserver -u backupmanager -p backuppass -b
The backup folder contains the log files (maintenance and backup), and one folder for each server that is backed up by the script.
Each server folder holds one folder for each database that exists on the server (and that is not excluded from backup).
In each database folder live the 4 different backup folder (current, daily, weekly and monthly), and, if configured, a copy of the latest backup that wad made for this database.
The backups are bzipped and are named with the date of creation.
Each time the script is called (with -b option), it creates a new backup of each database in the ‘current’ folder. If the daily backup doesn’t exist, the backup is copied from ‘current’ to ‘daily’. Once a week, the same test is done for the ‘weekly’ backup, and so one for the monthly backup.
The current backups are stored during 24 hours.
The daily backups are stored during 7 days.
The weekly backups are stored during 4 weeks.
The monthly backups are never deleted.