People who use phpMyAdmin may find using command line difficult as using wrong syntax can lead to anything, while using command line is the most secure and reliable way where phpMyAdmin fails due to scripts limits.
First, login to server using SSH like: ssh username@hostname (and use -p flag if using any port).
Before we use export and import commands, make sure you know MySQL login prompts.
How to export an SQL file using command line in MySQL?
We will use MySQL dump command here, like:
mysqldump -u USERNAME -p DB_NAME > export.sql
You will be asked for USERNAME’s password, enter that and export will start.
You can also assign a path to export like:
mysqldump -u USERNAME -p DB_NAME > /home/userfiles/dbsaves/export.sql
If you are not using any password or using default root user, you can also skip using username, -u, and -p.
How to import an SQL file using command line in MySQL?
Once we have exported an SQL file, let’s see how to import it back:
mysql -u USERNAME -p DB_NAME < import.sql
You will be asked for USERNAME’s password, enter that and import will start.
If you are located on a different path than the current database location, use path of file like:
mysql -u USERNAME -p DB_NAME < /home/userfiles/dbsaves/import.sql
If you are not using any password or using default root user, you can also skip using username, -u, and -p.
This is how we can export/import SQL file using the command line in MySQL and without using phpMyAdmin.
Leave a Reply