MySQL: Connect From an Other System / Computer

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> 

Bash script to create MySQL database and user

#!/bin/bash
 
EXPECTED_ARGS=3
E_BADARGS=65
MYSQL=`which mysql`
 
Q1="CREATE DATABASE IF NOT EXISTS $1;"
Q2="GRANT ALL ON *.* TO '$2'@'localhost' IDENTIFIED BY '$3';"
Q3="FLUSH PRIVILEGES;"
SQL="${Q1}${Q2}${Q3}"
 
if [ $# -ne $EXPECTED_ARGS ]
then
  echo "Usage: $0 dbname dbuser dbpass"
  exit $E_BADARGS
fi
 
$MYSQL -uroot -p -e "$SQL"

./createdb testdb testuser secretpass 

Perl create mysql database, user, password

my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pass = "111";
my $db_name = "test";
my $db_user = "test";
my $db_pass = "test";

my $dsn = "dbi:mysql::$host:$port";
my $dbh = DBI->connect($dsn, $user, $pass) or die "Unable to connect: $DBI::errstr\n";

$dbh->do("CREATE DATABASE $db_name");
$dbh->do("CREATE USER $db_user\@$host");
$dbh->do("GRANT ALL ON $db_name.* TO $db_user\@$host IDENTIFIED BY '$db_pass'");

$dbh->disconnect();