In daily maintenance work, data export operations are often required, and mysqldump is a frequently used tool for exporting data. It comes with many built-in functional parameters, and this article will list some commonly used operations.
What is mysqldump?
mysqldump is a MySQL client program used for dumping databases. It primarily generates a series of SQL statements that can be encapsulated into a file. This file contains all the SQL commands needed to reconstruct your database, such as CREATE DATABASE, CREATE TABLE, INSERT, etc. It can be used for lightweight and quick database migration or recovery. It is a method for performing logical backups in MySQL databases.
How to export a MySQL database using mysqldump?
1. Export all databases
This command exports all databases, including system databases.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Export all data from databases db1 and db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Exports multiple databases. All subsequent parameters after the option are considered database names.
Add a DROP DATABASE statement before creating each database.
mysqldump -uroot -p --databases test --add-drop-database
3. Export tables a1 and a2 from db1
Note that exporting specified tables can only be done for a single database, and the exported content differs from exporting a database. The exported text for specified tables does not include database creation statements; it only includes DROP TABLE, CREATE TABLE, and INSERT statements.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Override the --databases parameter and specify the tables to export.
mysqldump -uroot -p --host=localhost --databases test --tables test
Export all tablespaces.
mysqldump -uroot -p --databases test --all-tablespaces
Exclude all tablespace information from the export.
mysqldump -uroot -p --databases test --no-tablespaces
Add a DROP TABLE statement before creating each table (enabled by default, use --skip-add-drop-table to disable).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Conditional export: Export records with id=1 from table a1 in db1
Conditional export can only be performed on a single table.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
Dump only records selected by the given WHERE condition. If the condition contains command interpreters, spaces, or special characters, it must be quoted.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Export table structures only, without data
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Export data only, without adding CREATE DATABASE statements.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Export data only, without adding CREATE TABLE statements.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Exclude specific tables. To ignore multiple tables, repeat the option for each table. Each table must specify both the database and table name, e.g., --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Use delayed insert mode (INSERT DELAYED) when exporting data.
mysqldump -uroot -p --databases db1 --delayed-insert
Use INSERT statements with multiple VALUES clauses. This reduces the export file size and speeds up import. Enabled by default; use --skip-extended-insert to disable.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Export and import data between servers
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
This means exporting all data from db1 on server h1 and importing it into db2 on h2. The database db2 must exist; otherwise, an error will occur.
Use a named pipe to connect to MySQL.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Enable compression for communication between client and server.
mysqldump -uroot -p --all-databases --compress
7. Append master database binlog position and filename to the export file
This option, when run on a slave server, is equivalent to executing SHOW SLAVE STATUS.
Setting it to 1 outputs a CHANGE MASTER command in the data file.
Setting it to 2 adds comments before CHANGE MASTER.
This option enables --lock-all-tables unless --single-transaction is specified.
After execution, --lock-tables is automatically disabled. The default value for --dump-slave is 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Export events.
mysqldump -uroot -p --all-databases --events
8. Flush logs before export
Note: If exporting multiple databases (--databases or --all-databases), logs are flushed per database. If using --lock-all-tables or --master-data, logs are flushed once, locking all tables. To export and flush logs simultaneously, use --lock-all-tables or --master-data along with --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Ensure consistent state during export
Before exporting data, this option issues a BEGIN SQL statement. BEGIN does not block any applications and ensures database consistency during export. It is only applicable to multi-version storage engines (e.g., InnoDB). This option is mutually exclusive with --lock-tables, as LOCK TABLES implicitly commits any pending transactions. For exporting large tables, use it with the --quick option.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Export stored procedures and functions
mysqldump -uroot -p --host=localhost --all-databases --routines
Another method: using SELECT...INTO OUTFILE to export MySQL data
SELECT...INTO OUTFILE is a MySQL syntax used to export query results to a file. In MySQL, you can use the this statement to easily export data to a text file.
Basic Usage:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Example:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
In the above SQL statement, we selected the id, name, and email columns from the users table and wrote the results to the /tmp/user_data.csv file. FIELDS TERMINATED BY ',' specifies the column separator (comma), and LINES TERMINATED BY '\n' specifies the row separator (newline).
Important Notes:
Executing SELECT...INTO OUTFILE requires the appropriate permissions, and the output file's directory must be a location that the MySQL server can write to.
In the following example, we export data from the runoob_tbl table to the /tmp/runoob.txt file:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
You can specify the output format using command options. The following example exports data in CSV format:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Properties of SELECT ... INTO OUTFILE:
LOAD DATA INFILE is the reverse operation of SELECT ... INTO OUTFILE. To write database data to a file, use SELECT ... INTO OUTFILE, and to read the file back into the database, use LOAD DATA INFILE.
A SELECT statement in the form of SELECT...INTO OUTFILE 'file_name' writes the selected rows into a file. Since the file is created on the server host, you must have the FILE privilege to use this syntax.
The output file must not already exist, preventing data tampering.
You need an account with server login access to retrieve the file. Otherwise, SELECT ... INTO OUTFILE will not work.
On UNIX systems, after the file is created, it is readable, and its permissions are owned by the MySQL server. This means that although you can read the file, you may not be able to delete it.
Ultimate Protection for Your Database Ecosystem
In an era where data resilience is critical, choosing a backup solution with global scalability and multi-cloud agility is key. Vinchin Backup & Recovery stands out with its robust MySQL protection tailored for international enterprises:
✅ Comprehensive Backup Strategies
Supporting hot backup, full backup, incremental backup and transaction log backup strategies;
✅ Universal Database Support
Unify protection across MySQL, Oracle, SQL Server, and more in hybrid environments (physical/cloud/VM).
Simplify management with a single console for multi-database backup policies.
✅ Anti-ransomware reinforcement
Backup data encrypted storage + anti-ransomware feature to resist malicious attacks.
✅ Global Support, Local Efficiency
24/7 technical assistance and cost-optimized licensing, ideal for SMEs and distributed teams.
To create MySQL database backup jobs with Vinchin, 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.
Vinchin is trusted by financial institutions and multinational IT infrastructures across 60+ countries. Click the button below to try Vinchin's 60-day free trial to experience an efficient and reliable data backup solution!
Conclusion
In this article, we've explored various mysqldump options for efficiently exporting MySQL database data, from full database exports to more granular exports such as individual tables or specific records. Understanding these commands and their functionality helps streamline database backup, migration, and recovery processes.
