Best Practices to take MySQL server backup:
MySQL Replication
Setup Replication in MySQL. You will have to setup Master and Slave server. All read-writes to the DB could go to your Slave Server.
Advantage of having Replication is you can take a backup from your slave server without interrupting Master server, Your application will continue to work on Master without any downtime.
Using MySQL Dump
If your data set is small (I realize "small" is a relative term.. to qualify it, let's say <10GB), then mysqldump will probably work great. It's easy, it's online and it's very flexible. Just a few things mysqldump can do:
backup everything or just certain databases or tables
backup only the DDL
optimize the dump for a faster restore
make the resultant sql file more compatible with other RDBMSes
and many more things.
However, the most important options are related to the consistency of your backup. My favorite options are:
--single-transaction : this option gives a consistent backup, if (and only if) the tables are using InnoDB storage engine. If you have any non-read-only MyISAM tables, then don't use this option when backing them up.
--master-data=2 : this option will make sure your dump is consistent (by doing a lock-all-tables unless you've added the option --single-transaction). The --master-data option also records the binary log position in the resulting dump file (=2 causes this line to be a comment in the dump file)
Final note about mysqldump: keep in mind that the restore time may be significantly longer than the backup time. It will depend on several factors, for example, how many indexes you have.
LVM snapshot
For those that have larger datasets, a physical backup is the way to go. While you could take a cold backup (i.e., shutdown the MySQL service, copy the data directory, restart the service), many people do not want downtime. My favorite solution is snapshots. This can be hot (for InnoDB) or require a brief lock (for MyISAM). Don't forget to include all your data (include the ib_logfiles). Lenz provides a nice utility to help with this: http://www.lenzg.net/mylvmbackup/
Using MySQL Enterprise Backup
Advantages of using MySQL Enterprise Backup:
- "Hot" Backups of InnoDB tables takes place entirely online, without blocking
Backup only particular tables or tablespaces
- Only backup the data that changed since a prior backup
- Compressed Backup - Saves storage upto 90% and many more..
Reference:
http://www.mysql.com/products/enterprise/backup/features.html
http://www.mysql.com/products/enterprise/backup.html