MySQL single machine multiple instance sharing

Achieving multiple instances on a single machine can be easily achieved through Docker. Here we mainly share the process of using the built-in tool mysqld_multi and two pitfalls. I hope this article can help everyone.

1. Install MySQL

System: Ubuntu 16.04LTS (xenial)
Version: Percorna Server 5.7.21-20
Installation: Official website tutorial

2 . mysqld_multi configuration file

Create a new file /etc/mysql/multi_server.cnf

[mysqld_multi]
# If the MySQL service is not started through mysqld_safe, this should be commented out
#mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = 123456
[mysqld1]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
[mysqld2]
user = mysql
pid-file=/var/run/mysqld/mysql.pid2
socket=/var/run/mysqld/mysqld. sock2
port = 3307
basedir = /usr
datadir = /var/lib/mysql2

3. Start the service

View the instance running
mysqld_multi -- defaults-file=/etc/mysql/multi_server.cnf report

Start service
mysqld_multi --defaults-file=/etc/mysql/multi_server.cnf start

Because of the general situation, We have already installed the first one, and the root user has also set it up, so it is unlikely to cause problems.
Possible problems with mysql2:

FATAL ERROR: Tried to start mysqld under group [mysqld2 ], but no data directory was found or could be created. data directory used: /var/lib/mysql2

MySQL5.7 The command line for adding a new instance is as follows

mysqld --datadir= /var/lib/mysql2 --initialize --initialize-insecure ...
Official website link

Look at the 343 lines of the /usr/bin/mysqld_multi script again

            print "\n\nInstalling new database in $datadir\n\n";
            $install_cmd="/usr/bin/mysqld ";
            $install_cmd.="--initialize ";
            $install_cmd.="--user=mysql ";
            $install_cmd.="--datadir=$datadir";

A quick look There seems to be no problem. In fact, I use the command line to execute

which mysqld
得到的结果
/usr/sbin/mysqld

So after modifying this script, it can start normally.
Of course, if it still doesn’t work, you may have to consider the user permissions
If you use Ubuntu, you may also want to check whether apparmor has permission restrictions.

Continuing from the above, if it is started like this, the mysql2 instance will randomly create a random password for the root user, causing subsequent clients to be unable to log in to the instance. .If you need to retrieve the password, you can only use error_log
At this time, you also need to modify the script, add the parameter "--initialize-insecure" to the script, pay attention to the space between the parameters.
After establishing the connection, then Execute

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

4. Follow-up operations

Add permissions to the root user

GRANT ALL PRIVILEGES ON . TO root@'%' IDENTIFIED BY "123456";
flush privileges;

Add shutdown permission to the mysqld_multi management user multi_admin in each instance

GRANT SHUTDOWN ON . TO multi_admin@localhost IDENTIFIED BY 'password';

If the instance cannot be effectively shut down through mysqld_multi stop, you can directly shut down through mysqladmin

If the instance fails to start due to improper operation, you can view the error log through the command

tail -30 /var/log/mysql/error.log

FAQ

2018 -02-22T02:02:28.227868Z 0 [ERROR] /usr/sbin/mysqld: Can't create/write to file '/var/run/mysqld/mysql.pid3' (Errcode: 13 - Permission denied)
2018-02-22T02:02:28.227889Z 0 [ERROR] Can't start server: can't create PID file: Permission denied

At this time, you can see the /var/run/mysqld folder As follows, delete all the files and restart again

-rw-r--r-- 1 root root 0 Feb 22 02:01 mysqld.pid
-rw-r--r -- 1 root root 0 Feb 21 15:35 mysqld.pid2
srwxrwxrwx 1 mysql mysql 0 Feb 22 02:02 mysqld.sock=
srwxrwxrwx 1 mysql mysql 0 Feb 22 02:02 mysqld.sock2=
-rw------- 1 mysql mysql 6 Feb 22 02:02 mysqld.sock2.lock
srwxrwxrwx 1 mysql mysql 0 Feb 22 02:02 mysqld.sock3=
-rw---- --- 1 mysql mysql 6 Feb 22 02:02 mysqld.sock3.lock
-rw------- 1 mysql mysql 6 Feb 22 02:02 mysqld.sock.lock
-rw-r --r-- 1 root root 0 Feb 22 02:01 mysql.pid2
-rw-r--r-- 1 root root 0 Feb 22 02:01 mysql.pid3

Related recommendations:

MySQL single-machine multi-instance solution deployment

MySQL single-machine multi-instance configuration

LBOMP – MySQL single-machine backup monitoring tool

The above is the detailed content of MySQL single machine multiple instance sharing. For more information, please follow other related articles on the PHP Chinese website!