MySQL Replication – Part One: Multi

In this two-part series we will provide some food for thought on MySQL, and strategies for Master / Slave replication.  First off, the discussion and examples given are based on a CentOS Linux install of MySQL.

The particulars are as follows:

• Operating System: CentOS 6.4
• Database Engine: MySQL 5.5

The first thing we will discuss is setting up MySQL to run multiple instances on one server.  The reason for this will be discussed in more detail in Part Two.  Each instance of MySQL can host multiple databases.  Master/Slave replication is discussed in MySQL Replication – Part Two: Master / Slave.

The premise

Let’s say we have three Linux servers.  Each of those has a standard MySQL engine running and supports multiple databases.  We wish to add two new MySQL database engines on each server.  In Part Two, we will configure those extra database engines as replication slaves.

The single MySQL engine is listening on the standard port 3306.  We are assuming that this MySQL engine is in production and contains data.

We now want to add two MySQL engines to this sever, listening on different ports.  We selected 33062 and 33063.  The original MySQL engine maintains its configuration and data.

MySQL ships with a script called “mysqld_multi” which makes a proposition like this relatively simple.  A man page is available here.

The mysqld_multi script

Here are the steps involved in setting up the extra engines:

• Create a MySQL user for the mysqld_multi script.  This user only needs shutdown privileges
• Edit the /etc/my.cnf file to specify configuration parameters and options
• Create the necessary directory structure to support the new MySQL engines
• Copy the mysql database raw data files to the data directory for the new engines

Once everything is configured, we start all three instances, and verify that they are indeed running.

Create a MySQL user for mysqld_multi

Nothing complicated here.  You can name the user anything you want to, and set the password to anything, but it is standard to call the user “multi_admin”.  The password can be “secret”.  Login to MySQL as the admin user, and execute the following command:

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' 
mysql> IDENTIFIED BY 'secret';

Edit /etc/my.cnf

Basically, we empty the [mysqld] section, and create a [mysqldN] section for each MySQL instance, so N = {1,2,3}.  In addition, we create the [mysld_multi] section, and we leave the [mysqld_safe] section as is.

The standard (as installed) /etc/my.cnf typically looks like this

[mysqld]
# bind-address=127.0.0.1
# local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port=3306

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The items to note here are, in the [mysqld] section, the data directory where MySQL keeps its raw data, the socket file for direct local connections, the user account under which MySQL runs, and the port number for TCP connections.

The new /etc/my.cnf file will have the following additional sections:

• [mysqld_multi] which handles the mysqld_multi script
• [mysqld1] .. the first database engine .. we will keep that configured as the original one
• [mysqld2] .. the second database engine
• [mysqld3] .. the third database engine

Furthermore, the [mysqld] section will be mostly stripped of its entries.   The expanded /etc/my.cnf looks like this

[mysqld]
symbolic-links=0

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe # location MySQL binary 
mysqladmin = /usr/bin/mysqladmin 
log = /var/log/mysqld_multi.log 
user = multi_admin 
password = secret

[mysqld1] 
port = 3306
datadir = /var/lib/mysql 
socket = /var/lib/mysql/mysql.sock 
user = mysql 
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid 

[mysqld2] 
port = 33062
datadir = /var/lib/mysql-databases/mysqld2 
socket = /var/lib/mysql-databases/mysqld2/mysql.sock 
user = mysql 
log-error = /var/log/mysql2.log
pid-file = /var/lib/mysql-databases/mysqld2/mysqld.pid 

[mysqld3] 
port = 33063
datadir = /var/lib/mysql-databases/mysqld3 
socket = /var/lib/mysql-databases/mysqld3/mysql.sock 
user = mysql 
log-error = /var/log/mysql3.log
pid-file = /var/lib/mysql-databases/mysqld3/mysqld.pid 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

It is important to note that each engine needs to have its own pid file, so they are specified in the [mysqldN] sections.

Create the directory structure

Notice that the [mysqld1] options are configured like the original standard [mysqld] entry.  The two new database engines however require the creation of the /var/lib/mysql-databases/ directory, and the subdirectories ./mysqld2 and ./mysqld3 underneath it.

# mkdir /var/lib/mysql-databases
# mkdir /var/lib/mysql-databases/mysqld2
# mkdir /var/lib/mysql-databases/mysqld3
# chown mysql:mysql /var/lib/mysql-databases -R

And of course, these new directories have to be owned by the mysql user.

Copy the mysql raw database files

Without a mysql database, the new engines will not start.  The simplest solution is to copy the raw data files from /var/lib/mysql/mysql to the two new data directories.

# cp -a /var/lib/mysql/mysql /var/lib/mysql-databases/mysqld2/ 
# cp -a /var/lib/mysql/mysql /var/lib/mysql-databases/mysqld3/

Copying the “original” mysql database files ensures that all logon credentials from the original are duplicated for the two new database engines.

Starting, Stopping and Connecting

Now that everything is configured, we can start the new databases.  If we have not stopped the mysqld service, our original database engine is still running.  The mysqld_multi script can report on its status.

# mysqld_multi report
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running

Let’s start all database engines ..

# mysqld_multi start
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running

The three most used options are “start” “stop” and “report”.  Optionally, we can add a number, so mysqld_multi stop 2 will stop the second engine.

# mysqld_multi stop 2
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is running

Note that mysqld_multi stop/start without a number will stop/start all instances, including the default engine. Just to convince ourselves that we have indeed three instances running ..

# mysqld_multi start
# ps -e | grep mysql
 6357 pts/3    00:00:00 mysqld_safe
 6364 pts/3    00:00:00 mysqld_safe
 6378 pts/3    00:00:00 mysqld_safe
 6627 pts/3    00:00:00 mysqld
 6640 pts/3    00:00:00 mysqld
 6646 pts/3    00:00:00 mysqld

Connecting

On the local host, connect by specifying the port number.

# mysql -u root --port=33062 --protocol=tcp

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.02 sec)

Note that the “default” connection will go to the first database engine, since we kept the [mysqld1] parameters congruent with the default.

# mysql -u root

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MS_test            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Conclusion

We configured two additional MySQL engines on an existing Linux box.  We kept the “original” engine, databases etc. intact.

This setup will be the basis of the Master / Slave replication deployment in Part Two.  Stay tuned :).