To achieve MySQL database management, various backup tools and techniques have been developed, each with its unique strengths and use cases. This article explores the key features, mechanisms, and considerations of these popular MySQL backup and management tools, helping you make informed decisions for your database operations.
1. Logical Backup: mysqldump
In MySQL, mysqldump is one of the most commonly used tools for exporting databases as portable SQL files. It supports backing up single tables, single databases, or multiple databases and provides a rich set of options to meet different backup needs. Its backup mechanism connects to the MySQL database via a protocol, queries the data to be backed up, and converts the queried data into corresponding “INSERT” statements. When we need to restore the data, executing these “INSERT” statements will restore the corresponding data.
The mysqldump backup process is roughly as follows:
Apply read locks to all tables in a specific database;
Loop through and back up table data;
Release the read lock;
Repeat the above three steps;
Backup is complete.
From the above process, it is clear that during the mysqldump backup, when backing up a specific database, all tables in that database will be in read-only mode, preventing any modifications until the backup is complete. This is generally unacceptable for online environments. If the “--master-data” or “--dump-slave” option is specified, a global read lock (“FLUSH TABLES WITH READ LOCK”) will be applied at the beginning of the backup and will persist until the backup is complete. One alternative is to perform the backup on a replica database to avoid affecting online operations.
Another major advantage of using mysqldump is that since it generates SQL statements, it supports cross-platform and cross-version data migration or restoration, which is not possible with physical backups. However, because mysqldump produces SQL statements, extra caution is required during use to prevent catastrophic issues. Common problems when using mysqldump include:
Migrating only part of the data to a new instance but accidentally deleting the original data;
Due to time zone issues, the restored tables and time-related data may be incorrect;
After importing backup data into the primary database, the replica database does not synchronize;
Due to character set issues, the restored data appears garbled.
Therefore, when using mysqldump, it is crucial to understand the function of each option, verify what operations are included in the exported SQL file, and assess the impact on existing data.
2. Mydumper
The principle of Mydumper is similar to that of mysqldump, but its main difference is the introduction of multi-threaded backup, where each backup thread processes a portion of the tables. The concurrency granularity can even reach the row level, achieving multi-threaded backups.
Key Features of Mydumper:
Lightweight, written in C
Execution speed is 10 times faster than mysqldump
Consistent snapshots of transactional and non-transactional tables (for versions 0.2.2 and above)
Fast file compression
Supports exporting binlogs
Multi-threaded restore (for versions 0.2.1 and above)
Works as a daemon for scheduled snapshots and continuous binary logs (for versions 0.5.0 and above)
Open-source (GNU GPLv3)
Since Mydumper uses multi-threaded exports, it cannot guarantee the same export order as mysqldump, which may cause data inconsistencies for time-dependent features such as routines and events. It is recommended to export and import the MySQL system database separately from other databases.
Mydumper’s database extraction and merging functions rely on delimiters, so database names should not contain dots (“.”), and table names should not contain hyphens (“-”).
3. mysqlbackup
mysqlbackup, abbreviated as MEB, is a backup tool developed by Oracle for the enterprise version of MySQL. This tool provides robust MySQL backup capabilities, including hot backups, incremental backups, selective data backups, and data restoration.
By utilizing parallel processing, mysqlbackup performs block-level parallel backups of database data, improving backup efficiency and speed.
MEB supports three types of MySQL database backups: Full, Differential, and Incremental. It also supports compressed storage of these backups. After performing a backup, three types of files are generated: ibdata, ibd, and ibz. Among them:
“ibdata” contains the InnoDB system tablespace data;
“Ibd” contains the actual data files;
“Ibz” contains compressed data files.
Additionally, MEB generates files such as “backup-mysql.cnf” (a backup of the MySQL configuration at the time of backup) and incremental backup files like “ibbackup_ibd_files” and “ibbackup_logfile”, which store incremental data and logs. MySQL has also introduced a separate redo log backup mechanism to enhance data integrity.
4. Physical Backup: Percona XtraBackup
Percona XtraBackup is a hot backup tool for MySQL developed by Percona, designed for physical backups based on InnoDB’s crash recovery mechanism. Its basic working principle is as follows:
1. At startup, a redo log copying process is created to capture and record the current log sequence number (LSN) and continuously copy modified redo logs from that point onward.
2. A thread is initiated to copy “ibdata1”, undo tablespaces, and all “ibd” files.
3. Once “ibd” file copying is complete, it triggers a “FLUSH TABLES WITH READ LOCK” (or applies a backup lock).
4. It then backs up non-InnoDB data files (.frm, .MRG, .MYD, .MYI, etc.).
5. It also backs up information related to the replica database and binary logs.
6. Finally, it flushes logs, copies the latest redo logs, exits the log copying thread, and releases global locks while recording backup metadata to complete the process.
During restoration, Percona XtraBackup applies the copied redo logs, commits completed transactions, and rolls back uncommitted transactions to restore the database to a consistent state.
Since Percona XtraBackup produces physical files, it avoids many of the issues associated with logical backups like mysqldump when restoring or migrating databases. However, different backup parameters may impact the database to varying degrees.
XtraBackup has the advantages of minimal impact on the database and fast recovery, making it the preferred choice for routine backups. In contrast, mysqldump is more flexible but requires careful handling to avoid disrupting existing data.
5. phpMyAdmin
phpMyAdmin is a MySQL database management tool based on PHP, structured as a web-based application running on a web host. It allows administrators to manage MySQL databases through a web interface, providing a convenient way to input complex SQL syntax, particularly useful for importing and exporting large amounts of data.
phpMyAdmin offers a graphical database interface similar to Microsoft Access and SQL Server, enabling users to perform various MySQL operations such as creating databases, tables, and generating MySQL database script files.
One major advantage of phpMyAdmin is that, like other PHP applications, it runs on a web server. However, users can access the generated HTML pages from anywhere, enabling remote MySQL database management. This facilitates the creation, modification, and deletion of databases and tables. Additionally, phpMyAdmin can generate commonly used PHP syntax, ensuring the correctness of SQL statements when developing web applications.
6. MySQL Workbench
MySQL Workbench is a free, official graphical tool for managing MySQL databases, offering a user-friendly interface for database design, development, and administration. It allows users to create and edit database structures visually with ER diagrams, generate SQL scripts, and synchronize changes with the database. The built-in SQL editor supports syntax highlighting, auto-completion, and query execution with visualized results. Additionally, it provides tools for data import/export, user permission management, performance monitoring, and database migration. Compatible with Windows, macOS, and Linux, it caters to both beginners and experienced developers, streamlining database tasks without requiring extensive command-line knowledge.
7. Vinchin Backup & Recovery
Vinchin Backup & Recovery offers a more automated, efficient, and secure solution, streamlining MySQL backup processes with centralized management, fast recovery, incremental backups, and flexible storage strategies to ensure long-term data availability. And it also provides flexible backup strategies for Oracle DB, SQL Server, SQL Server, PostgreSQL, Postgres Pro, and MariaDB.
Its optimized storage features, such as data deduplication and compression, significantly reduce backup file storage requirements and reduce backup time. At the same time, its encryption and transmission security functions ensure the security of backup data, making it suitable for enterprises that need to strictly protect data.
To create MySQL database backup jobs, please go to Physical Backup > Database Backup > Backup page:
1. Select the databases which need to be backed up.
2. Select a backup node on which you want the backup data to be processed and stored.
3. Configure backup strategies according to your needs.
4. Review and confirm the settings.
Click the button below to try Vinchin's 60-day free trial to experience an efficient and reliable data backup and recovery solution!
MySQL Backup Tools FAQs
1. What is the difference between logical and physical backups?
Logical backups store data as SQL statements, while physical backups copy raw database files.
2. What is point-in-time recovery (PITR) in MySQL?
It allows restoring a database to a specific moment using binary logs (binlog).
Conclusion
Selecting the right backup tool for your MySQL database is crucial for ensuring data safety and operational efficiency. Understanding the strengths and limitations of each tool allows you to tailor your backup strategy to meet specific needs, ensuring robust data protection and seamless recovery in any scenario.
