1

I have just installed Ubuntu 17.10 and I want to run MySQL with multiple instances. I've read the MySQL documentation about this (https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html) but I still don't understand.

I have disabled the apparmor profile for mysql and added these lines to /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld@replica01]
datadir=/var/lib/mysql-replica01
socket=/var/lib/mysql-replica01/mysql.sock
port=3307
log-error=/var/log/mysql/replica01.log

[mysqld@replica02]
datadir=/var/lib/mysql-replica02
socket=/var/lib/mysql-replica02/mysql.sock
port=3308
log-error=/var/log/mysql/replica02.log

Is it enough to copy /lib/systemd/system/mysql.service to /lib/systemd/system/[email protected] or do I need to modify it somehow? Because if I just copy it and run

systemctl start mysql@replica01

It will just start the default MySQL instance on port 3306. And if I start mysql@replica02 it won't start since it tries to start the same instance and I just get a bunch of

Unable to lock ./ibdata1 error: 11

What am I missing here? The only thing I can find online about this are old posts before this was supported in .deb-files. But since version 5.7.19 this should work and as of writing this the version is 5.7.21 that gets installed from Ubuntus repo.

I have an old installation on a Debian machine that is using the mysqld_multi but that's not supported anymore if I understand the documentation right.

2 Answers 2

1

Above solution works fine, but to make it compatible with MySQL reference manual solution (single options file and --defaults-group-suffix) /lib/systemd/system/[email protected] should look like this:

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStart=/usr/sbin/mysqld --defaults-group-suffix=@%I
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

BTW, there is no /lib/systemd/system/[email protected] in Ubuntu by default, so it has to be added manually (and it doesn't work if you just copy /lib/systemd/system/mysql.service to /lib/systemd/system/[email protected]).

0

I don't know if this is the right way but this is how I did it. Please leave a comment if there is something wrong here.

My /lib/systemd/system/[email protected] looks like this:

[Unit]
Description=Multi Instance MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/mysql/mysql.conf.d/my_%I.cnf
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

I then created a separate config file for each instances in /etc/mysql/mysql.conf.d with the names my_replica01.cnf etc. I just copied the main config file from MySQL and changed the paths and port number. Then I created the data folder for each instance like this:

mkdir /var/lib/mysql-replica01
mkdir /var/lib/mysql-replica02
chown -R mysql:mysql /var/lib/mysql-replica*

etc.

Then I initialized the instance with:

mysqld --user=mysql --initialize --datadir=/var/lib/mysql-replica01

Then I had to reload systemd after I added the [email protected]-file

systemctl daemon-reload

Then just enable the instances with:

systemctl enable mysql@replica01
systemctl enable mysql@replica02

Now the instances starts automatically when the server boots. To start them manually you just run:

systemctl start mysql@replica01

EDIT:

Just to add, I had to connect to the instance using the socket like this:

mysql -S /var/lib/mysql-replica01/mysql.sock -p

And then change the password etc. for the root account. For some reason the .mysql_secret-file wasn't created as it says in every documentation I could find.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .