When you enter the field of multiple MySQL instances on one physical server you might run into the trouble. Lack of documentation, cryptic bugs, and insane settings is something you need to get used to. I almost quit a few times in the process and only my habit of trying unusual things when everything logical failed kept me up to the solution.
For starters, let’s say I worked with MySQL version 5.7. It’s important because of a few issues on which I spent quite a lot time solving might be fixed in the newer versions.
Recently I took the job of restructuring our servers’ architecture and found out that some of them are underused. For example, one server is used only from time to time while we are testing new software features. That server required the same setup as the production server so it was not possible to install fresh MySQL server to be used as a slave for replication purpose.
While playing with the idea of setting up two MySQL instances I thought that it should be quite straightforward as with most other servers where you copy the server’s binaries and just pay attention to run each instance on a different port. However, I learned that MySQL has its own method of running multiple instances which suppose to be easier to set up and run. The only requirement was to change the configuration file and you are good to go. And it was easy as that until I tried to start servers 😉
Ok, let’s see the multiple server configuration.
[mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock port = 3307 datadir = /data/mysql/mysql1 [mysqld2] user = mysql pid-file = /var/run/mysqld/mysqld2.pid socket = /var/run/mysqld/mysqld2.sock port = 3308 datadir = /data/mysql/mysql2 .... [mysqldN] user = mysql pid-file = /var/run/mysqld/mysqldN.pid socket = /var/run/mysqld/mysqldN.sock port = N datadir = /data/mysql/mysqlN
If you want to keep your current MySQL instance, you just need to rename [mysqld] section name to [mysqld1] because the multiple servers setup requires a different naming convention. Everything else will work fine. As you can see from example configuration you need to provide different pid file, socket file and data dir for each instance which is also logical. You don’t need to create files manually. Just keep in mind directory’s permissions because the mysql user should be able to create required files and directories. Some people like to initialize a new server with:
mysqld --initialize --user=mysql --datadir=/home/mysql/mysqlN
For managing multiple instances MySQL has a tool called mysqld_multi. As per documentation, this tool should be used to start, stop and monitor multiple instances, instead of traditional methods like service, systemctl or init scripts. Let see few examples:
$ mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running $ mysqld_multi stop $ mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is not running $ mysqld_multi start $ mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is running MySQL server from group: mysqld2 is running $ mysqld_multi stop 1 $ mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld1 is not running MySQL server from group: mysqld2 is running
If you have more than 2 instances you can start/stop only some of them like this:
$ mysqld_multi stop 1,5,8 $ mysqld_multi start 1,5,8
However, when I first run mysqld_multi I got nothing. Quick “ps awux | grep mysqld” revealed that no server is running. The tool itself is not verbose at all and even if something went wrong you won’t get any error message. The key issue is the configuration file. As you probably know MySQL changed the location of the configuration file to:
/etc/mysql/mysql.conf.d/mysqld.cnf
But if you add your multiple MySQL instances configuration to this file it simply won’t work. I couldn’t find anything useful on the internet regarding this. Only by pure luck, I copied the same configuration to /etc/my.cnf and it magically started to work. But it’s not the end of the problems. Let’s continue …
If you want to access specific MySQL instance via console you need to specify particular socket file related to that instance:
mysql -u root -p -S /var/run/mysqld/mysqld1.sock
This is important because you won’t be able to stop instances with mysqld_multi tool before you grant some user that permission:
mysql> grant shutdown on *.* to multi_admin@localhost identified by 'multipass'; mysql> flush privileges;
And then you need to add the following to the configuration:
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass
Logically, after the debacle of the original configuration file, I added that section to /etc/my.cnf. But to my surprise, it didn’t work. Now I was experienced with MySQL weirdness so I immediately added the section to the original configuration file and it worked.
I’m still not sure what was the issue but I don’t have time to dig deeper and to debug MySQL toolset. It’s important that everything finally works fine so I was able to setup MySQL slave for replication on one of the instances.