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

1. Background

* centralized OPS of MySQL database can be deployed to run multiple MySQL service processes on one server, and different socket listens to different service ports to provide their own services. Each instance is independent of each other, and the datadir, port, socket and pid of each instance are different.

* multiple instances on the Internet are generally implemented with the same instance version. This time, multiple instances are deployed with different versions (5.5, 5.6, 5.7).

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-504.el6.x86_64 [root@MySQL ~] # getenforce Disabled

4. MySQL binary package preparation

* download the official 5.5 binary installation package

[root@MySQL ~] # 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

[root@MySQL ~] # 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

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

5. Initialize and uniformly change passwords in mysql version

* create MySQL users

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

* create a MySQL data directory

[root@MySQL ~] # mkdir-vp / data/mysql_data_ {5.. 7} mkdir: created directory `/ data'mkdir: created directory` / data/mysql_data_5'mkdir: created directory `/ data/mysql_data_6'mkdir: created directory` / data/mysql_data_7'

* modify the users and groups of the MySQL data directory

[root@MySQL] # chown mysql.mysql-R / data/mysql_data_*

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

[root@MySQL] # tar zxf mysql-5.5.57-linux-glibc2.12-x86_64.tar.gz-C / usr/local/ [root@MySQL ~] # tar zxf mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz-C / usr/local/ [root@MySQL ~] # tar xf mysql-5.7.19-linux-glibc2.12-x86_64.tar-C / usr/local/

* MySQL 5.5 initialization

[root@MySQL] # chown mysql.mysql-R / usr/local/mysql-5.5.57-linux-glibc2.12-x86_64 [root@MySQL] # / 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

[root@MySQL] # / usr/local/mysql-5.5.57-linux-glibc2.12-x86_64/bin/mysqld_safe-- datadir=/data/mysql_data_5 & [root@MySQL] # / 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 [root@MySQL ~] # killall mysqld

* MySQL 5.6 initialization

[root@MySQL] # chown mysql.mysql-R / usr/local/mysql-5.6.37-linux-glibc2.12-x86_64 [root@MySQL] # / 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

[root@MySQL] # / usr/local/mysql-5.6.37-linux-glibc2.12-x86_64/bin/mysqld_safe-- datadir=/data/mysql_data_6 & [root@MySQL] # / 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 [root@MySQL ~] # killall mysqld

* MySQL 5.7 initialization [pay attention to the random password prompted for initialization]

[root@MySQL ~] # mkdir / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/mysql-files [root@MySQL ~] # chown root.mysql-R / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64 [root@MySQL ~] # chown mysql.mysql-R / data/mysql_data_7 / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/mysql-files [root@MySQL ] # / 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

[root@MySQL] # / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld_safe-- datadir=/data/mysql_data_7 & [root@MySQL] # / 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 mysql, 0 rows affected (0.00 sec) mysql > quitBye [root@MySQL ~] # 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# set mysql listening IP address bind_address = 0.0.0." turn off DNS anti-resolution skip-name-resolve = turn off listening performance_schema = off# set buffer pool size innodb_buffer_pool_size = 32M# set error log file name log_error = error. Log [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 the path where mysql runs the program mysqld = / usr/local/mysql-5.5.57-linux-glibc2.12-x86_64/bin/mysqld# set the path where mysql management runs the program mysqladmin = / usr / local/mysql-5.5.57-linux-glibc2.12-x86_64/bin/mysqladmin# sets instance data directory-- different datadir = / data/mysql_data_5# sets socket file path in multiple instances-- different socket = / tmp/mysql.sock5# sets instance listening port in multi-instances-- different port = 3305 [mysqld6] basedir = / usr in multiple instances / local/mysql-5.6.37-linux-glibc2.12-x86_64mysqld = / usr/local/mysql-5.6.37-linux-glibc2.12-x86_64/bin/mysqldmysqladmin = / usr/local/mysql-5.6.37-linux-glibc2.12-x86_64/bin/mysqladmindatadir = / 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 to / etc/init.d/ from the support-files directory in the random binary package

[root@MySQL ~] # cp / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/support-files/mysqld_multi.server / etc/init.d/mysqld_ multi[ root @ MySQL ~] # chmod + x / etc/init.d/mysqld_multi

* create soft links in any version and set environment variables

[root@MySQL ~] # ln-s / usr/local/mysql-5.7.19-linux-glibc2.12-x86_64 / usr/local/mysql [root@MySQL ~] # export PATH=/usr/local/mysql/bin:$PATH

7. test

* View the status of multiple instances

[root@MySQL ~] # / etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld5 is not runningMySQL server from group: mysqld6 is not runningMySQL server from group: mysqld7 is not running

* start multiple instances [wait a few seconds]

[root@MySQL ~] # / etc/init.d/mysqld_multi start [root@MySQL ~] # / 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 [root@MySQL ~] # netstat-lntp | grep mysqldtcp 0 0 0.0 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0 of the grep mysqldtcp * LISTEN 43750 / Mysqld tcp 0 0 0.0.0.0 LISTEN 43756/mysqld 3306 0.0.0.0 LISTEN 43756/mysqld * LISTEN 43753/mysqld tcp 0 0 0.0.0.0 LISTEN 43756/mysqld

* connect instances separately

[root@MySQL] # 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 [root@MySQL ~] # 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 [root@MySQL ~] # 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) 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

[root@MySQL ~] # / etc/init.d/mysqld_multi stop [root@MySQL ~] # / etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld5 is not runningMySQL server from group: mysqld6 is not runningMySQL server from group: mysqld7 is not running

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