Export and import a MySQL database

What you need before step 1

Applies to: MySQL 8.0 and 8.4 (commands also used with MariaDB), command line or phpMyAdmin, as documented in the MySQL Reference Manual and phpMyAdmin documentation

8 steps

1. 1

   Export with mysqldump

   Where: SSH session or terminal on a machine that can reach the source database (Linux/macOS shell or Windows Command Prompt)

   Dump one database. --databases adds CREATE DATABASE and USE statements so the dump reloads into a database of the same name; leave it out if you want to load into a database with a different name. --single-transaction issues a BEGIN before dumping; with it, the LOCK TABLES privilege is not required. --routines and --events include stored procedures, functions and events. Enter the password when prompted.

   mysqldump -h db.contoso.com -u contoso_admin -p --single-transaction --routines --events --databases contoso_db > contoso_db.sql

   You should see: The command returns to the prompt with no errors and contoso_db.sql has a non-zero size.
2. 2

   Windows PowerShell: use --result-file

   Where: Windows PowerShell

   MySQL warns that a dump made in PowerShell with > redirection is UTF-16 encoded and cannot be loaded correctly. In PowerShell write the file with --result-file instead.

   mysqldump -h db.contoso.com -u contoso_admin -p --single-transaction --routines --events --databases contoso_db --result-file=contoso_db.sql

   You should see: contoso_db.sql is created and opens as readable text.
3. 3

   Or export with phpMyAdmin

   Where: Hosting control panel > phpMyAdmin > select the database in the left pane > Export tab

   Choose Custom, make sure the format is SQL, tick Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement, choose gzipped compression for a smaller file, and select Go. For big tables, the Maximal length of created query option splits large extended INSERTs, which phpMyAdmin's documentation says can help avoid max_allowed_packet errors on import.

   You should see: The browser downloads contoso_db.sql or contoso_db.sql.gz.
4. 4

   Copy the dump to the target server

   Where: Your PC or the source server

   Copy the file with scp, SFTP or your host's file manager. Compress it first if it is large.

   gzip contoso_db.sql
   scp contoso_db.sql.gz admin@newserver.contoso.com:/home/admin/

   You should see: The file is on the target server with the same size.
5. 5

   Create the target database if the dump has no CREATE DATABASE

   Where: SSH session on the target server

   Skip this if you used --databases. Otherwise create an empty database first (many hosts make you create it in the control panel instead). Use the same character set as the source database; check it on the source with SHOW CREATE DATABASE contoso_db;.

   mysql -u contoso_admin -p -e "CREATE DATABASE IF NOT EXISTS contoso_db CHARACTER SET utf8mb4;"

   You should see: No output. SHOW DATABASES; now lists contoso_db.
6. 6

   Import with the mysql client

   Where: SSH session on the target server (Linux/macOS shell or Windows Command Prompt)

   If the dump contains CREATE DATABASE and USE, load it with no database name. If not, name the target database. In Windows PowerShell the < character is reserved, so wrap the command in cmd.exe /c. You can also load it from inside the mysql client with source.

   gunzip contoso_db.sql.gz
   mysql -u contoso_admin -p < contoso_db.sql
   mysql -u contoso_admin -p contoso_db < contoso_db.sql
   cmd.exe /c "mysql -u contoso_admin -p contoso_db < contoso_db.sql"

   You should see: The command returns to the prompt with no ERROR lines.
7. 7

   Or import with phpMyAdmin

   Where: Target host control panel > phpMyAdmin > select the target database > Import tab

   Choose the .sql or .sql.gz file and select Import (or Go). If the file is too big to upload, phpMyAdmin's FAQ 1.16 lists the options: raise upload_max_filesize, post_max_size (which must be larger), memory_limit and max_execution_time in php.ini (your host can do this), upload the file to the server and use $cfg['UploadDir'], or import over SSH with the mysql client.

   You should see: phpMyAdmin reports that the import finished, and the tables appear in the left pane under the database.
8. 8

   Compare the source and target

   Where: mysql client on both servers (or phpMyAdmin Structure tab)

   Compare the table list and row counts for key tables on the source and the target.

   mysql -u contoso_admin -p -e "SHOW TABLES;" contoso_db
   mysql -u contoso_admin -p -e "SELECT COUNT(*) FROM orders;" contoso_db

   You should see: The same tables exist and the row counts match.

Expert tips

How to confirm it worked

• The table list on the target matches the source.

• Row counts for your largest and most important tables match.

• The application (for example WordPress) connects to the new database and shows recent records, with accented characters and emoji displayed correctly.

How to reverse it

Errors you might see

Questions

Official sources

Vendors change menus and requirements. Confirm on the source before you rely on a detail. Last checked 4 Nov 2026.