Description:
I needed to get multiple instances of mysql running within Laravel Homestead in order to test replication. I tried every permutation of instructions for setting up multiple instances of mysql at these links and an exhaustive search of related links:
https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysq...
https://www.howtoforge.com/one_machine_mysql_replication
http://www.bitbull.ch/wiki/index.php/MySQL_Replication_HowTo
https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
mysql_install_db is deprecated and mysqld –initialize is not adequately documented to show how to add an additional instance if the first instance's data directory already exists. It's also not clear if mysql will automatically create the new instance(s) when mysqld is restarted. Also commenters pointed out that mysqld_multi doesn't work on systems with systemd. When I realized that's what I had, it led me to the mysql systemd documentation.
There are likely typos on https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html for example the block after the line "Debian platforms:" should probably say:
[mysql@replica01] instead of [mysqld@replica01]
With so many permutations of options, possible typos, and the complexity of apparmor and systemd thrown in, I wasn't able to get it working even after spending an entire day on it.
Without a working example of multiple mysql instances under systemd as proof, I propose that if someone at MySQL can't get it to work then it's likely not supported as of mysql 5.7.17.
How to repeat:
Install the latest version of Laravel Homestead with default settings (or use an off-the-shelf snapshot of Ubuntu 16.04):
https://laravel.com/docs/5.4/homestead
vagrant up --provision
vagrant ssh
Follow the systemd instructions at:
https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
A summary is as follows:
```
systemctl status mysql
sudo cp /lib/systemd/system/mysql.service /lib/systemd/system/mysql@.service
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# add the following to the bottom:
[mysql@replica01]
datadir=/var/lib/mysql-replica01
socket=/var/lib/mysql-replica01/mysql.sock
port=3307
log-error=/var/log/mysql/replica01.log
# update apparmor to allow access to those paths
sudo nano /etc/apparmor.d/local/usr.sbin.mysqld
# add the following lines:
# Allow pid, socket, socket lock file access
/var/run/mysqld/mysqld-replica01.pid rw,
/var/run/mysqld/mysqld-replica01.sock rw,
/var/run/mysqld/mysqld-replica01.sock.lock rw,
/run/mysqld/mysqld-replica01.pid rw,
/run/mysqld/mysqld-replica01.sock rw,
/run/mysqld/mysqld-replica01.sock.lock rw,
# Allow error msg and charset access
/usr/share/mysql-replica01/ r,
/usr/share/mysql-replica01/** r,
# Allow data dir access
/var/lib/mysql-replica01/ r,
/var/lib/mysql-replica01/** rwk,
# Allow data files dir access
/var/lib/mysql-files-replica01/ r,
/var/lib/mysql-files-replica01/** rwk,
# Allow keyring dir access
/var/lib/mysql-keyring-replica01/ r,
/var/lib/mysql-keyring-replica01/** rwk,
# Allow log file access
/var/log/mysql-replica01.err rw,
/var/log/mysql-replica01.log rw,
/var/log/mysql/ r,
/var/log/mysql/** rw,
sudo systemctl daemon-reload
sudo systemctl -f stop mysql
sudo systemctl -f stop mysql@replica01
sudo systemctl start mysql
sudo systemctl start mysql@replica01
# the following shows nothing listening on port 3307:
ss -l -p -n
```
Suggested fix:
Please update the documentation at https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html with a working example.
I'm posting my notes here to show that a sufficient hackathon was attempted to show due diligence but that the fix is beyond my humble abilities. Hopefully these notes help someone and they can leave a comment with a working example.
Thanks in advance for any help you can provide!
```
# replication (systemd with mysql 5.7+) https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# documentation after line that says "Debian platforms:" is incorrect and should say [mysql@replica01] instead of mysqld
systemctl status mysql
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
sudo cp /lib/systemd/system/mysql.service /lib/systemd/system/mysql@.service
sudo systemctl daemon-reload
sudo systemctl -f stop mysql
sudo systemctl -f stop mysql@replica01
sudo systemctl start mysql
sudo systemctl start mysql@replica01
# test manually with (tail in separate console):
tail -f /var/log/mysql/error.log
sudo killall mysqld
sudo -u mysql mysqld --defaults-group-suffix=@replica01
# shows which ports are open:
ss -l -p -n
sudo cp /etc/mysql/my.cnf /etc/mysql/my1.cnf
sudo mysql_upgrade --defaults-file=/etc/mysql/my1.cnf
# https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysq...
# https://www.howtoforge.com/one_machine_mysql_replication
# http://www.bitbull.ch/wiki/index.php/MySQL_Replication_HowTo
# https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# add the following lines, double checking that they are similar to the [mysql] section:
[mysqld1]
user = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket = /var/run/mysqld/mysqld1.sock
port = 3307
datadir = /var/lib/mysql1
# make a directory for storing the slave db data
sudo mkdir /var/lib/mysql1
sudo chown -R mysql /var/lib/mysql1
sudo chgrp -R mysql /var/lib/mysql1
# install new mysql instance
# show configuration for new instance
my_print_defaults --defaults-file=/etc/mysql/my.cnf mysqld1
# restart mysql
sudo service mysql restart
# or
sudo /etc/init.d/mysql restart
# connect to new mysql instance
mysql --port=3307 --host=127.0.0.1
# or
mysql -uhomestead -psecret -S /var/run/mysqld/mysqld1.sock
# update apparmor to let mysql write
# https://dba.stackexchange.com/a/139844
sudo nano /etc/apparmor.d/local/usr.sbin.mysqld
# reload apparmor
sudo invoke-rc.d apparmor reload
# old https://www.howtoforge.com/one_machine_mysql_replication
# adding slave instance of mysql for your OS:
arch
cat /etc/*-release
mysql --version
# install mysql in separate directory
# download "Linux - Generic" tar like (mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz) into /opt from https://downloads.mysql.com/archives/community/
wget https://downloads.mysql.com/archives/get/file/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
sudo tar xzvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -C /opt
cd /opt/mysql-5.7.17-linux-glibc2.5-x86_64
./configure --prefix=/usr/local/mysql-slave
# set up config for master-slave
sudo nano /etc/mysql/my.cnf
# or (most likely for Ubuntu)
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
sudo nano /etc/mysql/conf.d/mysql.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# optional but recommended:
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# add replication user
mysql
CREATE USER 'homestead_slave'@'%' IDENTIFIED BY 'secret';
GRANT REPLICATION SLAVE ON *.* TO 'homestead_slave'@'%';
# to delete the user:
DROP USER IF EXISTS 'homestead_slave'@'%';
```
