How to setup a MySQL server on Local Area Network (LAN)

Image Source: https://www.flickr.com/photos/xmodulo/14522157584

Setting up a centralized database with in your local area network(LAN) is much simpler than you think with MySQL. The database server can be easily setup on either a desktop, laptop, workstation or a headless server by following the steps below.

• First install MySQL in your system

on Linux,

sudo apt-get update
sudo apt-get install mysql-server

and test whether the installation succeeded by running

systemctl status mysql.service

the above should output the details about your MySQL server.

on Windows,

Download installer (.exe) from https://dev.mysql.com/downloads/installer/ and the follow the instructions on the installation wizard for installing.

• Now with MySQL being installed we need to bind it (MySQL server) to a given IP with in your network (LAN).

Before setting this up, make sure that your network (LAN) is configured to have a static IP for your MySQL host(desktop,server or etc.) Next we need to configure my.cnf located in your system, on Linux it is located at /etc/mysql/my.conf and on Windows most probably it will be located on C:\Program Files\MySQL\MySQL Server 5.5\my.cnf . Open the above file in your favorite text editor, and add the following entry.

#bind-address = 127.0.0.1
bind-address = 10.100.85.50

Substitute 10.100.85.50 with the static IP allocated to your host(desktop,server or etc) on the connected LAN. If you already has 127.0.0.1 binded, please comment it.

Note: If you already have a public IP allocated for the above instance and are willing to access the MySQL server over the Internet. Please do comment anybind-address=any-ip-previously-binded lines in the above file, in order to make MySQL server listen from all the IPs allocated.

• Restart MySQL server by running

on Linux

sudo service mysql restart

on Windows

net stop mysql & net start mysql

• Next we need to grant all privileges to the `root` user coming from any IP within your network. For that first log in to the MySQL shell by running the following command on the command line. (This is same for both Linux and Windows).

mysql -u root -p

and provide the password when prompted. Next run the following commands to allow access to the `root` user from any IP.

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

Substitute the `newpassword` with the password that the clients (in LAN) should use in connecting to your MySQL server.