Saturday 29 October 2011

How to install a standalone mysql database server on CentOS 5.6


For small web applications using MySQL database it might be okay to have both the web application and MySQL database on the same server. But as your database size grows, in order get the best performance, it's better to separate the database server from the web application server. In other words, you run the MySQL server on one server and the web application on a different server.
Let's say we have the following scenario:


MySQL Database Server hostname: mysql.example.com and it's internal IP address is 192.168.1.100
Web application server hostname: web.example.com and it's internal IP address is 192.168.1.200
After a fresh server install of CentOS 5.6, do the following:
yum install mysql-server
chkconfig mysqld on
service mysqld start
mysqladmin -u root password 'set_new_pasword'
mysql -u root -p
mysql> create database webdb;
mysql> grant all privileges on webdb.* to webuser@'192.168.1.200' identified by 'webuserpassword';
mysql> flush privileges;
mysql> exit;

What this means is the MySQL engine will allow the user "webuser" to connect from the web application server with IP address: 192.168.1.200.
You can check if you can connect from the web application server to this mysql server as follows:
mysql -u webuser -h mysql.example.com -p
NOTE: On the mysql server, on the grant all privileges command, you can use the hostname web.example.com instead of it's IP address: 192.168.1.200. If you do this, please make sure that you can resolve the hostname by looking up the IP address. In orther words, from the mysql server, if you do "nslookup 192.168.1.200', it should return web.example.com.

No comments:

Post a Comment