load balanced configuration

[Alex Dean]

On May 19, 2010, at 7:47 PM, Bryan O'Neal wrote:

> mysql dump on a cron job. You write the dump pipe it to gzip and set  
> the name with the date and life is golden.

mysqldump is great if it's OK for your database to be locked while the  
dump is going on.  If that's acceptable, it's definitely simple, and  
is what I do for lower traffic databases.

For higher-volume DB servers where that's not an option, it's pretty  
easy to set up a mysql slave.  Then you can take your dumps from the  
slave server.  You get a completely consistent backup, but your  
production database is never down at all.  If dumping to SQL takes up  
too much disk space, you can also stop the slave server, cp its data  
files, and start it back up again.

Look into mysqld_multi.  Makes it easy to run multiple instances of  
mysql on the same machine.  Your slave & master can be on the same  
hardware.

http://www.howtoforge.com/mysql_database_replication
http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html

alex