Linux Apps Tutorials - Herong's Tutorial Examples - v1.03, by Herong Yang
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:
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:
Table of Contents
Running Apache HTTP Server (httpd) on Linux Systems
Running Apache Tomcat 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
Conda - Environment and Package Manager