SoFunction
Updated on 2025-04-11

Implementation steps of MySQL logical backup

MySQL logical backup refers to using SQL statements to back up the database's structure and data, rather than directly backing up the database files. Usually usedmysqldumpThe tool performs logical backup.

1.mysqldump

mysqldumpIt is the most commonly used logical backup tool, which can generate SQL script files that can be used to rebuild database structure and data.

Basic commands:

mysqldump -u [username] -p[password] [Database name] > [Backup files.sql]

Parameter options:

  • -u, --user=[name]: Specify the MySQL username.
  • -p, --password[=password]: Specify the MySQL password. If no password is provided,mysqldumpIt will be prompted to enter.
  • -h, --host=[hostname]: Specify the MySQL server host name.
  • -P, --port=[port_num]: Specify the MySQL server port number.
  • --protocol=[{TCP|SOCKET|PIPE|MEMORY}]: Specify the connection protocol.
  • -r, --result-file=[file]: Write the output directly to the file, not through standard output.
  • -t, --no-create-info: Do not write statements that create tables, only export data.
  • -d, --no-data: Do not write data, only export the database structure.
  • --add-drop-table: Add before each table statementDROP TABLE IF EXISTSSentence.
  • --add-locks: Use before and after inserting dataLOCK TABLESandUNLOCK TABLESSentence.
  • --disable-keys: Use before inserting dataALTER TABLE ... DISABLE KEYS, use after inserting dataALTER TABLE ... ENABLE KEYS。
  • -B, --databases: Specify the database to be backed up. If this option is used, all database names that follow will be backed up.
  • -A, --all-databases: Backup all databases.
  • --tables: Specifies the table to be backed up, which must be followed by the database name.
  • -n, --no-create-db: No statements that create database are written.
  • -w, --where='condition': Only export rows that meet the specified criteria.
  • --single-transaction: Export all data in one transaction, suitable for tables that support transactions (such as InnoDB).
  • --lock-tables: Lock all tables before export, suitable for tables that do not support transactions (such as MyISAM).
  • --master-data[=#]: Add the binary log location and file name of the main server in the export to set up replication.
  • --flush-logs: Refresh the MySQL server logs before exporting.
  • --routines: Export stored procedures and functions.
  • --triggers: Export trigger.
  • --events: Export events.
  • --hex-blob: Export binary columns in hexadecimal format.
  • --set-gtid-purged=[OFF|ON|AUTO]: Controls whether to include GTID information in the backup, suitable for GTID replication.

Example

Backup the entire database

mysqldump -u root -p mydatabase > mydatabase_backup.sql

Backup multiple databases

mysqldump -u root -p --databases db1 db2 > multi_database_backup.sql

Back up all databases

mysqldump -u root -p --all-databases > all_databases_backup.sql

Backup the database structure only

mysqldump -u root -p --no-data mydatabase > mydatabase_structure_backup.sql

Backup only specific tables

mysqldump -u root -p mydatabase table1 table2 > mydatabase_tables_backup.sql

Add options to effectively handle lock table issues

mysqldump -u root -p --single-transaction --quick --lock-tables=false mydatabase > mydatabase_backup.sql

Recover data

Recover the database

grammar

mysql -u [username] -p[password] [Database name] < [Backup files.sql]

Example

mysql -u root -p mydatabase < mydatabase_backup.sql

Recover tables in the library

mysqldump -u root -p[password] [Library name] [Table name] &gt; staff_backup.sql

Use source recovery

First log in to mysql and restore it using the following syntax in mysql

source File path;

Example:

source /path/to/employees_backup.sql;

Things to note

  • Permission Management: Ensure executionmysqldumpThe user with the recovery command has corresponding database read and write permissions.
  • Regular backup: Logical backups usually require high data consistency and integrity. It is recommended to regularly backup and verify the validity of the backup files.
  • Storage location: Store backup files in a secure location to avoid loss or improper modification.

2. mysqlpump

mysqlpumpIt is a backup tool introduced by MySQL 5.7. It supports multi-threading and can perform backups faster. It ismysqldumpEnhanced version.

Mysqlpump Features

  • Parallel processing：mysqlpumpSupports concurrent backup of multiple tables and multiple databases, greatly improving backup speed.
  • More functional options: Supports filtering specific databases, tables and data, and compressing while exporting.
  • Enhanced flexibility: Provide more parameters to customize backup operations.

Basic commands:

mysqlpump -u [username] -p[password] [Database name] &gt; [Backup files.sql]

Common options:

  • --exclude-databases: Exclude specific databases
  • --include-databases: Contains specific databases
  • --default-parallelism: Set the parallelism
  • --skip-definer: Ignore the DEFINER clause
  • --set-gtid-purged: Backup for GTID

Example

Backup a single database

mysqlpump -u root -p mydatabase > mydatabase_backup.sql

Backup multiple databases

mysqlpump -u root -p --databases db1 db2 > multi_database_backup.sql

Back up all databases

mysqlpump -u root -p --all-databases > all_databases_backup.sql

Concurrent processing

mysqlpump -u root -p --default-parallelism=4 --databases db1 db2 > multi_database_backup.sql

use--default-parallelismThe parameters can set the number of concurrent threads to speed up the backup speed.

Filter table

mysqlpump -u root -p mydatabase --exclude-tables=table1,table2 > mydatabase_backup.sql

use--exclude-tablesSpecific tables can be excluded

Backup table structure only

mysqlpump -u root -p mydatabase --skip-dump-data > mydatabase_structure_backup.sql

Compress backup files

mysqlpump -u root -p mydatabase | gzip > mydatabase_backup.

Backup data directory

mysqlpump --users --routines --databases db1 db2 > backup_with_users_routines.sql

use--usersBack up user accounts and use--routinesBackup storage functions and stored procedures.

Recover the database

Unzip the backup file

gunzip < mydatabase_backup. | mysql -u root -p mydatabase

Restore backup

mysql -u [username] -p [Database name] &lt; [Backup files.sql]

Example

mysql -u root -p mydatabase < mydatabase_backup.sql

Things to note

  • Permission Management: Ensure executionmysqlpumpThe user of the recovery command has the necessary database read and write permissions.
  • Concurrency: Set the number of concurrent threads reasonably to avoid excessive server load.
  • Regular backup: It is recommended to regularly backup and verify the integrity of the backup files.
  • Storage location: Store backup files in a safe and reliable location to avoid loss or improper modification.

Pros and cons of logical backup

advantage：

  • Strong readability: Backup files are SQL scripts in text format, which are easy to view and edit.
  • Cross-platform: Backup files can be ported between different operating systems and different versions of MySQL.
  • Flexible backup: You can choose to back up the entire database, specific tables, or structures.

shortcoming：

  • Large performance overhead: There will be large CPU and I/O overhead during backup and recovery, especially for databases with large data volumes.
  • Slow recovery speed: Compared with physical backups, logical backups are slower to restore.
  • Consistency Issue: If the transaction consistency option is not used correctly, data inconsistency may be caused.

Backup table data extensions only

/var/lib/mysql-filesis the directory MySQL uses to store exported files by default. This directory is usually usedSELECT INTO OUTFILEThe file generated by other operations.

mysqldump -n

introduce

mysqldumpis a tool for backing up MySQL databases.-nParameters allow you to only backup data in the table, excluding the table structure. The backup file is a pure SQL statement that can be used to restore data.

Give an example

Scene: There is a name calledemployeesdatabase containingstaffsurface. BackupstaffData in tables, but not table structure.

step:

Go to the command line.

Execute the following command:

mysqldump -u root -p[password] --no-create-info employees staff &gt; staff_data_only.sql

3. Results: Generatedstaff_data_only.sqlFile content:

INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (1, 'Alice', 'Manager', 50000);
INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (2, 'Bob', 'Engineer', 40000);
INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (3, 'Charlie', 'Technician', 30000);

recover:

  • In the new environment, make sure that the same structure already existsstaffsurface.
  • Enter the MySQL client and execute:
source /path/to/staff_data_only.sql;

SELECT INTO OUTFILE

introduce

SELECT INTO OUTFILEis an SQL statement used to export query results to a file. File formats can be defined as needed (such as CSV format or xlsx-end format), and are usually used for data analysis and transmission.

Give an example

1. Scene: There is a name calledemployeesdatabase containingstaffsurface. ExportstaffThe data in the table is analyzed as a CSV file.

2. Steps:

Enter the MySQL client.

Execute the following SQL statement:

SELECT * FROM staff INTO OUTFILE '/var/lib/mysql-files/staff_data.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
 LINES TERMINATED BY '\n';

 Command interpretation (you can also use the default, so there are no need for so many specified requirements statements later):
 -- Select all columns and rows from the staff table
 SELECT * FROM staff

 -- Write the query result to the file with the specified path
 INTO OUTFILE '/var/lib/mysql-files/staff_data.csv'

 -- The data for each field (column) is separated by commas
 FIELDS TERMINATED BY ','

 -- The value of each field is surrounded by double quotes
 ENCLOSED BY '"'

-- Each line of records is separated by newline characters
LINES TERMINATED BY '\n';

3. Results: Generatedstaff_data.csvFile content:

    "1","Alice","Manager","50000"
    "2","Bob","Engineer","40000"
    "3","Charlie","Technician","30000"

recover:

  • In the new environment, make sure that the same structure already existsstaffsurface.
  • copystaff_data.csvFile to the server.
  • Use the following SQL statements to import data:
      LOAD DATA INFILE '/var/lib/mysql-files/staff_data.csv'
      INTO TABLE staff
      FIELDS TERMINATED BY ',' 
      ENCLOSED BY '"'
      LINES TERMINATED BY '\n';

Summarize

Different uses:

  • mysqldump -nMainly used to back up and restore MySQL database data. Generate SQL files to facilitate recovery of data when needed.
  • SELECT INTO OUTFILEMainly used for data export for analysis or transmission. The generated file format such as CSV can be opened by tools such as Excel.

Recover the specified table:

  • mysqldump -nRecovering data is easier, just load the source file directly into MySQL.
  • SELECT INTO OUTFILEThe exported file format is flexible, but additional steps are required to import the data (such as usingLOAD DATA INFILE）。

Convenience and flexibility:

  • mysqldump -nSuitable for cross-platform environments, it is relatively convenient to restore databases.
  • SELECT INTO OUTFILEApplicable to export data for external analysis, with flexible format but slightly complex operation.

This is the end of this article about the implementation steps of MySQL logical backup. For more related contents of MySQL logical backup, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!