What are the best practices for Mysql backup

Question

We have one php application and mysql server running on one of our production server. Mysql server is currently 4GB big with intention to grow up to tens or even up to hundreds of GB.

What am curious to find out is what are the best practices for backup of mysql database in condition that application must be live under any circumstance? What is better, to have mysql replication server on which we will run backup scripts or to run on live server? What is more likely to slow down We have possibility to add additional server(s) if needed. Where do I need to store mysql dumps? Is it suggested to ftp copy mysql backup files to remote server.

What is the best practice to organize web application backup if don't have problem with number of server instances?

Answer 1

Replication is something that protects against hardware errors, for example, a hard disk crashed.

Backup - protects against software errors, for example, due to the human factor, data has been deleted from a table.

It is definitely good practice to combine both of these technologies by running a utility to create a backup on a replica. This not only reduces the load on the product database, but also covers more recovery scenarios.

In case of a hardware error, you can restore the most up-to-date data from the replica, and in cases of data corruption, you can already consider about from the what date to use the backup for recovery. Well, if your both the main server and the replica fail, then the backup will also save you.

What is the best way to make backups?

mysqldump is a good solution for small databases. This is a utility for creating logical backups nad it is included to MySQL Server. At the output, the utility creates a .sql file to recreate the database.

For large databases, it is better to use a physical backup. There are two ways on how to do it.

mysqlbackup is a utility included with MySQL Enterprise Solution. As a result, you get a binary file. Such a backup is created much faster than using mysqldump and is less load on the server.

xtrabackup, from Percona, is a lot like the MySQL Enterprise backup utility, but it's free. A more detailed comparison can be found here.

How often the backups should be made?

The more often you make backups, the better, but you can't make many such backups - since you will run out of space in the backup storage. There are two ways:

1. Find a compromise between the frequency of backups and the duration of storage.
2. Use incremental backups. The above utilities support incremental backups, but the management of such backups is more complicated (read more here)

Where the backups should be stored?

Anywhere you prefer, but not in the same place as the MySQL Server. Overall, I think using cloud storage is a good choice. Almost everyone today has a command line interface.

How to automate a backup?

The process of creating regular backups should be automated, and a person should intervene in it only in case of failure. A good backup process should include the following steps:

1. Creating a backup copy
2. Compression\Encryption
3. Uploading to storage
4. Sending success\fail notification
5. Removing old backups from the storage (so that it does not overflow)

The simplest script that implements this can be found, for example, here.

Something else?

Yes, the most important thing is not to create a backup and then restore it. Therefore, it is best practice to regularly test the recovery scenarios.

Happy backups!

Answer 2

What is better, to have mysql replication server on which we will run backup scripts or to run on live server

It depends on your db size (and time needed to dump it using mysqldump) and your reliability requirements.

If your db is relatively small and mysqldump dumps it in seconds or in a few minutes then its ok to just run scheduled backups. For most cases it is sufficient to have a daily backup which runs at a time when your app is mostly idle (at night when you clients are sleeping). You can use a nice tool automysqlbackup for that: it cares about the scheduling and backup rotation, all you need to do is to add it as your cron task and set up its config once.

Setting up a replica is only needed if:

1. Your backup takes long time (dozens of minutes or hours) to complete so you can not just stop your service for that long.
2. You can not afford loosing any history in case of main db crash. E.g. if you process financial transactions you may want to ensure that nothing will be lost if master db server dies.

In this cases you may want a replica with backups. Though you must understand that adding replication adds a new layer of problems: replicas may go out of sync, silently crash (and you will not notice that as the master and your app is running fine) etc.

Answer 3

MySQL backup methods are documented on MySQL documentation.

The ideal backup solution will be to use MySQL Enterprise Backup. This is a licensed product sold on Oracle store. It is very fast compared to mysqldump.

MySQL Enterprise Backup: A licensed product that performs hot backups of MySQL databases. It offers the most efficiency and flexibility when backing up InnoDB tables, but can also back up MyISAM and other kinds of tables.

If you are looking for a free solution with MySQL community edition, then you can install another replication server and either run mysqldump to take backup or make a raw data backup. During backup on your replication server, your main master database will be running. Since your data is big or will get bigger, it is recommended to backup raw data files. It is basically a process of copying data and log files from disk. Details are explained on MySQL documentation.

For larger databases, where mysqldump would be impractical or inefficient, you can back up the raw data files instead. Using the raw data files option also means that you can back up the binary and relay logs that will enable you to recreate the slave in the event of a slave failure.

Finally, you should copy backup files to another physical disk on the same to recover from disk failures or to another physical server to easily recover from complete server failures.