1. The centralized operation of the MySQL database can be done by deploying multiple MySQL service processes on a single server, and using different sockets to monitor 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. 2. Multi-instance features * Efficient 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. 3. Environment [off SELinux][[email protected] ~]# cat/etc/redhat-releasecentos release 6.9 (Final) [[email Protected] ~]# uname-r2.6.32-696.3.2.el6.x86_64 [[email protected] ~]# getenforceDisabled4. Deploy [4 instances]* download MySQL 5.7 Two package [recommended official DOWNLOAD] This download version is greater than 5.7.5[[email protected] ~]# wget wget https://dev.mysql.com/get/Downl oads/mysql-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz* unzip the MySQL 5.7 binary package to the specified directory [[email protected] ~]# tar zxvf Mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local/* Create MySQL soft link [[email protected] ~]# ln-s/usr/local/ mysql-5.7.18-linux-glibc2.5-x86_64/usr/local/mysql* Create a MySQL user [[email protected] ~]# groupadd-r mysql[[email& Nbsp;protected] ~]# useradd-r -G mysql-s/bIn/false mysql* Create mysql-files directory in MySQL binary package directory [MySQL data import/Export Data exclusive directory][[email protected] ~]# mkdir-v/usr/local/mysq l/mysql-filesmkdir:created directory '/usr/local/mysql/mysql-files ' * Create multi-instance data directory [[email protected] ~]# 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 ' * Modify the MySQL binary package directory of the owning user and the owning group 1 [[email protected] ~]# chown mysql:mysql-r/usr/local/mysql-5.7.18-linux-glibc2.5-x86 _64* Modify MySQL Multi-instance data directory with data import/export exclusive directory of the owning user and owning group [[email protected] ~]# chown mysql:mysql-r/usr/local/mysql/ mysql-files/data/mysql_data{1..4}* Configuring MySQL configuration file/etc/my.cnf[mysqld][mysqld_multi]mysqld =/usr/local/mysql/bin/ Mysqldmysqladmin =/usr/local/mysql/bin/mysqladminlog =/tmp/mysql_multi.log [mysqld1]# Set Data directory [must be different in multiple instances] DataDir =/data/mysql_data1# Set sock store file name [must be different in multiple instances]socket =/tmp/mysql.sock1#Set listening open ports [multiple instances must be different]port = 3306# Set Run user = mysql# shutdown monitoring Performance_schema = off# setting InnoDB cache Size Innodb_buffer_pool_size = 32m# Set Listener IP Address bind_address = 0.0.0.0# Turn off DNS reverse Resolution skip-name-resolve = 0 [mysqld2]datadir =/data/mysql_data2socket =/T Mp/mysql.sock2port = 3307user = Mysqlperformance_schema = Offinnodb_buffer_pool_size = 32mbind_address = 0.0.0.0skip-name-resolve = 0 [mysqld3]datadir =/data/mysql_data3socket =/tmp/mysql.sock3port = 3308user = Mysqlperformance_schema = Offinnodb_buffer_pool_size = 32mbind_address = 0.0.0.0skip-name-resolve = 0 [mysqld4] DataDir =/data/mysql_data4socket =/tmp/mysql.sock4port = 3309user = Mysqlperformance_schema = Offinnodb_buffer_pool_ Size = 32mbind_address = 0.0.0.0skip-name-resolve = 0* initializes each instance [after initialization is complete, it comes with a random password in the output log][[email protected] ~]#/usr/loc Al/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data1[[email Protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data2[[email protected] ~]#/usr/local/mysql/bin/mysqld-- Initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data3[[email protected] ~]#/usr/ Local/mysql/bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data4* Each instance turns on SSL connection [[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql-- Datadir=/data/mysql_data1[[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_rsa_setup--user=mysql-- Basedir=/usr/local/mysql--datadir=/data/mysql_data2[[email protected] ~]#/usr/local/mysql/bin/mysql_ssl_ Rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data3[[email protected] ~]#/usr/local /mysql/bin/mysql_ssl_rsa_setup--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql_data4* Copy the multi-instance script to the service management directory [/etc/init.d/][[email protected] ~]# cp/usr/local/mysql/support-files/mysqld_multi.server/etc/init.d/mysqld_multi* Add script execution permissions [[email protected] ~]# chmod +x/etc/init.d/mysqld_multi* add into service services management [[ Email protected] ~]# chkconfig--add mysqld_multi5. Start the test ( failed add variable export path= $PATH:/usr/local/mysql/bin) * Check multi-instance status [[email protected] ~]#/etc/init.d/mysqld_multi reportreporting MySQL serversmysql server from Group:mysqld1 Not RUNNINGMYSQL servers from GROUP:MYSQLD2 are not runningmysql servers from GROUP:MYSQLD3 are not runningmysql servers from Group:mysqld4 is not running* start multi-instance [[email protected] ~]#/etc/init.d/mysqld_multi start (or) service Mysqld_mu LTI start* View Multi-instance status:/etc/init.d/mysqld_multi reportreporting MySQL serversmysql server from Group:mysqld1 is RUNNINGMYSQ L server from Group:mysqld2 are Runningmysql server from GROUP:MYSQLD3 are Runningmysql server from GROUP:MYSQLD4 are Runn Ing* viewing instance listening port [[email protected] ~]# NETSTAT-LNTP | grep mysqldtcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2673/mysqldtcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2676/mysqldtcp 0 0 0.0.0 .0:3308 0.0.0.0:* LISTEN 2679/mysqldtcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 2682/mysqld6. Connection Test (after a picture)* Example 1[[email protected] ~]#/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 is insecure. Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.7.18 copyright (c), and Oracle and/or its affiliates. All rights Reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names is trademarks of their respectiveowners. 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) * Instance 2[[email protected] ~]#/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 7Server version:5.7.18 copyright (c), and Oracle and/or its affiliates. All rights Reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names is trademarks of their respectiveowners. 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)
MySQL multi-instance installation
