Optimise MySQL on CentOS v5 x86/64-bit

The final step in this MySQL series of posts (read the Install and Backup posts), is the regular optimisation of MySQL.

The my.cnf file has a lot of options that will enable you to customise your MySQL environment to the hardware available and the demand it receives. There are some very good performance monitoring scripts available that will run diagnostics against the MySQL server and offer advice on how to enhance your MySQL configuration.

I had MySQL databases running in one database environments and others in Shared Hosting with multiple environments, so I would set these scripts to run weekly and provide me with the necessary advice to continually optimise the performance.

First step, is to download the performance script and set the execute permissions:

• cd /home/scripts
• wget mysqltuner.pl
• chmod u+x mysqltuner.pl.pl

We can run the above script, and it will provide the necessary diagnostics. However, that is a manual task and we want to automate the script to run weekly and send us an email with the results:

• vi mysql_tuner_weekly.sh

• #!/bin/sh
• /home/scripts/mysqltuner.pl –user mysql-local –pass <Password> > /home/scripts/mysqltuner.log
• cat /home/scripts/mysqltuner.log | mail -s “MySQL Tuner Log” email@domain.com
• rm -rf /home/scripts/mysqltuner.log

The above script calls the mysqltuner.pl Perl script, providing the authentication to connect to the local MySQL instance. It will run the diagnostics, outputting the results to the mysqltuner.log file. This file is then emailed off to email@domain.com and the script then does housekeeping by remove the log file.

This script will not run on Windows, so if you want to optimise a Windows instance of MySQL, you need to use the following script:

• #!/bin/sh
• /home/scripts/mysqltuner.pl –host <RemoteServerFQDN> –port <RemoteMySQLPort> –forcemem <RemoteServerRAM> –user mysql-public –pass <Password> > /home/scripts/mysqltuner.log
• cat /home/scripts/mysqltuner.log | mail -s “<RemoteServerFQDN> MySQL Tuner Log” email@domain.com
• rm -rf /home/scripts/mysqltuner.log

I actually had a management server that had one script, connecting to all the Windows instances of MySQL and a script running locally on all the Linux instances.

Final step is to now schedule the script to run daily at 21:30:

• vi /etc/crontab

• 0 6 * * 0 root /home/scripts/mysql_tuner_weekly.sh

I would allow your MySQL server to run for a few days before you first run the script, so that MySQL can generate some stats for the scripts to analye. You will find that the first time it runs, you will get a lot of options to update in my.cnf, but after that, it die down.

Another performance script is available:

• wget http://www.day32.com/MySQL/tuning-primer.sh

However, it only ever ran it locally because the output to email was too messy and I couldn’t get clean results. However, it is very useful to keep and run when running heavy performance diagnostics on the local MySQL server.

Backup MySQL on CentOS v5 x86/64-bit

My previous post ran through the steps to install, configure and secure MySQL on CentOS. This post will run through some steps to keep your MySQL installation backed up.

First up, login as root and create a location to store local backups as well as a location to store our backup scripts:

• mkdir /home/backups
• mkdir /home/backups/mysql
• mkdir /home/scripts

We don’t need to reinvent the wheel and there are excellent backup scripts available with regular backup rotation features built in, freely available on the Internet:

• cd /home/scripts
• wget “http://sourceforge.net/projects/automysqlbackup/files/AutoMySQLBackup/AutoMySQLBackup
  VER 2.5/automysqlbackup-2.5.1-01.sh”
• mv automysqlbackup-2.5.1-01.sh mysql_backup_all_daily.sh
• chmod u+x *.sh

The last two commands above rename the backup script to something a little more friendly, and then set the required permissions for the script to be executed.

We can now customise the script to connect to our MySQL instance and back up the databases:

• vi mysql_backup_all_daily.sh

• USERNAME=mysql-local
• PASSWORD=<Password>
• BACKUPDIR=”/home/backups/mysql”
• MAILADDR=”email@domain.com

The above configurations will essentially back up the local MySQL instance to /home/backups/mysql and then send an email to you, informing you of the completion success/failure of the backup. The script has notes inside that will allow you to customise more features such as emailing you the backups, selecting databases to backup, etc.

Final step is to now schedule the script to run daily at 21:30:

• vi /etc/crontab

• 30 21 * * * root /home/scripts/mysql_backup_all_daily.sh

The script will retain a backup for every day, a week and a month, and will rotate these backups for you. Nice!

You can run this script from a remote backup server, but you will need to ensure you open the necessary firewall ports and use the mysql-public account for remote access.

Install MySQL on CentOS v5 x86/64-bit

I have built a few Linux web servers, running on CentOS with the LAMP design – the M being MySQL. Any server running database software needs to have security in mind, right from the install, configuration and use.

Here is my breakdown of the install, configuration and hardening that might be of use to others. See the following posts on how to install CentOS on Hyper-V to get a secure O/S environment setup.

Step one is to obviously get MySQL installed and started up:

• yum install -y mysql-server
• chkconfig –level 2345 mysqld on
• service mysqld start

MySQL is now installed and the service is started. Now we need to configure the MySQL my.cnf configuration file:

• vi /etc/my.cnf

• [client]
• port=3306
• socket=/var/lib/mysql/mysql.sock

• [mysqld]
• port=3306
• datadir=/var/lib/mysql
• socket=/var/lib/mysql/mysql.sock
• old_passwords=1
• default-storage-engine=INNODB
• skip-locking
• skip-bdb

• [mysqld-safe]
• log-error=/var/log/mysqld.log
• pid-file=/var/run/mysqld/mysql.pid

The above configuration is very basic and uses all the default values, but I will show you how to run tools that will enhance the configuration file for optimal performance.

If you intend to access this MySQL server remotely and you have the option available, it is often ideal to change hte [client] and [mysqld] ports from 3306 to something else to further reduce your attack surface.

Now restart the MySQL service to put the new changes in to effect:

• service mysqld restart

If the service fails to restart then you probably have a spelling mistake in your configuration, so use the following command to view the MySQL log file for help:

• vi /var/log/mysqld.log

Now we need to harden MySQL,  so lets get rid of any guest databases, user accounts:

• mysql –u root
• drop database test;
• use mysql;
• delete from mysql.user where user =”;
• delete from mysql.user where Host=’127.0.0.1′;
• delete from mysql.user where Host=’<ComputerName>’;

The last three commands will leave only ‘root’@'localhost’ as the only user who will be able to access MySQL; which does mean that no one can access your MySQL whilst you continue with your configuration.

The MySQL root user has full system access, and it is a commonly used in via brute force dictionary attacks, so we are going to rename it to mysql-local and give the account a password:

• update user set user=’mysql-local’ where user=’root’;
• update user set password=’<Password>‘ where user=’mysql-local’;
• grant all on *.* to  ‘mysql-local’@'localhost’ identified by ‘<Password>‘ with grant option;

I normally create two root/super-user accounts:

• mysql-local = a root user that can only be used locally on the MySQL server with terminal access, and not be used from a remote machine
• mysql-public = a root user that can only be used from a remote machine, and can not be used locally with terminal access.

If you don’t want anyone to access the root account from a remote machine, then you can ignore this step. Otherwise we can create our remote root user

• insert into user (Host,User,Password) values (‘%’,'mysql-public’,
  ‘<Password>‘);
• grant all on *.* to  ‘mysql-public’@'%’ identified by ‘<Password>’ with grant option;

The final step before finishing off, is to flush any existing permissions and apply the new changes:

• flush privileges;

Time to log off, restart MySQL (just for good measure) and verify we can re-login with the new mysql-local user account:

• exit;
• service mysqld restart
• mysql –u mysql-local -p<Password>
• exit;

The following command is very useful as it clears the history of all the work we have done above and we don’t want someone to login to the server and be able to view the history of the work we have done:

• cat /dev/null > ~/.mysql_history

And that is that. If you need remote access, then you will need to open TCP port 3306 in your firewall for incoming traffic. If you decide to change this port number (which you should if you can), then you will need to open up that port instead.

Please also note that MySQL is a Linux application, so it is case-sensitive. This applies to both the username, password, and databases!

Even though the Windows installation procedure for MySQL will differ, you can still apply the same configuration steps.