MySQL Multi-instance explanation

Directory

First, the basic concept

1, MySQL multi-instance

is to open multiple different ports on a single machine and run multiple MySQL service processes. These MySQL multi-instance common set of installers, using different (can also be the same) profile, launcher, data files. In the provision of services, multi-instance MySQL logically appears to be separate, multiple instances of itself is based on the configuration file corresponding to the set value, to obtain the relevant hardware resources of how much.

2. Application Scenario

1. The reasons for using a data pseudo-distributed architecture, and the initial start of the project does not necessarily have that many users, for this first set of physical database server, but the deployment of multiple instances, easy to follow-up migration

2. To circumvent the shortcomings that MySQL does not support for SMP architectures, use multi-instance binding processors to allocate different databases to different instances to provide data services

3. A physical database server supports data services for multiple databases, with multi-instance deployment to improve the recovery efficiency of MySQL replicated slave machines

4. Already for the dual-master replication MySQL database server architecture, want to part of the important business data more than a remote computer room hot backup, and MySQL replication temporarily does not support multi-master replication mode, and do not provide services to the user, in order to effectively control costs, will consider a remote computer room to deploy an excellent performance of the physical server, Even in the case of a disk enclosure, this will also deploy multiple instances;

5. The traditional game industry MMO/MMORPG, as well as Web game, each service corresponds to a database, and may have to do a lot of data query and data correction work, in order to reduce the probability of maintenance errors, may also adopt multi-instance deployment, the concept of the allocation of the database by District;

3. Functions and problems

1, the efficient use of server resources, a single server resources have surplus, you can make full use of the remaining resources to provide more services.

2, the resources compete with each other, such as memory, CPU needs to turn on NUMA, and MySQL bound to the fixed core, the network card interrupt request, resource contention, the most important is the disk IO

MySQL Multi-instance configuration method

1. Single configuration file

2, multi-configuration files.

Second, practical steps:

1. Synchronization Time

2. Prepare MySQL Dependency package

3. Environment Preparation

3.1 Adding a MySQL user

3.2 Compiling and installing MySQL

4. Install MySQL multi-instance

5. Prepare the configuration file

6. Start MySQL

7, login MySQL and some security measures

8. Turn off MySQL

9, provide simple management script

10. Create a password for the root user of MySQL

11, attached, multi-configuration file installation MySQL multi-instance, here to increase the MySQL 3309 port

12. Summary

1. Synchronization Time

Yum install epel*ntp 202.120.2.101yum install-y ntpntpdate 202.120.2.101hwclock-w

2. Prepare MySQL dependency package

Yum install-y autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool* openssl*yum Install cmake-y

3. Environment Preparation

3.1 adding mysql users

Groupadd mysqluseradd-g MySQL MySQL mkdir-pv/data/{3306,3307,3308}/datachown-r mysql.mysql/data/cd/usr/local/src/

3.2 compiling and installing MySQL

TAR-XF mysql-5.5.45.tar.gzcd mysql-5.5.45chown mysql.mysql-r. CMake. -dcmake_install_prefix=/usr/local/mysql-dmysql_datadir=/data-dsysconfdir=/etc-dwith_innobase_storage_engine=1- dwith_archive_storage_engine=1-dwith_blackhole_storage_engine=1-dwith_readline=1-dwith_ssl=system-dwith_zlib= System-dwith_libwrap=0-dmysql_unix_addr=/tmp/mysql.sock-ddefault_charset=utf8-ddefault_collation=utf8_general_ Cigmakemake Install

4. Install MySQL Multi-instance

cd/usr/local/mysql/scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/data/3306/scripts/ mysql_install_db--user=mysql--basedir=/usr/local/mysql--datadir=/data/3307/scripts/mysql_install_db--user= MySQL--basedir=/usr/local/mysql--datadir=/data/3308/

5. Prepare the configuration file

Mysqld_multi--example >/data/multi.cnf #提供多配置文件模板

vim /data/multi.cnf    #修改模板, it's simpler here [mysqld_multi]mysqld     =  /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser        = root   #这个用户应该有关机权限, and then no other permissions. It is recommended to create a generic, multi_admin user control other MySQL user, here is an example #grant shutdown on *.* to [email  protected] identified by  ' Password '  [mysqld1]socket     = / Tmp/mysql.sock1port       = 3306pid-file   = /data /3306/mysql.piddatadir    = /data/3306/data [mysqld2]socket      = /tmp/mysql.sock2port       = 3307pid-file    = /data/3307/mysql.piddatadir    = /data/3307/data#language    = /usr/local/mysql/share/mysql/english#user       = UNIX_USER1 [MYSQLD3] #mysqld      =  /path/to/mysqld_safe#ledir      = /path/to/mysqld-binary/#mysqladmin  = /path/to/mysqladminsocket     = /tmp/mysql.sock3port        = 3308pid-file   = /data/3308/mysql.piddatadir     = /data/3308/data#language   = /usr/local/mysql/share/mysql/swedish #user        = unix_user2

the above is a simple configuration file. This configuration file only lets MySQL start up.

6. Start MySQL

mysqld_multi --defaults-file=/data/multi.cnf start 1,2,3ss -tnl   # Viewing the Listening port listen     0      50                          *:3306                      *:*     LISTEN     0       50                         *:3307                      *:*      LISTEN     0      50                         *:3308                      *: *

7, login mysql and some security measures

Mysql-s/tmp/mysql.sock1 #指定socket登录

mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  test               |+-------- ------------+4 rows in set  (0.01 sec)  mysql> drop database  Test query ok, 0 rows affected  (0.04 sec)  mysql> select user,host  from mysql.user;+------+-----------+| user | host       |+------+-----------+| root | 127.0.0.1 | |  root | ::1       | |       | localhost | |  root | localhost | |       | node1     | |  root | node1     |+------+-----------+6 rows in set   (0.01 sec)  mysql> delete from mysql.user where user= '; query ok, 2 rows affected  (0.00 sec)  mysql> delete from  Mysql.user where host= ':: 1 '; query ok, 1 row affected  (0.00 sec)  mysql> delete from  mysql.user where host= ' Node1 '; query ok, 1 row affected  (0.00 SEC)

The rest of the operation is similar, not the demo

8. Turn off MySQL

Mysqladmin-s/tmp/mysql.sock1 shutdown recommends using Musqld_multi--defaults-file stop [#,#]killall mysqld not recommended

9,provide simple management script

#!/bin/bash#basedir=/usr/local/mysqlbindir=/usr/local/mysql/binconf=/data/multi.cnfexport path= $bindir:/$ pathif test -x  $bindir/mysqld_multi then    mysqld_multi= "$bindir/ Mysqld_multi ";  else    echo " Can ' t execute  $bindir/mysqld_multi  from dir  $basedir ";    exit;  ficase "  in       ' start '  )            "$mysqld _ Multi " --defaults-extra-file= $conf  start $2           ;;        ' Stop '  )            ' $mysqld _multi " --defaults-extra-file= $conf  stop $2           ;;        ' report '  )            "$mysqld _multi"  --defaults-extra-file= $conf  report $2           ;;        ' restart '  )             "$mysqld _multi"  --defaults-extra-file= $conf  stop $2            "$mysqld _multi"  --defaults-extra-file= $conf  start $2           ;;       *)           echo  " Usage: $0 {start|stop|report|restart} " >&2           ;;   esac

Createa password for the root user of MySQL

Mysqladmin-uroot-s/tmp/mysql.sock2 Password "123456" #指定socket文件位置即可

the rest of the operation is the same

One, attached, multi-config file install mysql multi-instance, here add mysql 3309 port

mkdir -pv /data/3309/data                   chown mysql.mysql -R  3309/cd /usr/local/mysqlscripts/mysql_install_db --datadir=/data/3309/data/ --user=mysql - -BASEDIR=/USR/LOCAL/MYSQL/CP SUPPORT-FILES/MY-SMALL.CNF /DATA/3309/MY.CNF 

vim /data/3309/my.cnf[client]port        = 3309                   #这里也需要注意下吧socket             = /tmp/mysql.sock4  [ mysqld]port        = 3309               #注意端口socket              = /tmp/mysql.sock4           # Note Socketskip-external-lockingkey_buffer_size = 16kmax_allowed_packet = 1mtable_open_cache  = 4sort_buffer_size = 64kread_buffer_size = 256kread_rnd_buffer_size =  256Knet_buffer_length = 2Kthread_stack = 128Kdatadir=/data/3309/data          #注意datadirserver-id  = 1 [mysqldump]quickmax_allowed_packet = 16m 

 mysqld_safe --defaults-file=/data/3309/my.cnf 2&1 > > /dev/null     #启动mysqld 

ss -tnl | grep 330                  #查看端口LISTEN      0      50                          *:3306                      *:*     LISTEN      0      50                         *:3307                       *:*     listen     0      50                          *:3308                      *:*     LISTEN     0       50                         *:3309                      *:*

The rest of the startup method is similar to the Shutdown method

12. Summary:

1, through this MySQL multi-instance installation, we have been able to decide according to their preferences is to install the MySQL multi-instance through multiple configuration files , or but configure a single file installation.

2, the process is relatively simple, there is no particularly clever skills, but by the small see big, after the configuration file can be perfected.

3, is the following will also be the intention to write a series of MySQL blog, I hope the expert guidance, greatly appreciated

Reference Link: http://freeloda.blog.51cto.com/2033581/1349312

This article is from the "Linux Growth path" blog, so be sure to keep this source http://amyhehe.blog.51cto.com/9406021/1696975

MySQL Multi-instance explanation