MySQL 5.7--------Multi-instance deployment best Practice

1. Background

the centralized operation of MySQL database can be done by running multiple MySQL service processes on a single server, using different sockets to monitor different service ports to provide their own services. Each instance is independent of each other, each instance of the DataDir, port, socket, PID are different.

2. Multi-Instance features

* effective use of server resources, when the individual server resources have surplus, can make full use of the remaining resources to provide more services.

* Resource preemption problem, when a service instance service is high concurrency or when slow query is turned on, it consumes more memory, CPU, disk IO resources, causing the quality of service on other instances on the server to degrade.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/99/FB/wKioL1lPymPxK5ulAADAhFfCdKg045.jpg "title=" MySQL multi-instance. jpg "alt=" wkiol1lpympxk5ulaadahffcdkg045.jpg "/>

3. Environment [Off SELinux]

[Email protected] ~]# cat/etc/redhat-release CentOS release 6.9 (Final) [[email protected] ~]# uname-r2.6.32-696.3.2.el6 . X86_64[[email protected] ~]# Getenforce Disabled

4. Deployment [4 Instances]

* Download MySQL 5.7 Two package [recommended official DOWNLOAD] This download version is greater than 5.7.5

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

* unzip the MySQL 5.7 binary package to the specified directory

[Email protected] ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local/

* Create MySQL Soft link

[Email protected] ~]# ln-s/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/usr/local/mysql

* Create MySQL user

[[email protected] ~]# useradd-r-s/sbin/nologin MySQL

* Create mysql-files directory in MySQL binary package directory [MySQL data import/Export data dedicated directory ]

[Email protected] ~]# mkdir-v/usr/local/mysql/mysql-filesmkdir:created directory '/usr/local/mysql/mysql-files '

* Create multi-instance data directory

[[email protected] ~]# mkdir-vp/data/mysql_data{1..4}mkdir:created directory '/data ' mkdir:created directory '/data/my Sql_data1 ' mkdir:created directory '/data/mysql_data2 ' mkdir:created directory '/data/mysql_data3 ' mkdir:created Directory '/DATA/MYSQL_DATA4 '

* Modify the user and owning group of the MySQL binary package directory

[Email protected] ~]# chown root.mysql-r/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

* Modify MySQL Multi-instance data directory with data import/export exclusive directory of the owning user and owning group

[Email protected] ~]# 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 file name [multiple instances must be different]socket = /tmp/mysql.sock1#  settings listening open ports [must be different in multiple instances]port = 3306#   Set run user user = mysql#  turn off monitoring performance_schema = off#  set innodb  cache size InnoDB _buffer_pool_size = 32m#  set the Listener IP address bind_address = 0.0.0.0#  close dns  Reverse parsing 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

* initialization of each instance [ after initialization is complete, the random password is included in the output log ]

[Email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data1[[email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/ MySQL--datadir=/data/mysql_data2[[email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql-- Basedir=/usr/local/mysql--datadir=/data/mysql_data3[[email protected] ~]#/usr/local/mysql/bin/mysqld--initialize --user=mysql--basedir=/usr/local/mysql--DATADIR=/DATA/MYSQL_DATA4

* Each instance turns on SSL connection

[Email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/ Data/mysql_data1[[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/ MySQL--datadir=/data/mysql_data2[[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql-- Basedir=/usr/local/mysql--datadir=/data/mysql_data3[[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_ Setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data4

* Copy Multi-instance script to service management directory [/etc/init.d/]

[Email protected] ~]# Cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi

* Add script execution Permissions

[Email protected] ~]# chmod +x/etc/init.d/mysqld_multi

* add into service management

[Email protected] ~]# chkconfig--add mysqld_multi

5. Start the test

* check a multi-instance status

[[email protected] ~]#/etc/init.d/mysqld_multi reportreporting MySQL serversmysql server from Group:mysqld1 are not Runni Ngmysql server from Group:mysqld2 are not runningmysql servers from GROUP:MYSQLD3 are not runningmysql servers from group:m YSQLD4 is not running

* Start multi-instance

[[email protected] ~]#/etc/init.d/mysqld_multi start

* View Multi-instance status

Reporting MySQL serversmysql Server from Group:mysqld1 are Runningmysql server from GROUP:MYSQLD2 are Runningmysql server From GROUP:MYSQLD3 are Runningmysql server from Group:mysqld4 is running

* View instance listening port

[[email protected] ~]# netstat -lntp | grep mysqldtcp         0      0 0.0.0.0:3306          0.0.0.0:*                 LISTEN      2673/mysqld          tcp        0       0 0.0.0.0:3307         0.0.0.0:*                 LISTEN       2676/mysqld         tcp         0      0 0.0.0.0:3308          0.0.0.0:*                listen       2679/mysqld         tcp         0      0 0.0.0.0:3309          0.0.0.0:*                 listen      2682/mysqld

6. Connection test

* Example 1

[[Email protected] ~]# /usr/local/mysql/bin/mysql -s /tmp/mysql.sock1  -p ' Z +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 '; query ok, 0 rows affected  (0.00 SEC)

* Example 2

[[email protected] ~]# /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 '; query ok, 0 rows affected  (0.00 SEC)

9. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1941788

MySQL 5.7--------Multi-instance deployment best Practice