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

This article mainly shows you "Mysql multi-instance installation configuration", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Mysql multi-instance installation configuration" this article.

1.OS user creation / software decompression / OS permission configuration

Click (here) to collapse or open

[root@wbg software] # groupadd mysql

[root@wbg software] # useradd-r-g mysql-s / bin/false mysql

[root@wbg software] # cd / usr/local

[root@wbg local] # tar zxvf ~ / software/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

Decompressing.

[root@wbg local] # ln-s mysql-5.7.18-linux-glibc2.5-x86_64/ mysql

[root@wbg mysql] # mkdir mysql-files

[root@wbg mysql] # chmod 750 mysql-files

[root@wbg mysql] # chown-R mysql.

[root@wbg mysql] # chgrp-R mysql.

two。 Write / etc/my.cnf

Click (here) to collapse or open

[mysqld_multi]

Mysqld = / usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqld_safe

Mysqladmin = / usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqladmin

User = root

Password = gg

Log = / root/multi.log

[mysqld3306]

Socket = / tmp/mysql.sock3306

Port = 3306

Pid-file = / mydata/3306/hostname.pid.3306

Datadir = / mydata/3306/

# language = / usr/local/mysql/share/mysql/english

User = mysql

[mysqld3308]

Mysqld = / usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqld_safe

# ledir = / path/to/mysqld-binary/

Mysqladmin = / usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqladmin

Socket = / tmp/mysql.sock3308

Port = 3308

Pid-file = / mydata/3308/hostname.pid.3308

Datadir = / mydata/3308/

# language = / usr/local/mysql/share/mysql/swedish

User = mysql

[client]

# port=3306

# socket=/tmp/mysql.sock3306

# port=3308

# socket=/tmp/mysql.sock3308

I used the official template to do it, using fewer parameters, and I commented out several parameters on the basis of the official template.

3. Create a separate directory for each strength

Click (here) to collapse or open

[root@wbg mysql] # mkdir / mydata

[root@wbg mysql] # cd / mydata

[root@wbg mydata] # mkdir 3306

[root@wbg mydata] # mkdir 3308

[root@wbg mydata] # chown-R mysql:mysql / mydata/

4. Initialize the instance (both need to be done, only one is posted here) (the first two are negative tutorials, and the last one is correct)

Click (here) to collapse or open

[root@wbg mysql] # bin/mysqld-initialize-user=mysql defaults-file=/etc/my.cnf

2017-10-23T09:13:53.872808Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-10-23T09:13:54.385216Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:13:54.547668Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:13:54.630447Z 0 [ERROR] Too many arguments (first extra is' defaults-file=/etc/my.cnf').

2017-10-23T09:13:54.630493Z 0 [ERROR] Aborting

Need to add-- explicit_defaults_for_timestamp this configuration, otherwise there is a warning.

Click (here) to collapse or open

[root@wbg mysql] # bin/mysqld-initialize-user=mysql-defaults-file=/etc/my.cnf-datadir=/mydata/3308/-explicit_defaults_for_timestamp

2017-10-23T09:45:43.659977Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:45:43.806836Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:45:43.912638Z 0 [ERROR] unknown variable 'defaults-file=/etc/my.cnf'

2017-10-23T09:45:43.912685Z 0 [ERROR] Aborting

Also pay attention to the order of initialize and defaults-file. I've wasted a lot of time here.

Click (here) to collapse or open

[root@wbg mysql] # bin/mysqld-defaults-file=/etc/my.cnf-initialize-user=mysql-datadir=/mydata/3308/-explicit_defaults_for_timestamp

2017-10-23T09:50:48.217054Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:50:48.430856Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:50:48.500114Z 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: a5c39a70-b7d7-11e7-a63a-080027736559.

2017-10-23T09:50:48.510651Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-10-23T09:50:48.513336Z 1 [Note] A temporary password is generated for root@localhost: 6epfT_PyU+EG

The last one is correct.

5. Install SSL

Click (here) to collapse or open

[root@wbg mysql] # bin/mysql_ssl_rsa_setup

Generating a 2048 bit RSA private key

.. + +

.. +

Writing new private key to 'ca-key.pem'

-

Generating a 2048 bit RSA private key

. +

. +

Writing new private key to 'server-key.pem'

-

Generating a 2048 bit RSA private key

.. + +

. +

Writing new private key to 'client-key.pem'

-

In versions of MySQL 5.7.6 and above, you need to install this, otherwise there will be the following warning

Click (here) to collapse or open

2017-10-23T18:15:21.506002Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

6. Add a section of configuration to profile

Click (here) to collapse or open

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

7. Set the login password for each instance (both instances)

Set the password and use the default password

Click (here) to collapse or open

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

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 12

Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > alter user user () identified by 'bb'

Query OK, 0 rows affected (0.00 sec)

8. Test it

Click (here) to collapse or open

[root@wbg ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3308 is not running

[root@wbg ~] # mysqld_multi start

[root@wbg ~] # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is running

At this point, the configuration of multiple instances is complete.

Additional instructions:

1. In the end, only one instance of this configuration can use tcp/ip. In the end, only one set of configurations can take effect under [client] of my.cnf. All instances can be connected with socket.

two。 What I demonstrate here is to use root as the administrative account, and their passwords are all the same. If the passwords of the root are different, the official documentation also has a solution.

Click (here) to collapse or open

Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin program) has the same user name and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different user names or passwords for the administrative accounts, you might want to create an account on each server that has the same user name and password. For example, you might set up a common multi_admin account by executing the following commands for each server:

Click (here) to collapse or open

Shell > mysql-u root-S / tmp/mysql.sock-p

Enter password:

Mysql > CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY' multipass'

Mysql > GRANT SHUTDOWN ON *. * TO 'multi_admin'@'localhost'

3. When editing my.cnf, note that the user under [mysqld_multi] is the administrative account of mysqld_multi, which is the user of mysql.

User under [mysqldN] is a user of OS. These can be quickly seen through the template of the official document.

These are all the contents of the article "how to install and configure multiple instances of Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.