Remote MySQL Access
Hello Experts,
I am having problems setting up a remote access from one server to another. I have opened port 3306 on the server that houses the database, but I still cannot telnet that port. What other causes could there be for the inability to connect?
I am having problems setting up a remote access from one server to another. I have opened port 3306 on the server that houses the database, but I still cannot telnet that port. What other causes could there be for the inability to connect?
Also check that the user ID your attempting to use is permitted to use the IP / Hostname your attempting to connect via e.g.
select user, host from mysql.user;
Have you check if iptables (stop temporary) is running ?
ASKER
Thank you Experts for your assistance in this matter. Your help is deeply appreciated.
@DaveBaldwin:
That's not a MySQL problem unless the server isn't running.
Yes, I realize that now. MySQL is running fine on the server I am trying to access.
When you try to telnet to a MySQL server and do make a connection, what you usually what you get is a sudden blank screen or the version of the server software.
Right, I can get that when I try and connect with one of my other servers.
Port 3306 is the 'standard' port but it can be set up to use a different port.
As far as I can tell, port 3306 is being used by MySQL on the server in question.
@arober11: Yes, I forgot to mention that I created a user on the server in question using the specific IP address for the server that is trying to connect.
@Mazdajai: Forgive my ignorance, but if iptables had stopped running, shouldn't I be able to get through? But, on your suggestion, I did check and make sure iptables was running. It is.
@DaveBaldwin:
That's not a MySQL problem unless the server isn't running.
Yes, I realize that now. MySQL is running fine on the server I am trying to access.
When you try to telnet to a MySQL server and do make a connection, what you usually what you get is a sudden blank screen or the version of the server software.
Right, I can get that when I try and connect with one of my other servers.
Port 3306 is the 'standard' port but it can be set up to use a different port.
As far as I can tell, port 3306 is being used by MySQL on the server in question.
@arober11: Yes, I forgot to mention that I created a user on the server in question using the specific IP address for the server that is trying to connect.
@Mazdajai: Forgive my ignorance, but if iptables had stopped running, shouldn't I be able to get through? But, on your suggestion, I did check and make sure iptables was running. It is.
If you can get the connection on other computers on the same network (?), then your problem is only on that computer. Can you telnet from that computer to anything else? Try making an exception for 'telnet' in your firewall settings on that computer. Or for port 3306 both in and out if you're using telnet from Linux.
Also, making a user in MySQL doesn't help with the telnet connection test. Telnet does not use the MySQL protocol and will not be able to send the correct info to login. The most you will be able to see with telnet is the server name and version which is the only thing sent in the clear.
Also, making a user in MySQL doesn't help with the telnet connection test. Telnet does not use the MySQL protocol and will not be able to send the correct info to login. The most you will be able to see with telnet is the server name and version which is the only thing sent in the clear.
>>if iptables had stopped running, shouldn't I be able to get through? But, on your suggestion, I did check and make sure iptables was running. It is.
Do you mean iptables is running? Can you try to connect with iptables shut off?
Do you mean iptables is running? Can you try to connect with iptables shut off?
service iptables stopASKER
@DaveBaldwin: Thanks for your reply.
I am logging into remote servers using SSH. All our servers are set up as Linux/Apache/Centos systems. When I log into the server that needs access to the remote database using root access and do a telnet from there, I cannot connect. Thanks to your question, I also tried telnetting directly from my computer to that server and could not connect either.
This would normally make me suspect iptables on the server housing the database, but as I stated, I have already opened up the port for both sending and receiving. I can ping the server just fine using the same IP address.
I am logging into remote servers using SSH. All our servers are set up as Linux/Apache/Centos systems. When I log into the server that needs access to the remote database using root access and do a telnet from there, I cannot connect. Thanks to your question, I also tried telnetting directly from my computer to that server and could not connect either.
This would normally make me suspect iptables on the server housing the database, but as I stated, I have already opened up the port for both sending and receiving. I can ping the server just fine using the same IP address.
Two things... you also need to allow firewall access on port 3306 on the machine that is running telnet (in addition to the server with MySQL). Ping tells you that the lowest level connection to that server is working. Is any other service like SSH, FTP, or HTTP working on the machine with MySQL?
And you should not be allowing the MySQL 'root' user to be accessed remotely at any time. That doesn't have anything to do with the telnet connection problem. But 'root' in MySQL is never setup for remote access and should Not be allowed remote access. That would be a serious security problem because 'root' is allowed to do anything and everything.
And you should not be allowing the MySQL 'root' user to be accessed remotely at any time. That doesn't have anything to do with the telnet connection problem. But 'root' in MySQL is never setup for remote access and should Not be allowed remote access. That would be a serious security problem because 'root' is allowed to do anything and everything.
Stopping iptables temporary is to rule it out of the problem.
If you haven't to do so, and post the output -
If you haven't to do so, and post the output -
netstat -ant ASKER
OK, I opened up port 3306 on the remote server (the server I run telnet from) so that both the MySQL and telnet servers are open. The MySQL server is accessible via SSH, FTP and HTTP.
I was not referring to MySQL root permission, but server root permission when I said I access the MySQL server.
I still am unable to telnet. Any other ideas?
I was not referring to MySQL root permission, but server root permission when I said I access the MySQL server.
I still am unable to telnet. Any other ideas?
Have you successfully used telnet on the remote server to connect to anything else on any port? Can you telnet to the telnet server on the MySQL machine and login? Just trying to figure out if telnet is running properly.
Are they on the same network segment? I would guess so if you can ping.
Are they on the same network segment? I would guess so if you can ping.
ASKER
Have you successfully used telnet on the remote server to connect to anything else on any port?
No, I haven't. Do you have any suggestions for tests?
Can you telnet to the telnet server on the MySQL machine and login?
Yes, telnet is successful going from the MySQL server to the telnet server on port 3306.
Are they on the same network segment?
Yes.
No, I haven't. Do you have any suggestions for tests?
Can you telnet to the telnet server on the MySQL machine and login?
Yes, telnet is successful going from the MySQL server to the telnet server on port 3306.
Are they on the same network segment?
Yes.
Yes, telnet is successful going from the MySQL server to the telnet server on port 3306.Ok, I'm confused. What is Not working?
ASKER
The other way around is not working. I cannot telnet from the telnet server to the MySQL server on port 3306. I don't know about other ports.
I thought you said that you could see the server name and version in telnet when you tried to make that connection. That is all you will ever be able to do with telnet and MySQL. You can Not use telnet to login to MySQL. MySQL has it's own secure protocol that must be used from a remote client.
You can use telnet or SSH to login to the machine that is running MySQL and use the 'mysql' command line client. But you can Not make a direct connection to MySQL without using a MySQL client driver.
You can use telnet or SSH to login to the machine that is running MySQL and use the 'mysql' command line client. But you can Not make a direct connection to MySQL without using a MySQL client driver.
ASKER
Dave, really? Can you show me my comment where I said that? I get a "Cannot connect to the server" error when I try and connect. There is no server name and version showing up. If it did, I would be really happy, you would have your points, and the question would be closed.
The server is properly set up with the necessary MySQL drivers. Other websites housed on the same server have no problem connecting to their respective databases.
The server is properly set up with the necessary MySQL drivers. Other websites housed on the same server have no problem connecting to their respective databases.
I thought you said that at https://www.experts-exchange.com/questions/28282793/Remote-MySQL-Access.html?anchorAnswerId=39616655#a39616655 above.
Let's create identifiers. W1 will be the workstation you are using. R1 will be the remote server that you are connecting to with SSH. M1 is the server that is running MySQL. As I understand it, when you connect with SSH from W1 to R1, you can not connect from R1 to M1 with telnet on port 3306.
Where are the websites you are talking about and where are their databases? Can you SSH into any of the machines and make a connection with telnet on port 3306 to any MySQL server? If so, which machines?
The server is properly set up with the necessary MySQL drivers. Other websites housed on the same server have no problem connecting to their respective databases.I find that a little confusing in two ways. What is currently working with MySQL on that machine?
Let's create identifiers. W1 will be the workstation you are using. R1 will be the remote server that you are connecting to with SSH. M1 is the server that is running MySQL. As I understand it, when you connect with SSH from W1 to R1, you can not connect from R1 to M1 with telnet on port 3306.
Where are the websites you are talking about and where are their databases? Can you SSH into any of the machines and make a connection with telnet on port 3306 to any MySQL server? If so, which machines?
I've found one thing that could cause the problem you are seeing but still allow the MySQL server to work but only on that machine without remote connections. The default 'bind-address' for MySQL is 0.0.0.0 which allows connections from any IP address to be used. However, sometimes people put in 127.0.0.1 which is 'localhost' for IPV4. That allows Only local connections and no remote connections.
See 'bind-address' on this page.
http://dev.mysql.com/doc/refman/5.1/en/server-options.html
See 'bind-address' on this page.
http://dev.mysql.com/doc/refman/5.1/en/server-options.html
ASKER
Absolutely Dave. Smart move using the identifiers. Let me see if I can be more clear.
M1 houses several database driven websites and houses all the MySQL databases for all our servers (we have 3) The other two servers (R1 being one of them) also house database driven websites, however no other server houses MySQL databases even though MySQL is installed on all servers (which kind of contradicts what I just said since the fact that MySQL is installed and operating necessitates the fact that there is a mysql database. My point is, there are no databases for any of the websites on any other machine other than M1)
Now as far as your understanding of my problem is concerned, you are correct. I can SSH from my maching W1 to either M1 and R1 with no problem. I can SSH from W1 to M1 and successfully telnet R1's port 3306. I cannot SSH from W1 to R1 and successfully telnet M1's port 3306 (once again, as you correctly surmised.)
M1 houses several database driven websites and houses all the MySQL databases for all our servers (we have 3) The other two servers (R1 being one of them) also house database driven websites, however no other server houses MySQL databases even though MySQL is installed on all servers (which kind of contradicts what I just said since the fact that MySQL is installed and operating necessitates the fact that there is a mysql database. My point is, there are no databases for any of the websites on any other machine other than M1)
Now as far as your understanding of my problem is concerned, you are correct. I can SSH from my maching W1 to either M1 and R1 with no problem. I can SSH from W1 to M1 and successfully telnet R1's port 3306. I cannot SSH from W1 to R1 and successfully telnet M1's port 3306 (once again, as you correctly surmised.)
Two questions then. How are the websites on M1 accessing MySQL? Are they using 'localhost' for the connection?
And how are the websites on R1 connecting to the databases on M1? They can't use 'localhost' to connect to a remote server on M1. You should be able to use the same address and port as the R1 web sites are using when you try to connect with telnet.
And how are the websites on R1 connecting to the databases on M1? They can't use 'localhost' to connect to a remote server on M1. You should be able to use the same address and port as the R1 web sites are using when you try to connect with telnet.
ASKER
How are the websites on M1 accessing MySQL? Are they using 'localhost' for the connection?
Yes.
And how are the websites on R1 connecting to the databases on M1?
Well, the short answer is, they are not. That is what I am trying to achieve and the purpose of my asking this question.
I have done what I have done in the past in order to set up remote database access on separate servers, but nothing I have tried to this point has helped me in this situation. This includes:
1. Setting up a new user based on the R1 IP address.
2. Opening ports in iptables.
Yes.
And how are the websites on R1 connecting to the databases on M1?
Well, the short answer is, they are not. That is what I am trying to achieve and the purpose of my asking this question.
I have done what I have done in the past in order to set up remote database access on separate servers, but nothing I have tried to this point has helped me in this situation. This includes:
1. Setting up a new user based on the R1 IP address.
2. Opening ports in iptables.
See my comment above: https://www.experts-exchange.com/questions/28282793/Remote-MySQL-Access.html?anchorAnswerId=39618101#a39618101 If the 'bind-address' is set to 127.0.0.1, then 'localhost' works but nothing else will.
ASKER
No, this server has something going on with it. I cannot set a bind-address. It messes up my JIRA.
That doesn't make any sense. 'bind-address' is set in 'my.conf' or 'my.cf' and applies Only to MySQL. Please read this page: http://dev.mysql.com/doc/refman/5.1/en/server-options.html
ASKER
Yes, JIRA runs on MySQL. In other words, I can't bind anything other than 0.0.0.0. Also remember that all this has nothing to do with the telnet issue I'm facing.
Actually it probably IS the issue you are facing. You're trying to connect on port 3306 from R1 to M1. Port 3306 on M1 'belongs' to MySQL unless you' changed it.
What is the JIRA that you are running? All I saw was something for Apache.
What is the JIRA that you are running? All I saw was something for Apache.
This documentation says nothing about the 'bind-address': https://confluence.atlassian.com/display/JIRA/Connecting+JIRA+to+MySQL It only has the usual precautions about creating the 'connection' which applies to all user creations. ??
ASKER
Actually it probably IS the issue you are facing.
Really? How can we prove this?
What is the JIRA that you are running?
JIRA v6.0.8
This documentation says nothing about the 'bind-address'
It is true. We found out there were problems when we tried to set up the bind-address to bind to the R1 server.
Really? How can we prove this?
What is the JIRA that you are running?
JIRA v6.0.8
This documentation says nothing about the 'bind-address'
It is true. We found out there were problems when we tried to set up the bind-address to bind to the R1 server.
The bind-address in MySQL is for it to know what IP address it is listening to. It can't listen to an IP address that does not belong to an interface on the machine it is running on. And 0.0.0.0 means listen on all interfaces... on this machine. Which is why it couldn't work when you tried to use the R1 address. Because it's not an interface that is available on M1.
That is completely different than the 'host' specified in a user set up. The 'host' in a user set up is the address or host that that user is allowed to connect From. It always connects To the 'bind-address'.
As far as I could tell in the JIRA docs, it is set up with a standard user and connection to it's own database. What machine is JIRA running on and what is the connection specified for it's user?
That is completely different than the 'host' specified in a user set up. The 'host' in a user set up is the address or host that that user is allowed to connect From. It always connects To the 'bind-address'.
As far as I could tell in the JIRA docs, it is set up with a standard user and connection to it's own database. What machine is JIRA running on and what is the connection specified for it's user?
Note: from this page http://dev.mysql.com/doc/refman/5.0/en/connecting.html
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.I believe what that page is telling you is that connecting thru 'localhost' bypasses the TCP/IP stack so even the 'bind-address' is ignored. But you can only do that, of course, on the machine that you are on. Because that is always where 'localhost' is.
ASKER
And 0.0.0.0 means listen on all interfaces... on this machine. Which is why it couldn't work when you tried to use the R1 address. Because it's not an interface that is available on M1.
Ah, ok. To tell the truth, I just did what some other websites showed about setting up a remote connection to a database. How about this, can you walk me through the steps to set up a remote connection from R1 to M1 and see if we run into any issues there?
That is completely different than the 'host' specified in a user set up. The 'host' in a user set up is the address or host that that user is allowed to connect From. It always connects To the 'bind-address'.
Well, yeah, but since M1 runs its own websites of its database, shouldn't the host be localhost?
What machine is JIRA running on and what is the connection specified for it's user?
JIRA is running on M1 using the standard connections indicated by its installation.
I believe what that page is telling you is that connecting thru 'localhost' bypasses the TCP/IP stack so even the 'bind-address' is ignored. But you can only do that, of course, on the machine that you are on. Because that is always where 'localhost' is.
That sounds about right, but that still doesn't provide a solution to my problem.
Ah, ok. To tell the truth, I just did what some other websites showed about setting up a remote connection to a database. How about this, can you walk me through the steps to set up a remote connection from R1 to M1 and see if we run into any issues there?
That is completely different than the 'host' specified in a user set up. The 'host' in a user set up is the address or host that that user is allowed to connect From. It always connects To the 'bind-address'.
Well, yeah, but since M1 runs its own websites of its database, shouldn't the host be localhost?
What machine is JIRA running on and what is the connection specified for it's user?
JIRA is running on M1 using the standard connections indicated by its installation.
I believe what that page is telling you is that connecting thru 'localhost' bypasses the TCP/IP stack so even the 'bind-address' is ignored. But you can only do that, of course, on the machine that you are on. Because that is always where 'localhost' is.
That sounds about right, but that still doesn't provide a solution to my problem.
First, the user connection and the bind-address are two completely different but related things. The user connection is the machine where the user is and the bind-address is where the server is. And 'localhost' is on the machine where you are.
'localhost' on R1 is ON R1 and 'localhost' on M1 is ON M1. Connections coming from R1 must use the hostname or IP address of M1 for the 'host' in the MySQL connection. If you try to use 'localhost' on R1, it will be trying to connect to the database ON R1, not on M1.
So we actually have 3 things to deal with. One is the bind-address that the MySQL daemon listens to. 0.0.0.0 tells it to listen on all interfaces on the machine.
Second is the connection that a MySQL user is allowed to connect from. You can specify 'localhost', '%' (anyhost), or a specific host like 10.10.10.10. If the users on R1 are setup to only use 'localhost', they will never be able to connect to M1 because 'localhost' is only on the local machine and R1 is not the local machine for the MySQL server on M1.
Third is the 'host' specified in a connection statement/request on the client. If the client is running on the same machine (and is not blocked by the bind-address), they can use 'localhost' like your sites on M1 are probably doing. If they are on a different machine, they must use the hostname or IP address of M1 in their connection 'host'. And their user must be allowed to connect to M1 from either anyhost '%' or the IP address of R1.
'localhost' on R1 is ON R1 and 'localhost' on M1 is ON M1. Connections coming from R1 must use the hostname or IP address of M1 for the 'host' in the MySQL connection. If you try to use 'localhost' on R1, it will be trying to connect to the database ON R1, not on M1.
So we actually have 3 things to deal with. One is the bind-address that the MySQL daemon listens to. 0.0.0.0 tells it to listen on all interfaces on the machine.
Second is the connection that a MySQL user is allowed to connect from. You can specify 'localhost', '%' (anyhost), or a specific host like 10.10.10.10. If the users on R1 are setup to only use 'localhost', they will never be able to connect to M1 because 'localhost' is only on the local machine and R1 is not the local machine for the MySQL server on M1.
Third is the 'host' specified in a connection statement/request on the client. If the client is running on the same machine (and is not blocked by the bind-address), they can use 'localhost' like your sites on M1 are probably doing. If they are on a different machine, they must use the hostname or IP address of M1 in their connection 'host'. And their user must be allowed to connect to M1 from either anyhost '%' or the IP address of R1.
ASKER
Ok, to answer your respective comments:
First, what happens if bind-address is not set?
Second, ok, so if I am going to set up a user to access M1 from R1, where should I set up the user, and what should be the hosts that I use?
Third, are you saying I need to set up two users, one on M1 and one on R1?
First, what happens if bind-address is not set?
Second, ok, so if I am going to set up a user to access M1 from R1, where should I set up the user, and what should be the hosts that I use?
Third, are you saying I need to set up two users, one on M1 and one on R1?
First... The default bind-address is 0.0.0.0. That's the value if you have done nothing to change it. I can't find a way to check it beside reading the 'my.conf' file.
Second... The username, password, and connection are set up on the server itself which in this case is M1.
Third... if the user needs to connect from both M1 and R1, then yes, you need to create two different users. But the only difference can be the connection. Username and password can be the same and in fact, that is what I usually do. One user for 'localhost' and one for '%' anyhost.
http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
Second... The username, password, and connection are set up on the server itself which in this case is M1.
Third... if the user needs to connect from both M1 and R1, then yes, you need to create two different users. But the only difference can be the connection. Username and password can be the same and in fact, that is what I usually do. One user for 'localhost' and one for '%' anyhost.
http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
ASKER
First, I've done nothing to change the bind-address (the entry does not exist in my.cnf) so by your comments we can assume it to be 0.0.0.0.
Second, the username, password and connection are set up on M1
Third, are you saying two users on M1, or one on M1 and one on R1?
Second, the username, password and connection are set up on M1
Third, are you saying two users on M1, or one on M1 and one on R1?
The users with name, password, and connection have to be created on the server M1 and used from the client, 'localhost' from M1 and a remote host from R1. If you have a user with a 'localhost' connection on M1, a second one with either '%' for anyhost or the IP address of R1 must be created.
'localhost' can only be used to connect to the computer you are on, it can't connect to another computer. A website on M1 can use 'localhost' to connect to the MySQL server on M1. A website on R1 can Not connect to the MySQL server on M1 by using 'localhost'. You must have user that has permissions to connect from either '%' anyhost or the IP address of R1. So you need to have two separate users to connect from both computers. They can have the same username and password but they have to have different connections, 'localhost' and '%' anyhost.
'localhost' can only be used to connect to the computer you are on, it can't connect to another computer. A website on M1 can use 'localhost' to connect to the MySQL server on M1. A website on R1 can Not connect to the MySQL server on M1 by using 'localhost'. You must have user that has permissions to connect from either '%' anyhost or the IP address of R1. So you need to have two separate users to connect from both computers. They can have the same username and password but they have to have different connections, 'localhost' and '%' anyhost.
ASKER
Ok, just to make sure I am understanding you correctly, I create two users on M1, one with localhost and the other with %. I don't have to create any users on R1, is that right?
Additionally, I don't want justa any host to be able to hook up to this database, so instead of % I should use the IP of R1, isn't that right?
Additionally, I don't want justa any host to be able to hook up to this database, so instead of % I should use the IP of R1, isn't that right?
Yes to both of those. You might want to try '%' (anyhost) first to make sure R1 can connect and then change it to R1's IP address. But you have the right idea.
ASKER
Thanks for clarifying that.
Just to be sure, I went ahead and dropped all the users for the database on M1 and set up just the two users as you suggested (both localhost and %.) I then tried to telnet from R1 to M1 and got a telnet: connect to address xx.xx.xx.xx: Connection timed out error (ip has been blocked for security purposes.) I tried to connect via mysql and got a ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (110) error. Both of the users I set up have all privileges including GRANT OPTION.
Just to be sure, I went ahead and dropped all the users for the database on M1 and set up just the two users as you suggested (both localhost and %.) I then tried to telnet from R1 to M1 and got a telnet: connect to address xx.xx.xx.xx: Connection timed out error (ip has been blocked for security purposes.) I tried to connect via mysql and got a ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (110) error. Both of the users I set up have all privileges including GRANT OPTION.
(ip has been blocked for security purposes.)Has that ever come up before? That's not a telnet or MySQL error message. Where is that coming from?
ASKER
Sorry, that is my comment to you to tell you that the message gave the actual IP address, not xx.xx.xx.xx.
I'm lost then. I have 8 computers here running MySQL, two Linux, two Macs, and four Windows computers and I don't have these problems. I can connect to MySQL From any of them To any of them using the methods that I have described. I also have MySQL running on six+ Linux and Windows web hosting sites and I have no problems making remote connections to them.
So something is going on with your situation that I am unable figure out. It should have been very simple but it's not working the way that all of my MySQL installations are working.
So something is going on with your situation that I am unable figure out. It should have been very simple but it's not working the way that all of my MySQL installations are working.
ASKER
Yeah, that's where I came in. It's ok Dave, you gave it your best shot. I'm putting out a request for attention to see if anyone else has any ideas.
Thanks for the great effort.
Thanks for the great effort.
Not sure how much more I can add to what Dave's said but I'll try. I apologize in advance if I end up covering some of the same ground. I'd like to look at the telnet issue first.
-- Can you telnet on the MySQL server to localhost:3306 ie telnet localhost 3306?
-- What are the results of using a web hosted portscan of your IP on port 3306? such as http://www.t1shopper.com/tools/port-scan/
eg in "scan this list of port numbers" enter 3306. I get the following results (i'm NOT running MySQL nor do I have the port open)
Scanning ports on 101.xxx.xx.xxx
101.xxx.xx.xxx isn't responding on port 3306 (mysql).
-- Can you telnet on the MySQL server to localhost:3306 ie telnet localhost 3306?
-- What are the results of using a web hosted portscan of your IP on port 3306? such as http://www.t1shopper.com/tools/port-scan/
eg in "scan this list of port numbers" enter 3306. I get the following results (i'm NOT running MySQL nor do I have the port open)
Scanning ports on 101.xxx.xx.xxx
101.xxx.xx.xxx isn't responding on port 3306 (mysql).
ASKER
@tagit: Thank you. Any assistance you can give would be appreciated.
Can you telnet on the MySQL server to localhost:3306 ie telnet localhost 3306?
No, no I can not (strange I never tried this.) It gives me the same telnet: connect to address xx.xx.xx.xx: Connection timed out error as it did when I tried from R1 to M1 (see Dave's server designations in comment ID: 39618090 above.) I can connect to mysql locally, but I cannot telnet.
What are the results of using a web hosted portscan of your IP on port 3306? such as http://www.t1shopper.com/tools/port-scan/
t1shopper.com's tool says the same as you got, "Scanning ports on xx.xx.xx.xx
xx.xx.xx.xx isn't responding on port 3306 (mysql)."
Can you telnet on the MySQL server to localhost:3306 ie telnet localhost 3306?
No, no I can not (strange I never tried this.) It gives me the same telnet: connect to address xx.xx.xx.xx: Connection timed out error as it did when I tried from R1 to M1 (see Dave's server designations in comment ID: 39618090 above.) I can connect to mysql locally, but I cannot telnet.
What are the results of using a web hosted portscan of your IP on port 3306? such as http://www.t1shopper.com/tools/port-scan/
t1shopper.com's tool says the same as you got, "Scanning ports on xx.xx.xx.xx
xx.xx.xx.xx isn't responding on port 3306 (mysql)."
ok that's some interesting results...
Certainly looks like MySQL isn't listening on 3306.
1) Can you post your my.cnf and confirm that it's actually loading that config file (ie when the server is started what command line and options are used)?
2) You said that you can connect to your MySQL locally. Is that just using the mysql command line tool?
Lets come back to the port scan later....
Certainly looks like MySQL isn't listening on 3306.
1) Can you post your my.cnf and confirm that it's actually loading that config file (ie when the server is started what command line and options are used)?
2) You said that you can connect to your MySQL locally. Is that just using the mysql command line tool?
Lets come back to the port scan later....
I would also confirm that something is listening on port 3306 via the netstat as has been suggested above but I can't see if you've done that or not http:#a39617919
ASKER
Can you post your my.cnf and confirm that it's actually loading that config file (ie when the server is started what command line and options are used)?
Here is my.cnf:
But I have no idea as to how to confirm it is loading or not. A ps aux | grep mysql does not reveal any .cnf files in its list of parameters.
You said that you can connect to your MySQL locally. Is that just using the mysql command line tool?
I have no problems connecting to the database either that way or via the websites that are housed on that server.
I would also confirm that something is listening on port 3306 via the netstat as has been suggested
I'm afraid the netstat command gives a whole ton of ip addresses on this server which I would need to block out due to my work agreement. If such a list (with nothing but xx.xx.xx.xx would be helpful to you, I certainly could do that.
Here is my.cnf:
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
set-variable=local-infile=0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeoutBut I have no idea as to how to confirm it is loading or not. A ps aux | grep mysql does not reveal any .cnf files in its list of parameters.
You said that you can connect to your MySQL locally. Is that just using the mysql command line tool?
I have no problems connecting to the database either that way or via the websites that are housed on that server.
I would also confirm that something is listening on port 3306 via the netstat as has been suggested
I'm afraid the netstat command gives a whole ton of ip addresses on this server which I would need to block out due to my work agreement. If such a list (with nothing but xx.xx.xx.xx would be helpful to you, I certainly could do that.
Ok I'll have to assume from here that the fact your ps aux didn't bring up anything with the mysql command line, it's loading the default my.cnf.
Please follow the directions here to try and connect to the mysql server locally but imitating a remote connection. So on the mysql server, use the mysql -h xx.xx.xx.xx, where xx.xx.xx.xx is the ip address of the server (don't use 127.0.0.1).
http://dev.mysql.com/doc/refman/5.0/en/connecting.html
All I was looking for with the netstat is whether you can see if the server is listening on port 3306 (use netstat -ant | grep 3306). If it is, you should see something similar to:
0.0.0.0:3306 0.0.0.0 LISTENING
XX.XX.XX.XX:3306 XX.XX.XX.XX LISTENING
Please follow the directions here to try and connect to the mysql server locally but imitating a remote connection. So on the mysql server, use the mysql -h xx.xx.xx.xx, where xx.xx.xx.xx is the ip address of the server (don't use 127.0.0.1).
http://dev.mysql.com/doc/refman/5.0/en/connecting.html
All I was looking for with the netstat is whether you can see if the server is listening on port 3306 (use netstat -ant | grep 3306). If it is, you should see something similar to:
0.0.0.0:3306 0.0.0.0 LISTENING
XX.XX.XX.XX:3306 XX.XX.XX.XX LISTENING
Just another thought, did
Telnet localhost 3306
Telnet 127.0.0.1 3306
Both time out?
If so, it's quite possible that there are security restrictions in place for telnet in which case I would suggest turning off iptables as mazdajai suggested above. http:#a39617702 I again couldn't see if you'd tried that.
Telnet localhost 3306
Telnet 127.0.0.1 3306
Both time out?
If so, it's quite possible that there are security restrictions in place for telnet in which case I would suggest turning off iptables as mazdajai suggested above. http:#a39617702 I again couldn't see if you'd tried that.
ASKER
Do you want me to log in to mysql as any user, or as root?
Here are the results of the netstat command:
The actual ip's have been blocked out.
Just another thought, did
Telnet localhost 3306
Telnet 127.0.0.1 3306
Both time out?
Surprisingly, no. Telnet 127.0.0.1 3306 connected just fine, so I suppose turning off iptables will not be necessary, which is very good because this is a high traffic server that is under constant attack. I would be very wary of dropping its defenses even for a second.
Here are the results of the netstat command:
# netstat -ant | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 0 0 xx.xx.xx.xx:3306 xx.xx.xx.xx:37500 ESTABLISHED
tcp 0 0 xx.xx.xx.xx:3306 xx.xx.xx.xx:37499 ESTABLISHED
tcp 0 0 xx.xx.xx.xx:37500 xx.xx.xx.xx:3306 ESTABLISHED
tcp 0 0 xx.xx.xx.xx:37499 xx.xx.xx.xx:3306 ESTABLISHED
tcp 0 0 xx.xx.xx.xx:3306 xx.xx.xx.xx:37338 TIME_WAIT
#The actual ip's have been blocked out.
Just another thought, did
Telnet localhost 3306
Telnet 127.0.0.1 3306
Both time out?
Surprisingly, no. Telnet 127.0.0.1 3306 connected just fine, so I suppose turning off iptables will not be necessary, which is very good because this is a high traffic server that is under constant attack. I would be very wary of dropping its defenses even for a second.
Ok the mysql server is only accepting connections via localhost. Would you agree from what we've tested?
It's now about getting mysql to listen on the servers ip
It's now about getting mysql to listen on the servers ip
ASKER
Yes, but unfortunately, it is very late where I live, and I have to get up early in the morning. Thank you so much for your help, I hope we can continue tomorrow.
I'll be here or sleeping :-)
Ok the mysql server is only accepting connections via localhost.No, it's accepting them from 127.0.0.1. The MySQL docs are quite clear that MySQL considers those as two completely separate sources as stated in the link you posted above. On some machines, 'localhost' can be an IPV6 address '::1'. http://dev.mysql.com/doc/refman/5.5/en/ipv6-support.html
Thanks for clarifying Dave. My point was mysql wasn't accepting on the public ip but it's certainly a good point you bring up.
I'd like you try connecting to the ip from the MySQL server
Eg
Please follow the directions here to try and connect to the mysql server locally but imitating a remote connection. So on the mysql server, use the mysql -h xx.xx.xx.xx, where xx.xx.xx.xx is the ip address of the server (don't use 127.0.0.1).
http://dev.mysql.com/doc/refman/5.0/en/connecting.html
Eg
Please follow the directions here to try and connect to the mysql server locally but imitating a remote connection. So on the mysql server, use the mysql -h xx.xx.xx.xx, where xx.xx.xx.xx is the ip address of the server (don't use 127.0.0.1).
http://dev.mysql.com/doc/refman/5.0/en/connecting.html
ASKER
Good Morning,
Sorry for the break in communications. Let me know when you are available to resume. On the mysql server (M1) I have no problem connecting through mysql using the IP address of the server.
Sorry for the break in communications. Let me know when you are available to resume. On the mysql server (M1) I have no problem connecting through mysql using the IP address of the server.
Good Morning,
That tells me that mysql is accepting connections on the public IP so this will be a firewall problem.
Is R1 on the same network as M1?
That tells me that mysql is accepting connections on the public IP so this will be a firewall problem.
Is R1 on the same network as M1?
ASKER
Is R1 on the same network as M1?
Not sure. Is there a way I can tell?
Not sure. Is there a way I can tell?
That's ok, i wasreally just trying to ascertain, one step at a time, that this is a firewall issue.
So the goal here is to connect from R1 to M1. It now means you need to modify the iptables on both servers so that outbound connections from R1 are allowed and incoming on M1 are allowed.
What have you done so far?
So the goal here is to connect from R1 to M1. It now means you need to modify the iptables on both servers so that outbound connections from R1 are allowed and incoming on M1 are allowed.
What have you done so far?
You did try the following from R1:
Mysql -h M1
Where M1 is the ip of the mysql server.
Forget telnet at this stage as it could be restricted with selinux
Mysql -h M1
Where M1 is the ip of the mysql server.
Forget telnet at this stage as it could be restricted with selinux
ASKER
I used what I found on the Internet to modify iptables to allow for access through port 3306 from this writeup: http://www.cyberciti.biz/t
Did you also allow outgoing on R1?
Task: Allow outgoing MySQL request on TCP port 3306
ASKER
You did try the following from R1:
Mysql -h M1
Where M1 is the ip of the mysql server.
Yes, and ended up with an "ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (110)" error (see comment ID: 39625335 above.)
Did you also allow outgoing on R1?
Gosh, you know it's been so long now, I've forgotten. Is there any way I can tell, or should I just follow the steps again?
Mysql -h M1
Where M1 is the ip of the mysql server.
Yes, and ended up with an "ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (110)" error (see comment ID: 39625335 above.)
Did you also allow outgoing on R1?
Gosh, you know it's been so long now, I've forgotten. Is there any way I can tell, or should I just follow the steps again?
No harm to just follow steps again.
Though we need to determine if this is a firewall issue on just m1 and/or on r1.
what about testing from your personal computer to m1? Ideally using mysql but telnet if you don't have it instead.
Though we need to determine if this is a firewall issue on just m1 and/or on r1.
what about testing from your personal computer to m1? Ideally using mysql but telnet if you don't have it instead.
ASKER
I'm pretty certain we've determined the issue to be on M1. Just for laughs, since you said there was no harm in running the commands again, I set up and ran all four commands from that tutorial (not including the ones which limit to a specific ip) on both R1 and M1 and ran telnet and mysql tests. There was no change.
I don't have mysql on my personal computer, so the mysql command is unavailable. When I try to telnet to M1, I get a "Could not open connection to the host, on port 3306: Connect failed" error. (FYI, I can, however, connect just fine to R1, port 3306.)
I don't have mysql on my personal computer, so the mysql command is unavailable. When I try to telnet to M1, I get a "Could not open connection to the host, on port 3306: Connect failed" error. (FYI, I can, however, connect just fine to R1, port 3306.)
Bugger :)
Is there any other services such as FTP, http etc running on m1? If so can you telnet to them ?
You're right it is the issue certainly looks to be on m1 but the issue isn't MySQL but rather the firewall or selinux.
What have you noticed in the firewall log file? /var/log/firewall? Anything to indicate the connection was refused?
Is there any other services such as FTP, http etc running on m1? If so can you telnet to them ?
You're right it is the issue certainly looks to be on m1 but the issue isn't MySQL but rather the firewall or selinux.
What have you noticed in the firewall log file? /var/log/firewall? Anything to indicate the connection was refused?
ASKER
Is there any other services such as FTP, http etc running on m1? If so can you telnet to them ?
Sorry, I'm a newbee to servers. I mean I know I can list all the services by doing a service --status-all command, but I have no idea which of them I can telnet to.
Also, the /var/log/firewall file does not exist.
Sorry, I'm a newbee to servers. I mean I know I can list all the services by doing a service --status-all command, but I have no idea which of them I can telnet to.
Also, the /var/log/firewall file does not exist.
sorry my bad, it's in /var/log/messages. lets start there and not worry about the other services for now.
Try a connection while polling the log file with "tail -F /var/log/messages"
ASKER
Ok, tried to connect via telnet from my PC to M1 and polling the log file at /var/log/messages using the tail -f command. I polled the messages file both before and after the connection attempt. There was no change in the log (the log on M1.)
Ok and do you see anything in the /var/log/messages when you connect using mysql -h xx.xx.xx.xx on the M1 ?
ASKER
No, no change at all. =|
you also said you are using ssh to connect to the server so it is allowing tcp connections. Can you post the output (minus your public ip) of:
iptables --list
iptables --list
ASKER
Here you go. Website domains have been replaced by "example" and "othersite" and IP addresses by x's:
# iptables --list
Chain INPUT (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
ACCEPT tcp -- anywhere anywhere tcp dpts:60000:65000
ACCEPT tcp -- anywhere anywhere tcp dpt:pcsync-https
ACCEPT tcp -- anywhere anywhere tcp dpt:cddbp-alt
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
ACCEPT tcp -- anywhere anywhere tcp dpt:ftp
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:submission
ACCEPT tcp -- anywhere anywhere tcp dpt:25
ACCEPT tcp -- anywhere anywhere tcp dpt:smtps
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3s
ACCEPT tcp -- anywhere anywhere tcp dpt:imap
ACCEPT tcp -- anywhere anywhere tcp dpt:imaps
DROP tcp -- anywhere anywhere tcp dpt:poppassd
ACCEPT tcp -- mail.example.org anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- cpe-xx-xx-xx-xx.socal.res.rr.com anywhere tcp dpt:mysql
ACCEPT tcp -- xx.xx.xx.xx anywhere tcp dpt:mysql
ACCEPT tcp -- mail.example.com anywhere tcp dpt:mysql
DROP tcp -- anywhere anywhere tcp dpt:mysql
DROP tcp -- anywhere anywhere tcp dpt:postgres
DROP tcp -- anywhere anywhere tcp dpt:9008
DROP tcp -- anywhere anywhere tcp dpt:glrpc
DROP udp -- anywhere anywhere udp dpt:netbios-ns
DROP udp -- anywhere anywhere udp dpt:netbios-dgm
DROP tcp -- anywhere anywhere tcp dpt:netbios-ssn
DROP tcp -- anywhere anywhere tcp dpt:microsoft-ds
DROP udp -- anywhere anywhere udp dpt:openvpn
ACCEPT udp -- anywhere anywhere udp dpt:domain
ACCEPT tcp -- anywhere anywhere tcp dpt:domain
ACCEPT icmp -- anywhere anywhere icmp type 8 code 0
DROP all -- anywhere anywhere
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT tcp -- xx-xx-xx-xx.dedicated.codero.net mail.example.net tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- anywhere mail.example.net tcp spt:mysql dpts:1024:65535 state ESTABLISHED
ACCEPT tcp -- anywhere mail.example.net tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
Chain FORWARD (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
DROP all -- anywhere anywhere
Chain OUTPUT (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
ACCEPT all -- anywhere anywhere
ACCEPT tcp -- mail.example.net xx-xx-xx-xx.dedicated.codero.net tcp spt:mysql dpts:1024:65535 state ESTABLISHED
ACCEPT tcp -- xx-xx-xx-xx.dedicated.codero.net anywhere tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- mail.example.net anywhere tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- mail.example.net anywhere tcp spt:mysql dpts:1024:65535 state ESTABLISHED
#
Ok see line 23 thru 30, especially line 30:
ACCEPT tcp -- mail.example.org anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- cpe-xx-xx-xx-xx.socal.res.
ACCEPT tcp -- xx.xx.xx.xx anywhere tcp dpt:mysql
ACCEPT tcp -- mail.example.com anywhere tcp dpt:mysql
DROP tcp -- anywhere anywhere tcp dpt:mysql
You do have this on line 43 but it would never get there because it is listed in priority order:
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT tcp -- mail.example.org anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- othersite.com anywhere tcp dpt:mysql
ACCEPT tcp -- cpe-xx-xx-xx-xx.socal.res.
ACCEPT tcp -- xx.xx.xx.xx anywhere tcp dpt:mysql
ACCEPT tcp -- mail.example.com anywhere tcp dpt:mysql
DROP tcp -- anywhere anywhere tcp dpt:mysql
You do have this on line 43 but it would never get there because it is listed in priority order:
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
You have selective connection rules for mysql as opposed to ssh (and others):
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
ACCEPT tcp -- anywhere anywhere tcp dpt:ftp
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:submission
ACCEPT tcp -- anywhere anywhere tcp dpt:25
ACCEPT tcp -- anywhere anywhere tcp dpt:smtps
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3s
ACCEPT tcp -- anywhere anywhere tcp dpt:imap
ACCEPT tcp -- anywhere anywhere tcp dpt:imaps
create a backup and remove all the lines referring to mysql and replace with:
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
ACCEPT tcp -- anywhere anywhere tcp dpt:ftp
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:submission
ACCEPT tcp -- anywhere anywhere tcp dpt:25
ACCEPT tcp -- anywhere anywhere tcp dpt:smtps
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3s
ACCEPT tcp -- anywhere anywhere tcp dpt:imap
ACCEPT tcp -- anywhere anywhere tcp dpt:imaps
create a backup and remove all the lines referring to mysql and replace with:
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ASKER
Whoa, you saw how I had to go to the Internet just to find out how to open a port. Now you are talking about creating backups and removing lines... I know nothing about any of that. Is the iptables list located in a file somewhere I can edit? If that were the case, why did I have to execute those elaborate ACCEPT commands, etc?
Hey don't worry - I'll walk you through it. Just if we made a mistake I wanted you to have a copy of the iptables before we started :)
ASKER
Ok, I'm all ears. <"> How do I make a backup?
No it's not listed in a file you can access. You'll need to execute a few more elaborate commands:
If you still don't have it, get a copy of what you've posted above with the correct hostnames and ips for safe keeping.
Now follow the two steps below until all the mysql entries are gone. They are together from line 20 to line 27 (ignore the one on line 40, we want to keep this). As you delete row 20 each time, they'll move up.
this will delete row 20 in your INPUT chain
STEP 1) iptables -D INPUT 20
run this
STEP 2) iptables --list
If you still don't have it, get a copy of what you've posted above with the correct hostnames and ips for safe keeping.
Now follow the two steps below until all the mysql entries are gone. They are together from line 20 to line 27 (ignore the one on line 40, we want to keep this). As you delete row 20 each time, they'll move up.
this will delete row 20 in your INPUT chain
STEP 1) iptables -D INPUT 20
run this
STEP 2) iptables --list
The backup I meant was just a copy of the iptables before we start removing entries. one thing to add them, another to delete them.
iptables --list > iptables_backup.txt
iptables --list > iptables_backup.txt
As I mentioned at the second post, you should have looked at the iptables at the very beginning to troubleshoot connection issue.
stopping iptables and make a test connection should take you no more than five seconds.
If you are continue inclined to follow experts suggestions. It is likely to go nowhere.
stopping iptables and make a test connection should take you no more than five seconds.
If you are continue inclined to follow experts suggestions. It is likely to go nowhere.
ASKER
Hi tagit!
My sincere apologies for not getting back to you earlier. I had to leave the office to attend to some personal matters and couldn't respond to you until now.
I made the backup, modified iptables according to your suggestion and saved it. I then tried both a telnet and a mysql connection from R1 with no joy :(
Here is the modified iptables (again with domains changed to example and ip's to xx.xx.xx.xx):
Could line 34 - DROP all -- anywhere anywhere possibly be a problem?
NOTE: for anyone reading this, I found that adding the --line-numbers option to the iptables --list command was helpful.
My sincere apologies for not getting back to you earlier. I had to leave the office to attend to some personal matters and couldn't respond to you until now.
I made the backup, modified iptables according to your suggestion and saved it. I then tried both a telnet and a mysql connection from R1 with no joy :(
Here is the modified iptables (again with domains changed to example and ip's to xx.xx.xx.xx):
# iptables --list
Chain INPUT (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
ACCEPT tcp -- anywhere anywhere tcp dpts:60000:65000
ACCEPT tcp -- anywhere anywhere tcp dpt:pcsync-https
ACCEPT tcp -- anywhere anywhere tcp dpt:cddbp-alt
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
ACCEPT tcp -- anywhere anywhere tcp dpt:ftp
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:submission
ACCEPT tcp -- anywhere anywhere tcp dpt:25
ACCEPT tcp -- anywhere anywhere tcp dpt:smtps
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3
ACCEPT tcp -- anywhere anywhere tcp dpt:pop3s
ACCEPT tcp -- anywhere anywhere tcp dpt:imap
ACCEPT tcp -- anywhere anywhere tcp dpt:imaps
DROP tcp -- anywhere anywhere tcp dpt:poppassd
DROP tcp -- anywhere anywhere tcp dpt:postgres
DROP tcp -- anywhere anywhere tcp dpt:9008
DROP tcp -- anywhere anywhere tcp dpt:glrpc
DROP udp -- anywhere anywhere udp dpt:netbios-ns
DROP udp -- anywhere anywhere udp dpt:netbios-dgm
DROP tcp -- anywhere anywhere tcp dpt:netbios-ssn
DROP tcp -- anywhere anywhere tcp dpt:microsoft-ds
DROP udp -- anywhere anywhere udp dpt:openvpn
ACCEPT udp -- anywhere anywhere udp dpt:domain
ACCEPT tcp -- anywhere anywhere tcp dpt:domain
ACCEPT icmp -- anywhere anywhere icmp type 8 code 0
DROP all -- anywhere anywhere
ACCEPT tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT tcp -- xx-xx-xx-xx.dedicated.codero.net mail.example.net tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- anywhere mail.example.net tcp spt:mysql dpts:1024:65535 state ESTABLISHED
ACCEPT tcp -- anywhere mail.example.net tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
Chain FORWARD (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
DROP all -- anywhere anywhere
Chain OUTPUT (policy DROP)
target prot opt source destination
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
REJECT tcp -- anywhere anywhere tcp flags:!FIN,SYN,RST,ACK/SYN reject-with tcp-reset
DROP all -- anywhere anywhere state INVALID
ACCEPT all -- anywhere anywhere
ACCEPT all -- anywhere anywhere
ACCEPT tcp -- mail.example.net xx-xx-xx-xx.dedicated.codero.net tcp spt:mysql dpts:1024:65535 state ESTABLISHED
ACCEPT tcp -- xx-xx-xx-xx.dedicated.codero.net anywhere tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- mail.example.net anywhere tcp spts:1024:65535 dpt:mysql state NEW,ESTABLISHED
ACCEPT tcp -- mail.example.net anywhere tcp spt:mysql dpts:1024:65535 state ESTABLISHED
#Could line 34 - DROP all -- anywhere anywhere possibly be a problem?
NOTE: for anyone reading this, I found that adding the --line-numbers option to the iptables --list command was helpful.
Hi and no worries. I was flying yesterday anyway :-)
As for the DROP line just above, I would remove that too, however the better way would be to delete the mysql line below and insert it up with the other services (use the -I INPUT 10)
As for the DROP line just above, I would remove that too, however the better way would be to delete the mysql line below and insert it up with the other services (use the -I INPUT 10)
ASKER
Flying? Wow, now I'm jealous. ;)
As far as inserting the line up with the other services, um sorry, but can you take my hand and show me how to do that?
As far as inserting the line up with the other services, um sorry, but can you take my hand and show me how to do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tagit, you are a genius! With those changes I can both telnet and connect via mysql with no problem whatsoever. Thank you for helping me to figure out this issue. Someday we are going to have to talk about flying around the Australian countryside.
ASKER
Sigh! You all have heard of the old saying, "if it's one thing, then it's another?" Well, my joy bubble I got out of this resolved question just burst.
Any of you experts (and especially you tagit) who would like to participate to see if you can solve this issue with me are welcome.
The url is http://www.experts-exchang
Thanks!
Any of you experts (and especially you tagit) who would like to participate to see if you can solve this issue with me are welcome.
The url is http://www.experts-exchang
Thanks!
(** EDIT **: New question linked to above was deleted as the problem has resolved itself on its own accord )
Glad you got there though bugger about three mysql now dropping the connection while performing the query...
As for the points, thanks, but I do feel both the other experts in this thread deserve some credit as I leveraged off what they'd said. Especially Mazdajai who was on to the iptables issue from the start.
Good luck
Glad you got there though bugger about three mysql now dropping the connection while performing the query...
As for the points, thanks, but I do feel both the other experts in this thread deserve some credit as I leveraged off what they'd said. Especially Mazdajai who was on to the iptables issue from the start.
Good luck
When you try to telnet to a MySQL server and do make a connection, what you usually what you get is a sudden blank screen or the version of the server software. If you get that, you've gotten all that you can with telnet. MySQL does not use an ASCII protocol that you can do anything with.
Port 3306 is the 'standard' port but it can be set up to use a different port.