Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of stand-alone and multi-instance in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Multiple instances on a single machine can be easily achieved through Docker. The main purpose here is to share the process of using the built-in tool mysqld_multi and two potholes.

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/mysql23. Start the service

View the instance running

Mysqld_multi-defaults-file=/etc/mysql/multi_server.cnf report

Start the service

Mysqld_multi-defaults-file=/etc/mysql/multi_server.cnf start

Because in general, the first one has been installed by us and set up by root users, it is unlikely to go wrong.

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

The command line for the new instance of MySQL5.7 is as follows

Mysqld-datadir=/var/lib/mysql2-initialize--initialize-insecure...

Official website link

Look at / usr/bin/mysqld_multi script 343 lines up and down

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"

At first glance, there seems to be no problem. In fact, execute it on the command line.

Results obtained by which mysqld / usr/sbin/mysqld

So after modifying the script, it can start normally.

Of course, if it doesn't work, you may want to consider the issue of user rights.

If you use Ubuntu, you may also want to check whether the permissions of apparmor are restricted.

Then, if you start it like this, the mysql2 instance will randomly create a random password for root users, causing subsequent clients to fail to log in to the instance. If you need to retrieve the password, you can only use error_log.

At this point, you also need to modify the script, add the parameter "--initialize-insecure" to the script, and pay attention to the spaces between the parameters.

After the connection is established, execute the

Mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' new_password';4. Subsequent operation

Add permissions to root users

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

Flush privileges

Add shutdown permissions to the mysqld_multi administrative 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 shutdown directly through mysqladmin.

Later, if the instance cannot be started due to improper operation, you can view the error log through the command.

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

common problem

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 point, you can see that the / var/run/mysqld folder is as follows. Delete all the files and restart them.

-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 is all the contents of the article "sample Analysis of multiple instances on a single Machine in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.