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

1. Brief introduction of multiple instances of MySQL

MySQL multi-instance, to put it simply, is to open multiple different service ports (such as 3306, 3307) on a server and run multiple MySQL service processes at the same time. These service processes provide services through different socket listeners from different ports.

Multi-instance not only saves the cost of physical host, but also effectively improves the efficiency of CPU and disk Imax O of a single physical host, and it can also deploy database HA solution among multiple instances.

With the increase of the number of instances, we are faced with the problem of unified management, so we need to use MySQL's own management program mysqld_multi to manage.

II. MySQL startup process

Mysqld_multi # Multi-instance Manager

The main startup method of mysqld # MySQL, which has many parameters; now using multiple instances requires a new mysql_safe to start mysql

Mysql_safe # actually calls mysqld and reads the my.cnf configuration parameters in mysqld to start mysql,mysql_safe itself with many parameters, but these parameters take precedence over my.cnf

Configuration file for my.cnf # mysql

The sock file created by my.sock # mysql, opens, stops, logs in and manages the mysql through this interface file.

Third, based on the mysql5.5.52 version, please see MySQL5.5.52 compilation and installation, and configure a multi-instance with mysqld_multi.

1. Stop single instance mysql database

[root@db01] # / etc/init.d/mysqld stopShutting down MySQL. SUCCESS!

2. Self-startup is prohibited.

[root@db01 ~] # chkconfig mysqld off [root@db01 ~] # chkconfig-- list mysqldmysqld 0: close 1: close 2: close 3: close 4: close 5: close 6: close

3. Create a multi-instance root directory / data/ directory

[root@db01] # mkdir-p / data/ {3306 3307} / data

4. Copy mysqld_multi program files

[root@db01 ~] # cp / application/mysql/support-files/mysqld_multi.server / etc/init.d/mysqld_multi.server

1) modify mysqld_multi.server path configuration

[root@db01 ~] # sed-I's benchmark basedirqqlbinedirqqlbinqlbinqlbindirqqlbindirqqlbindirqqlbindirqlqlbindirqqlbindirqlqlbindir.server.server [root @ db01 ~] # sed-I's bind dirt

2) add the / etc/mysqld_multi.cnf configuration file used by mysqld_multi

# this template file can be exported with the command mysqld_multi-- example

[root@db01 ~] # vim / etc/mysqld_ multi.cnf [mysqld _ multi] mysqld = / application/mysql/bin/mysqld_safemysqladmin = / application/mysql/bin/mysqladmin#user = multi_admin#password = my_ password [mysqld1] socket = / data/3306/mysql.sockport = 3306pid-file = / data/3306/mysql.piddatadir = / data/3306/data#language = / application/mysql/share/mysql/englishuser = mysql [mysqld2 ] socket = / data/3307/mysql.sockport = 3307pid-file = / data/3307/mysql.piddatadir = / data/3307/data#language = / application/mysql/share/mysql/englishuser = mysql

5. Configure file permissions for multiple instances of MySQL

Authorize mysql users and user groups to manage the root / data of the entire multi-instance with the following command

[root@db01] # chown-R mysql.mysql / data

6. Initialize the database file of multiple instances of MySQL

(1) initialize MySQL database

Cd / application/mysql/scripts/-basedir=/application/mysql\ >-datadir=/data/3306/data\ >-user=mysqlWARNING: The host 'db01' could not be looked up with resolveip.This probably means that your libc libraries are not 100% compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that hostname resolving will not work.This means that you should use IP addresses instead of hostnameswhen specifying MySQL privileges! Installing MySQL system tables...161117 14:14:14 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46676... OKFilling help tables...161117 14:14:15 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46683... OK if there are two ok This means that the 3307 instance [root@db01 scripts] # / application/mysql/scripts/mysql_install_db\ >-- basedir=/application/mysql\ >-- datadir=/data/3307/data\ >-- user=mysqlInstalling MySQL system tables...161117 14:18:20 [Note] / application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46733... OKFilling help tables...161117 14:18:21 [Note] / application/mysql/bin/mysqld ( Mysqld 5.5.52) starting as process 46740... OK if there are two ok It indicates that the initialization is successful

7. Start multiple instances:

1) View database status

Mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf report [root@db01 ~] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf reportReporting MySQL serversMySQL server from group: mysqld1 is not runningMySQL server from group: mysqld2 is not running

2) start the database

[root@db01 ~] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf start 1 mysqld_multi 2 [root@db01 ~] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf report Reporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is running

3) View Port

[root@db01 ~] # ss-nlutp | grep 330tcp LISTEN 0 50 *: 3306 *: * users: (("mysqld", 47045, 10)) tcp LISTEN 0 50 *: 3307 *: * users: (("mysqld", 47041 10))

8. Stop the database

[root@db01 ~] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf stop 1 mysqld_multi 2 [root@db01 ~] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf reportReporting MySQL serversMySQL server from group: mysqld1 is not runningMySQL server from group: mysqld2 is not running

9. Log in to the database

1) start the database

[root@db01] # mysqld_multi-- defaults-extra-file=/etc/mysqld_multi.cnf start 1pm 2

2) Log in to the database

Method 1: specify the port and host IP, which is suitable for remote connection

Mysql-uroot-h227.0.0.1-P3306

Method 2: specify socket login, which is suitable for local connection

Mysql-S / data/3307/mysql.sock

Operation demonstration

Method 1: specify the port and host IP, which is suitable for remote connection

[root@db01] # mysql-uroot-h227.0.0.1-P3306Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.5.52 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Method 2: specify socket login, which is suitable for local connection

[root@db01] # mysql-S / data/3307/mysql.sockWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.5.52 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

At this point, the multi-instance configuration of MySQL is complete. O (∩ _ ∩) has completed the configuration of multiple instances.

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.