How to Activate MySQL for Multiple Devices

mysql , database , networking , tutorial

This tutorial will guide you through the steps to allow multiple devices to access your MySQL server over a network.

Open the MySQL configuration file (my.cnf or my.ini).
- On Windows: Usually located at C:\ProgramData\MySQL\MySQL Server X.X\my.ini
- On Linux: Usually at /etc/mysql/my.cnf or /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line starting with bind-address.

bind-address        = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

Save the file and restart the MySQL service.

⚠️ Important: At this point MySQL will accept connections from anywhere. If your server is exposed to the internet, secure it:

Create a new user or update an existing user to allow access from a specific IP or any IP (%):

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Replace username and password with your desired credentials.

On the client device, use the following command to connect:

mysql -h <server-ip-address> -u username -p

Replace <server-ip-address> with the IP address of your MySQL server.

Optimizations

edit: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
max_allowed_packet = 128M
net_read_timeout = 120
net_write_timeout = 120

check using:

SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'net_read_timeout';
SHOW VARIABLES LIKE 'net_write_timeout';

edit: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
max_connections = 500
wait_timeout = 60
interactive_timeout = 60

check using

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

You have now enabled MySQL access for multiple devices on your network!