mysql_maint MySQL Backup Script

A nightly backup and maintenance script for MySQL servers.

Copyright © 2014 by Daniel G. Delaney, Fluid Mind Software
This script is free and open source. You may copy and modify it in any way you wish as long as you keep the copyright information at the top of the script.

This is a fairly simple script that reads the list of databases in your MySQL instance, and runs mysqldump for them, placing the output files into separate folders in the backup directory and naming each file with the database name and the date of backup. It then deletes all backup files older than the specified number of days. Optionally, it can also rsync the entire backup directory to another server.

Installation and Usage

NOTE: DO NOT RUN THIS SCRIPT AS ROOT! It is not designed to run as root and could be very dangerous. This script is designed to be run by the mysql user.

Download the perl script either as a text file or as a gzipped file (you might have to right-click those links and choose "Save as"). Place this script anywhere on your server, it doesn't have to be in a directory in the PATH. You might put it in /usr/local/sbin, or possibly a "bin" directory in the mysql user's home directory. Just make sure the mysql user has access to execute it.
Create a directory where backups will be stored. Set the owner and group to the user that your mysqld daemon runs under (usually 'mysql'). Set it's permissions to 2770.
```
mkdir /data/backup/hostname/mysql
chown mysql:mysql /data/backup/hostname/mysql
chmod 2770 /data/backup/hostname/mysql
```
Edit the options in the OPTIONS area in the script to set where the backup files will be stored.
Create a ".my.cnf" file in the home directory of the user running the script and put the following into it:
```
[client]
user = root
password = "rootpassword"
```
That's the MySQL "root" user, not the system "root" user. Set this file's permission to 600 so that no one else can read it.
To run the script every night at 1:00 AM, just login as root, type 'su - mysql' to become the mysql user, type 'crontab -e' and add the following line to the file (using your path to the script):
```
0  1  *  *  *  /usr/local/sbin/mysql_maint -noprompt -silent
```
To rsync files to a remote server, the mysql user will have to have an SSH key (type 'ssh-keygen -t rsa' as the mysql user to generate one) and the public key will have to be placed into the "authorized_keys" file in the home directory of the user you're logging in as on the remote server (in that example, the "mysql" user). Just type "cat ~/.ssh/id_rsa.pub" and copy the whole thing, then paste it into the ~/.ssh/authorized_keys file on the remote server. (Note that sshd on some servers defaults to not using the authorized_keys file. In the /etc/ssh/sshd_config file on the remote server, the following options will need to be uncommented: RSAAuthentication, PubkeyAuthentication, AuthorizedKeysFile.)