MySQL--------Multi-version multi-instance hybrid deployment

1. Background

* centralized operation of MySQL database, you can run multiple MySQL service processes on a single server, through different sockets to listen to 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.

* Multi-instance on-line is generally implemented by the same instance version, this time with different versions to achieve multi-instance deployment (5.5, 5.6, 5.7).

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 "alt=" Wkiol1lpympxk5ulaadahffcdkg045.jpg "/>

3. Environment [Off SELinux]

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

4. MySQL Binary Package Preparation

* Download the official 5.5 binary installation package

[Email protected] ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.57-linux-glibc2.12-x86_64.tar.gz

* Download the official 5.6 binary installation package

[Email protected] ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz

* Download the official 5.7 binary installation package

[Email protected] ~]# wget Https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar

5. mysql version initialization and unified password change

* Create MySQL user

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

* create MySQL Data directory

[[email protected] ~]# mkdir-vp/data/mysql_data_{5..7}mkdir:created directory '/data ' mkdir:created directory '/data/m Ysql_data_5 ' mkdir:created directory '/data/mysql_data_6 ' mkdir:created directory '/data/mysql_data_7 '

* modify MySQL Data directory user and owning group

[Email protected] ~]# chown mysql.mysql-r/data/mysql_data_*

* unzip the MySQL version to the/usr/local directory

[[Email protected] ~]# tar zxf mysql-5.5.57-linux-glibc2.12-x86_64.tar.gz-c/usr/local/[[email protected] ~]# tar zxf mys Ql-5.6.37-linux-glibc2.12-x86_64.tar.gz-c/usr/local/[[email protected] ~]# tar XF mysql-5.7.19-linux-glibc2.12-x86_ 64.tar-c/usr/local/

* MySQL 5.5 initialization

[Email protected] ~]# chown mysql.mysql-r/usr/local/mysql-5.5.57-linux-glibc2.12-x86_64[[email protected] ~]#/usr/ local/mysql-5.5.57-linux-glibc2.12-x86_64/scripts/mysql_install_db--user=mysql--datadir=/data/mysql_data_5-- Basedir=/usr/local/mysql-5.5.57-linux-glibc2.12-x86_64

  * mysql 5.5 Change password

[[email protected] ~]# /usr/local/mysql-5.5.57-linux-glibc2.12-x86_64/bin/mysqld_safe -- datadir=/data/mysql_data_5 &[[email protected] ~]# /usr/local/ mysql-5.5.57-linux-glibc2.12-x86_64/bin/mysqlwelcome to the mysql monitor.   commands end with ; or \g.your mysql connection id is  1server version: 5.5.57 mysql community server  (GPL) Copyright  (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 = password (' 123 '); query ok, 0 rows affected  (0.00 sec) mysql> quitbye[[email protected ] ~]# killall mysqld

* MySQL 5.6 Initialization

[Email protected] ~]# chown mysql.mysql-r/usr/local/mysql-5.6.37-linux-glibc2.12-x86_64[[email protected] ~]#/usr/ local/mysql-5.6.37-linux-glibc2.12-x86_64/scripts/mysql_install_db--user=mysql--datadir=/data/mysql_data_6-- Basedir=/usr/local/mysql-5.6.37-linux-glibc2.12-x86_64

* MySQL 5.6 Change Password

[[email protected] ~]# /usr/local/mysql-5.6.37-linux-glibc2.12-x86_64/bin/mysqld_safe -- datadir=/data/mysql_data_6 &[[email protected] ~]# /usr/local/ mysql-5.6.37-linux-glibc2.12-x86_64/bin/mysqlwelcome to the mysql monitor.   commands end with ; or \g.your mysql connection id is  1server version: 5.6.37 mysql community server  (GPL) Copyright  (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 = password (' 123 '); query ok, 0 rows affected  (0.00 sec) mysql> quitbye[[email protected ] ~]# killall mysqld

* MySQL 5.7 Initialization [ note the random password of the initialization prompt ]

[Email protected] ~]# mkdir/usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/mysql-files[[email protected] ~]# chown Root.mysql-r/usr/local/mysql-5.7.19-linux-glibc2.12-x86_64[[email protected] ~]# chown mysql.mysql-r/data/mysql_ Data_7/usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/mysql-files[[email protected] ~]#/usr/local/ Mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld--initialize--user=mysql--datadir=/data/mysql_data_7--basedir=/ Usr/local/mysql-5.7.19-linux-glibc2.12-x86_64

* MySQL 5.7 Change Password

[[email protected] ~]# /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld_safe -- datadir=/data/mysql_data_7 &[[email protected] ~]# /usr/local/ Mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysql -p ' Inogk (hoj9>/' 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 4server 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 =  ' 123 '; query ok, 0 rows affected  (0.00 sec) mysql> quitbye[[email protected ] ~]# killall mysqld

6. Multi-Version deployment

* Edit/etc/my.cnf

[client]#  Set Login user user = root#  set login user password password = 123[mysqld]# mysql  Run user user = mysql#  settings  mysql  monitoring  IP  address bind_address = 0.0.0.0#  Close  DNS  Reverse resolution skip-name-resolve = 0#  turn off monitoring performance_schema = off#  Set buffer pool  size innodb_buffer_pool_size = 32m[mysqld_multi]#  set multi  log log  = /tmp/mysql_multi.log[mysqld5]#  set the directory where the instance is located basedir = /usr/local/ mysql-5.5.57-linux-glibc2.12-x86_64#  set Instance Data directory  --  multi-instance must be different Datadir = /data/mysql_data_ 5#  set socket  file path  --  Multiple instances must be different socket = /tmp/mysql.sock5#  set Instance listener port  --   Multiple instances must be different port = 3305[mysqld6]basedir = /usr/local/mysql-5.6.37-linux-glibc2.12-x86_ 64DATADIR = /DATA/MYSQL_DATA_6SOCKET = /TMP/MYSQL.SOCK6PORT = 3306[MYSQLD7] basedir = /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64datadir = /data/mysql_data_7socket = /tmp/mysql.sock7port = 3307 

* Copy the Mysqld_multi.server startup script from the Support-files directory in the random version binary package to/etc/init.d/

[Email protected] ~]# cp/usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/support-files/mysqld_multi.server/etc/ Init.d/mysqld_multi[[email protected] ~]# chmod +x/etc/init.d/mysqld_multi

* free version to initiate soft links and set environment variables

[Email protected] ~]# ln-s/usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/usr/local/mysql[[email protected] ~]# Export Path=/usr/local/mysql/bin: $PATH

7. Testing

* View Multi-instance status

[[email protected] ~]#/etc/init.d/mysqld_multi reportreporting MySQL serversmysql server from GROUP:MYSQLD5 are not Runni Ngmysql server from GROUP:MYSQLD6 are not runningmysql servers from GROUP:MYSQLD7 are not running

* Start multiple instances [wait a few seconds]

[[email protected] ~]# /etc/init.d/mysqld_multi start [[email protected] ~] # /etc/init.d/mysqld_multi reportreporting mysql serversmysql server from  group: mysqld5 is runningmysql server from group: mysqld6 is  runningmysql server from group: mysqld7 is running[[email protected] ~ ]# netstat -lntp | grep mysqldtcp        0       0 0.0.0.0:3305                 0.0.0.0:*                    listen      43750/ Mysqld        tcp        0       0 0.0.0.0:3306                 0.0.0.0:*                    LISTEN      43753/mysqld         tcp        0       0 0.0.0.0:3307                 0.0.0.0:*                    listen      43756/mysqld

* Connect instances separately

[[Email protected] ~]# mysql -s /tmp/mysql.sock5welcome to the mysql  monitor.  commands end with ; or \g.your mysql connection  id is 4Server version: 5.7.18 MySQL Community Server  (GPL) copyright  (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> quitbye[[email protected] ~]# mysql -s /tmp/ Mysql.sock6welcome to the mysql monitor.  commands end with ; or \g.your mysql connection id is 4server version: 5.7.18  mysql community server  (GPL) copyright  (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> quitbye[[email protected] ~]# mysql -s /tmp/ mysql.sock7welcome to the mysql monitor.  commands end with ;  or \g.Your MySQL connection id is 4Server version: 5.7.18  mysql community server  (GPL) copyright  (c) &nbsP;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> quitbye

* Stop Multiple instances

[Email protected] ~]#/etc/init.d/mysqld_multi stop[[email protected] ~]#/etc/init.d/mysqld_multi reportreporting MySQL Serversmysql Server from GROUP:MYSQLD5 are not runningmysql servers from GROUP:MYSQLD6 are not runningmysql server fr Om Group:mysqld7 is not running

8. 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/1948537

MySQL--------Multi-version multi-instance hybrid deployment