Running Multiple MySQL Instances on CentOS 6 / RHEL 6

There are many reasons why a server admin may want to run multiple instances of MySQL on the same physical server. These could include consolidating physical servers, increasing uptime per product, or to fully utilize existing hardware. In our case, we want to set up a separate MySQL instance to house Zabbix database. Zabbix is an open source systems monitoring platform. We want this database to remain online as much as possible, even when we are performing maintenance to our main MySQL database.

Spinning up a separate MySQL instance isn’t difficult at all. We will walk you though the steps you need to take and files you need to modify. As always, back up your system before starting. We will not be held responsible for any data loss or corruption to your system. Throughout the document, we will be referring to the new database as “mysqlzabbix”. Our server is running CentOS 6.5 x64 with MySQL 5.6.10 x64.

Step 1
To begin, we’ll need to create a data directory for the new instance and make sure the folder is owned by the mysql user.

mkdir /var/lib/mysqlzabbix
chown mysql:mysql /var/lib/mysqlzabbix

01

Step 2
Next, make a new copy of the MySQL configuration file. In our case, we are calling it “myzabbix.cnf”. After it’s copied, open it for editing.

cp -p /etc/my.cnf /etc/myzabbix.cnf
vim /etc/myzabbix.cnf

02

Step 3
This will be the core configuration of your MySQL instance. You will need to update the data directories, and error log location. In addition, pick a port number you would like to run your MySQL instance on. For our configuration, we started with the default configuration file and modified it to our liking.

[mysqld]
datadir=/var/lib/mysqlzabbix
innodb_data_home_dir=/var/lib/mysqlzabbix
user=mysql
port=3310
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqldzabbix.log

03

Step 4
Next, make a copy of the startup script. We will be making several edits to this file. First, find and replace all occurrences of “my.cnf” with “myzabbix.cnf”. There will be quite a few.

cp -p /etc/init.d/mysql /etc/init.d/mysqlzabbix
vim /etc/init.d/mysqlzabbix

04

Second, locate the line that begins with “$bindir/mysqld_safe”. In our init script, it is line 283. Change it to the code below. This will point it to our new configuration file.

$bindir/mysqld_safe --defaults-file=/etc/myzabbix.cnf --socket="$datadir"/myzabbix.sock --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

05

The last edit to this file will be a flag to allow MySQL to read our new configuration file when parsing server arguments. You’ll want to find the “parse_server_arguments” line (around line 256)and change it to:

parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server -c/etc/myzabbix.cnf`

08

Step 5
Once all the init script changes are in place, it’s time to initialize the new data directory with a clean database.

mysql_install_db --datadir=/var/lib/mysqlzabbix --defaults-file=/etc/myzabbix.cnf --user=mysql

06

Step 6
Finally, it’s time to start your new database instance!

service mysqlzabbix start

07

Step 7
Once your database starts, make sure to set your root password. Pick a nice complex secure one.

mysqladmin -u root -h127.0.0.1 -P3310 password 'zabbixpassword'

09

Step 8
Log in to your database to confirm it’s running and double check the port it’s operating on.

mysql -uroot -h127.0.0.1 -P3310 -p
<input password>
show global variables like '%port%';

10

Step 9
Finally, set your instance to run on startup.

chkconfig mysqlzabbix on

11

Leave a Reply