Export and import database using SSH

Last updated 5 years, 6 months ago | 1910 views 75     5

Tags:- MySQL SSH

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.

  1. The file must be in .sql format. It can not be compressed in a .zip or .tar.gz file.
  2. Start by uploading the .sql file onto your server.
  3. 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