MySQL Database Backup and Recovery Using mysqldump

Overview of Backup Approaches

MySQL database backups can be categorized into two primary types:

1. Cold Backup
2. Hot Backup

Cold backup in MySQL doesn't have a dedicated tool. Instead, it involves shutting down the database and using OS commands to copy database files.

Hot backup refers to online backup performed without stopping the database service. This is the standard approach in production environments. Hot backups can be further divided into:

1. Logical Backup
2. Physical Backup

For logical backup, the commonly used tool is MySQL's built-in mysqldump. For physical backup, Percona's XtraBackup is typically employed.

For smaller databases with moderate activity levels, mysqldump is usually the preferred solution.

Core Implementation Principles

mysqldump operates at the SQL level, exporting database tables into SQL script files. This approach is particularly suitable for upgrading between different MySQL versions and represents the most common backup method.

The mysqldump command backs up database data into a text file where both table structures and data are stored. The process works by first determining the structure of tables to be backed up, generating CREATE statements in the text file, then converting all table records into INSERT statements. These statements can recreate tables and insert data.

The basic backup flow of mysqldump is as follows:

mysqldump should only be executed during low-traffic periods. Frequent data operations during backup can cause the undo tablespace to grow significantly. The undo tablespace is typically placed in the shared tablespace, and ibdata files have a characteristic of expanding but not shrinking.

The efficiency of mysqldump is relatively low. The START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ command only completes after all tables are backed up. A more efficient approach would be to commit after backing up each table, which would free up undo tablespace snapshot space faster. However, this method doesn't provide consistent backups across all tables.

Non-Consistent Backup Methodology

When performing backup without the --single-transaction parameter, such as:

mysqldump -uadmin -ps3cr3t db_name --triggers --routines --events > /backup/db_name.sql

The backup process involves:

1. Obtaining current GTID information
2. Identifying tables to be backed up in the target database
3. Applying a global read lock (read lock)
4. Using SHOW CREATE TABLE statements for backup (looping through all tables in the database)
5. Releasing the read lock on tables

Characteristics:

• Backup data may be inconsistent
• Table lock duration is proportional to backup size
• Suitable for consistent backups of non-transactional engines (like MyISAM)

Consistent Backup Methodology

mysqldump -uadmin -ps3cr3t db_name --master-data=2 --single-transaction --triggers --routines --events > /backup/db_name.sql

FLUSH TABLES: Closes all open tables, forces tables in use to be closed, and flushes the query cache and prepared statement cache. FLUSH TABLES also removes all query results from the query cache, similar to RESET QUERY CACHE.

The purpose of flush table operation: mysqldump performs a simple assessment of the MySQL database to be backed up, checking for large transactions or DDL operations; it also reduces the time for the second FTWRL lock table operation. If no issues are found, the backup proceeds.

Backup process involves:

1. The initial flush doesn't require table locking - it flushes all data to disk and checks if a locked consistent backup is possible
2. FTWRL applies a global read lock across all databases
3. Sets transaction isolation level to RR (Repeatable Read), which is MySQL's default isolation level, preparing for consistent snapshot reading
4. Starts a transaction with a consistent snapshot
5. Obtains GTID
6. Releases the global read lock applied by FTWRL
7. Creates a savepoint
8. Retrieves information about all tables in the database
9. Uses SHOW CREATE TABLE statements for backup (looping through all tables), returning to the savepoint after each table backup
10. Drops the savepoint

Characteristics:

• Short table lock duration (setting transaction isolation to RR mode and enabling consistent transaction snapshot prevents phantom reads, allowing table locks to be released)
• Consistent backup for InnoDB
• Backup effectiveness depends on the initial flush tables operation
• For non-transactional engines, consistant backup is not guaranteed

Recommended Production Backup Commands

Consistent Backup Without GTID Output:

mysqldump -uadmin -ps3cr3t db_name --set-gtid-purged=OFF --master-data=2 --single-transaction --triggers --routines --events --log-error=/tmp/mysqldump_error_log.err > /backup/db_name.sql

Consistent Backup With GTID Output:

mysqldump -uadmin -ps3cr3t db_name --master-data=2 --single-transaction --triggers --routines --events --log-error=/tmp/mysqldump_error_log.err > /backup/db_name.sql

For InnoDB storage engine, consistent backup is recommended. It has shorter lock times and checks for large transactions before proceeding, minimizing database impact.

Running Backup in Background:

nohup sh backup_script.sh > backup.log 2>&1 &

Additional Backup Options

Compressed Backup:

mysqldump -hlocalhost -uadmin -p'p@ssw0rd' db_name table_name | gzip > /tmp/table_name.sql.gz

Backup All Databases:

mysqldump -u admin -p --all-databases > all.sql

Backup Specific Database:

mysqldump -u admin -p db_test > test.sql

Backup Specific Table:

mysqldump -u admin -p db_test table_emp > emp.sql

Backup Multiple Tables:

mysqldump -u admin -p db_test table_emp table_dept > emp_dept.sql

Export Data with Custom Delimiter:

mysqldump -uadmin -p -T /var/lib/mysql-files/ db_test table_test --fields-terminated-by ','

Backup Multiple Databases:

mysqldump -u username -p --databases db1 db2 > backup.sql

Cross-Host Backup:

mysqldump --host=source_host --opt source_db| mysql --host=target_host -C target_db

Structure-Only Backup:

mysqldump --no-data --databases db1 db2 db3 > structure.dump

Large Data Volume Backup:

mysqldump -uadmin --master-data=2 -p --single-transaction -q --set-gtid-purged=OFF db_test table_large > large_table.sql

Conditional Export with Custom Insert Format:

mysqldump -uadmin -p'p@ssw0rd' db_name table_name -t --set-gtid-purged=OFF --single-transaction --skip-extended-insert --where="id IN (SELECT id FROM temp_table)" > /backup/custom_export.sql

Key Parameters Explained

Essential Parameters:

• --all-databases, -A: Export all databases
• --single-transaction: Creates a consistent snapshot without locking tables (only for InnoDB)
• --master-data: Includes binary log position information (1=uncommented CHANGE MASTER, 2=commented CHANGE MASTER)
• --triggers, -E: Include triggers in dump
• --routines, -R: Include stored procedures and functions
• --events: Include events
• --set-gtid-purged: Control GTID information in dump (OFF, ON, AUTO)
• --log-error: Log errors to specified file

Performance Optimization:

• --quick, -q: Don't buffer query results, dump directly to stdout
• --extended-insert, -e: Use multiple-row INSERT syntax (default)
• --skip-extended-insert: Use one-row INSERT statements
• --net-buffer-length: Adjust network buffer size for better performance

Control Over Output:

• --no-data, -d: Dump only schema, no data
• --no-create-info, -t: Dump only data, no CREATE TABLE statements
• --add-drop-database: Add DROP DATABASE before CREATE DATABASE
• --add-drop-table: Add DROP TABLE before CREATE TABLE (default)
• --hex-blob: Use hexadecimal format for binary columns
• --where, -w: Dump only rows matching condition

Locking Behavior:

• --lock-all-tables, -x: Lock all tables globally (short but blocks everything)
• --lock-tables, -l: Lock tables per database (less safe for cross-db consistency)
• --flush-logs: Flush logs before starting dump

Advanced Options:

• --opt: Combination of useful options (default enabled)
• --compact: Produce more compact output (less comments, headers)
• --xml, -X: Output in XML format
• --tab, -T: Generate separate files for table data and schema
• --order-by-primary: Sort rows by primary key (useful for MyISAM to InnoDB conversion)

Best Practices

1. Schedule backups during low-traffic periods
2. For InnoDB databases, always use --single-transaction
3. Include --master-data for point-in-time recovery capability
4. Regularly test backup restoration procedures
5. Store backups on separate storage from the database server
6. For large databases, consider using --quick and adjusting --net-buffer-length
7. When possible, use --skip-lock-tables with --single-transaction to minimize locking

Troubleshooting

Common issues and solutions:

• "unknown option" errors: Check for typos or use --no-defaults to bypass config files
• Lock timeouts: Reduce lock time with --single-transaction or perform during maintenance windows
• Large dump files: Use compression or split into smaller logical units
• Permission errors: Ensure backup user has necessary privileges

Conclusion

mysqldump provides a versatile and reliable method for MySQL backups when used with appropriate parameters. Understanding the different backup modes and their implications allows database administrators to choose the best approach for their specific requirements.