Import and Export MySQL Database Command Line Superfast. Efficiently importing and exporting MySQL databases is essential for backups, migrations, and restoring data. Using the command line, you can perform these tasks quickly and securely. Below are the fastest methods to handle MySQL database imports and exports.

Exporting MySQL Database

The most common tool for exporting is mysqldump, which creates a SQL file containing the database structure and data.

Basic Export Command:

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

Examples:

1. Export a single database: mysqldump -u root -p my_database > my_database.sql
2. Export all databases: mysqldump -u root -p --all-databases > all_databases.sql

Optimize Speed:

• Use the --quick and --single-transaction options for large databases: mysqldump -u root -p --quick --single-transaction my_database > my_database.sql
• Compress the output to save space: mysqldump -u root -p my_database | gzip > my_database.sql.gz

---

Importing MySQL Database

To import a MySQL database, use the mysql command to restore the SQL file.

Basic Import Command:

mysql -u [username] -p [database_name] < [input_file.sql]

Examples:

1. Import a single database: mysql -u root -p my_database < my_database.sql
2. Import a compressed file: gunzip < my_database.sql.gz | mysql -u root -p my_database

Optimize Speed:

• Disable foreign key checks during import for faster processing: SET FOREIGN_KEY_CHECKS=0; SOURCE my_database.sql; SET FOREIGN_KEY_CHECKS=1;

---

Advanced Tools for Speed

1. mysqlpump: A faster, parallelized alternative to mysqldump: mysqlpump -u root -p my_database > my_database.sql
2. mydumper and myloader: Multi-threaded tools for large databases.

---

Conclusion

For smaller databases, mysqldump and mysql work effectively. For large-scale operations, consider tools like mysqlpump or mydumper for parallel processing. Always optimize server parameters and leverage compression for faster results.