ameir/mysqlbackup

MySQL Backup

Backup Your MySQL Databases to Disk, FTP, and Email

This is a shell script that backs up your MySQL servers. It can talk to as many servers as you'd like (remote or local), and has some features to filter out specific databases and table names.

Requirements

To backup databases locally, you'll only need bash and gzip, which ship with just about all Linux distros.

To backup to FTP, you'll need the 'ftp' program.

To backup to email, you'll need the 'mutt' program.

Available options

MySQL Settings

DBNAMES

Databases you want to backup, separated by a space; leave empty to backup all databases on this host.

Example:

DBNAMES[0]="db1 db2"

DBUSER

Your MySQL username.

Example:

DBUSER[0]="root"

DBPASS

Your MySQL password.

Example:

DBPASS[0]="password"

DBHOST

Your MySQL server's location (IP address is best).

Example:

DBHOST[0]="localhost"

DBTABLES

Tables you want to backup or exclude, separated by a space; leave empty to back up all tables.

Example:

DBTABLES[0]="db1.table1 db1.table2 db2.table1"

DBTABLESMATCH

If you set this to 'include', it will backup ONLY the tables in DBTABLES, 'exclude' will backup all tables BUT those in DBTABLES.

Example:

DBTABLESMATCH[0]="include"

DBOPTIONS

If you want to give mysqldump other options, include them here.

Example:

DBOPTIONS[0]="--quick --single-transaction"

Email Settings

EMAILS

Email addresses to send backups to, separated by a space.

Example:

EMAILS="[email protected] [email protected]"

SUBJECT

This is the subject of the email that you'll get.

Example:

SUBJECT="MySQL backup on $SERVER ($DATE)"

FTP Settings

FTPHOST

Example:

FTPHOST[0]="ftphost"

FTPUSER

Example:

FTPUSER[0]="username"

FTPPASS

Example:

FTPPASS[0]="password"

FTPDIR

Example:

FTPDIR[0]="backups"

Backing up multiple servers

For each server you're backing up, you will need to copy/paste the block under MySQL Settings and increment the key in brackets (e.g. the first server will be 0, the next will be 1, and so on).

mysqlbackup's Issues

Cannot connect to a different port.

MySQL Server is running on 3340, script fails to connect.

DBHOST[0]="127.0.0.1:3340"
Error - Got error: 2005: Unknown MySQL server host '127.0.0.1:3340' (2) when trying to connect.

Update documentation

S3 sync and Duplicity support have been added, but documentation does not reflect it. Creating this issue to keep this on my radar.

Add Variable for Sleep time post SQL dump

One of my sizes has a sizeable DB, and began running into an issue where the mySQL DB would not be finished dumping before the script attempted to compress it. This was resolved by adding a "sleep 10" line in the script, to give it time to finish.

If this pertains to larger databases, and more time might be needed, I would suggest adding a Sleep variable that can be set in the config, so that larger sites can have a higher pause before moving on to the compression. (Optionally, if the size of the DB can be easily determined, have the script estimate a sleep time based off the size of the DB.)

Use ISO 8601 for Date format in filename

In short, change the filename of the dump file to be YYYY-MM-DD, so that a large number of files become easy to sort by filename while still being chronological.

https://en.wikipedia.org/wiki/ISO_8601