Using command-line to Export and Import MySQL Database

Waqar Ahmed
2 min readMay 5, 2021


There can be multiple ways for MySQL/MariaDB database export and import during database server migration or database backup. The quickest seems to be the command-line one, it seems much faster than PHPMyAdmin or SequelPro clients. I am sharing Linux commands for MySQL, MariaDB export and import and an error that I encountered when I first used these command-line tools.

Export MySQL Database

MySQL data backup program mysqldump creates a dump file of SQL statements for table data and database schema.

mysqldump client utility to backup databases
MySQL Server database Migration with mysql export and import

Log into MySQL on Linux shell. Depending on your MySQL server settings, use one

mysqldump -u username -p database-name > db-dump.sql

mysqldump does not show a success message. Do ls in the directory where you ran the command to find db-dump.sql. You can include or exclude specific content into a dump and have gzipped compression, for details visit MySQL dump: Database backup program resources.

Moving MySQL dump to a new server

Depending on your convenience and database size you can move SQL dump via FTP, SCP, or simply move the dump to a public_html directory and access it on HTTP. On your destination server run a WGET command to copy the dump file.

You will need to create a new database after database creation have a new user account for this database so you do not have to use the root user. Check this post to setup database, mysql user and grant all necessary permissions to the user.

Import MySQL Database

On the Linux shell of the destination server, move to the directory where you have the database SQL dump file and run this command

mysqlimport -u username -p database-name < db-dump.sql

you can also import in MySQL command line, log in to MySQL command line and run

mysql> use database-name;

mysql > source db-dump.sql

or simply run this one command in Linux shell

mysql -u username -p database-name < db-dump.sql

MySQL Database Error 1604 (42000)

During MySQL database import I received this error. It was due to running mysqldump inside the MySQL command-line. The mysqldump is not a MySQL command it is a separate program to be run in a Linux shell.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump -u username -p database-name > database-dump.sql’ at line 1



Waqar Ahmed

Most often use technologies like PHP, Laravel, JavaScript, SQL and Linux. Portsmouth university alumni. Like a one-line, elegant algorithm.