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.
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
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
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
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
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 &
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`
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
Finally, it’s time to start your new database instance!
service mysqlzabbix start
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'
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%';
Finally, set your instance to run on startup.
chkconfig mysqlzabbix on