How Can I Efficiently Export and Import MySQL Databases from the Command Line with Customization Options?

Exporting and Importing .sql Files from Command Line with Customization

Exporting and importing .sql files via command line is a convenient way to manage MySQL databases. This capability provides flexibility and support for specific requirements during the export and import process.

MySQLdump for Exporting Data:

To export a database, use the mysqldump command:

mysqldump -u [username] -p [database_name] > [filename].sql

MySQL for Importing Data:

For importing a .sql file, execute the following:

mysql -u [username] -p -h [hostname] [database_name] < [filename].sql

Customizing Export and Import Options

Export Options:

• --no-data: Exports only the table structure, excluding data.
• --foreign-key-checks=0: Disables foreign key checks during the export.

Import Options:

• --replace: Replaces existing data with imported data.
• --ignore-table={table_name}: Skips importing specific tables.
• --check-same-server: Verifies if the database server is the same as the one used for export.

Example Command:

To export the 'blog' database without data and disable foreign key checks:

mysqldump -u vivek -p blog --no-data --foreign-key-checks=0 > blog_structure.sql

To import the 'data.sql' file into the 'blog' database, replace existing data, and ignore the 'users' table:

mysql -u vivek -p blog --replace --ignore-table=users < data.sql

By utilizing these options, administrators can tailor the export and import process to meet specific requirements, enhancing the efficiency and flexibility of database management from the command line.

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