There are various ways of backing up MySQL servers like mysql binary backup. The methods used are determined by several factors. They include the kind of hardware as well as the size of the data. It should be noted that every alternative has its own tradeoffs. It is, therefore, important to consider them all in order to choose the most suitable one.

best-mysql-backups

Physical or Logical Backups

Backup alternatives can either be physical or logical. In order to make use of physical backups, database files are copied from the disk. They are, for that reason, also referred to as binary backups. The fact that the backup process does not include the actual database demands that utmost care should be taken in order to ensure that the backup is consistent. The occurrence of updates in the mid of the copying process is, however, prevented by cold backups which either enable a shutdown of the MySQL or a lock-down of the database. This fastens up the process as well as simplifies it. Hot backups, on the hand, have to exploit mechanisms that are complex enough to ensure consistency, since the files are copied when the database is operational. Nevertheless, they cannot be retrieved in different versions of the database.

Logical backups work by scanning the rows in the database, generating a single ‘Insert’ statement for every table and row. They also come up with ‘create Index’ and ‘create table’ statements. One of the main advantages of logical backups lies in the fact that they are small in size and that they can be interchanged with different versions of databases. They are, therefore, usually used for migrations.  The process of using them is, however, slow.

Mysqldump

It is the most common and simplest backup method since it comes with Client Programs on MySQL. It can be used for logical backups. It can also be used in backing up every database. The SQL statements that accompany it enable the creation of tables and filling them up with data. However, big databases make it an extremely slow process. One of the reasons for this is that the processes of restoration and the backup are single-threaded.

Mydumper

They are used when the slowness of the msqldump is miserably slow, but there is still a need for logical backup. Due to its multi-threaded qualities, the software enables a faster process. They are, additionally, used in production and for a long period of time.

Mysqlpump

It also comes with the MySQL client programs. While sharing similarities with mysqldump, it considered a tool which is still new in the market. Further, it is not provided together with other MySQL facilities including MariaDB.

MySQL Enterprise Backup

The software enables the performance of hot backup processes for databases used in MySQL. It is designed for provision of reliable and efficient backup of tables made up by the InnoDB, a storage engine. It also works through backing up tables from storage engines including MyISAM.

Percona xtrabackup

They replace other backup methods in the event that the database is big. The software is the most commonly used tool where the physical backup is put into practice. It does not only support non-blocking backups but is also faster. The tables, nevertheless, must be XtraDB or InnoDB.  It can also come up with a hot backup, removing the need for the database server to be stopped.

The software also enables the creation of a standard output stream or files for local backup. While the backups’ size is as large as the database, Xtrabackup works excellently with qpress- compressed files. The size is thus considerably cut down. In addition, it can stream with AES256, AES192 and AES128. It can also enable encryption of the backups.

Make your Choice

In order to pick just the right backup software for your kind of job, you should have in mind a few factors. Among the greatest is size. You would, for example, be better placed if you used Percona xtrabackup, if you working with big sizes. This is because it is not only faster to restore but also to backup. Undeniably, it has its own downsides, but it has won the hearts of many people who use MySQL, especially due to its maturity. With all said, it is would be judicious to remember that mysql binary backup technology is always moving a notch higher. Therefore, it would be safe to expect many more types of backup products with greater capabilities.

Leave a Reply