Access mysql remote database from command line

Question

I have a server with Rackspace. I want to access the database from my local machine command line.

I tried like:

mysql -u username -h my.application.com -ppassword

But it gives an error:

ERROR 2003 (HY000):

Can't connect to MySQL server on 'my.application.com' (10061)

What causes this error and how can I connect to the remote database?

It's best to use the `-p` flag without the password, so the password isn't saved in your bash history. — reynoldsnlp, Jul 14 '21 at 01:24

score 475 · Answer 1 · edited Jan 18 '23 at 01:38

475

To directly login to a remote mysql console, use the below command:

mysql -u {username} -p'{password}' \
    -h {remote server ip or name} -P {port} \
    -D {DB name}

For example

mysql -u root -p'root' \
        -h 127.0.0.1 -P 3306 \
        -D local

no space after -p as specified in the Using Options on the Command Line documentation

It will take you to the mysql console directly by switching to the mentioned database.

edited Jan 18 '23 at 01:38

Manuel Jordan

15,253
21
95
158

answered Apr 06 '16 at 12:54

Venkat M

4,831
1
10
6

2

To specify the database name we need to add -D . mysql -u {username} -p{password} -h {remote server ip} -D {DB name} – randeepsp Apr 06 '18 at 09:06
1

what if we need to specify the specific PORT? – serge Apr 25 '18 at 15:43
2

Didn't work in Terminal on MacOS High Sierra Version 10.13.3 – realPK Aug 16 '18 at 21:31
21

**no intervening space** after `-p` https://dev.mysql.com/doc/refman/8.0/en/command-line-options.html – Ivan Chau Oct 27 '18 at 03:50
Is interesting that in https://dev.mysql.com/doc/refman/8.0/en/command-line-options.html does not do mention about the `-D` parameter – Manuel Jordan Jul 18 '19 at 02:51
I was able to connect to the database without typing any space between -u and the username, -p and the password, -h and the host. Like this: `mysql -u$username -p$password -h$host -D $database_name`. – SametSahin Jan 08 '20 at 11:09
5

If this is for the case of a user typing at the console you should easily avoid passing the password in the command line as unencrypted text. Just use `-p` and the rest as specified. The execution of the script will interrogate you for the password securely. – Pablo Adames Mar 19 '20 at 19:36
1

For example `mysql -u root -h 127.0.0.1 -P 3306 -D local -p` – David Winiecki May 12 '21 at 17:36

score 45 · Answer 2 · answered Jun 01 '17 at 12:28

45

simply put this on terminal at ubuntu:

mysql -u username -h host -p

Now hit enter

terminal will ask you password, enter the password and you are into database server

answered Jun 01 '17 at 12:28

Kedar Jangir

485
5
6

score 36 · Answer 3 · answered Apr 08 '13 at 06:29

36

edit my.cnf file:

vi /etc/my.cnf:

make sure that:

bind-address=YOUR-SERVER-IP

and if you have the line:

skip-networking

make sure to comment it:

#skip-networking

don't forget to restart:

/etc/init.d/mysqld restart

answered Apr 08 '13 at 06:29

Nir Alfasi

53,191
11
86
129

bind-address=YOUR-SERVER-IP . here which IP i need to mention is it remote server IP? – user1986299 Apr 08 '13 at 06:35
@user1986299 no, your need your server's IP (not the remote) – Nir Alfasi Apr 08 '13 at 06:36
i AM GETTING ERROR: ERROR 1045 (28000): Access denied for user 'root'@'115.119.117.94' (using password: YES) – user1986299 Apr 08 '13 at 07:56
1

you should grant yourself access... login your DB from the local server by typing mysql and then: `grant all privileges on *.* to 'root'@'%' identified by 'your-password'` – Nir Alfasi Apr 08 '13 at 15:45
8

NEVER grant 'root'@'%' this is a huge security no no! – josh123a123 Apr 27 '15 at 14:57
for mariadb i edited /etc/mysql/mariadb.conf.d/50-server.cnf – tsukimi Jan 05 '18 at 14:36
MySQL 5.7 is under /etc/mysql/mysql.conf.d/mysql.cnf – kurdtpage Jan 29 '18 at 01:24
1

@alfasin this is a small tweak with a deep impact, thank you! – Christopher Schönfeldt Jul 30 '19 at 11:18

score 22 · Answer 4 · answered Aug 09 '18 at 07:59

22

For Mac, use the following command:

mysql -u app -h hostaddress -P port -D dbname -p

and then enter the password when prompted.

answered Aug 09 '18 at 07:59

KayV

12,987
11
98
148

1

Don't try to enter the password after -p flag, that won't work. As mentioned above you'll be prompted for the password value later on. This is done so that there are no unmasked credentials stored in the terminal history. – Konstantin Grigorov Nov 01 '21 at 15:43

score 16 · Answer 5 · edited Oct 31 '16 at 06:42

16

Try this command mysql -uuser -hhostname -PPORT -ppassword.

I faced a similar situation and later when mysql port for host was entered with the command, it was solved.

edited Oct 31 '16 at 06:42

the_unknown_spirit

2,518
7
34
56

answered Oct 31 '16 at 05:42

rajarajan2809

308
5
11

6

`mysql -u testuser -h 192.168.**.* -P 3306 -p password` worked for me – Mahebub A Sayyed Nov 08 '16 at 07:40

Cornea Valentin · Answer 6 · 2018-05-18T07:20:15.360

If you want to not use ssh tunnel, in my.cnf or mysqld.cnf you must change 127.0.0.1 with your local ip address (192.168.1.100) in order to have access over the Lan. example bellow:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Search for bind-address in my.cnf or mysqld.cnf

bind-address            =  127.0.0.1

and change 127.0.0.1 to 192.168.1.100 ( local ip address )

bind-address            =  192.168.1.100

To apply the change you made, must restart mysql server using next command.

sudo /etc/init.d/mysql restart

Modify user root for lan acces ( run the query's bellow in remote server that you want to have access )

root@192.168.1.100:~$ mysql -u root -p

..

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

If you want to have access only from specific ip address , change 'root'@'%' to 'root'@'( ip address or hostname)'

CREATE USER 'root'@'192.168.1.100' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Then you can connect:

nobus@xray:~$ mysql -h 192.168.1.100 -u root -p

tested on ubuntu 18.04 server

score 10 · Answer 7 · edited Jan 18 '23 at 01:43

10

try telnet 3306. If it doesn't open connection, either there is a firewall setting or the server isn't listening (or doesn't work).
run netstat -an on server to see if server is up.
It's possible that you don't allow remote connections.

For more details see:

How Do I Enable Remote Access To MySQL Database Server?

edited Jan 18 '23 at 01:43

Manuel Jordan

15,253
21
95
158

answered Apr 08 '13 at 06:27

score 7 · Answer 8 · answered Jun 08 '17 at 00:07

7

I assume you have MySQL installed on your machine. Execute the command below after filling missing details:

mysql -uUSERNAME -pPASSWORD -hHOSTNAME -P3306

answered Jun 08 '17 at 00:07

realPK

2,630
29
22

score 5 · Answer 9 · answered Jun 23 '19 at 18:36

5

mysql servers are usually configured to listen only to localhost (127.0.0.1), where they are used by web applications.

If that is your case but you have SSH access to your server, you can create an ssh tunnel and connect through that.

On your local machine, create the tunnel.

ssh -L 3307:127.0.0.1:3306 -N $user@$remote_host

(this example uses local port 3307, in case you also have mysql running on your local machine and using the standard port 3306)

Now you should be ale to connect with

mysql -u $user -p -h 127.0.0.1 -P 3307

answered Jun 23 '19 at 18:36

mivk

13,452
5
76
69

How do I forward multiple ports to different servers at the same time? My use case -- we have several production Wordpress servers that are behind a firewall. Also assume that each of them uses a different SSH key to allow access. – RodrikTheReader Oct 07 '20 at 03:53
@RodrikTheReader : just repeat the commands with different port numbers and the appropriate $user@$host variables. You can also add different `-i $identity_file` to the ssh command if you have different keys. – mivk Oct 07 '20 at 07:41

score 4 · Answer 10 · answered Aug 06 '19 at 12:30

4

There is simple command.

mysql -h {hostip} -P {port} -u {username} -p {database}

Example

mysql -h 192.16.16.2 -P 45012 -u rockbook -p rockbookdb

answered Aug 06 '19 at 12:30

Manish Goswami

442
4
10

{hostip} will not add any kind of port detail like 192.10.10.26::45124 – Manish Goswami Aug 06 '19 at 12:42

zoro juro · Answer 11 · 2023-08-12T08:38:45.790

4

you can use the following code to connect to a remote MY SQL database

mysql -u {database_user} -p{db_password} -h {host_name} -P {port_number}
mysql -u your_db_username -p'your_password' -h your-company.aws.com -P 3306

edited Aug 12 '23 at 08:38

answered Sep 27 '21 at 15:10

zoro juro

96
5

score 2 · Answer 12 · answered Apr 08 '13 at 06:28

2

Must check whether incoming access to port 3306 is block or not by the firewall.

answered Apr 08 '13 at 06:28

Amirol Ahmad

532
5
21

How can I check this – ChandraMouli Poreddy Nov 10 '19 at 08:28

score 1 · Answer 13 · answered Jan 25 '17 at 11:09

this solution worked for me:

On your remote machine (example: 295.13.12.53) has access to your target remote machine (which runs mysql server)

ssh -f -L 295.13.12.53:3306:10.18.81.36:3306 user@295.13.12.53

Explained:

ssh -f -L your_ssh_mashine_ipaddress:your_ssh_mashine_local_port:target_ipaddress:target_port user@your_ip_address -N

your_ssh_mashine_ipaddress - it is not local ip address, it is ip address that you ssh to, in this example 295.13.12.53

your_ssh_mashine_local_port -this is custom port not 22, in this example it is 3306.

target_ipaddress - ip of the machine that you trying to dump DB.

target_port - 3306 this is real port for MySQL server.

user@your_ip_address - this is ssh credentials for the ssh mashine that you connect

Once all this done then go back to your machine and do this:

mysqldump -h 295.13.12.53 -P 3306 -u username -p db_name > dumped_db.sql

Will ask for password, put your password and you are connected. Hope this helps.

score 1 · Answer 14 · answered Feb 10 '18 at 10:43

1

Try this, Its working:

mysql -h {hostname} -u{username} -p{password} -N -e "{query to execute}"

answered Feb 10 '18 at 10:43

ravi teja

11
2

score 1 · Answer 15 · answered Dec 17 '19 at 10:17

This one worked for me in mysql 8, replace hostname with your hostname and port_number with your port_number, you can also change your mysql_user if he is not root

      mysql --host=host_name --port=port_number -u root -p

Further Information Here

score 0 · Answer 16 · answered Dec 21 '16 at 15:57

0

You should put your password with 'p'

mysql -u root -u 1.1.1.1 -p'MyPass'

answered Dec 21 '16 at 15:57

Karin Lahyani

131
1
6

score 0 · Answer 17 · answered Jul 10 '17 at 14:01

I was too getting the same error. But found it useful by creating new mysql user on remote mysql server ans then connect. Run following command on remote server:

CREATE USER 'openvani'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'openvani'@'localhost WITH GRANT 
OPTION;
CREATE USER 'openvani'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'openvani'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Now you can connect with remote mysql with following command.

mysql -u openvani -h 'any ip address'-p

Here is the full post:

http://openvani.com/blog/connect-remotely-mysql-server/

score 0 · Answer 18 · answered Dec 31 '19 at 15:07

0

If you are on windows, try Visual Studio Code with MySQL plugins, an easy and integrated way to access MySQL data on a windows machine. And the database tables listed and can execute any custom queries.

answered Dec 31 '19 at 15:07

Arun Panneerselvam

2,263
1
17
24

score 0 · Answer 19 · answered Feb 01 '23 at 11:27

If port is default, some version required data base name which you trying to connect.

mysql -u <<your username>> -h <<your host>> <<your db name >> -p

This will prompt password Then type your password. If port is not default 3306 Then:

mysql -u <<your username>> -h <<your host>> -P <<your port>> <<your db name >> -p

Access mysql remote database from command line

19 Answers19

Linked

Related