Ubuntu automysqlbackup

There is a script called "automysqlbackup" which is a pretty straightforward shell script wrapping up routine MySQL backups. The Ubuntu package is mostly preconfigured so you would not necessarily even have to modify the stock configuration.

• Gets the maintenance user from "/etc/mysql/debian.cnf" for credentials
• Dynamically determines what databases are on the system
• Has a default schedule and backup path (/var/lib/automysqlbackup)

You should consider changing a couple of the defaults found in "/etc/default/automysqlbackup".

• BACKUPDIR to preferred backup path
• MAILADDR to an appropriate recipient in case there are errors

It does not remove old backup files so you may want to make a basic script which does remove them. There's a "PREBACKUP" variable so you can hook in such a script. I like this because it runs before your backup so you don't accidentally nuke your fresh backups and keeps things simple:

• PREBACKUP="find $BACKUPDIR -mtime +90 -delete"

Finally, remember to copy your backups offsite if appropriate. "rsync" to some remote system or otherwise. Use the "POSTBACKUP" script - again a good hook here because it will push out your backups right away after they have been created.

Ubuntu Man page:
http://manpages.ubuntu.com/manpages/wily/man8/automysqlbackup.8.html

MySQL Replication

MySQL replication is very flexible for running multiple servers. Most MySQL administrators should already have a copy of "High Performance MySQL" (Baron Schwartz, Peter Zaitsev, Vadim Tkachenko); if you don't, get this book as it is top notch and will guide you through most configurations imaginable. Rather than repeat what is already written, here's a few things I've found that can help make things simpler.

There are a few things with replication you need to be attentive to.

• Set a unique "server-id" for each server
• Keep your binary logs (and relay logs) with your databases
• Initialize your slave cleanly
• Run your slave "read-only" 
• Use "skip-slave-start" if the slave is used as a backup

Figure out a scheme to create a unique "server-id". If your database hosts all have unique number in their name, you could use that. I've been using their IP addresses converted to decimal (IP Decimal Converter). Even if you're aren't thinking about master-master or many slaves configurations today, starting with this set will save you the trouble later of having to reinitialize everything.

Unlike best practices on other platforms like SQL Server, with MySQL it is going to be simpler to keep the binary logs with the databases. Like if you try to initialize slaves with LVM snapshots, capturing binary logs with databases in one snapshot is going to be the best. And moving binary logs later is a challenge. So for the "log-bin" and "relay-log", set these to a file name only and not a full path ("mysql-bin", and "<hostname>-relay-bin").

 Create a script to (re)initialize a slave. If your environment is on the messy side and there's a lot of strange things that people are doing in the databases, you are going to find your slave is at risk of getting out of sync with the master. I would suggest re-initializing the slave(s) as often as possible, like monthly. Even if your environment is pristine, you will inevitable make changes from time to time and will want to have had a consistent tool for initializing slaves. Again, "High Performance MySQL" has several good ways of doing this. LVM snapshots and rsync are what are in my scripts. The Percona Toolkit (including the former Maatkit) also has some good tools for you.
Running a slave in "read-only" ensures only "root" can make changes which helps ensure the slave integrity. If you want to have a writable slave, e.g. one out of sync with the master, why use replication? Load data as needed and skip the whole replication thing. Even so, very bad schema (like tables with no unique keys) are still susceptible to falling out of sync as replicated transactions may not behave consistently. There may be other reasons for slaves to fall out of sync, but this has been my problem so I couldn't attribute any out-of-sync issues to anything else.

For a server that is intended to be the backup of the primary, the "skip-slave-start" is necessary for the times you do use the backup server. It means every time you restart MySQL, you have to manually issue "start slave" which prevents the backup server from downloading transactions from the primary server like after you have made a cut-over and are trying to restore the primary.

Again with the tapes

In a previous post I said that to get around devices changing their numbering, it was useful to use the /dev/tape/by-id instead of the generic /dev/nst0. Unfortunately, this is also imperfect I've just learned as the device which was previously "scsi-35000e11138aa0001-nst" this time came up as "scsi-35000e11138aa0001". And you can guess how gracefully the software handled that (not at all). Now I don't know if was a driver update (possible) or if the device was switched to a different SAS interface (also possible), or maybe just the gremlins. Whatever it was, once again, I had to reconfigure the software to find the new device ID.