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] <p><strong>Additional Options</strong></p><p>In addition to the export and import commands, mysqldump provides several additional options for customization:</p>

• --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 <p>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.</p>

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!