MySQL – Master / Slave Replication and MySQL Multi – Part One
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 :).