How do I connect to my MySQL database server from an other server (say Apache or Tomcat app server) in same VLAN under CentOS / Fedora / RHEL / Redhat Linux?
First, you need to turn on the remote access for your database server.
Sample Setup
Consider the following sample setup:
+----------- server1 192.168.1.6 | +------------ tomcat1 192.168.1.7 | +------------------+ | | MySQL Server | --------+------------ apache2 192.168.1.8 | 192.168.1.5:3306 | --------+ +------------------+ | LAN 192.168.1.0/24 +------------- pc1 192.168.1.51 | | +-------------- pc25 192.168.1.76
You need to allow access to 192.168.1.5 from apache server located at 192.168.1.8.
Step #1: Configure MySQL Server For Remote Access
Open a terminal or login to 192.168.1.5 using the ssh command:
$ ssh root@192.168.1.5
Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Modify or append as follows:
# make sure the following line is deleted or commented out # skip-networking bind-address = 192.168.1.5
Save and close the file. Restart the mysql server, enter:
# service mysqld restart
Make Sure TCP Port # 3306 is Opened For Business
Verify that the TCP port 3306 is open, enter:
# netstat -tulpn | grep :3306
Step #2: Linux Firewall Configuration For TCP Port # 3306
You need to open TCP port # 3306 at the firewall level, enter:
# iptables -A INPUT -i eth0 -s 192.168.1.8 -p tcp --destination-port 3306 -j ACCEPT
# service iptables save
Step #3: Configure Database Remote Access
You need to grant access to an existing database called salesdb from remote IP called 192.168.1.8 using a username called foo. First, connect to mysql server as root user, enter:
# mysql -u root -p mysql
Type the following command At mysql> prompt, enter:
mysql> update db set Host='192.168.1.8' where Db='salesdb';
mysql> update user set Host='192.1681.8' where user='foo';
mysql> \q
Login to 192.168.1.8 and type the following command to test mysql server remote access:
$ mysql -u foo -h 192.168.1.5 -p salesdb
Sample outputs:
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27720995 Server version: 5.0.77 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
🐧 4 comments so far... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
great article as always!
I find it more intuitive and secure to forward a a local port
ssh -NL 5000:localhost:3306 ssh@server.com
and then connect locally by specifying the forwarded port
mysql -u mysqluser -P 5000 -p
hi !
i dont see this # skip-networking on my my.cnf
Hi,
It’s very nice and complete document for newbies such as me..!
good luck
Thank you very much for this guide!
Just wanted to point out that “service iptables save” didn’t work on my Ubuntu server and instead I had to use “iptables-save”.