MySQL Server TCP/IP Connection on CentOS

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:

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 Intener. 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:

Table of Contents

 About This Book

 Introduction to Linux Systems

 Cockpit - Web Portal for Administrator

 Process Management

 Files and Directories

 Users and Groups

 File Systems

 Installing CentOS

 SELinux - Security-Enhanced Linux

 Network Connection on CentOS

 Software Package Manager on CentOS - DNF and YUM

 Running Apache Web Server (httpd) on Linux Systems

 Running PHP Scripts on Linux Systems

Running MySQL Database Server on Linux Systems

 Install MySQL Database Server on CentOS

 Manage MySQL Server 'mysqld' on CentOS

 Set MySQL Server "root" Password on CentOS

 MySQL Server File Locations on CentOS

 MySQL Server Data Backups on CentOS

 MySQL Server Log Files on CentOS

 MySQL Server Socket Connection on CentOS

MySQL Server TCP/IP Connection on CentOS

 Open Firewall for MySQL Server on CentOS

 "Server sent charset unknown to the client" Error

 Performance of Inserting Integers to MySQL Database

 Performance Comparison of Inserting Integers vs. Strings

 Running Python Scripts on Linux Systems

 vsftpd - Very Secure FTP Daemon

 Postfix - Mail Transport Agent (MTA)

 Dovecot - IMAP and POP3 Server

 Email Client Tools - Mail User Agents (MUA)

 GCC - C/C++ Compiler

 Conda - Environment and Package Manager

 Tools and Utilities

 References

 Full Version in PDF/EPUB