Using command-line to Export and Import MySQL Database
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.
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
