How to import SQL dump file to database via SSH

[myVesta]

If you are logged in as 'root' in SSH, then run:

Code: Select all

mysql database_name < dump_of_database.sql

If you are not logged in as 'root' in SSH, then you need login credentials for database:

Code: Select all

mysql -h localhost -u mysqlUsername --password='somepass' database_name < dump_of_database.sql

• hostname - it's localhost if you are dumping database from the same server
• mysqlUsername - The username for MySQL.
• password - The password of MySQL user.
• database_name - The name of the database you want to backup.
• dump_of_database.sql - The file name for your sql dump.

That's it!

WARNING if you didn't created that sql dump via 'mysqldump' command:
If you didn't created that sql dump via 'mysqldump' command, it may contain SQL statements that can make you a problem.

Before importing, check if your SQL dump contains 'CREATE DATABASE' :

Code: Select all

grep 'CREATE DATABASE' dump_of_database.sql

In case that 'grep' command gave you any output, then edit your SQL file and delete the whole SQL statement:

Code: Select all

CREATE DATABASE ...

and also delete statements that begin with

Code: Select all

USE ...

Before importing the SQL dump file, please also check if it contains a dump of the 'MySQL' database (at the end or the top of the SQL dump file).
The easiest way to check if it contains 'DROP DATABASE' is:

Code: Select all

grep 'DROP DATABASE' dump_of_database.sql

(first grep command for 'CREATE DATABASE' would probably be enough, because it will give you a hint that SQL dump would try to do something with the 'MySQL' database)

If you see something like "CREATE DATABASE `mysql`" or "DROP DATABASE `mysql`" then you must delete whole that part (related to 'mysql' database) from your SQL dump file.
Otherwise, you will rewrite your own 'mysql' database, which would lead to losing all your mysql users - which means that all databases on the server will instantly become unavailable.

Here you can find how to EXPORT the database: viewtopic.php?t=659

Potential errors during this process, and how to fix them:

• Error: Unknown collation while importing SQL dump file