Import a local SQL File To MySQL on a Remote Server Using SSH Tunnel

Question

I have a connection between my localhost and a remote server using putty SSH tunnel.

Thats fine.

Now I need a command to get the sql file on my local machine i.e. c:\folder\test.sql and import it into mysql on the remote server

I thought maybe...

mysql -u prefix_username -p testpass -h localhost -P 3307 prefix_testdb

then do a command like

mysql -p testpass -u prefix_username prefix_testdb < c:\folder\test.sql 

this command did not work.

How can I acheive this?

Answer 1

You should run this command

mysql -h host -u user_name -pPassword database < file.sql > output.log

file.sql contains the sql queries to run and output.log makes sense only when you have a query that returns something (like a select)

The only thing different I can see in your code is the blank space between the -p option and the password. If you use the -p option, you must write the password without leaving any blank space. Or you just can user the option --password=Password

I hope you can solve the problem

Answer 2

You will need to ssh to the remote machine with the mysql command appended:

ssh remote_user@remote_server mysql -p testpass -u username testdb < c:\folder\test.sql 

Answer 3

 1. mysql -h xxx -uxxx -pxxx . //login to the remote mysql
 2. use DATABASE.             //assign which db to import
 3. source path/to/file.sql  //the path can be your local sql file path.

Reference: Import SQL file into mysql