MySQL Dump Import: Fix Common Errors

Importing a MySQL database dump is a routine task when moving a site, restoring a backup, or cloning a database. This guide walks you through the standard import process using the command line and phpMyAdmin, then explains the most frequent import errors and how to resolve them. You’ll learn what each error message means and the exact commands or settings to fix it.

What You’ll Need

• Access to the MySQL server (root or a user with CREATE and INSERT privileges on the target database).
• The dump file (usually a .sql, .sql.gz, or .zip file).
• Command-line access (Terminal on macOS/Linux, Command Prompt or PowerShell on Windows) or a tool like phpMyAdmin.
• The target database name – create it beforehand if it doesn’t exist.
• MySQL client tools (mysql, mysqldump) installed on your machine – they come with the MySQL server package.

Step-by-Step

1. Create the target database (if needed). Connect to MySQL as a privileged user:
   mysql -u root -p
   Then run:
   CREATE DATABASE IF NOT EXISTS your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   Exit with EXIT;
2. Import a plain .sql file from the command line. Use the mysql client with the database name and the dump file:
   mysql -u username -p your_database_name < /path/to/dump.sql
   You'll be prompted for the password. If the dump contains CREATE DATABASE and USE statements, you can omit the database name from the command.
3. Import a compressed dump (.sql.gz). Pipe the decompressed file directly into mysql:
   gunzip < /path/to/dump.sql.gz | mysql -u username -p your_database_name
   On Windows, use gzip -dc if you have GnuWin32, or extract the file first.
4. Import via phpMyAdmin. Log in, select the target database in the left sidebar, click the Import tab, choose your file, and click Go. For large files, increase the upload_max_filesize and post_max_size in php.ini.
5. Verify the import. Check the table count and row counts:
   mysql -u username -p -e "USE your_database_name; SHOW TABLES;"
   Run a simple query like SELECT COUNT(*) FROM your_table; to confirm data is present.

Common Mistakes

• Forgetting to create the database first – the import fails with “Unknown database�.
• Using the wrong character set – the dump may be UTF-8 but the target database is latin1, causing garbled text.
• Importing a compressed file without decompressing – mysql sees binary garbage and throws a syntax error.
• Not using the -p flag – mysql may prompt for a password, but if you have no password set, omit -p; otherwise it waits forever.
• Importing into a database that already has tables – you get “Table already exists� errors unless the dump includes DROP TABLE statements.
• Exceeding PHP limits in phpMyAdmin – the import silently stops or times out for large files.

Tips

• Always back up the target database before importing, in case you need to revert.
• If the dump is huge, use the command line instead of phpMyAdmin – it's faster and more reliable.
• Use --default-character-set=utf8mb4 in the mysql command to force the character set.
• For production imports, consider using mysql --max-allowed-packet=512M to avoid packet size errors.
• Check the dump file for a USE statement – if it exists, you don't need to specify the database name.
• If you're importing a dump from an older MySQL version, run mysql_upgrade afterward to update system tables.

Troubleshooting

• “ERROR 1045 (28000): Access denied for user� – Your username or password is wrong, or the user lacks privileges on the target database. Grant privileges with:
  GRANT ALL PRIVILEGES ON your_database_name.* TO 'username'@'localhost'; FLUSH PRIVILEGES;
• “ERROR 1049 (42000): Unknown database� – The database doesn't exist. Create it first as shown in Step 1.
• “ERROR 1064 (42000): You have an error in your SQL syntax� – Usually caused by importing a compressed file or a dump that uses a different MySQL version. Decompress the file first, and check the dump's first lines for version-specific syntax.
• “ERROR 2006 (HY000): MySQL server has gone away� – The import is too large for the server's max_allowed_packet. Increase it in my.cnf under [mysqld]:
  max_allowed_packet=512M
  Then restart MySQL.
• “ERROR 1114 (HY000): The table is full� – The disk is full or the table size limit is reached. Free up disk space or increase the table size limit (if using MyISAM).
• Import times out in phpMyAdmin – Increase execution_time_limit and memory_limit in php.ini, or use the command line.
• “Unknown collation: 'utf8mb4_unicode_520_ci'� – The dump was created on a newer MySQL version. Replace the collation in the dump file with a supported one (e.g., utf8mb4_unicode_ci) using a text editor or sed.

Frequently Asked Questions

Can I import a dump without creating the database first?

Yes, if the dump file contains CREATE DATABASE and USE statements. Otherwise, you must create the database manually.

How do I import a .sql.zip file?

Unzip it first to get a .sql file, then import that. Or use a pipe: unzip -p dump.zip | mysql -u user -p dbname.

Why does the import stop halfway with no error?

This often happens with phpMyAdmin due to memory or time limits. Check the PHP error log and increase limits, or switch to the command line.

What does “max_allowed_packet� mean and how do I change it?

It's the maximum size of a single SQL statement or packet. Set it higher in my.cnf under [mysqld] and restart MySQL. You can also set it per session: SET GLOBAL max_allowed_packet=1073741824;

Can I import a dump from MySQL 5.7 into MySQL 8.0?

Generally yes, but you may see compatibility warnings. Use mysql_upgrade after import and check for deprecated syntax.

Is it safe to import a dump over an existing database?

It depends on the dump. If it includes DROP TABLE IF EXISTS, it will replace data. Otherwise, you'll get duplicate key errors. Always backup first.

Related

• LG TV Sound Not Working With HDMI? Fixes
• Data Warehouse vs Lakehouse for AI Workloads: How to Choose
• OLED vs IPS Gaming Monitors: Which Is Better?