33

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?

Jkk
  • 331
  • 1
  • 3
  • 3
  • Do you really want to import it to the mysql server on the remote machine? Then there's no need to first transfer it to your local machine. – cb0 May 23 '12 at 12:20
  • It is an SQL file from a different server to the remote server I will be using – Jkk May 23 '12 at 13:07

5 Answers5

46

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

Dallas
  • 17,186
  • 21
  • 64
  • 82
user2993479
  • 471
  • 4
  • 3
  • This is ideally true. Still, may not be a good practise to allow remote connections to the mysql server, until you really know what you are doing. – augusto Dec 03 '14 at 21:37
  • 1
    If your `sql` file contains the schema definition, all you need to run is: `mysql -h host -u user_name -pPassword < file.sql` – CommandZ Jul 10 '15 at 14:57
  • `host` here means? `root@myServerIp`? – pvaitonis Apr 17 '18 at 08:09
  • what if when the .sql file is on the local computer! – Ankit Sahu Apr 29 '19 at 09:46
  • I know this is old but it could help someone... The easiest way to handle passwords if on Linux or even using Windows Subsystem Linux, is to create a file called ".my.cnf" in your home directory. The file should contain the following lines for restore: [mysql] username=username password=mysecret – BrettJ Nov 01 '19 at 05:34
17

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
paulguy
  • 919
  • 12
  • 25
12
 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

Dai Kaixian
  • 877
  • 2
  • 12
  • 21
6

Use 'scp' to copy and mysql to insert to you local machine.

Syntax:

scp remote_user@remove_server:/path/to/sql/file.sql ~/path/to/local/directory

after you transfered the file use:

mysql -uYouUserName -p name_of_database_to_import_to < ~/path/to/local/directory/file.sql
cb0
  • 7,631
  • 9
  • 49
  • 76
  • or you use this mysql -u username -p database_name < /path/to/file.sql From within mysql: mysql> use db_name; mysql> source backup-file.sql; – Kennedy Maikuma Oct 31 '19 at 12:03
-1

You can use pscp to upload file to the server. Go to your command line and type this

pscp.exe c:\folder\test.sql usernameoftheserver@websitename.com:/serverpath
Seki
  • 10,429
  • 5
  • 39
  • 63
PeterPan
  • 1