Can't connect to local MySQL server through socket error when using SSH tunel
As silly as it sounds replacing localhost
with an IP address (127.0.0.1
) solves the problem.
src_mysql(
dbname = "mydb", user = "mysqluser", password = "mypassword",
host = "127.0.0.1", port=3307)
For an explanation take a look at the MySQL documentation:
On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs.
For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number.
To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server.
Can't connect to local MySQL Server using SSH tunnel
I had to change the DB connection string in the settings.py
to 127.0.0.1
instead of localhost
.
Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)
If your file my.cnf (usually in the /etc/mysql/ folder) is correctly configured with:
socket=/var/lib/mysql/mysql.sock
You can check if mysql is running with the following command:
mysqladmin -u root -p status
Try changing your permission to mysql folder. If you are working locally, you can try:
sudo chmod -R 755 /var/lib/mysql/
That solved it for me.
Connect mySQL via SSH error 2002
I needed to specify "127.0.0.1" instead of "localhost" as the address since I was binding the IP itself, and not the actually localhost.
MySQL connection over SSH tunnel
There are three issues here.
1 - Forget about the SSH tunnel for now
You cannot bind MySQL to more than one specific IP.
The first bind-address
clause is overridden (therefore, ignored) by the second one. Your server only listens to 99.99.99.99
.
The reason why you can connect with -h localhost
but not with -h 127.0.0.1
is that in the first form, you do not actually connect through TCP/IP, but through a local socket.
Look in your my.cnf
for a socket
clause.
Remove one redundant bind-address
clause. You may want to use bind-address=0.0.0.0
, which instructs MySQL daemon to listen to all network interfaces.
2 - Let's setup your SSH tunnel
The reason for you error ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
is not obvious to me. I suspect SSH tunnel is actually established only when it receives a connection request (in your case, when you run the mysql
client). Since your server does not listen to 127.0.0.1 (see previous paragraph), the SSH tunnel cannot be established, connection fails, and your client interprets it as a network failure.
3 - Why mysql -v -h localhost -P 9989 -u user userdb -p
fails
Please post the output of
[edit : just added ...OR host LIKE 'localhost'
below, as this might be relevant for troubleshooting purposes]
mysql > SELECT user, host FROM mysql.user WHERE user LIKE 'user' OR host LIKE 'localhost';
(replace 'user'
, after the LIKE
clause, with the actual user name if necessary)
MySQL access control checks both the username/password (user
) and the origin of the connection (host
) to identify a user. You probably did not create a user 'user'@'localhost'
.
N.B.: mysql.com being unreachable from my location at this time, I cannot link to the relevant manual pages.
Related Topics
Retrieve Census Tract from Coordinates
R: How to Make a Barplot with Labels Parallel (Horizontal) to Bars
Treat Na as Zero Only When Adding a Number
Convert Xml_Nodeset to Data.Frame
Add Image (Png File) to Header of PDF File Created with R
Using Predict to Find Values of Non-Linear Model
Multiply Columns in a Data Frame by a Vector
Subset Data Based on Partial Match of Column Names
Datatable Is Not Printed in Combination with Cat Command in Rmd/Rstudio
How to Reset All Options() Arguments to Their Default Values
How to Pass the "..." Parameters in the Parent Function to Its Two Children Functions in R
Porting Set Operations from R's Data Frames to Data Tables: How to Identify Duplicated Rows
Use a Custom Icon in Plotly's Pie Chart in R
R Memory Management Advice (Caret, Model Matrices, Data Frames)
Create Multiple Data Frames from One Based Off Values with a for Loop