38

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
  • 381
  • 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

6 Answers6

56

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

Smern
  • 18,746
  • 21
  • 72
  • 90
user2993479
  • 571
  • 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`? – Aipo Apr 17 '18 at 08:09
  • 1
    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
  • 1,045
  • 16
  • 28
15
 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
  • 1,045
  • 2
  • 14
  • 24
7

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
  • 8,415
  • 9
  • 52
  • 80
  • 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
0

mysql {mydbname} --host {server}.mysql.database.azure.com --user {login} --password={password} < ./{localdbbackupfile}.sql

As managed services, DevOps, and CI/CD workflows have become more popular by this point, most providers of those managed services want to remove the human error part of getting the connection strings correct. If you happen to be using Azure, AWS, GCP, etc, There usually is a page or terminal command that shows you these strings to help you easily integrate. Don't forget to check their docs if you're using something like that. They are auto generated, so they are most likely 'best practice' with spot-on correct syntax for the db version you may be using.

The above command is from "connection strings" on the product details page of my Azure Managed Mysql DB Server instance.

Not necessarily asked, but an fyi, a lot of those services auto generate templates for use in a lot of common connection scenarios:

{
  "connectionStrings": {
    "ado.net": "Server={server}.mysql.database.azure.com; Port=3306; Database=mytestdb; Uid={login}; Pwd={password};",
    "jdbc": "jdbc:mysql://{server}.mysql.database.azure.com:3306/mytestdb?user={login}&password={password}",
    "jdbc Spring": "spring.datasource.url=jdbc:mysql://{server}.mysql.database.azure.com:3306/mytestdb  spring.datasource.username={login}  spring.datasource.password={password}",
    "mysql_cmd": "mysql mytestdb --host {server}.mysql.database.azure.com --user {login} --password={password}",
    "node.js": "var conn = mysql.createConnection({host: '{server}.mysql.database.azure.com', user: '{login}', password: {password}, database: mytestdb, port: 3306});",
    "php": "$con=mysqli_init(); [mysqli_ssl_set($con, NULL, NULL, {ca-cert filename}, NULL, NULL);] mysqli_real_connect($con, '{server}.mysql.database.azure.com', '{login}', '{password}', 'mytestdb', 3306);",
    "python": "cnx = mysql.connector.connect(user='{login}', password='{password}', host='{server}.mysql.database.azure.com', port=3306, database='mytestdb')",
    "ruby": "client = Mysql2::Client.new(username: '{login}', password: '{password}', database: 'mytestdb', host: '{server}.mysql.database.azure.com', port: 3306)"
  }
}
user658182
  • 2,148
  • 5
  • 21
  • 36
-2

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
  • 11,135
  • 7
  • 46
  • 70
PeterPan
  • 1