Gfilui

The short version of the problem is that, upon boot, the default mysql startup script starts an unneeded general instance of the server with default settings, followed by mysqld_multi starting the required instances correctly, resulting in too many server instances.

I'm on Ubuntu 14.04. Just upgraded the MySQL Server to version 5.7. I am aiming at running two instances of the MySQL server, one master and one slave, using the same binaries, but other settings being different.

The point of such a setup is to filter tables out of the replication process. Before replicating to a third external instance, controlled by a third party, I want to make sure that only certain tables show up in the binary logs. I have been using this method successfully for years.

After upgrading to MySQL 5.7 my setup looks like this:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass

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

[mysqld2]
basedir = /usr
server-id = 2
port = 3307
user = mysql
pid-file = /var/run/mysqld/mysqld_blackhole_slave.pid
socket = /var/run/mysqld/mysqld_blackhole_slave.sock
datadir = /var/lib/mysql_blackhole_slave
log-error = /var/log/mysql_blackhole_slave/error.log
...

It is interesting to note that MySQL creates the /var/run/mysqld directory when the server is started, and deletes it when the server is shut down (and no other process is using it).

In my /etc/init.d I have two related startup scripts: mysql and mysqld_multi.

When the mysql script runs, it checks the my.cnf file, finds that there is no [mysqld] group, and therefore, starts a server instance with default settings. It creates the /var/run/mysqld directory, as mentioned above, and gets everything going. At this point I have a server instance running with default values that I did not specify.

Then comes the mysqld_multi script. It looks for groups of [mysqldN] in my.cnf, and starts server instances accordingly. This will start two additional instances according to my specifications. At this point I have three server instances running instead of the two I requested in the configuration file.

If I remove the mysql script from /etc/init.d, the default instance will not start, but neither will the other two because:

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't
exists. (sic)

Please note, it does not say it was unable to create the directory. It seems to assume the directory should exist at this point, but due to the removal of the mysql startup script, it never gets created. I believe this process does not attempt to create the directory. If it did attempt, one would assume a permission problem. I have disabled apparmor, and I have tried running these processes as root to rule out permission related issues.

I can of course do:

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

and then start mysqld_multi. This works fine, but is a very unorthodox way of starting up your system.

So I am wondering, is there not a proper way of starting mysqld_multi on this present system without hacking the process?