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

1. Background

The centralized operation and maintenance of MySQL database can provide their own services by deploying and running multiple MySQL service processes on one server and listening to different service ports through different socket. Each instance is independent of each other, and the datadir, port, socket and pid of each instance are different.

two。 Multi-instance characteristics

* effective use of server resources, when a single server resource is left, you can make full use of the remaining resources to provide more services.

* Resource preemption is a problem. When the concurrency of a service instance is very high or slow query is enabled, more memory, CPU and disk IO resources will be consumed, resulting in a decline in the service quality provided by other instances on the server.

3. Environment [turn off SeLinux]

[root@MySQL ~] # cat / etc/redhat-release CentOS release 6.9 (Final) [root@MySQL ~] # uname-r2.6.32-696.3.2.el6.x86_64 [root@MySQL ~] # getenforce Disabled

4. Deploy [4 instances]

* download MySQL 5.7binary package [recommended official download] this download version is greater than 5.7.5

[root@MySQL ~] # wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

* extract the MySQL 5.7binary package to the specified directory

[root@MySQL] # tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-C / usr/local/

* create MySQL soft links

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

* create MySQL users

[root@MySQL] # useradd-r-s / sbin/nologin mysql

* create a mysql-files directory in the MySQL binary package directory [MySQL data import / export data dedicated directory]

[root@MySQL ~] # mkdir-v / usr/local/mysql/mysql-filesmkdir: created directory `/ usr/local/mysql/mysql-files'

* create a multi-instance data directory

[root@MySQL ~] # mkdir-vp / data/mysql_data {1.. 4} mkdir: created directory `/ data'mkdir: created directory` / data/mysql_data1'mkdir: created directory `/ data/mysql_data2'mkdir: created directory` / data/mysql_data3'mkdir: created directory `/ data/mysql_data4'

* modify the users and groups of the MySQL binary package directory

[root@MySQL] # chown root.mysql-R / usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

* modify the users and groups of the MySQL multi-instance data directory and the data import / export directory

[root@MySQL] # chown mysql.mysql-R / usr/local/mysql/mysql-files / data/mysql_data {1.. 4}

* configure MySQL configuration file / etc/my.cnf

[mysqld_multi] mysqld = / usr/local/mysql/bin/mysqld mysqladmin = / usr/local/mysql/bin/mysqladminlog = / tmp/mysql_ multi.log [mysqld1] # set data directory [must be different in multiple instances] datadir = / data/mysql_data1# set sock storage file name [must be different in multiple instances] socket = / tmp/mysql.sock1# settings listen on open port [multiple instances Must be different in] port = 330 settings run user user = mysql# close monitoring performance_schema = off# set innodb cache size innodb_buffer_pool_size = 32M# set listening IP address bind_address = 0.0.0.turn off DNS reverse resolution skip-name-resolve = 0 [mysqld2] datadir = / data/mysql_data2socket = / tmp/mysql.sock2port = 3307user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind _ address = 0.0.0.0skip-name-resolve = 0 [mysqld3] datadir = / data/mysql_data3socket = / tmp/mysql.sock3port = 3308user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind_address = 0.0.0.0skip-name-resolve = 0 [mysqld4] datadir = / data/mysql_data4socket = / tmp/mysql.sock4port = 3309user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mbind_address = 0.0.0.0skip-name-resolve = 0

* initialize each instance [after initialization, it will bring its own random password in the output log]

[root@MySQL ~] # / usr/local/mysql/bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_ data 1 [root @ MySQL ~] # / usr/local/mysql/bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_ data 2 [root @ MySQL ~] # / usr/local/mysql/bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir= / data/mysql_ data 3 [root @ MySQL ~] # / usr/local/mysql/bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_data4

* enable SSL connection for each instance

[root@MySQL ~] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_ data 1 [root @ MySQL ~] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_ data 2 [root @ MySQL ~] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- user=mysql-- basedir= / usr/local/mysql-- datadir=/data/mysql_ data 3 [root @ MySQL ~] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- user=mysql-- basedir=/usr/local/mysql-- datadir=/data/mysql_data4

* copy multi-instance scripts to the service management directory [/ etc/init.d/]

[root@MySQL ~] # cp / usr/local/mysql/support-files/mysqld_multi.server / etc/init.d/mysqld_multi

* add script execution permissions

[root@MySQL ~] # chmod + x / etc/init.d/mysqld_multi

* add to service service management

[root@MySQL] # chkconfig-- add mysqld_multi

5. Start the test

* check the status of multiple instances

[root@MySQL ~] # / etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld1 is not runningMySQL server from group: mysqld2 is not runningMySQL server from group: mysqld3 is not runningMySQL server from group: mysqld4 is not running

* start multiple instances

[root@MySQL ~] # / etc/init.d/mysqld_multi start

* View the status of multiple instances

Reporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running

* View the listening port of the instance

[root@MySQL] # netstat-lntp | grep mysqldtcp 0 0 0 lntp 3306 0 0 0 grep mysqldtcp * LISTEN 2673/mysqld tcp 0 0 0 7 0 0 0 7 0 0 0 7 0 0 0. 0 lntp * LISTEN 2676/mysqld tcp 0 0 0. 0. 030 8 0. 0. 0. 0. 0 LISTEN 2679/mysqld tcp 0 0 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0 * LISTEN 2682/mysqld

6. Connection test

* instance 1

[root@MySQL] # / usr/local/mysql/bin/mysql-S / tmp/mysql.sock1-pendant Ilo* > s:3kw'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 6Server version: 5.7.18Copyright (c) 2000, 2017, 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 > set password = '123456 rows affected, 0 rows affected (0.00 sec)

* instance 2

[root@MySQL] # / usr/local/mysql/bin/mysql-S / tmp/mysql.sock2-p'b*AHUrTgu1rl'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7Server version: 5.7.18Copyright (c) 2000, 2017, 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 > set password = '123456 rows affected, 0 rows affected (0.00 sec)

7. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

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.