External access to MySQL database

To gain external access to MySQL database (f.e. in PHPSTorm or an external PHPMyAdmin) you can do:

  1. GRANT necessary privileges:
    CREATE USER 'wdn_live_user'@'%' IDENTIFIED BY '***';
    GRANT USAGE ON *.* TO 'wdn_live_user'@'%' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
    GRANT ALL PRIVILEGES ON `wdn_live`.* TO 'wdn_live_user'@'%';

    If the user already exists, you can leave the first line.

  2. Allow access from outside localhost:
    $> sudo sed -i "s/^bind-address/#bind-address/" /etc/mysql/my.cnf
    $> sudo service mysql restart

    On CentOS the my.cnf usually is directly located in /etc/ and the service name is mysqld.

Check if configuration for external access to MySQL database was successful

You can get the privileges of the currently connected MySQL user by connection to the database server on shell an running:

$> mysql -uroot -pTOPSECRET
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 93
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, 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> SHOW GRANTS;

This should show you something like that:

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF965E05AA2576AF0FD78ED04B6A2' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

If you want to see which permissions a certain user has from localhost:

mysql> SHOW GRANTS FOR dbuser@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for dbuser@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF965E05AA2576AF0FD78ED04B6A2' |
| GRANT ALL PRIVILEGES ON `dbname`.* TO 'dbuser'@'localhost' WITH GRANT OPTION                                  |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

And to check external access to MySQL database:

mysql> SHOW GRANTS FOR dbuser;
+-------------------------------------------------------------------------------------------------------+
| Grants for dbuser@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF965E05AA2576AF0FD78ED04B6A2' |
| GRANT ALL PRIVILEGES ON `dbname`.* TO 'dbuser'@'%'                                                    |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

As you can see, if you let out the host portion, you will get the privileges for ‘%’, which means every host.

One thought on “External access to MySQL database

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.