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

This article mainly gives you a brief introduction to the use of MySQL5.7 to achieve multi-example explanation, related professional terms can be checked on the Internet or find some related books to supplement, here do not dabble in, straight to the topic, hoping to bring you some practical help.

Preface

In this paper, the mysql5.7 version is used to implement multiple instances, and the ports are 3306 and 3307.

1. Multi-instance nature

Open multiple different mysql service ports (3306 and 3307) on a machine and run multiple mysql service processes that provide their services through different socket listening on different service ports

Multiple instances share a set of mysql installers, configuration files can be the same (but preferably different, different configuration files are used in this article), startup programs can use the same (preferably different, text with different startup scripts), and data files are different (must be different)

The hardware resources of CVM are common, and logically multiple instances are independent.

2. The function of multiple instances.

Utilize CVM resources effectively and save CVM resources

However, multiple instances will definitely preempt each other's resources. When a service instance has high concurrency or slow query, it will consume more memory and CPU of the whole server, which will inevitably cause other instances to run very slowly.

3. Multi-instance application scenarios

Companies that are cash-strapped

Concurrent access is not a very big business.

4. Install multi-instance database

1. Create the appropriate directory

Mkdir / mysql/ {3306 mysql/ 3307} / {data,logs,conf,tmp}-mysql socket / directory interpretation data # storage data logs # storage mysql log and binlog log conf # storage mysql configuration file tmp # storage mysql socket file

two。 Create mysql user, login method nologin, do not create home directory

Groupadd mysql-g 1002useradd mysql-u 1002-g 1002-s / sbin/nologin-M

3. Install some dependency packages

Yum install ncurses-devel libaio-devel-y

4. Upload mysql 5.7to native / application directory

/ / extract mysql5.7 and move it to / application/mysql-5.7.20 directory mkdir-p / application/mysql-5.7.20 tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz. / mysql-5.7.20mv mysql-5.7.20-linux-glibc2.12-x86_64/* mysql-5.7.20/rm-rf mysql-5.7.20-linux-glibc2.12-x86_64// Soft connection ln-s / application/mysql-5.7.20/ / application/mysql// adds / application/mysql/bin directory to PATH environment variable vim / etc/profileexport MYSQL_HOME=/application/mysql/binexport PATH=$PATH:$MYSQL_HOME

5. Configure my.cnf file for each instance

3306 instances

# vim / mysql/3306/conf/ my.cnf [mysqld] basedir=/application/mysqldatadir=/mysql/3306/datatmpdir = / mysql/3306/tmpsocket=/mysql/3306/tmp/mysql.socklog-error=/mysql/3306/logs/mysql.logport=3306server_id=3306max_connections = 1000innodb_buffer_pool_size = 260Mskip_name_resolve# character set character-set-server = utf8mb4collation-server = utf8mb4_general_cinit_connect='SET NAMES utf8mb4'# bin-log log log-bin=/mysql/3306/data/mysql-binbinlog_ Format=row# slow _ query_log = 1slow_query_log_file = / mysql/3306/logs/slow.loglog_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 5long_query_time = 1 [mysql] socket=/mysql/3306/tmp/ mysql.sock [mysqladmin] socket=/mysql/3306/tmp/mysql.sock

3307 instances

# vim / mysql/3307/conf/ my.cnf [mysqld] basedir=/application/mysqldatadir=/mysql/3307/datatmpdir = / mysql/3307/tmpsocket=/mysql/3307/tmp/mysql.socklog-error=/mysql/3307/logs/mysql.logport=3307server_id=3307max_connections = 1000innodb_buffer_pool_size = 260Mskip_name_resolve# character set character-set-server = utf8mb4collation-server = utf8mb4_general_ci# bin-log log log-bin=/mysql/3307/data/mysql-binbinlog_format=row# slow log slow _ query_log = 1slow_query_log_file = / mysql/3307/logs/slow.loglog_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 5long_query_time = 1 [mysql] socket=/mysql/3307/tmp/mysql.sock [mysqladmin] socket=/mysql/3307/tmp/mysql.sock

6. Initialize the mysql database file using the mysqld command

/ / authorize mysql users to manage the corresponding mysql directory chown-R mysql:mysql / mysql/// initialize multi-instance mysqld-- defaults-file=/mysql/3306/conf/my.cnf-- basedir=/application/mysql-- initialize-insecure-- user=mysqlmysqld-- defaults-file=/mysql/3307/conf/my.cnf-- basedir=/application/mysql-- initialize-insecure-- user=mysql

7. Start multi-instance mysql with the mysqld_safe command

Mysqld_safe-defaults-file=/mysql/3306/conf/my.cnf & mysqld_safe-defaults-file=/mysql/3307/conf/my.cnf &

8. Check operation

/ / check whether ps-ef exists in the process | grep mysql | grep-v "grep" / / check whether ss-lntup exists on the port | grep-E "3306 | 3307" / / Local login (- S specifies the socket of different instances) mysql-uroot-p-S / mysql/3306/tmp/mysql.sock

9. Multi-instance startup script

#! / bin/bashPORT=3306SOCK_FILE_LOCK=/mysql/$ {PORT} / tmp/mysql.sock.lock# startfunction func_start () {if [- f ${SOCK_FILE_LOCK}]; then echo "MySQL ${PORT} has started..." Exit 1else echo "MySQL ${PORT} is start..." Mysqld_safe-- defaults-file=/mysql/3306/conf/my.cnf > / dev/null & result=$? [${result}-ne 0] & & echo "MySQL ${PORT} start failed..." | | echo "MySQL ${PORT} start success.." fi} # stopfunction func_stop () {if [- f ${SOCK_FILE_LOCK}]; then echo "MySQL ${PORT} is stop..." Kill $(cat / mysql/$ {PORT} / tmp/mysql.sock.lock) count=0 while [$count-ne 60] do ((count++)) # echo $count sleep 1 if [!-f ${SOCK_FILE_LOCK}]; then echo "MySQL ${PORT} stop success..." Break else echo "MySQL ${PORT} is stop..." Fi doneelse echo "MySQL ${PORT} don't start..." Exit 1fi} case $1 in start) func_start;; stop) func_stop sleep 3 func_start echo "MySQL ${PORT} restart success...";; esac

You just need to replace the PORT variable

Use MySQL5.7 to achieve multi-instance explanation first to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.