Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Can't connect to mySQL
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Portage & Programming
View previous topic :: View next topic  
Author Message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Tue Aug 13, 2013 7:17 am    Post subject: Can't connect to mySQL Reply with quote

Hi, ALL,
Here is the problem.
I am running Gentoo with the mySQL installed (at the moment 5.5 on 64-bit - ~x64).
What I am trying to do is to connect from the Windows XP machine, using python and MySQLdb extension.

Trying to connect thru python shell, I'm getting this:

"_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '192.168.1.11' (10061)"

Upon reading documentation of mySQL I see that it is a network issue.
The server is running fine which can be seen here:

Code:

IgorReinCloud mysql # ps xa | grep mysql
 1414 ?        Ssl    0:28 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf
12900 pts/2    S+     0:00 grep --colour=auto mysql


However, the next command suggested on the documentation failed:

Code:

IgorReinCloud mysql # mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'


As far as I know, mySQL server was started with the network access enabled. Also I can successfully run mysql-workbench from Gentoo machine connecting to the server as "root".

I don't have firewall on XP running at the moment so the traffic shouldn't be blocked.

How to make server and client (python) talk to each other?

Thank you.
Back to top
View user's profile Send private message
creaker
l33t
l33t


Joined: 14 Jul 2012
Posts: 651

PostPosted: Tue Aug 13, 2013 12:48 pm    Post subject: Reply with quote

In addition to networking enabling you should create new user and grant permissions to him:
Code:
CREATE USER 'user_name'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

% means all the possible hosts
Did you do it?
Back to top
View user's profile Send private message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Thu Aug 15, 2013 3:10 am    Post subject: Reply with quote

creaker,
I just tried to execute following code:

Code:

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


The first line executed fine, however, the second gave me:

Quote:

Error code: 1045. Access denied for user 'root@'localhost'


This is what happens in mysql-workbench.

Now what I'm trying in python is connecting as 'root'. I am able connect using mysql-workbench which is installed on the Gentoo box along with mysql. But I wasn't able to connect from Windows from python.

Any idea how to track this down?

Thank you.

[EDIT]
I can ping Gentoo box from Windows without any problems. And there is no firewall on either side.
[/EDIT]
Back to top
View user's profile Send private message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Thu Aug 15, 2013 6:06 am    Post subject: Reply with quote

Surprisingly enough:

Code:

mysql> CREATE USER 'igor'@'localhost' IDENTIFIED BY 'wasqra';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'igor'@'localhost' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> SHOW GRANTS FOR 'igor';
+-----------------------------------------------------------------------------------------------------+
| Grants for igor@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'igor'@'%' IDENTIFIED BY PASSWORD '*XXX"
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'igor'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for igor@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'igor'@'localhost' IDENTIFIED BY PASSWORD 'XXX"
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


So, I guess I should just do "FLUSH PRIVILEGES;", correct?

Thank you.
Back to top
View user's profile Send private message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Thu Aug 15, 2013 6:50 am    Post subject: Reply with quote

After reading this and running mysql_upgrade the "GRANT ALL PRIVILEGES..." command executed successfully for the newly created users (both local and global).

However, I still can not connect remotely, whereas, I can do it locally.

Code:

igor@IgorReinCloud ~ $ mysql --user=root mysql --password="wasqra"
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'igor'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'igor'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
igor@IgorReinCloud ~ $ mysql --user=igor mysql --password         
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


and connecting remotely still brings the same error message:

Code:

mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL Server on 192.168.1.11 (10061)


Anything else I can try?

Thank you.
Back to top
View user's profile Send private message
creaker
l33t
l33t


Joined: 14 Jul 2012
Posts: 651

PostPosted: Thu Aug 15, 2013 11:27 am    Post subject: Reply with quote

ONEEYEMAN wrote:

Anything else I can try?

Since you have new user 'igor' that allowed to connect from remote host, check your my.cnf or my.ini file. It should contain
Code:
bind-address = YOUR-REMOTE-CLIENT-IP

under [mysqld] section. As well 'skip-networking' option should be commented.
Here is my server's my.ini file part:
Code:
[mysqld]
basedir="DB/mysql/"
datadir="DB/mysql/data/"
port      = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer = 32M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
bind-address = 192.168.1.2
#skip-networking

I can connect to server that has ip 192.168.1.5 from 192.168.1.2 machine
P.S.
Do not forget to restart the server after file editing.
Back to top
View user's profile Send private message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Thu Aug 15, 2013 7:33 pm    Post subject: Reply with quote

Hi,
I just did that and rebooted as it is a dual-boot laptop.
Now the boot process stuck on the mysql start.

I strongly suspect that it is trying to bind the address, but the networking is not started yet.
It is holding now for a very long time.

How do I solve it?
I probably should move mysql start up after the networking, but how?

Thank you.
Back to top
View user's profile Send private message
ONEEYEMAN
Advocate
Advocate


Joined: 01 Mar 2005
Posts: 3552

PostPosted: Thu Aug 15, 2013 7:46 pm    Post subject: Reply with quote

OK, system booted and mysql didn't start.
Network is started, but trying to start mysql gives the same symptom: it just hangs.

Any idea?

Relevant code of /etc/mysql/mysql.cnf
Code:

[mysqld]
character-set-server            = utf8
user                                            = mysql
port                                            = 3306
socket                                          = /tmp/mysqld.sock
pid-file                                        = /tmp/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-external-locking
key_buffer                                      = 16M
max_allowed_packet                      = 1M
table_open_cache                        = 64
sort_buffer_size                        = 512K
net_buffer_length                       = 8K
read_buffer_size                        = 256K
read_rnd_buffer_size            = 512K
myisam_sort_buffer_size         = 8M
lc_messages_dir                 = /usr/share/mysql
#Set this to your desired error message language
lc_messages                     = en_US

# security:
# using "localhost" in connects uses sockets by default
# skip-networking
bind-address                            = 192.168.1.5

log-bin
server-id                                       = 1


Code:

IgorReinCloud igor # /etc/init.d/mysql start
 * Starting mysql ...

Back to top
View user's profile Send private message
creaker
l33t
l33t


Joined: 14 Jul 2012
Posts: 651

PostPosted: Thu Aug 15, 2013 11:48 pm    Post subject: Reply with quote

Sorry, it is my mistake...
bind-address should point to SERVER ip, not client ip.
In your case (if I'm not wrong) it should be 192.168.1.11, not 192.168.1.5
Back to top
View user's profile Send private message
chiefbag
Guru
Guru


Joined: 01 Oct 2010
Posts: 542
Location: The Kingdom

PostPosted: Fri Aug 16, 2013 4:00 pm    Post subject: Reply with quote

Just comment out the
Code:
bind-address
line in the my.cnf file

That way MySQL will bind to all available interfaces, also you won't run into issues later if you are running dhcp and not a static ip on the MySQL server.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Portage & Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum