Ubuntu server. Give access to MySQL

Question

I installed MySQL on my home ubuntu server. From the server itself I can connect via root user to MySQL.

1 Logged in, created a new user:

mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'admin'@'%' IDENTIFIED BY 'мойПароль'; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> SELECT User,Host FROM mysql.user; +------------------+-----------+ | User | Host | +------------------+-----------+ | admin | % | | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 4 rows in set (0,00 sec)

2 Next setting up the file /etc/mysql/my.cnf

File Text:

 # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] character-set-server=utf8 collation-server=utf8_general_ci init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' skip-character-set-client-handshake [client] default-character-set=utf8 port = 2508 [mysqldump] default-character-set=utf8

3 rebooted

 sudo service mysql restart

4 Redirected port 2508 on the router to the server machine (Did the same for Transmission and SSH. Everything works)

5 I can’t connect via Workbench from a computer in the same network (as a server)

external ip: port 2508 (I work with this option via SSH, with another port of course)
internal server ip: port 2508
- tried other ports (3306)
- I tried to delete the line "port = 2508" from the file my.cnf
- I tried to add the string bind-address = 0.0.0.0 in the client field

On netstat request I get this:

 sudo netstat -tap | grep mysql tcp 0 0 localhost:mysql *:* LISTEN 1384/mysqld

I searched on the Internet, there is no one to set up a port at all. Well, the truth and do not speak about connecting from the outside. I do not understand what I am doing wrong.

Now you have mysql running only on 127.0.0.1. You need to set the value for this parameter to 0.0.0.0 so that it
added in the client field the string "bind-address = 0.0.0.0" restarted the service, nothing has changed.

Opossum opossum 128 13 · Accepted Answer · 2017-01-12T13:59:23

Thanks @Mike, you had to write to the [mysqld] block

All code of the /etc/mysql/my.cnf file:

 # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] character-set-server=utf8 collation-server=utf8_general_ci init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' skip-character-set-client-handshake #Для доступа из вне bind-address = 0.0.0.0 #Для замены стандартного 3306 порта port = 2508 [client] default-character-set=utf8 [mysqldump] default-character-set=utf8

Perhaps because FLUSH PRIVILEGES; forgot FLUSH PRIVILEGES;

Ubuntu server. Give access to MySQL

1 answer 1

More articles: