How Can I Efficiently Export and Import MySQL Databases Using the Command Line?

Exporting and Importing .sql Files from the Command Line with Options

Exporting and importing SQL files is a common task for database management. The mysqldump utility provides a powerful command-line tool for performing these operations, offering various options to customize the output.

Exporting a SQL File

To export a MySQL database into a SQL file, use the following syntax:

mysqldump -u [username] -p [password] -h [host] --[options] [database] > [filename].sql

• Options:

  • --no-data: Export only the table structure, not data.
  • --skip-constraints: Skip checking foreign key constraints during export.

Importing a SQL File

To import a SQL file into a MySQL database, use the following syntax:

mysql -u [username] -p -h [host] [database] < [filename].sql

Additional Options

In addition to the export and import commands, mysqldump provides several additional options for customization:

• --all: Export or import all databases.
• --databases: Export or import a list of specific databases.
• --extended-insert: Improve the performance of data insertion during import.
• --debug: Enable debugging output.

Example

To export the "blog" database without data and foreign key checks, and import it into the "mydb" database:

Export:

mysqldump -u user -p --no-data --skip-constraints blog > blog_structure.sql

Import:

mysql -u user -p mydb < blog_structure.sql

By utilizing mysqldump with these options, system administrators and database developers can efficiently export and import MySQL databases, tailoring the process to meet specific requirements.

The above is the detailed content of How Can I Efficiently Export and Import MySQL Databases Using the Command Line?. For more information, please follow other related articles on the PHP Chinese website!