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

This article mainly introduces how to install multiple mysql instances in mysqld_multi, which is very detailed and has a certain reference value. Friends who are interested must finish reading it!

Install multiple mysql instances on one machine, in addition to compiling and installing each instance in a different directory, specify a different port, socket, configuration file, installation directory, etc. You can also use the mysqld_multi method.

Mysqld_multi is a tool designed by mysql to manage mysql instances of different ports and different socket. It can start and stop multiple mysql instances at the same time, or it can view the status of those instances.

Mysqld_multi checks each set of configurations starting with [mysqldN] in the configuration file my.cnf (or the configuration file specified by the-defaults-file parameter) (N here is a number). This N is used by mysqld_multi to distinguish each mysql instance. The usage is as follows:

Mysqld_multi start | stop | reload | report N1, N2, N3, … / / reload is equivalent to stop and start.

The information required by mysqld_multi is recorded under the [mysqld_multi] group in the configuration file my.cnf.

Note that in order to manage all mysql instances with mysqld_multi statistics, the administrative account must exist on all mysql instances and have the same password.

Here are the tests I did and the installation steps:

1. Write the configuration file my.cnf as follows:

[root@CentOSMysql1 etc] # cat my.cnf

[mysqld_multi]

Mysqld=mysqld_safe

# means to start mysql with mysqld_safe

Mysqladmin=/usr/local/mysql/bin/mysqladmin

# specify the path to the mysqladmin tool

Log=/usr/local/mysql/mysqld_multi.log

# specify the log file of mysqld_multi

User=root

Pass=123456

# specify the user and password to use the mysqld_multi tool

[mysqld3307]

# specify some configuration parameters for instance number 3307

Datadir=/opt/data3307

Port=3307

Socket=/tmp/mysql.sock3307

[mysqld3308]

# specify some configuration parameters for instance number 3308

Datadir=/opt/data3308

Port=3308

Socket=/tmp/mysql.sock3308

[mysqld56]

# specify some configuration parameters for instance number 56

Basedir=/usr/local/mysql56/

Datadir=/opt/data56

Socket=/tmp/mysql.sock56

Port=3310

2. Prepare to work before installation

(1) check whether there are mysql users and mysql groups

[root@CentOSMysql1 etc] # cat group | grep mysql

[root@CentOSMysql1 etc] # groupadd mysql

[root@CentOSMysql1 mysql] # cat / etc/passwd | grep mysql

[root@CentOSMysql1 etc] # useradd-g mysql mysql

(2) download the installation file of mysql. What I download here is of linux generic type.

[root@CentOStest1 opt] # wget http://downloads.mysql.com/archives/get/file/mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz

[root@CentOStest1 opt] # wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

(3) decompress these installation packages

[root@CentOSMysql1 opt] # tar-zxf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz

[root@CentOSMysql1 opt] # tar-zxf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

3. Install the mysql instance numbered 3307. Here I use the version of mysql5.7 for calculation.

[root@CentOSMysql1 opt] # mv mysql-5.7.16-linux-glibc2.5-x86_64 / usr/local

[root@CentOSMysql1 opt] # cd / usr/local/

[root@CentOSMysql1 local] # ln-s mysql-5.7.16-linux-glibc2.5-x86_64 mysql

[root@CentOSMysql1 local] # cd mysql

[root@CentOSMysql1 mysql] # chown-R mysql:mysql.

[root@CentOSMysql1 mysql] # bin/mysqld-initialize-user=mysql-datadir=/opt/data3307

/ / after the command is executed successfully, a temporary password is printed at the end, as follows:

2017-12-21T02:08:32.598340Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:08:33.002195Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:08:33.090315Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:08:33.179548Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d898cae9-e5f3-11e7-9e66-000c299323ae.

2017-12-21T02:08:33.181477Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:08:33.184759Z 1 [Note] A temporary password is generated for root@localhost: Little0GRAPHY XaweYi

[root@CentOSMysql1 mysql] # bin/mysql_ssl_rsa_setup-- datadir=/opt/data3307

At this point, the instance numbered 3307 has been initially installed, and one more step is to change the temporary password. Before changing the password, the mysql service needs to be started. Here, we start it with mysqld_multi. Check the status first, as follows:

[root@CentOSMysql1 bin] # mysqld_multireport

-bash: mysqld_multi: command not found

The solution is to add the path where the mysqld_multi tool is located to the environment variable:

[root@CentOSMysql1 bin] # vi / etc/profile

/ / add the following statement at the end of the file

PATH=/usr/local/mysql/bin:$PATH

"/ etc/profile" 79L, 1828C written

[root@CentOSMysql1 bin] # source / etc/profile

Retry after the environment variable takes effect

[root@CentOSMysql1 mysql] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 mysql] # mysqld_multi start 3307

[root@CentOSMysql1 mysql] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

You can see that the mysql instance numbered 3307 has been started, and then change the root password. Because the password of the root user is 123456 configured in my.cnf, we now change the password of root to "123456":

[root@CentOSMysql1 mysql] # mysql-uroot-p-S / tmp/mysql.sock3307

Enter password:

/ / the temporary password that appeared during the installation is output here.

Mysql > set password=password ("123456")

At this point, the instance numbered 3307 is installed.

4. Install the mysql instance numbered 3308. The version of mysql5.7 is still used here, but the port number and datadir should be based on those in my.cnf.

[root@CentOSMysql1 mysql] # bin/mysqld-initialize-user=mysql-datadir=/opt/data3308

2017-12-21T02:36:31.294011Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:36:31.779696Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:36:31.881428Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:36:31.948774Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c138a63c-e5f7-11e7-8f7a-000c299323ae.

2017-12-21T02:36:31.950062Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:36:31.953758Z 1 [Note] A temporary password is generated for root@localhost: whKyXCtxz9;g

/ / temporary passwords are also generated at the end.

[root@CentOSMysql1 mysql] # bin/mysql_ssl_rsa_setup-- datadir=/opt/data3308

Pull up the mysql instance service with number 3308

[root@CentOSMysql1 mysql] # mysqld_multi start 3308

Change the password of the root user in the mysql instance numbered 3308 to "123456".

[root@CentOSMysql1 mysql] # mysql-uroot-p-S / tmp/mysql.sock3308

Enter password:

/ / the temporary password that appeared during the installation is output here.

Mysql > set password=password ("123456")

At this point, the instance numbered 3308 is installed.

5. Install the mysql instance numbered 56, where the version of mysql5.6 is used, and the installation directory, port number and datadir are based on those in my.cnf.

[root@CentOSMysql1 opt] # mv mysql-5.6.34-linux-glibc2.5-x86_64 / usr/local

[root@CentOSMysql1 opt] # cd / usr/local/

[root@CentOSMysql1 local] # ln-s mysql-5.6.34-linux-glibc2.5-x86_64 mysql56

[root@CentOSMysql1 local] # cd mysql56

[root@CentOSMysql1 mysql56] # chown-R mysql:mysql.

[root@CentOSMysql1 mysql56] # scripts/mysql_install_db-user=mysql-datadir=/opt/data56

This step is somewhat different from mysql5.7 in that temporary passwords are not generated. The temporary password for Mysql5.6 is empty by default.

[root@CentOSMysql1 local] # mysqld_multi start 56

Pull up the mysql instance service with number 56

[root@CentOSMysql1 local] # mysql-uroot-p-S / tmp/mysql.sock56

Change the password of the root user in the mysql instance numbered 56 to "123456".

Mysql > set password= "123456"

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Mysql > set password=password ("123456")

At this point, the instance numbered 56 is installed.

6. Finally, try mysqld_multi to start and stop all mysql instances.

[root@CentOSMysql1 ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~] # mysqld_multi stop

[root@CentOSMysql1 ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 ~] # mysqld_multi start

[root@CentOSMysql1 ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

These are all the contents of the article "how to install multiple mysql instances in mysqld_multi". 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.