6.4.2 How to import or export a database via SSH

TheHost FTP Banner

Import/Export Database Using SSH

For import, you need to first upload the database dump to the server. The dump should be in .sql format. Next, connect to the server via SSH and execute the following command:

  1. mysql -u database_user -p user_password database_name < dump_file

In case of failure, an error will always be specified, indicating the reason for the unsuccessful or interrupted import.

For export, there is a utility that allows you to back up the database in the traditional SQL format –- mysqldump. The general command in the command line looks like this:

  1. mysqldump -u database_user -p user_password database_name  > dump_file
  • database_user - the username of the database user with access to the database;
  • user_password - password of the specified user;
  • database_name - the name of the database created on the server;
  • dump_file - this is the file in the current directory.

Database Management Using SSH

In addition to import and export, using SSH significantly simplifies other nuances of database management. Below are some key useful examples of using mysqldump and others:

Example: how to create a new database 
  1. mysqladmin -u USER -p PASSWORD create NEWDATABASE
Example: how to view the list of user databases 
  1. mysqlshow -u USER -p PASSWORD
Example: how to view the list of tables in the database 
  1. mysqlshow -u USER -p PASSWORD DATABASE
Example: the command with the -–all-databases option will save all databases on the MySQL server 
  1. mysqldump -–all-databases -u USER -p PASSWORD > /path/mysql-db-server.sql
Example: how to save tables table1, table2, table3 from the DATABASE in the file DATABASE_t1-t2-t3.sql 
  1. mysqldump -u USER -p PASSWORD DATABASE table1 table2 table3 > /path/ DATABASE_t1-t2-t3.sql
Example: using the --no-data option will save the table structure (without data) in the file DATABASE_schema.sql 
  1. mysqldump --no-data -u USER -p PASSWORD DATABASE > /path/DATABASE-schema.sql
Example: the --add-drop-table option will add the DROP TABLE command (table deletion) before creating tables 
  1. mysqldump --add-drop-table -u USER -p PASSWORD DATABASE > /path/DATABASE.sql
Example: the --databases option will add the CREATE DATABASE command before creating the database. This allows not creating and specifying a database during restoration 
  1. mysqldump --databases -u USER -p PASSWORD DATABASE > /path/DATABASE.sql
Example: how to remotely back up the DATABASE from the server with the IP address 192.168.0.1 
  1. mysqldump -u USER –p PASSWORD –h192.168.0.1 DATABASE > /path/DATABASE.sql
Example: the --max_allowed_packet=8M option will forcefully change the size of the packet of read data in RAM to 8 megabytes 
  1. mysqldump --max_allowed_packet=8M -u USER –p PASSWORD DATABASE > /path/DATABASE.sql
Example: the --quick option forces the command to write data directly to the disk 
  1. mysqldump --quick -u USER –p PASSWORD DATABASE > /path/DATABASE.sql
Example: forcibly specify the encoding cp1251 
  1. mysqldump --default-character-set=cp1251 -u USER –p PASSWORD DATABASE > /path/DATABASE.sql
Example: sequence of obtaining a database archive using gzip 
  1. mysqldump -u USER –p PASSWORD DATABASE | gzip -c /path/DATABASE.sql.gz

To restore, you will need to extract from the archive in advance.

Example: how to load a backup archive into the database 
  1. gunzip < /path/to/outputfile.sql.gz | mysql -u USER -p PASSWORD DATABASE

or like this:

  1. cat /path/to/outputfile.sql.gz | mysql -u USER -p PASSWORD DATABASE