Skip to main content
MySQL Basics
CHAPTER 23 Beginner

Importing and Exporting Databases

Updated: May 16, 2026
5 min read

# CHAPTER 23

Importing and Exporting Databases

1. Introduction

You have spent weeks building the perfect database structure on your Localhost (XAMPP). You have inserted test data, built Views, and coded Triggers. Now, the website is ready to launch on a live Cloud Server (like AWS or DigitalOcean). How do you physically move a database from your laptop to the cloud? You cannot just drag and drop a folder. You must Export the database into a portable file, and Import it on the target server. In this chapter, we master Data Migration.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Explain what a .sql Dump file is.
  • Export a database visually using phpMyAdmin.
  • Import a database visually using phpMyAdmin.
  • Use the mysqldump CLI tool for massive databases.
  • Understand the mechanics of database migration.

3. What is a SQL "Dump" File?

When you export a database, MySQL does not create a proprietary binary file or a zip folder. It creates a simple, plain-text file with a .sql extension. This file contains thousands of raw SQL commands. It literally writes out the CREATE TABLE and INSERT INTO commands necessary to perfectly recreate your entire database from scratch. Because it is plain text, a .sql file is highly portable and can be opened in any text editor.

4. Exporting via phpMyAdmin (Visual Method)

For small to medium databases (under 50 MB), the visual interface is perfect.
  1. 1. Open http://localhost/phpmyadmin in your browser.
  1. 2. Click on your specific database (e.g., companydb) on the left sidebar.
  1. 3. Click the Export tab at the top.
  1. 4. Export Method: Select Quick.
  1. 5. Format: Select SQL.
  1. 6. Click Go.
*A file named companydb.sql will instantly download to your computer.*

5. Importing via phpMyAdmin (Visual Method)

Now, imagine logging into the phpMyAdmin panel on your new live production server.
  1. 1. Create a blank database on the new server (e.g., CREATE DATABASE companydb;).
  1. 2. Click on the new, empty database in the left sidebar.
  1. 3. Click the Import tab at the top.
  1. 4. Click Choose File and select your companydb.sql file.
  1. 5. Click Go at the bottom.
*phpMyAdmin will read the text file and execute all the CREATE TABLE and INSERT commands one by one. In seconds, your database is fully restored!*

6. The mysqldump CLI (The Professional Method)

phpMyAdmin is a PHP script. It has memory limits (usually 2MB to 40MB). If your database is 5 Gigabytes, phpMyAdmin will crash trying to import it. Professional database administrators rely entirely on the Command Line Interface (CLI).

Exporting a database via CLI: Open your terminal (Command Prompt/Bash) and type:

bash
12 
# syntax: mysqldump -u username -p database_name > output_file.sql
mysqldump -u root -p company_db > backup_company.sql

Importing a database via CLI: First, log into the MySQL CLI and CREATE DATABASE company_db;. Then exit the MySQL prompt back to the standard terminal and type:

bash
12 
# syntax: mysql -u username -p database_name < input_file.sql
mysql -u root -p company_db < backup_company.sql

*Notice the arrow directions! > exports out to a file. < imports into the database.*

7. Exporting Structure vs. Data

Sometimes, you want to launch the website, but you DON'T want to migrate your fake Localhost test users. You only want the empty tables!
  • In phpMyAdmin: Choose "Custom" export, and under "Format-specific options", select Structure only (uncheck Data).
  • In CLI: Add the --no-data flag: mysqldump -u root -p --no-data companydb > structure.sql

8. Common Mistakes

  • Importing without Selecting a Database: If you open the CLI and just type mysql -u root -p < file.sql, MySQL will throw an error saying "No database selected." A .sql dump file usually only contains CREATE TABLE commands. It relies on YOU to CREATE DATABASE and USE database before initiating the import!
  • File Encoding: Always ensure your .sql file is exported using UTF-8 encoding. If you use a weird text editor that saves in a different format, emojis or foreign characters in your database will turn into ???? garbage text upon import.

9. Best Practices

  • Zip Compression: A 1 Gigabyte .sql text file will compress down to roughly 100 Megabytes in a .zip or .gz format! If you are migrating a massive database over a slow internet connection, always compress the dump file first to save hours of upload time.

10. Exercises

  1. 1. What is the fundamental difference in the file contents generated by an "Export Structure Only" vs an "Export Structure and Data" operation?
  1. 2. Write the terminal command to export a database named blogdb using the mysqldump utility.

11. MCQ Quiz with Answers

Question 1

When you export a MySQL database using the standard SQL format, what does the resulting .sql file actually contain?

Question 2

Why is the mysqldump command line utility preferred over phpMyAdmin when exporting or importing a massive 5GB database?

12. Interview Questions

  • Q: Explain the purpose of the mysqldump utility and describe the specific arrow (< vs >) syntax used to dictate the flow of data during CLI import/export operations.
  • Q: A developer imports a .sql file and successfully recreates the tables, but all the data is missing. What specific export configuration likely caused this?

13. FAQs

Q: Can I export just a single table instead of the whole database? A: Yes! In phpMyAdmin, just click on the specific table before clicking the Export tab. In the CLI, just append the table name: mysqldump -u root -p mydatabase specifictable > table.sql.

14. Summary

Data migration is a critical skill for launching web applications. By understanding the plain-text nature of .sql dump files, and by mastering the robust mysqldump CLI utility to bypass the limitations of visual web tools, you can seamlessly transfer architectures and live data between local environments and cloud production servers.

15. Next Chapter Recommendation

You exported your database to move it. But what if the server hard drive physically catches on fire tomorrow? Does your company go bankrupt? In Chapter 24: Database Backup and Recovery, we graduate from manual exports to automated, mission-critical Disaster Recovery strategies.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Triggers in MySQL
Next Chapter Database Backup and Recovery

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6