CentOS 6 – MySQL Server & PHPMyAdmin

Install and Configure

Notes

If you already have MySQL installed from the yum repositories, then you won’t be able to follow this guide as your MySQL version is too old. Please see [here] which may be able to help you (untested).

Environment

Fresh install of CentOS-6.3-x86_64-minimal with the latest updates yum update -y

 # uname -sro
Linux 2.6.32-279.22.1.el6.x86_64 GNU/Linux

I have also disabled SELINUX. Please see [here] for a guide.

Prerequisites

Additional Repositories

Nb. You can check for the latest EPEL repository from http://mirror.datacenter.by/pub/fedoraproject.org/epel/6/x86_64/repoview/epel-release.html

rpm -ivh http://mirror.datacenter.by/pub/fedoraproject.org/epel/6/x86_64/epel-release-6-8.noarch.rpm rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

Then you need to enable the REMI repository

nano /etc/yum.repos.d/remi.repo
[remi]
enabled=1

Additional Packages

I used nano as the text editor, but you can just as easily use vi if you are familiar with it.

yum install -y wget nano perl

Configure Firewall

nano /etc/sysconfig/iptables

Make sure you add any other rules you are using which aren’t listed here.

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT

# Uncomment the following line to allow direct remote access to your mysql server, 
# changing -s 192.168.0.0/16 to your own network or remove it to allow access from anywhere
# This has serious security implications so only do it if you know what you're doing
#-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -s 192.168.0.0/16

-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
service iptables restart

Create SSL Certificates

mkdir /etc/ssl/certs/mysql
openssl req -new -nodes -x509 -keyout /etc/ssl/certs/ca-cert.pem -out /etc/ssl/certs/ca-cert.pem -days 3650
openssl req -new -nodes -days 3650 -keyout /etc/ssl/certs/mysql/server-key.pem -out /etc/ssl/certs/mysql/server-req.pem openssl rsa -in /etc/ssl/certs/mysql/server-key.pem -out /etc/ssl/certs/mysql/server-key.pem openssl x509 -req -in /etc/ssl/certs/mysql/server-req.pem -CA /etc/ssl/certs/ca-cert.pem -CAcreateserial -days 3650 -out /etc/ssl/certs/mysql/server-cert.pem
openssl req -new -nodes -days 3650 -keyout /etc/ssl/certs/mysql/client-key.pem -out /etc/ssl/certs/mysql/client-req.pem openssl rsa -in /etc/ssl/certs/mysql/client-key.pem -out /etc/ssl/certs/mysql/client-key.pem openssl x509 -req -in /etc/ssl/certs/mysql/client-req.pem -CA /etc/ssl/certs/ca-cert.pem -CAcreateserial -days 3650 -out /etc/ssl/certs/mysql/client-cert.pem

Example of what to fill in but input your own answers.

Country Name (2 letter code) [XX]:IM
State or Province Name (full name) []:Isle of Man
Locality Name (eg, city) [Default City]:Colby
Organization Name (eg, company) [Default Company Ltd]:ITManx Ltd
Organizational Unit Name (eg, section) []:ICT
Common Name (eg, your name or your server's hostname) []:secure.itmanx.com
Email Address []:support@itmanx.com

* You will get asked the following on the server and client certificates created. Leave these blank!
A challenge password []:
An optional company name []:

You can test the certificate is ok by typing

openssl verify -CAfile /etc/ssl/certs/ca-cert.pem /etc/ssl/certs/mysql/server-cert.pem /etc/ssl/certs/mysql/client-cert.pem
/etc/ssl/certs/mysql/server-cert.pem: OK
/etc/ssl/certs/mysql/client-cert.pem: OK

Install

MySQL

At the time of writing, the version of MySQL in the CentOS yum repositories was version 5.1 which is near EOL so don’t use it unless you have to.

If you would prefer to have the latest version of MySQL, then see the note below instead

yum install -y mysql mysql-server

Note: To install the latest version of MySQL, you can get the latest version of MySQL from http://dev.mysql.com/downloads/mysql/#downloads(select Red Hat Linux 6) and note you need Server, Client and Compatibility Libraries

rpm -ivf http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/ rpm -ivf http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/ rpm -ivf http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-compat-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

The installation creates a random root password which you can see in /root/.mysql_secret

mv /usr/my.cnf /usr/my.cnf.original nano /usr/my.cnf
[client]
ssl_ca=/etc/pki/tls/certs/ca-cert.pem
ssl_cert=/etc/pki/tls/certs/mysql/client-cert.pem
ssl_key=/etc/pki/tls/certs/mysql/client-key.pem

[mysqld]
# Set to the amount of RAM for the most important data cache in MySQL.
# Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

character_set_server=utf8

ssl_ca=/etc/pki/tls/certs/ca-cert.pem
ssl_cert=/etc/pki/tls/certs/mysql/server-cert.pem
ssl_key=/etc/pki/tls/certs/mysql/server-key.pem

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
service mysql start

Take a look at the log file to make sure no start-up errors

cat /var/log/mysqld.log

Now grab the pre-set password and login

cat /root/.mysql_secret
mysql -u root -p ** enter the password from /root/.mysql_secret

Change mypassword to your own password. The password should be at least 10 characters. You can generate a password [here].

SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('mypassword');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('mypassword');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypassword');
DROP DATABASE test;
quit

phpMyAdmin

yum install -y httpd mod_ssl php php-mysql php-mcrypt php-mbstring php-gd

You can get the latest version from http://www.phpmyadmin.net/home_page/downloads.php

cd /var/www/html wget http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/3.5.7/phpMyAdmin-3.5.7-all-languages.tar.bz2/download tar -jxvf phpMyAdmin-* rm -f php*.bz2 mv phpMyAdmin-* phpmyadmin cp phpmyadmin/config.sample.inc.php phpmyadmin/config.inc.php
nano /etc/httpd/conf.d/phpmyadmin.conf
Alias /phpmyadmin /var/www/html/phpmyadmin

<Directory /var/www/html/phpmyadmin>
 Options -Indexes
</Directory>

<Directory /var/www/html/phpmyadmin/setup>
 Order Deny,Allow
 Deny from All
</Directory>

<Directory /var/www/html/phpmyadmin/libraries>
 Order Deny,Allow
 Deny from All
</Directory>
nano /var/www/html/phpmyadmin/config.inc.php

The password can be up to 46 characters. You can generate a password [here].

$cfg['blowfish_secret'] = '5@#@7ecr39R@musU99GAkE+!ASt63$aB+es3zedu_ep$ey'	 /* Change to your own password */
nano /etc/php.ini

Search for date.timezone and set it to your timezone. See [here] for a list of timezones.

date.timezone = UTC
chkconfig httpd on service httpd start

Test

Log in to https://webserver/phpmyadmin/ with username root and the password you set when configuring MySQL earlier.

Monitor

Resources

Type top to view resources or better yet, install htop yum install -y htop and then type htop (see at the bottom for filter and enter mysql)

Live Queries

If you want to log live queries, you can enable logging in the MySQL configuration file.

 

BE AWARE THAT THIS WILL LOG EVERY QUERY PASSED TO THE MYSQL SERVER SO WILL QUICKLY CONSUME FREE DISK SPACE AND SLOW PERFORMANCE!

touch /var/log/mysql.log chown mysql:mysql /var/log/mysql.log
nano /usr/my.cnf
[mysqld]
.....
general_log=1
general_log_file=/var/log/mysql.log
service mysqld reload

You can then view live queries by typing tail -f /var/log/mysql.log

System Resource Monitoring

Install htop to see system resource usage

yum install -y htop

Run by typing htop

 

Reference

http://dev.mysql.com/doc/refman/5.6/en/linux-installation-rpm.html

http://dev.mysql.com/doc/refman/5.6/en/creating-ssl-certs.html

http://www.openssl.org/docs/apps/req.html