Install and Configure
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).
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.
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
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
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
Create SSL Certificates
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 :[email protected] * 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
/etc/ssl/certs/mysql/server-cert.pem: OK /etc/ssl/certs/mysql/client-cert.pem: OK
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
[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
Take a look at the log file to make sure no start-up errors
Now grab the pre-set password and login
cat /root/.mysql_secretmysql -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
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.phpnano /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>
The password can be up to 46 characters. You can generate a password [here].
$cfg['blowfish_secret'] = '[email protected]#@[email protected]+!ASt63$aB+es3zedu_ep$ey' /* Change to your own password */
Search for date.timezone and set it to your timezone. See [here] for a list of timezones.
date.timezone = UTCchkconfig httpd on service httpd start
Log in to https://webserver/phpmyadmin/ with username root and the password you set when configuring MySQL earlier.
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)
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!
[mysqld] ..... general_log=1 general_log_file=/var/log/mysql.log
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