Import or Export database from MySQL or MariaDB database from MySQL shell or command line. This is the easiest method to export database or particular table from a database.
If you have a huge database which is more than 500Mb, it will be very hard to export which takes more time to import or export the entire database which may result in timeout error.
In this guide you are going to learn how to import or export database using command line from MySQL shell. You will also learn some fixes to the problems where you cannot export your database.
Prerequisites
- Server SSH access.
- MySQL username, database name, password to export database.
- MySQL user and password who has privileges to create database for importing.
Export MySQL Database from Command Line
Login to your server SSH and take note of your database name, username and password. Make sure your user has full access to the database you wish to export.
You can use the following command to export the entire database.
mysqldump -u username -p database_name > filename.sql
You will be prompted to enter the password for the username.
Once you have entered the password your database will be exported to a .sql format file and stored in your current directory.
Problems where you Can’t Export Database
Access denied error.
- You can try login as root user
sudo suand export the database. - May be password mismatch.
- May be user don’t have sufficient privileges to the database.
Access denied PROCESS privilege(s) error.
You need to use the --no-tablespaces option with the mysqldump command. So the command will look like the one below.
mysqldump --no-tablespaces -u username -p database_name > filename.sql
Export Only Certain tables
To export specific tables of a database from command line you need to pass the table names as shown below.
mysqldump -u username -p database_name table1 table2 > filename.sql
The above command will export only the table names specified.
Import MySQL Data from Command Line
This is the best method to import a huge database file without any errors.
Upload the .sql file to your server. Login to SSH and navigate to the directory where you have uploaded the database file.
If you don’t have a database you need to login as a user like root to create a database.
CREATE DATABASE database_name /*\!40100 DEFAULT CHARACTER SET utf8mb4 */;
To import database you can use the following command.
mysql -u username -p database_name < backup.sql
Make sure you have enough privileges on your database.
Caution: If you use > instead of <. All the data in the backup.sql will get erased or corrupted.
If your file size is big the time taken will be high for importing or exporting.
Conclusion
Now you have learned how to export and import MySQL database from command line.
Thanks for your time. If you face any problem or any feedback, please leave a comment below.
