{"id":6871,"date":"2017-07-21T15:59:47","date_gmt":"2017-07-21T07:59:47","guid":{"rendered":"http:\/\/rmohan.com\/?p=6871"},"modified":"2017-07-21T16:03:05","modified_gmt":"2017-07-21T08:03:05","slug":"mysql-5-7-multi-instance-installation-deployment-instance","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6871","title":{"rendered":"MySQL 5.7 multi-instance installation deployment instance"},"content":{"rendered":"

1. Background
\nMySQL database centralized operation and maintenance, through a server, the deployment of running multiple MySQL service process, through a different socket to monitor different service ports to provide their services.\u00a0Each instance is independent of each other, each instance of the datadir, port, socket, pid are different.<\/p>\n

2. Multi-instance features
\n* Effective use of server resources, when a single server resources are left, you can make full use of the remaining resources to provide more services.
\n* Resources to each other to seize the problem, when a service instance service is high or open slow query, it will consume more memory, CPU, disk IO resources, resulting in other instances of the server to provide services to reduce the quality.<\/p>\n

 <\/p>\n

[root@MySQL ~]# cat \/etc\/RedHat<\/a>-release
\nCentOS<\/a>\u00a0release 6.9 (Final)<\/p>\n

[root@MySQL ~]# uname -r
\n2.6.32-696.3.2.el6.x86_64<\/p>\n

[root@MySQL ~]# getenforce
\nDisabled<\/p>\n

[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C \/usr\/local\/<\/p>\n

[root@MySQL ~]# ln -s \/usr\/local\/mysql-5.7.18-linux-glibc2.5-x86_64 \/usr\/local\/mysql<\/p>\n

[root@MySQL ~]# useradd -r -s \/sbin\/nologin mysql<\/p>\n

[root@MySQL ~]# mkdir -v \/usr\/local\/mysql\/mysql-files
\nmkdir: created directory `\/usr\/local\/mysql\/mysql-files’<\/p>\n

[root@MySQL ~]# mkdir -vp \/data\/mysql_data{1..4}
\nmkdir: created directory `\/data’
\nmkdir: created directory `\/data\/mysql_data1′
\nmkdir: created directory `\/data\/mysql_data2′
\nmkdir: created directory `\/data\/mysql_data3′
\nmkdir: created directory `\/data\/mysql_data4′<\/p>\n

1 [root@MySQL ~]# chown root.mysql -R \/usr\/local\/mysql-5.7.18-linux-glibc2.5-x86_64<\/p>\n

[root@MySQL ~]# chown mysql.mysql -R \/usr\/local\/mysql\/mysql-files \/data\/mysql_data{1..4}<\/p>\n

\/etc\/my.cnf
\n[mysqld_multi]
\nmysqld\u00a0 \u00a0 = \/usr\/local\/mysql\/bin\/mysqld
\nmysqladmin = \/usr\/local\/mysql\/bin\/mysqladmin
\nlog\u00a0 \u00a0 \u00a0 \u00a0 = \/tmp\/mysql_multi.log<\/p>\n

 <\/p>\n

[mysqld1]<\/p>\n

datadir = \/data\/mysql_data1<\/p>\n

socket = \/tmp\/mysql.sock1<\/p>\n

port = 3306<\/p>\n

user = mysql<\/p>\n

performance_schema = off<\/p>\n

innodb_buffer_pool_size = 32M<\/p>\n

bind_address = 0.0.0.0<\/p>\n

skip-name-resolve = 0<\/p>\n

[mysqld2]
\ndatadir = \/data\/mysql_data2
\nsocket = \/tmp\/mysql.sock2
\nport = 3307
\nuser = mysql
\nperformance_schema = off
\ninnodb_buffer_pool_size = 32M
\nbind_address = 0.0.0.0
\nskip-name-resolve = 0<\/p>\n

[mysqld3]
\ndatadir = \/data\/mysql_data3
\nsocket = \/tmp\/mysql.sock3
\nport = 3308
\nuser = mysql
\nperformance_schema = off
\ninnodb_buffer_pool_size = 32M
\nbind_address = 0.0.0.0
\nskip-name-resolve = 0<\/p>\n

[mysqld4]
\ndatadir = \/data\/mysql_data4
\nsocket = \/tmp\/mysql.sock4
\nport = 3309
\nuser = mysql
\nperformance_schema = off
\ninnodb_buffer_pool_size = 32M
\nbind_address = 0.0.0.0
\nskip-name-resolve = 0<\/p>\n

 <\/p>\n

 <\/p>\n

[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysqld –initialize –user=mysql –basedir=\/usr\/local\/mysql –datadir=\/data\/mysql_data1
\n[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysqld –initialize –user=mysql –basedir=\/usr\/local\/mysql –datadir=\/data\/mysql_data2
\n[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysqld –initialize –user=mysql –basedir=\/usr\/local\/mysql –datadir=\/data\/mysql_data3
\n[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysqld –initialize –user=mysql –basedir=\/usr\/local\/mysql –datadir=\/data\/mysql_data4<\/p>\n

 <\/p>\n

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

 <\/p>\n

[root@MySQL ~]# chmod +x \/etc\/init.d\/mysqld_multi<\/p>\n

 <\/p>\n

[root@MySQL ~]# chkconfig –add mysqld_multi<\/p>\n

 <\/p>\n

[root@MySQL ~]# \/etc\/init.d\/mysqld_multi report
\nReporting MySQL servers
\nMySQL server from group: mysqld1 is not running
\nMySQL server from group: mysqld2 is not running
\nMySQL server from group: mysqld3 is not running
\nMySQL server from group: mysqld4 is not running<\/p>\n

 <\/p>\n

[root@MySQL ~]# \/etc\/init.d\/mysqld_multi start<\/p>\n

 <\/p>\n

Reporting MySQL servers
\nMySQL server from group: mysqld1 is running
\nMySQL server from group: mysqld2 is running
\nMySQL server from group: mysqld3 is running
\nMySQL server from group: mysqld4 is running<\/p>\n

 <\/p>\n

[root@MySQL ~]# netstat -lntp | grep mysqld
\ntcp\u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 0 0.0.0.0:3306\u00a0 \u00a0 \u00a0 \u00a0 0.0.0.0:*\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 LISTEN\u00a0 \u00a0 \u00a0 2673\/mysqld
\ntcp\u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 0 0.0.0.0:3307\u00a0 \u00a0 \u00a0 \u00a0 0.0.0.0:*\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 LISTEN\u00a0 \u00a0 \u00a0 2676\/mysqld
\ntcp\u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 0 0.0.0.0:3308\u00a0 \u00a0 \u00a0 \u00a0 0.0.0.0:*\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 LISTEN\u00a0 \u00a0 \u00a0 2679\/mysqld
\ntcp\u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 0 0.0.0.0:3309\u00a0 \u00a0 \u00a0 \u00a0 0.0.0.0:*\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 LISTEN\u00a0 \u00a0 \u00a0 2682\/mysqld<\/p>\n

 <\/p>\n

[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysql -S \/tmp\/mysql.sock1\u00a0 -p’z+Ilo*>s:3kw’
\nmysql: [Warning] Using a password on the command line interface can be insecure.
\nWelcome to the MySQL monitor.\u00a0 Commands end with ; or \\g.
\nYour MySQL connection id is 6
\nServer version: 5.7.18<\/p>\n

Copyright (c) 2000, 2017,\u00a0Oracle<\/a>\u00a0and\/or its affiliates. All rights reserved.<\/p>\n

Oracle is a registered trademark of Oracle Corporation and\/or its
\naffiliates. Other names may be trademarks of their respective
\nowners.<\/p>\n

Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.<\/p>\n

mysql> set password = ‘123456’;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n

[root@MySQL ~]# \/usr\/local\/mysql\/bin\/mysql -S \/tmp\/mysql.sock2\u00a0 -p’b*AHUrTgu1rl’
\nmysql: [Warning] Using a password on the command line interface can be insecure.
\nWelcome to the MySQL monitor.\u00a0 Commands end with ; or \\g.
\nYour MySQL connection id is 7
\nServer version: 5.7.18<\/p>\n

Copyright (c) 2000, 2017, Oracle and\/or its affiliates. All rights reserved.<\/p>\n

Oracle is a registered trademark of Oracle Corporation and\/or its
\naffiliates. Other names may be trademarks of their respective
\nowners.<\/p>\n

Type ‘help;’ or ‘\\h’ for help. Type ‘\\c’ to clear the current input statement.<\/p>\n

mysql> set password = ‘123456’;
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n","protected":false},"excerpt":{"rendered":"

1. Background MySQL database centralized operation and maintenance, through a server, the deployment of running multiple MySQL service process, through a different socket to monitor different service ports to provide their services. Each instance is independent of each other, each instance of the datadir, port, socket, pid are different.<\/p>\n

2. Multi-instance features * Effective use […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6871"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6871"}],"version-history":[{"count":3,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6871\/revisions"}],"predecessor-version":[{"id":6873,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6871\/revisions\/6873"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6871"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6871"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6871"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}