This guide will walk you through the process of exporting and importing MySQL databases using SQL dump files. Whether you're performing regular backups or migrating data between servers, understanding how to create and restore these dumps is crucial. We'll cover two methods: using phpMyAdmin and command-line tools like mysqldump and mysql.

Exporting a MySQL Database

Using phpMyAdmin

  1. Log in to your phpMyAdmin interface.
  2. Select the database you wish to export from the left-hand menu.
  3. Click on the "Export" tab at the top of the page.
  4. Choose the "Quick" export method for a simple backup or switch to "Custom" if you need more options.
  5. Select the format as SQL and ensure that all tables are included in the dump.
  6. Click on the "Go" button at the bottom of the page to start the download of your database dump file.

Using Command Line (mysqldump)

  1. Open a terminal or command prompt.
  2. Run the following command, replacing username, password, and database_name with your MySQL username, password, and database name respectively: 
    mysqldump -u username -p database_name > backup.sql
  3. You will be prompted to enter the password for the MySQL user.
  4. The command will create a file named backup.sql in your current directory containing the SQL dump of the specified database.

Importing a MySQL Database

Using phpMyAdmin

  1. Log in to your phpMyAdmin interface.
  2. Select the target database from the left-hand menu where you want to import data. If it doesn't exist, create a new one by clicking on "Databases" and entering the name of the new database.
  3. Click on the "Import" tab at the top of the page.
  4. Choose your SQL dump file from your local machine using the "Browse" button.
  5. Click on the "Go" button to start the import process.

Using Command Line (mysql)

  1. Open a terminal or command prompt.
  2. Create an empty database if it doesn't already exist: 
    mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS target_database_name;"
  3. Import the SQL dump file into your MySQL database using the following command, replacing username, password, and target_database_name with your MySQL username, password, and target database name respectively: 
    mysql -u username -p target_database_name < backup.sql
  4. You will be prompted to enter the password for the MySQL user.
  5. The command will import the contents of backup.sql into the specified database.

Tips for Large Databases

  • Splitting Files: For very large databases, consider splitting your dump file into smaller parts to avoid memory issues during import. You can use tools like mysqldumpsplitter.pl.
  • Increase Timeout Settings: If you're working with a web-based tool like phpMyAdmin and encounter timeouts, increase the PHP execution time or use command-line tools which are less prone to timeouts.
  • Optimize Queries: For faster imports, disable foreign key checks and auto-commit before importing: 
    SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT=0;
    Remember to re-enable them after the import is complete.

Troubleshooting

  • Error 1064 (42000): You have an error in your SQL syntax. This usually indicates a problem with the dump file. Ensure that the dump was created correctly and is not corrupted.
  • Access Denied: Verify that you are using the correct MySQL username and password, and that the user has sufficient privileges to perform the export or import operations.