I'm installing DadaMail PRO http://dadamailproject.com/ and using the MySQL option to store the data. I have about 1,500 subscribers. I will only have a few subscribe / unsubscribe events a day. I've been researching and considering how best to administer the server when I need to do maintenance and/or restart the server. I will be taking hourly dumps of the database using a cron job to run

#!/bin/bash
TEMP=`date +%d-%m-%y-%T`
REST='-backup.sql.gz'
USE=$TEMP$REST
mysqldump -u root -pverysecret --all-databases --add-drop-table --opt --routines | gzip > $USE

I'm going to have Linode backups active. I'm going to use a cron job to copy a daily dump of the database to a computer at my home. Linode documentation states "The most straight forward method for creating a single coherent backup of the entire MySQL database management system uses the mysqldump utility from the command line."

I've tested the mysqldump command and given my quite small database, it only locks the database for a few seconds and so I can live with that.

I did some reading on MySQL database replication and the benefits of taking the mysqldump from the replicated server. Given the light load on my MySQL data base I believe I can tolerate the few seconds it will require for a subscribe or unsubscribe request to respond if mysqldump is processing.

What I'm wresting with is a reasonable way to do maintenance on the production server. The best I have been able to come up with is to have a image of the production server saved on one of my development Linodes. When I need to do maintenance I would:

1. boot up alternate server on one of my other Linodes in the same data centre
2. stop DadaMail on production server
3. stop MySQL on production server
4. mysqldump the data base
5. scp the data base to the alternate server
6. import the database into the alternate MySQL using mysqldump < just_backedup_database_unziped
7. start MySQL on alternate server
8. start DadaMail on alternate server
9. use Linode swap IP to point DNS entry as the alternate server

I can script this so the actual down time during the swap will be less than 10 - 20 seconds or so. To move back to the production server I would follow a similar process.

I'll remember to perform the same administration on the alternate server so that the two servers have the same software configuration.

I think my system is a bit small and simple for configuration management tools.

If fellow Linode members have suggestions or comments, that would be helpful. If you can direct me to any useful guides or documentation that would be helpful as well.

If I'm totally "out to lunch" on my understand or approach, that information would be very useful information.

Thanks in advance for reading and any assistance, Martin.

Swapping IP's requires a reboot.

gig:

Thanks, I totally missed that swapping IP addresses requires a reboot.

What storage engine are your tables using? If it's MyISAM, you could consider using mysqlhotcopy to take (binary) backups. For InnoDB, don't specify --opt since it implies --lock-tables (and is on by default anyway); you want to use the --single-transaction option instead. So you'd have

mysqldump -u root -pverysecret --all-databases --routines --single-transaction

(--add-drop-table is also on by default).

As for the maintenance question, an easier method may be to clone the production Linode, then do your maintenance on the clone. Once complete, you'll need to stop the production server and somehow migrate any changes to the clone. Then you can swap IPs and make the clone the new production server.

Vance:

I am using the MyISAM storage engine by default. I'll research, and test out, mysqlhotcopy. I'll also do some more reading and understanding of the "clone" my Linode opportunity. Thanks for the guidance, Martin.