Managing MySQL databases efficiently is crucial for web hosting, development, and server management. Whether you’re working with a VPS hosting plan or handling large-scale applications on a dedicated server, knowing how to import and export MySQL databases using the command line is essential for backups, migrations, and troubleshooting.

Why Use Command Line for MySQL Import and Export?

  • Faster execution compared to web-based tools.
  • Automate database management through scripts.
  • Handle large databases efficiently without timeouts.
  • Ensure data security with direct server access.

How to Export a MySQL Database

1. Log into Your Server

First, access your server via SSH:

ssh user@yourserver.com

2. Export the Database Using mysqldump

The mysqldump command is used to create a backup of your MySQL database:

mysqldump -u root -p database_name > backup.sql
  • -u root specifies the MySQL user.
  • -p prompts for the password.
  • database_name is the name of the database to export.
  • backup.sql is the output file where the database backup is stored.

3. Compress the Backup File (Optional)

To save space, you can compress the backup file using gzip:

gzip backup.sql

This will create backup.sql.gz, reducing storage usage.

How to Import a MySQL Database

1. Create a New Database (If Needed)

If the destination database does not exist, create one before importing:

mysql -u root -p -e "CREATE DATABASE new_database;"

2. Import the Database Using mysql

To restore a database from a backup file:

mysql -u root -p new_database < backup.sql
  • new_database is the name of the database where you want to import the data.
  • backup.sql is the file containing the exported database.

3. Import a Compressed Backup (Optional)

If the backup file is compressed, use the following command:

gunzip < backup.sql.gz | mysql -u root -p new_database

Best Practices for MySQL Import and Export

  • Always back up before making changes to avoid data loss.
  • Use compression for large databases to save disk space.
  • Verify the import process by checking the database tables.
  • Secure database credentials and avoid storing passwords in scripts.

Conclusion

Using the command line for MySQL import and export ensures efficiency, security, and flexibility in database management. Whether you’re running databases on a VPS hosting plan or a dedicated server, mastering these commands will streamline your workflow and improve overall database administration.