Mysql

I have a database on a machine with an ip address of 192.168.0.6.  I have another machine with an ip address of 192.168.0.11 which has a program on it that uses the database on the 192.168.0.6 machine.  I can ping from both machines to the other.  The user on the .0.11 machine is 'auction' and the password is 'auction'.  On the .0.6 machine I executed mysql as root and issued the command:

GRANT ALL ON auction6.* TO 'auction'@'192.168.0.11' identified by 'auction'.

When I execute the program on the .0.11 machine it gives me the following error:

MySQL error: Can't connect to MySQL server on '192.168.0.6' (110)
0                                                                

Any ideas anyone?   Thanks!
sargent240Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Seth SimmonsSr. Systems AdministratorCommented:
are you using iptables?
if so, you may need to create a rule to allow connectivity

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
service iptables save

Open in new window


then try connecting again
system error 110 is connection timeout
0
arnoldCommented:
Seth dealt with the connection issue.

After you ran the grant command, make sure you run flush privileges?

If your 192.168.0.11 is not a static IP (either configured as static or reserved within DHCP), when the IP changes, that system will no longer be able to access the 192.168.0.6 system's mysql instance.


To allow auction from anywhere use '%' for the host.
0
sargent240Author Commented:
I am not using iptables.  The two machines are set with static ips.  I was rather excited when you mentioned the flush privileges command as I remembered it from the past when you mentioned it arnold.  I did it and still have the same problem.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rajeev2353Commented:
hi,

you can try from this command

mysql -u root -p
mysql>GRANT ALL ON databasename.* TO databaseuser@'192.168.0.11'      IDENTIFIED BY 'databaseuserpassword;

mysql>FLUSH PRIVILEGES;
0
arnoldCommented:
can you from system on 192.168.0.11 connect to 192.168.0.6 port 3306?

on the mysql system, run netstat -an | find ":3306"

do you have a line item response?
if not, check /etc/my.cnf to see whether your instance of mysql is configured with a different TCP port on which it listens. Or you may have bound mysqld to the local interface (127.0.0.1) i.e. local connections only.

ps -ef | grep mysqld
once you see the PID, where ppid is not 1.
run lsof -p <PID from mysqld above>  | grep -i tcp
This will tell you the resources including the port on which mysqld is listening if at all.

if it is listening on a different port, you would need to account for this on your 192.168.0.11 setup or make an adjustment on the mysql server side.
0
Seth SimmonsSr. Systems AdministratorCommented:
i assume you mean grep instead of find on the netstat command :)
0
sargent240Author Commented:
rajeev2353  I have done exactly what you suggest earlier and no results but thanks.  arnold, I will do what you suggest. and report back.
0
sargent240Author Commented:
arnold, At the mysql prompt I enter

netstat -an | grep ":3306";

and the results were

tcp         0         0 0.0.0.0:3306         0.0.0.0:*         LISTEN
0
Dave BaldwinFixer of ProblemsCommented:
How are you trying to connect?  What language or connection string are you using?  

Why do you say you are not using 'iptables'?  A static IP has nothing to do with that.  Every distribution I know of installs 'iptables' automatically.  What distribution are you using?
0
sargent240Author Commented:
arnold, here is my results;

linux-66l0:/home/auction # ps -ef | grep mysqld
root      5505     1  0 08:12 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --mysqld=mysqld --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql
mysql     5543  5505  0 08:12 ?        00:00:22 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
root      7251  4144  0 19:02 pts/1    00:00:00 grep mysqld


I am now sure what ppid I should use in the lsof command.
0
arnoldCommented:
Seth,right. Locked in on the wrong environment.

The earlier netstat -an, points out that mysqld is listening on all interfaces port 3306
lsof  -p 5543

As Seth pointed out that the error is connection related, run iptables -L INPUT --line-numbers
That may point out/confirm that youhave iptables running

The only modification I would make to Seth's iptables
iptables -I INPUT 5 -p tcp -m tcp --dport 3306 -J ACCEPT

-A appends while -I inserts the rule (in the case the rule will be on the 5th line of the INPUT chain.
An alternative is to stop iptables.
0
sargent240Author Commented:
Everyone, It is just after midnight and I am going to have to turn in.  I will pick back up early in the AM.  Thanks you all very much and I will report my results later in the AM.
0
sargent240Author Commented:
WELL IT IS SAT AM AND I FINALLY AM ABLE TO GET BACK AT IT.  arnold, I AM ASSUMING YOU ARE SUGGESTING I RUN A COUPLE OF COMMANDS SO THE RESULTS ARE BELOW:

linux-66l0:/home/auction # lsof  -p 5543
lsof: WARNING: can't stat() fuse.gvfs-fuse-daemon file system /home/auction/.gvfs
      Output information may be incomplete.

linux-66l0:/home/auction # iptables -I INPUT 5 -p tcp -m tcp --dport 3306 -JACCEPT
iptables v1.4.2-rc1: Unknown arg `-J'
Try `iptables -h' or 'iptables --help' for more information.

I RAN SETHS COMMAND AND IT RETURNED A PROMPT.

linux-66l0:/home/auction # iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT
linux-66l0:/home/auction #

linux-66l0:/home/auction # service iptables save
service: no such service iptables

NOW WHEN I RUN MY PROGRAM THE ERROR MESSAGE IS:

MySQL error: Access denied for user 'auction'@'192.168.0.6' (using password:YES)

AND A DIALOG BOX COMES UP AND SAYS:

Could not initilize the database interface,
check that the mysql server is running
if the mysql server is running but is on a
seperate computer, check the physical connection

I CAN EXECUTE mysql ON BOTH MACHINES.  HOPE THIS HELP SHED A LITTLE LIGHT ON THE MATTER AND I DID THINGS RIGHT.  THANKS!!
0
sargent240Author Commented:
SORRY I MISSED ONE AND IT IS BELOW:

linux-66l0:/home/auction # iptables -L INPUT --line-numbers
Chain INPUT (policy DROP)
num  target     prot opt source               destination
1    ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
2    ACCEPT     all  --  anywhere             anywhere
3    ACCEPT     all  --  anywhere             anywhere            state ESTABLISHED
4    ACCEPT     icmp --  anywhere             anywhere            state RELATED
5    input_ext  all  --  anywhere             anywhere
6    input_ext  all  --  anywhere             anywhere
7    LOG        all  --  anywhere             anywhere            limit: avg3/min burst 5 LOG level warning tcp-options ip-options prefix `SFW2-IN-ILL-TARGET '
8    DROP       all  --  anywhere             anywhere
9    ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
10   ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
11   ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
12   ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
13   ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:mysql
linux-66l0:/home/auction #
0
arnoldCommented:
First, because of delays between your question/subsequent post and responses, things change such that running suggested commands should be done following the same process of the post.
In the case of lsof, you need to run ps -ef |grep mysql.
Using your posted data and my suggestion you need to look at the equivalent line you receive with the set of responses you posted.  then using the suggestion I had in that case was 5543 you need to select the equivalent data point from the current information.

In case of iptables, you need to first look at what you have, the error you received from running the command in my example deals with the -j ACCEPT being as -JACCEPT.


The error you get seems to be that you are running the test from the server on which mysql is running while you added the user with the hostname as 192.168.0.11.

username@192.168.0.11 is not the same as username@192.168.0.6
You were trying to make sure the username/password you were trying from the 192.168.0.11 is correct.

Please post the output of iptables -t nat -L --line-numbers, iptables -t filter -L --line-numbers

Are the two systems on the same network/within the same location?
0
arnoldCommented:
Note, you had an explicit drop in the INPUT tables (line number 8)
The suggestion Seth provided while valid, was adding the entry below the drop which means the drop directive was seen first and the packet was dropped.

Run the following command 5 times which will remove the lines below the drop entry.
iptables -R INPUT 9

Before making changes to iptables, always check the current settings first.
iptables -L INPUT --line-numbers

Only commit the iptables rules after you confirm that what you wanted to achieve is functional and is not affecting something else.  The use of iptables command is to dynamically update the firewall rules, such that if anything goes wrong,I.e. the wrong rule/wrong interpretation of the rule caused a problem a reboot of the system will revert the rules.(mainly deals with remote/lockout) I.e. a change broke your ssh session......
0
arnoldCommented:
Your second rule defeats the firewall settings by allowing. Everything, you might be better of disabling iptables altogether. There is no benefit to have each packet to be checked given you have currently no intention of filtering.
0
sargent240Author Commented:
In you last post you suggest I not use iptables.  If that is the case and I disable iptables what would I have?  All I want to do is give auction@192.168.0.11 access to a mysql database at 192.168.0.6.  I don't need anything fancy.
0
arnoldCommented:
iptables is a linux firewall.
disabling the service means that there will be no local firewall on the system.

At this point, based on your posting of iptables -L INPUT --line-numbers
line number 1 reflects the rule that all connection attempts to port 3306 on the local system will be permitted.

The mysql connection attempt you made that you posted with an error reflected the connection as coming from 192.168.0.6 pointing out that your attempt was made from the wrong system.

if you get into the system with IP 192.168.0.11, and run
mysql -u auction -p -h 192.168.0.6 auction6
once you type the correct password, you should be connected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sargent240Author Commented:
I have to run down to my office to do that.  I'll give it a go.  Thank you very much for all your help.  I will report back.  Again Thanks!
0
sargent240Author Commented:
It was the firewall on the 0.6 machine.  I turned it off and all is well.  Thanks for your patients.
0
Dave BaldwinFixer of ProblemsCommented:
You should turn it back on and allow incoming and outgoing network traffic on port 3306.
0
arnoldCommented:
Sargent,
Seth should have gotten the points.  His suggestion to deal with iptables was/is the solution to the issue you were facing.
I think you should reconsider the award of points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.