SSH | Export and Import Large Database OR Table
Using SSH can be a good practice to manage the files and databases on your account. It is most important to use in the following circumstance
- Servers without a control panel. These servers do not have phpMyAdmin installed. So, to manage a database without a control panel.
- When dealing with large databases over 50Mb
Using a simple command you can import or export a MySQL database.
Exporting MySQL Data
So, let's see an example:
Using SSH, execute the following command:
mysqldump -p -u username database_name > dbname.sql
You will be prompted for a password, type the password for the username, and press Enter.
Info:
Replace username, password, and database_name with your MySQL username, password, and database name.
The file dbname.sql now holds a backup of your database and is ready for download to your computer.
To export a single table from your database you would use the following command:
mysqldump -p --user username database_name tableName > tableName.sql
Import A MySQL Database
Below are some steps which are used for the same.
- The file must be in .sql format. It can not be compressed in a .zip or .tar.gz file.
- Start by uploading the .sql file onto your server.
- Using SSH, navigate to the directory where your .sql file is.
Next, run this command:
mysql -p -u username database_name < file.sql
To import a single table into an existing database you would use the following command:
mysql -u username -p -D database_name < tableName.sql