Home > Installation > Setting up a second MySQL instance

Setting up a second MySQL instance

This post describes how to set up a second MySQL instance on a RHEL type Linux with MySQL installed from MySQL RPM packages. Although most things are the same for other Linux distro’s there will be some differences, mainly in the startup scripts.

Sometimes it can be useful to install a second MySQL instance on a server. For example if the database it contains replicates from a different master than the database on the first instance. The downside is of course that you don’t get to share resources like memory so you end up with two separate buffer pools instead of one shared one. But separate control over the instances is worth something as well. Being able to stop and start one instance while the other one continues to operate can be very useful.

Installing a second instance is relatively easy. MySQL used to provide the MySQL Instance Manager for stopping and starting MySQL multiple instances but this product has been deprecated in 5.1 and removed altogether removed in 5.5. MySQL Instance Manager relied on sections in a single configuration file for each instance. I like having separate configuration files so that’s the type of setup I will describe. It’s a matter of personal taste but with separate configuration files you can’t accidentally mess up the configuration for all instances at the same time. Anyway, let’s go through the steps necessary to set up two instances (qa and uat) on the same server. Start by creating two directories: /etc/mysql_qa and /etc/mysql_uat. Make sure all new directories and files have mysql as owner. Create a copy of the existing my.cnf file in both directories. There are a couple of settings that need to be different between the two instances. I have put the setting for the qa instance first and the setting for the uat instance in the following line as a comment.

socket = /var/run/mysqld_qa/mysqld.sock
# /var/run/mysqld_uat/mysqld.sock

# Obviously choose another port for the second instance like 3406

datadir = /var/lib/mysql_qa
# /var/lib/mysql_uat

log_error = /var/log/mysql_qa/error.log
# /var/log/mysql_uat/error.log

server-id = 1
# server-id = 2

log_bin = /var/log/mysql_qa/mysql-bin.log
# log_bin = /var/log/mysql_uat/mysql-bin.log

!includedir /etc/mysql_qa/conf.d/
# /etc/mysql_uat/conf.d/

Obviously there are more settings that would need the same treatment if you have them enabled like the relay log, the general query log, the slow query log, etc. In all cases you need to create the necessary directories and make the mysql user the owner of those directories. Also keep in mind that the resources will not be shared so if you assign 80% of the available memory to the buffer pool for both instances you’re in for a lot of swapping.

Now we need to modify the /etc/init.d/mysqld script a bit. I always create a copy per instance and name the copies after the instances so mysqld_qa and mysqld_uat in this case. The main changes we need to make to these scripts is to make sure they use the appropriate configuration file because it seems like the gents at MySQL were not so picky about that. First of all we need to specify per script where the configuration file is located:

# config=/etc/mysql_uat/my.cnf

Then we need to make sure each call to the MySQL executable actually uses this file with the –defaults-file=${CONFIG} option. The following lines need to be modified. The modifications are shown in bold. I do apologize for not including line numbers but I am not entirely sure that the line numbers will be the same over all versions of MySQL.

parse_server_arguments `$print_defaults --defaults-file=$config 
   $extra_args mysqld server mysql_server mysql.server`

parse_manager_arguments `$print_defaults --defaults-file=$config 
   $extra_args manager`

$manager --defaults-file=$config --user=$user --pid-file=$pid_file 
   >/dev/null 2>&1 &

$bindir/mysqld_safe --defaults-file=$config --datadir=$datadir 
   --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

# Try to find appropriate mysqld process
mysqld_pid=`pidof $sbindir/mysqld`
# Small modification to make sure that the pid found has the configuration file
if test ! -z $mysqld_pid; then
  mysql_cnt=`ps -f -p$mysqld_pid | grep "\-\-defaults-file=$conf"`
if test -z $mysql_cnt ; then
  if test "$use_mysqld_safe" = "0" ; then

Now we need to create empty databases by calling:

mysql_install_db --datadir=/var/lib/mysql_qa -uroot
mysql_install_db --datadir=/var/lib/mysql_uat -uroot

We can now start up the instances individually by executing:

/etc/init.d/mysqld_qa start
/etc/init.d/mysqld_uat start

The final step is to make sure the instances start up automatically when the server is rebooted. For this we use the chkconfig command. Optionally you can disable the original MySQL if you do not want that instance to run anymore:

chkconfig --add mysqld_qa
chkconfig --add mysqld_uat
chkconfig --del mysqld

And that’s it. We now have two fully functioning MySQL instances running side by side. Keep in mind that if you’re trying to connect to the second instance on port 3406 the MySQL client has a tendency to ignore the port number (-P3406) in case a socket is used. You can override this by specifying –protocol=tcp on the command line or by removing the socket setting from the client section in the /etc/my.cnf file and replacing it with protocol=tcp.

One final note, since we modified the /etc/init.d/mysqld script it is important to keep in mind that this has an impact on performing upgrades of MySQL. If you upgrade MySQL by uninstalling the existing RPM’s and installing the new ones the installation process will install another default /etc/init.d/mysqld and configure it to start automatically.

Categories: Installation Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: