MySQL Server TCP/IP Connection on CentOS

Linux Apps Tutorials - Herong's Tutorial Examples

∟Running MySQL Database Server on Linux Systems

∟MySQL Server TCP/IP Connection on CentOS

This section provides a tutorial example on how to use the TCP/IP Connection for client programs from any hosts to connect to the MySQL 8 Server on CentOS 8 systems.

MySQL server supports 2 types of connections from client programs:

Socket Connection, or Named Pipe Connection - Allows client programs from the local host to access the server without using the TCP/IP protocol.
TCP/IP Connection - Allows client programs from any hosts to access the server using the TCP/IP protocol.

TCP/IP Connection works under the following rules:

1. By default, the TCP/IP Connection is turned on for MySQL 8 server.

2. The MySQL server listens on the IP address of the server host specified in the "bind_address" variable. A comma-separated IP list can be specified if the server has multiple IP addresses. Wildcard "*" can be specified to listen to all IP addresses on the server. "bind_address" is set to "*" by default for MySQL 8.0 server on CentOS 8.

mysql> show variables like '%bind_address';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| bind_address        | *     |
| mysqlx_bind_address | *     |
+---------------------+-------+

3. "bind_address" is a read only variable, you need to set it in MySQL server configuration file, /etc/my.cnf.d/mysql-server.cnf, which is included in the /etc/my.cnf file. For example, you can change "bind_address" to a specific IP address to make the server more secure. Remember to restart MySQL server after changing the configuration file.

herong$ sudo cat /etc/my.cnf.d/mysql-server.cnf

[mysqld]

# Listen to the loopback IP address only so only client programs
# from the server host can use the TCP/IP connection.
# bind_address=127.0.0.1

# Listen to the local IP address only so only client programs
# from other local IP address, 192.168.1.* can use the TCP/IP connection
# bind_address=192.168.1.100

# Default - Listen to all IP addresses on the server host.
bind_address=*

4. The MySQL server listens on IP port 3306 specified in the "port" variable, which is a read only variable too. If you need to change it, you have to set it MySQL server configuration file, /etc/my.cnf.d/mysql-server.cnf, which is included in the /etc/my.cnf file.

5. Users must be created with their IP address as host names to use the TCP/IP Connection. The format of a user from a given IP address is 'user'@'ip_address'. You can use wildcard characters in the IP address to represent a range of IP addresses. For example, 'herong'@'127.0.0.1' . allows me to connect to the server from my computer that has IP address of 192.168.1.11. 'herong'@'192.168.1.%' allows me to connect to the server from any computer on the local network. 'herong'@'%' allows me to connect to the server from any computer on the entire Intenet. See MySQL commands below to create multiple users to use the TCP/IP connection from different remote client hosts:

mysql> # To use the TCP/IP connection on the same host
mysql> create user 'herong'@'127.0.0.1' identified by 'TopSecret';

mysql> # Allow TCP/IP connection from my computer
mysql> create user 'herong'@'192.168.1.11' identified by 'TopSecret';

mysql> # Allow TCP/IP connection from any computer on the local network
mysql> create user 'herong'@'192.168.1.%' identified by 'TopSecret';

mysql> # Allow TCP/IP connection from any computer on the entire Internet
mysql> create user 'herong'@'%' identified by 'TopSecret';

mysql> select user, host from mysql.user;
+------------------+--------------+
| user             | host         |
+------------------+--------------+
| herong           | localhost    |
| herong           | 127.0.0.1    |
| herong           | 192.168.1.11 |
| herong           | 192.168.1.%  |
| herong           | %            |
+------------------+--------------+

6. Client programs can specify the IP address and the port number where the MySQL server is listening to make a TCP/IP Connection. F or example, the following PHP statements will connect to the MySQL server using the TCP Connection:

# MySQL is listening on 127.0.0.1 at 3306:
$conn = mysqli_connect("127.0.0.1", 'herong', 'TopSecret');

# MySQL is listening on 192.168.1.100 at 3306:
$conn = mysqli_connect("192.168.1.100", 'herong', 'TopSecret');

# MySQL is listening on 192.168.1.100 at 3307:
$conn = mysqli_connect("192.168.1.100:3307", 'herong', 'TopSecret');

7. MySQL client tools connect to the SQL server use the TCP/IP Connection with given "-h ip_address" and "-P port_nunber" options. See the example below:

herong$ mysql -u herong -h 192.168.1.100 -p

herong$ mysql -u herong -h 192.168.1.100 -P 3307 -p

8. The last issue you may have on using TCP/IP connection is network firewall. Firewall may stop your TCP/IP connection in 4 places:

Firewall on your computer must allow TCP outbound request to the MySQL server.
Firewall on your computer local network must allow your TCP request to go out.
Firewall on the gateway the MySQL server local network must allow your TCP request to go in.
Firewall on the MySQL server must to be open to accept TCP inbound requests from your IP address.