0

I am having trouble connecting to my remote MySQL server which is hosted on a CentOS7 server.

It was working well before then 1 day it just stop working and start throwing me these errors:

From my windows box:

Connection Failed: [HY000][MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '10.103.23.234' (10090)

From my other CentOS server:

ERROR 2003 (HY000): Can't connect to MySQL server on '10.103.23.234' (113)

So I looked for the top search result regarding the issue I'm experiencing and came across to many of the suggested solutions.

I already binded my server's ip to MySQL config file at /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=10.103.23.234
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Due to frustrations, I did delete-create the user and grant privileges on MySQL user

MariaDB [(none)]> SHOW GRANTS FOR 'faultanalytics'@'%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for faultanalytics@%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'faultanalytics'@'%' IDENTIFIED BY PASSWORD '*82CB5B45DC157D1466E8A2F031A55B06D9C8F9FA' |
| GRANT ALL PRIVILEGES ON `outageTicket`.* TO 'faultanalytics'@'%'                                              |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


MariaDB [(none)]> SELECT * FROM mysql.db WHERE user='faultanalytics' AND  host='%' AND db='outageTicket'\G;
 *************************** 1. row ***************************
                 Host: %
                   Db: outageTicket
                 User: faultanalytics
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
          Execute_priv: Y
            Event_priv: Y
          Trigger_priv: Y
1 row in set (0.00 sec)

ERROR: No query specified

BUT I NOTICE SOMETHING STRANGE on this NEXT TABLE BELOW, I can only see "USAGE" privilege for user 'faultanalytics'@'%'

MariaDB [(none)]> SELECT * from information_schema.user_privileges WHERE GRANTEE='\'faultanalytics\'@\'%\'';
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'faultanalytics'@'%' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+

I am thinking, is this the reason why I can't connect remotely to my MySQL server?

Checked my ports

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 10.103.23.234:3306      0.0.0.0:*               LISTEN
JCm
  • 1,919
  • 8
  • 27
  • 42
  • MySQL, by default, uses port 3306. Has anything changed there (e.g. router configs, firewalls, etc)? You already seem to have the proper privileges to get a connection. Did you check MySQL's error log? Try the `nmap` command against your database server and verify that the MySQL port is open. Also, are you the *only* individual who's authorized to access this server? – code_dredd Nov 12 '15 at 06:34
  • @janschweizer unfortunately i dont have telnet on any of my box, but i can ssh though thru that MySQL server. – JCm Nov 12 '15 at 06:37
  • @JCm: So, the command `mysql -h -u -p` allows you to connect from the Linux shell? – code_dredd Nov 12 '15 at 06:39
  • @janschweizer no i cannot established connection via mysql -h -u -p as mentioned above... – JCm Nov 12 '15 at 06:43

2 Answers2

1

Have you verified that port 3306 is open in the firewall? CentOS reloads firewall rules on reboot, so if you had added a non-permanent rule to your firewall settings, then it would be flushed when you rebooted the server.

For the quick fix, see the excellent accepted answer in this SO post: centos 7 - open firewall port

If you want to learn more about FirewallD, you can find the FirewallD manual here: https://fedoraproject.org/wiki/FirewallD and a good in-depth tutorial here: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-using-firewalld-on-centos-7

Community
  • 1
  • 1
madsen
  • 422
  • 3
  • 9
  • yes actually, you got it. I may stop my firewall before, but i had a server restart some few weeks back that would have probably restarted my firewall and causing me problem. I now have to allow access to my mysql. – JCm Nov 12 '15 at 07:00
0

After some further research I came across another stackoverflow question with similar experience

Accessing a mysql database from external host/ip? (ie: mysql workbench)

So like the answer of Yoosaf Abdulla in that post, I tried stoping my server's firewall and woalah! I successfully got connected. Then doing his solution to resolve should solve the problem.

Community
  • 1
  • 1
JCm
  • 1,919
  • 8
  • 27
  • 42