Multi-instance configuration of mysql5.7.11

Mysql has been through customs for a long time and has finally reached the second stage. It's time to do something interesting.

PS: the first time to use Markdown; to save time is to install the rpm package; the level is limited and correct; it is not compatible with Mysql5.5. # what you will see are:

1. Configure preparation for multiple instances of Mysql

two。 Start multiple Mysql instances

3. Connect Mysql

4. Some simple settings (optional)

Shutdown of 5.Mysql

6. Add another Mysql instance

# 1. Configure preparation for multiple instances of Mysql

1.1 create a file directory

Two instances are planned to run, one occupying port 3306 and the other occupying port 3307, hereinafter referred to as 3306 and 3307 instances.

Mkdir-p / mysql_multi/ {3306 ~ 3307} / data

Since it was created by root and the permissions are changed, leave this step to 2.1 for a while.

1.2 prepare the configuration file

Vim edits two my.cnf configuration files, and then you can press ZZ or wq! Save and exit directly:

To put it simply, it is 1. The port is different 2. Different directory paths

Configuration file for example 1:

Datadir=/mysql_multi/3306/data

Socket=/mysql_multi/3306/mysql.sock

Log-error=/mysql_multi/3306/mysqld.log

Pid-file=/mysql_multi/3306/mysqld.pid

Server-id=1

Port=3306

Configuration file for instance 2:

Datadir=/mysql_multi/3307/data

Socket=/mysql_multi/3307/mysql.sock

Log-error=/mysql_multi/3307/mysqld.log

Pid-file=/mysql_multi/3307/mysqld.pid

Server-id=2

Port=3307

Copy to / mysql_multi/3306 and / mysql_multi/3307

# 2. Start multiple Mysql instances

2.1 initialize two Mysql databases

Change the permissions of the directory first:

Chown-Rmysql:mysql / mysql_multi

Now it's Mysql5.7, which didn't happen in the previous 5.5 years.

For 3306, execute:

Mysqld-defaults-file=/mysql_multi/3306/my.cnf-initialize-insecure-user=mysql

Explanation:

-- defaults-file=/mysql_multi/3306/my.cnf: specify the configuration file for startup

-- initialize-insecure: initializes the database, plus-insecure does not generate random passwords

(login directly, without adding-insecure, the password will be generated in mysqld.log, and you will have to find your own temporary password.)

-- user=mysql: there is nothing to say about the specified user.

The same is true of 3307:

Mysqld-defaults-file=/mysql_multi/3307/my.cnf-initialize-insecure-user=mysql

Pay attention to check whether there are any related database files under the data directory. If you have any problems, take a look at your mysqld.log.

This is successfully initialized:

! [enter picture description] (/ 2014th7cj/d/file/p/20161019/ohzfepkkjtx.png "enter picture title here")

2.2 start the instance

Finally, at the critical time to start the instance, enter:

Launch 3306 instance: mysqld-- defaults-file=/mysql_multi/3306/my.cnf-- user=mysql &

Launch 3307 instance: mysqld-- defaults-file=/mysql_multi/3307/my.cnf-- user=mysql &

Note: join at the end & let it run in the background, otherwise it will be "stuck" by pressing enter after typing.

It is almost less than just now-- initialize-insecure initializes the database. For more information on whether the operation is successful, please see mysql.log or

Enter netstat-ntlp to view the processes occupying the port (if you succeed, you can see two mysqld processes occupying port 3306, respectively)

# 3. Connect Mysql

Connect

Connect 3306:mysql-uroot-p-S / mysql_multi/3306/mysql.sock

Prompt for the password and enter directly.

Connect 3307:mysql-uroot-p-S / mysql_multi/3307/mysql.sock

It's the same as above

You can enter system mysql-S / mysql_multi/3307/mysql.sock to switch instances

# 4. Some simple settings (optional)

Change the root password

UPDATE mysql.user SET authentication_string = PASSWORD ('123456') WHERE USER LIKE'% root%'

Grant all privileges on *. * to 'root'@'%' identified by' 123456 'with grant option

Flush privileges

From an administrative point of view, an administrator of system should be added for remote management

Grant all privileges on *. * to system@'%' identified by '12345678' with grant option

# disable 5.Mysql

Note: be sure to turn it off with mysqladmin

Mysqladmin-S / mysql_multi/3306/mysql.sock shutdown

Mysqladmin-S / mysql_multi/3307/mysql.sock shutdown

# 6. Add another Mysql instance

It is similar to the above steps.

1. Create related directories

Mkdir-p/mysql_multi/3308/data

two。 Change users and groups to which they belong

Chown-R mysql:mysql 3308

3. Copy and modify the configuration file (without going into details)

4. Initialization: mysqld-defaults-file=/mysql_multi/3308/my.cnf-initialize-insecure-user=mysql

5. Start: mysqld-- defaults-file=/mysql_multi/3308/my.cnf--user=mysql & complete ~