Shulou(Shulou.com)06/01 Report--

[preface] multiple mysql databases are often needed to simulate various environments and scenarios in the test environment. Because the resources in the test environment are generally limited, it is necessary to build multiple mysql databases on one server to complete the testing requirements. This document describes how to install multiple mysql 5.7databases in an Centos6.4 environment.

[1] installation of mysql database software

Mysql database software officially provides three installation files and methods, as follows:

RPM installation

Binary installation

Source code compilation and installation

Although the source code installation method is more troublesome, but the individual is still used to using the source code solution installation, mysql5.7 installation and early 5.5 installation methods and steps are almost the same, so I will not explain here.

[2] create multiple databases

The installation of the software is actually the same, but the creation of the database is a little different.

2.1 to plan the data file directory, this environment installs 5 databases, creates 5 folders under / data, and distinguishes them by port number

Click (here) to collapse or open

[root@db01 data] # pwd

/ data

[root@db01 data] # ll

Drwxr-xr-x. 6 mysql mysql 4096 July 8 05: 00 mysql3306

Drwxr-xr-x. 6 mysql mysql 4096 July 20 21: 46 mysql3307

Drwxr-xr-x. 7 mysql mysql 4096 July 20 21: 46 mysql3308

Drwxr-xr-x. 6 mysql mysql 4096 July 20 21: 46 mysql3309

Drwxr-xr-x. 5 mysql mysql 4096 July 20 21: 46 mysql3310

2.2 configure the parameter file

Click (here) to collapse or open

[root@db01 data] # vi / etc/my. Cnf

[mysqld_multi]

Mysqld = / usr/ local / mysql/bin/mysqld_safe

# basedir = / usr/ local / mysql

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

User = mysql

Pass = mysql

# password = mysql

# bindir = / usr/ local / mysql/bin

[mysqld7]

Port = 3307

Socket = / tmp/mysql. Sock7

Pid-file = / data/mysql3307/hostname. Pid7

Datadir = / data/mysql3307

User = mysql

Basedir = / usr/ local / mysql

Log-bin = / data/mysql3307/bin-log

Server_id = 7

Gtid_mode = ON

Enforce-gtid-consistency = TRUE

Log_slave_updates = ON

# skip-grant-tables

[mysqld8]

Socket = / tmp/mysql. Sock8

Port = 3308

Pid-file = / data/mysql3308/hostname. Pid8

Datadir = / data/mysql3308

User = mysql

Basedir = / usr/ local / mysql

# skip-grant-tables

Log-bin = / data/mysql3308/bin-log

Server_id = 8

Gtid_mode = ON

Enforce-gtid-consistency = TRUE

Log_slave_updates = ON

[mysqld9]

Socket = / tmp/mysql. Sock9

Port = 3309

Pid-file = / data/mysql3309/hostname. Pid9

Datadir = / data/mysql3309

User = mysql

Basedir = / usr/ local / mysql

# skip-grant-tables

Log-bin = / data/mysql3309/bin-log

Server_id = 9

Gtid_mode = ON

Enforce-gtid-consistency = TRUE

Log_slave_updates = ON

[mysqld10]

Socket = / tmp/mysql. Sock10

Port = 3310

Pid-file = / data/mysql3310/hostname. Pid10

Datadir = / data/mysql3310

User = mysql

Basedir = / usr/ local / mysql

# skip-grant-tables

Log-bin = / data/mysql3310/bin-log

Server_id = 10

Gtid_mode = ON

Enforce-gtid-consistency = TRUE

Log_slave_updates = ON

2.3 create a database, during which you need to record the initial password of the database

Create other databases in turn and record the default random password

Installation of mysql3307

Click (here) to collapse or open

[root@db01 data] # / usr/ local / mysql/bin/mysqld-- initialize-- datadir = / data/mysql3307

2017-07-20T14: 31: 01. 890314Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-07-20T14: 31: 09. 081679Z 0 [Warning] InnoDB: New log files created, LSN = 45790

2017-07-20T14: 31: 09. 626403Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-07-20T14: 31: 09. 867983Z 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: 12d65efe-6d58-11e7-9d39-000c29a755d3.

2017-07-20T14: 31: 09. 873982Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-07-20T14: 31: 09. 923044Z 1 [Note] A temporary password is generated for root@localhost: wAQ * p >. O: 4, p

Installation of mysql3308

[root@db01 mysql3307] # cat auto. Cnf

[auto]

Server-uuid = 12d65efe-6d58-11e7-9d39-000c29a755d3

[root@db01 mysql3307] # / usr/ local / mysql/bin/mysqld-- initialize-- datadir = / data/mysql3308

2017-07-20T14: 32: 36. 027225Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-07-20T14: 32: 38. 601806Z 0 [Warning] InnoDB: New log files created, LSN = 45790

2017-07-20T14: 32: 39. 071963Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-07-20T14: 32: 39. 167438Z 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: 48106897-6d58-11e7-a1b9-000c29a755d3.

2017-07-20T14: 32: 39. 172770Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-07-20T14: 32: 39. 212540Z 1 [Note] A temporary password is generated for root@localhost: Ak3XwQpb = ta0

Installation of mysql3309

[root@db01 mysql3307] # / usr/ local / mysql/bin/mysqld-- initialize-- datadir = / data/mysql3309

2017-07-20T14: 33: 32. 801680Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-07-20T14: 33: 35. 102950Z 0 [Warning] InnoDB: New log files created, LSN = 45790

2017-07-20T14: 33: 35. 443411Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-07-20T14: 33: 35. 557451Z 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: 69acd736-6d58-11e7-a 436-000c29a755d3.

2017-07-20T14: 33: 35. 562713Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-07-20T14: 33: 35. 607109Z 1 [Note] A temporary password is generated for root@localhost: sDXL5hh71I > R

Database mysql3310

Click (here) to collapse or open

[root@db01 mysql3307] # / usr/ local / mysql/bin/mysqld-- initialize-- datadir = / data/mysql3310

2017-07-20T14: 34: 14. 881243Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-07-20T14: 34: 17. 227399Z 0 [Warning] InnoDB: New log files created, LSN = 45790

2017-07-20T14: 34: 17. 744012Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-07-20T14: 34: 17. 904000Z 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: 82ea694b-6d58-11e7-a566-000c29a755d3.

2017-07-20T14: 34: 17. 908498Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-07-20T14: 34: 17. 923365Z 1 [Note] A temporary password is generated for root@localhost: Rq4 * Teq#l; Ve

[3] modify the initial password of the database

Start the database first

[root@db01 mysql3307] # / usr/ local / mysql/bin/mysqld_multi start

Change the default password

[root@db01 mysql3307] # mysqladmin-u root-p-P 3307-S / tmp/mysql. Sock7 password

Enter password: enter default password

New password:

Confirm new password:

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Log into the database

[root@db01 mysql3307] # mysql-P 3307-S / tmp/mysql.sock7-u root-p

Logging in with the default password will have the following message:

Click (here) to collapse or open

Mysql > show databases

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

[4] set the shutdown permission of mysqld_multi stop

By default, the database cannot be shut down through mysqld_multi, and additional settings are required, as follows:

Create a user whose mysql is closed

Click (here) to collapse or open

[root@db01 mysql3307] # mysql-u root-p-P 3310-S / tmp/mysql. Sock10

Enter password:

Mysql > grant shutdown on *. * to 'mysql' @' localhost' identified by 'mysql'

Mysql > flush privileges

Set the account of the parameter file

Click (here) to collapse or open

[root@db01 mysql3307] # cat / etc/my. Cnf

[mysqld_multi]

Mysqld = / usr/ local / mysql/bin/mysqld_safe

# basedir = / usr/ local / mysql

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

User = mysql

Pass = mysql

Through the above operations, the operation of installing multiple mysql databases on a single server is completed.

Attaching: common operation statements

/ usr/local/mysql/bin/mysqld_multi start # start all databases

/ usr/local/mysql/bin/mysqld_multi start 7 # start a single database

/ usr/local/mysql/bin/mysqld_multi stop # shut down all databases

/ usr/local/mysql/bin/mysqld_multi stop 7 # shut down a single database

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.