Linux: Configure MySQL Database Server

This vm node stores your data in RDBMS such as mysql or postgresql. In this setup, I’m going to use MySQL database server. You need to type the following commandss on vm04 having an IP address 192.168.1.13 only.

Install the mysql server

Type the following yum command to install mysql database server on RHEL /Centos Linux based systems:
# yum install mysql mysql-server

Configure the mysql server

Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Make sure mysql server can be accessed from your vm01 and vm02 server i.e. Apache+php5 server. Locate [mysqld] section and add/correct as follows so that mysqld can reached remotely :

# Make sure skip-networking directive is commented (or removed)
# skip-networking
# Turn on remote access
bind-address=192.168.1.13

Optimization settings

You need to optimize mysql server otherwise it is going to eat all your CPU and other resources on vm04. You can add or correct settings as follows (see mysql manual for more info):

#######################################################################
# WARNING!!!
# Security and optimization settings
# Read mysqld and my.cnf man page for more info
# as the following settings depends upon hardware and your requirements
########################################################################
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
## Go faster and skip some stuff, YMMV
skip-name-resolve
skip-slave-start
skip-external-locking
 
# PER CLIENT SETTINGS #
# bit high but I got tons of ram here #
sort_buffer_size 		= 2M
read_buffer_size 		= 2M
binlog_cache_size 		= 1M
wait_timeout 			= 200
interactive_timeout 		= 300
max_allowed_packet 		= 12M
thread_stack 			= 128K
table_cache 			= 1024
myisam_sort_buffer_size 	= 1M
tmp_table_size 			= 12M
max_heap_table_size 		= 12M
 
 
# LOGGING #
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/slowquery.log
 
# CACHES AND LIMITS #
tmp_table_size                 = 12M
max_heap_table_size            = 12M
query_cache_type               = 1
query_cache_limit              = 2M
query_cache_size               = 32M
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 1024
 
# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP
 
# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
 
 
# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1
 
# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 10G

Save and close the file. Restart / reload the mysql server:
# chkconfig mysqld on
# service mysqld start
# service mysqld reload
# service mysqld restart
Verify that mysqld running on tcp port #3306:
# netstat -tulpn | grep :3306

MySQL database server firewall configuration

Edit /etc/sysconfig/iptables, enter:
# vi /etc/sysconfig/iptables
Make sure vm01 and vm02 can access the database server:

## open mysqld server port for the apache and lighttpd web server #
-A INPUT -m state --state NEW -s 192.168.1.10 -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -s 192.168.1.11 -m tcp -p tcp --dport 3306 -j ACCEPT

Save and close the file. Restart iptables, enter:
# service iptables restart

Increase file system and ports limits on vm04 database server

For busy RDBMS server you need to increase system file descriptor (FD limits) and IP port limits:

# Increase system file descriptor limit to
fs.file-max = 50000
# Increase system IP port limits
net.ipv4.ip_local_port_range = 2000 65000

Load the changes by typing the following sysctl command to modify Linux kernel parameters at runtime:
# sysctl -p

Creating MySQL databases and accounts

This section provides basic instructions for manually creating a MySQL database. In this example, create a mysql database and user as follows:

• DB NAME : foo
• DB USER NAME : bar
• DB PASSWORD : mypassword
• ALLOW DB ACCESS FROM : localhost, vm01, and vm02 having an IP address 192.168.1.10 and 192.168.1.11 only

Type the following command to create the database and required users:
# /usr/bin/mysql -u root -h localhost -p
Type the following commands at mysql> prompt. To create your database called foo, type:

mysql> CREATE DATABASE foo;

You must grant access rights for this database to the MySQL user called bar through which the Apache+php5 application server will be connecting. Type:

mysql> GRANT ALL ON foo.* TO bar@localhost IDENTIFIED BY 'mypassword';
mysql> GRANT ALL ON foo.* TO bar@192.168.1.10 IDENTIFIED BY 'mypassword';
mysql> GRANT ALL ON foo.* TO bar@192.168.1.11 IDENTIFIED BY 'mypassword';

To exit from the MySQL monitor, type:

mysql> quit

You can now create tables or load data using .sql files. You can automate this procedure by writing shell or perl script to add the mysql user and database.

Test your new db and user settings from remote vm01 or vm02

Ssh into the vm01 or vm03 and type the following command to test the connectivity from the Apache / Lighttpd web server:
$ mysql -u bar -h 192.168.1.13 -p'mypassword' foo
OR
$ mysql -u bar -h vm04 -p'mypassword' foo

phpMyAdmin

The phpMyAdmin program act as the web-interface for MySQL. It is used to handle the administration of MySQL over the WWW using a browser. PhpMyAdmin can administer a whole MySQL server or a single database. This is recommended package for all new MySQL database users and admins.