SoFunction
Updated on 2025-03-04

Use mysqldump to export to import mysql table structure or data

Preface

Use the mysqldump tool to easily export table data in a MySQL database. mysqldump is a command line tool that is usually installed with a MySQL server. Here are the steps and examples of how to export table data using mysqldump.

Basic syntax

mysqldump -h[ipaddress] -P[port] -u [username] -p [Database name] [Table name] > [Output file name].sql

Example

Suppose you have a name calledmydatabase Database，One of them is called user_managetable, you want to export the data of the table to auser_manage_backup.sqlin the file.

  • Open the command line terminal(Command prompt or PowerShell on Windows, terminal on macOS or Linux).

  • RunmysqldumpOrder:

mysqldump  -u your_username -p mydatabase user_manage > user_manage_backup.sql
  • -u your_username: Specify your MySQL username.
  • -p: Prompt to enter your password.
  • mydatabase: The name of the database to export the data.
  • user_manage: The name of the table to export the data.
  • > user_manage_backup.sql: Save the exported data touser_manage_backup.sqlin the file.

Other common options

Export data contains Chinese

In the windows cmd window, add the parameter –default-character-set UTF8

mysqldump -h 192.168.10.10 -P3306 -uroot -proot --default-character-set UTF8 --databases  mydatabase > mydatabase_backup.sql

Export only data (not including table structure)

If you want to export only the data in the table without including the table structure, you can use--no-create-infoOptions:

mysqldump -u your_username -p --no-create-info mydatabase user_manage > user_manage_data_only.sql

Export only table structure (not including data)

If you just want to export the table structure without data, you can use--no-dataOptions:

mysqldump -u your_username -p --no-data mydatabase user_manage > user_manage_structure_only.sql

Add locks to ensure consistency

For large databases, you can use--single-transactionand--quickOptions to avoid long-term locking of tables and ensure transaction consistency:

mysqldump -u your_username -p --single-transaction --quick mydatabase user_manage > user_manage_backup.sql

Compress export files

To save disk space, you can compress the exported files into.gzdocument:

mysqldump -u your_username -p mydatabase user_manage | gzip > user_manage_backup.

Export multiple tables

If you want to export multiple tables, you can list all table names in the command:

mysqldump -u your_username -p mydatabase table1 table2 > multiple_tables_backup.sql

Export the entire database

If you want to export all tables and data for the entire database, you can omit the table name:

mysqldump -u your_username -p mydatabase > mydatabase_backup.sql

usemysqldumpExported SQL files can be imported into the database through MySQL's command line tools or through other client tools such as phpMyAdmin or MySQL Workbench. The following are the steps to import data through the command line tool.

Import SQL files

usemysqlCommand line tools import SQL files. Here are a few common methods:

Method 1: Use the mysql command directly

mysql -u your_username -p target_database < tenant_manage_backup.sql
  • -u your_username: Specify your MySQL username.
  • -p: Prompt to enter your password.
  • target_database: The name of the target database to import the data.
  • < tenant_manage_backup.sql:fromtenant_manage_backup.sqlThe file reads and executes SQL statements.

Method 2: Use the source command

First, enter the MySQL command line interface:

mysql -u your_username -p

Then, select the target database in the MySQL command line and executesourceOrder:

USE target_database;
SOURCE /path/to/tenant_manage_backup.sql;
  • USE target_database;: Select the target database.
  • SOURCE /path/to/tenant_manage_backup.sql;: Execute all commands in the SQL file. Make sure the path is correct.

4. Verify import

After the import is complete, you can verify that the data is imported correctly:

mysql -u your_username -p target_database -e "SELECT * FROM tenant_manage LIMIT 10;"

Things to note

  • Database size:For very large databases, it is recommended to use--single-transactionand--quickOption to reduce lock time.
  • Data consistency: If you need to ensure data consistency, you can lock tables when exporting, but this may affect database performance.

Summarize

This is the article about using mysqldump to export and import mysql table structure or data. For more related export and import mysql table structure or data content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!