Wednesday, August 14, 2013

MySQL: Connect From an Other System / Computer

How do I connect to my  database server from an other server (say Apache or Tomcat app server) in same  under  / Fedora / RHEL / Redhat ?
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 :
$ ssh @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:
#  -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>

HowTo: Find Out MySQL Server Version Number

How do I find out mysql server version number under UNIX / Linux operating systems?
You can use the mysqladmin command to get version information from server as follows. Open terminal and type the following command (for remote server login over ssh session):
$ mysqladmin -u root -p version
Sample outputs:
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.77, for redhat-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version  5.0.77
Protocol version 10
Connection  Localhost via UNIX socket
UNIX socket  /var/lib/mysql/mysql.sock
Uptime:   1 day 16 hours 37 min 50 sec
Threads: 2  Questions: 32558157  Slow queries: 0  Opens: 2992  Flush tables: 1  Open ta

MySQL startup script under BSD/Linux

You can start the MySQL server directly as described here. But most Linux distribution comes to special start and stop script. First, you must login as the root user. Use su – command to login as the root user.
If you are using Red Hat Linux / Fedora Linux:
Start MySQL
# service mysql start
OR
# /etc/init.d/mysql start
Stop MySQL
# service mysql stop
OR
# /etc/init.d/mysql stop
If you are using Debian Linux:
Start MySQL
# /etc/init.d/mysql start
Stop MySQL
# /etc/init.d/mysql stop
If you are using FreeBSD:
Open /etc/rc.conf file and add following line:
vi /etc/rc.conf
And append following line:
mysql_enable=”YES”
To Start MySQL under FreeBSD:
#/usr/local/etc/rc.d/mysql-server.sh start
To Stop MySQL under FreeBSD:
#/usr/local/etc/rc.d/mysql-server.sh stop

How do I start MySQL server without using startup script /etc/init.d/mysql?

MySQL startup script only works if you have installed MySQL server using binary method such as apt-get, rpm command or up2date/yum tools etc.
All you need to do is use mysqld_safe command to start the mysqld daemon on Unix or Linux oses. So to start MySQL just type following command:
# /usr/bin/mysqld_safe &
Usually mysqld_safe located at /usr/bin/ directory. However, if you custom compiled MySQL then it will be available in your custom directory (specified using –prefix=/path/to/dir option to ./configure command). For example if you installed MySQL in /server/mysql directory then you need to pass this option to mysqld_safe command:
# /usr/bin/mysqld_safe –basedir=/server/mysql –datadir=/data/mysql –log=/var/log/mysql.log &
Read man page of mysqld_safe for more information.