Unforeseen phenomena such as hardware failure, database corruption, user errors and malicious attacks can increase the risk of data loss. The solution is database backups, which make it easier to recover data and maintain the functioning of the company in a crisis situation. MySQL backup provides several options for backing up databases with different strengths.
It is up to you to choose the combination of methods that will best meet your needs. Let’s take a closer look at what a backup of the MySQL database entails. In this article we’ll look at two different database backup types, as well as whether and how to set up an automatic backup solution.
Database backup methods
Backups are not just a solution for emergencies. Under normal circumstances, they help you meet compliance and audit requirements your organization has to follow. They also enable you to restore a copy of your database to a specific point in the past.
How to create a backup of MySQL database? There are two ways to do this: physical and logic-based. It’s important to understand the difference between types of MySQL backup methods in order to provide consistent backup process of the database for each project.
A physical database copy copies the actual data files, while a logical backup generates statements such as CREATE TABLE and INSERT. They enable data to be restored later.
A physical copy of the MySQL database
As we briefly outlined above, a physical MySQL database backup contains raw copies of all files and directories as they exist on your drive. Who should choose this type of MySQL database?
A physical copy of backup data is a good solution for large databases. Copying raw files is much faster compared to performing a logical backup of the MySQL server.
Advantages:
- Simplicity and performance – physical backups do not require a lot of memory or many CPU cycles.
- Efficiency – just copy raw files and directories to the target backup location.
- Faster restore – Because MySQL does not have to restore the MySQL database objects or import data, physical database restores are faster than logical restores.
Drawbacks:
- Requires more space – The downside to physical backups of MySQL databases is that they contain transaction logs and undo logs – all of which take up disk space.
- Has difficulty transferring between environments – for this type of backups, the problem may be the inability to transfer databases between platforms, operating systems or MySQL versions.
- Duplicates damaged files – by transferring the damaged raw files as a whole, we leave them in the same, incorrect form.
What tools can be used to physically backup MySQL databases? The most popular ones include the following:
- MySQL Enterprise Backup,
- PerconaXtraBackup,
- MariaDB Mariabackup.
A logic backup of the MySQL database
A logic backup of a MySQL database contains data that MySQL can interpret as SQL or delimited text. We’re talking about representing a database as a sequence of SQL statements that can be executed to recreate database objects and import data. Who should consider choosing a MySQL logic database backup?
This is certainly a solution dedicated to small databases, because, unlike in the case of a physical copy, restoring logical backups takes more time. It is worth using it when migrating to managed database services in the cloud, or vice versa.
Unlike a physical backup, a logical backup outputs a database structure in a .sql file. It does it by generating the CREATE or INSERT statements. If you want to restore the database later, you can use the mysqldump utility.
Advantages:
- Flexibility – high granularity of backup and restore operations at the server level, database level, table level or even row level (if the table rows meet the WHERE condition) provides great flexibility.
- Easy to restore – Logical backups are easier to restore. Simply upload the backup file to your MySQL client and use the LOAD DATA statement or the mysqlimport command to load the text-delimited files.
- Remote Launch – Logical backups can be launched remotely, allowing you to back up or restore databases over the network. This solution is especially useful for databases in the cloud, regardless of whether it is Google Cloud SQL, Amazon RDS or Microsoft Azure.
- Avoiding data corruption – while in the case of physical copies, damaged data may go unnoticed until verification, in logical copies, which are most often text files, it is easy to view them using a text editor and detect any damage.
- Portability – another opposite to physical copies. Logical copies of databases can be easily moved between platforms, operating systems and MySQL versions.
- High level of backup compression.
Drawbacks:
- Time-consuming – Backups are slower here because you have to query the MySQL server to obtain the schema and rows, which then need to be converted to a logical format.
- Long-term restoration of logical backups – It can take a lot of time to restore the MySQL database from a logical backup. MySQL must execute SQL statements to create tables, import rows, and rebuild indexes.
- Greater server resources required – the creation of logical backups requires the use of greater CPU or RAM resources.
Popular utilities for creating logical backups include:
- mysqldump,
- mydumper,
- Mysqlpump.
How to restore a database with the mysqldump utility?
The mysqldump utility allows you to restore a single database or all databases stored on the server. If you want to restore all databases, use the mysql command-line tool with an SQL dump file. The file should contain the data and structure for all databases.
Find out more about database backups from a cloud architect.
Point-in-time recovery (PITR)
We most often use various data recovery tools when we lose access to them due to an error. Point-in-time recovery allows you to recover the instance to a specific point in time. For example, if an error caused data loss, using the PITR tool you can restore the backup and recover the database in the state it was in before the error occurred.
PITR is a two-step process based on binary logs. In the first one, it restores the full physical or logical backup that was at the time of its creation.
In a second step, binary logs can be used to recreate changes between the time the backup was created and the desired time. Binary logs contain all changes made to the database instance, including the creation of a table and the insertion, update or deletion of a row.
What does it look like in practice?
Let’s assume backups are created every day at 8 a.m. The problem occurred at 12 p.m.
The first step should be to restore the backups from 10 a.m. Then, using a binary log, events from the four-hour time window between 10 a.m. and 12 p.m. are restored.
Database backup within Google Cloud
Cloud SQL is part of Google Cloud. We wrote about how backup works within this article. You can also take advantage of the knowledge and experience of FOTC‘s architects, who will help you protect your databases against failures.
