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.
2. Multi-instance features
* 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.
* 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.
[root@MySQL ~]# cat /etc/RedHat-release
CentOS release 6.9 (Final)
[root@MySQL ~]# uname -r
2.6.32-696.3.2.el6.x86_64
[root@MySQL ~]# getenforce
Disabled
[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql
[root@MySQL ~]# useradd -r -s /sbin/nologin mysql
[root@MySQL ~]# mkdir -v /usr/local/mysql/mysql-files
mkdir: created directory `/usr/local/mysql/mysql-files’
[root@MySQL ~]# mkdir -vp /data/mysql_data{1..4}
mkdir: created directory `/data’
mkdir: created directory `/data/mysql_data1′
mkdir: created directory `/data/mysql_data2′
mkdir: created directory `/data/mysql_data3′
mkdir: created directory `/data/mysql_data4′
1 [root@MySQL ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64
[root@MySQL ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}
/etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /tmp/mysql_multi.log
[mysqld1]
datadir = /data/mysql_data1
socket = /tmp/mysql.sock1
port = 3306
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[mysqld3]
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3
port = 3308
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[mysqld4]
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4
port = 3309
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data4
[root@MySQL ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@MySQL ~]# chmod +x /etc/init.d/mysqld_multi
[root@MySQL ~]# chkconfig –add mysqld_multi
[root@MySQL ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
[root@MySQL ~]# /etc/init.d/mysqld_multi start
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@MySQL ~]# netstat -lntp | grep mysqld
tcp 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
[root@MySQL ~]# /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 6
Server version: 5.7.18
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
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)
[root@MySQL ~]# /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 7
Server version: 5.7.18
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
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)
Recent Comments