MySQL multi-instance application configuration Deployment Guide

One, Mysql multiple instances of the role of the problem

1, efficient use of server resources

When a single server resource has remaining, you can take advantage of the remaining resources to provide more services

2, save the resources of the server

When the company resources are tight, but the database needs to provide services independently, and the need for master-slave synchronization and other technologies, the use of multi-instance is the best.

3, the problem of resource preemption

When a service instance is high or slow to query, the entire instance consumes more CPU, memory, disk IO resources, resulting in the service quality of other instances on the server greatly reduced, equivalent to a large house, multiple bedrooms, everyone shared a bathroom, breakfast up, a person to the toilet, long-term occupancy, Other people have to wait for the same reason.

Second, Mysql multi-instance application scenario

1, a resource-intensive company

Small business volume, do not want to spend money, and more demand, so particularly suitable for multi-instance

2, concurrent access is not a particularly large site

When the company's business volume is not suitable, server resources are not fully utilized, there is a waste of time, you can use multi-instance

3, Baidu search engine used a multi-instance, Sina Network also used a multi-instance

Purpose, save IDC space and make full use of resources

Sina configuration SATA 15000 to 4 blocks do RAID5 48G RAM

Three, MySQL multi-instance configuration scheme

Scenario One: If you recommend using this

The advantages of this method are simple logic, simple configuration, low coupling degree

The disadvantage is that it is not easy to manage.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/85/wKioL1dXykbxo2UUAABz9qsUEVg225.jpg "title=" 001. JPG "alt=" wkiol1dxykbxo2uuaabz9qsuevg225.jpg "/>

Scenario Two: A single deployment scenario is not recommended, the coupling is too high

[Mysqld_muti]

That is, a profile launches multiple instances

Iv. start configuration of MySQL multi-instance

Environment Introduction:

MySQL version: 5.6.27

Operating system: Centos 6.5

Number of MySQL instances: 2

Instance occupancy ports are: 3306, 3307, respectively

First Configure Scenario One:

1. Download: Download on http://dev.mysql.com/downloads/mysql/official website

2. Unzip

Tar xvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz

3. Move to/usr/local/mysql

MV Mysql-5.6.27-linux-glibc2.5-x86_64 MySQL

4. Install the dependent LIB package:

Centos:yum Install Libaio-y

5. Configure user, Directory

shell> Groupadd mysqlshell> useradd-r-g MySQL mysqlshell> cd/usr/local/mysqlshell> chown-r MySQL. shell> ; Chgrp-r MySQL.

6. Use/data/directory as the total directory of MySQL multi-instance

Mkdir-p/data/{3306,3307}/data

7. Start configuring multi-instance configuration files

vim  /data/3306/my.cnf[mysqld]   #general  basedir = /usr/local/mysql  Datadir = /data/3306/data socket = /data/3306/mysql.sock user = mysql  port = 3306 server_id = 1 log-error = /data/3306/err.log  pid = /data/3306/mysql.pid  #binlog   log-bin  = /data/3306/mysql-bin  sync-binlog = 1  #InnoDB  innodb_flush_log_at_trx_commit innodb_support_xa =  1                        #other  character_set_server = utf8 default_storage_engine  =InnoDB ft_min_word_len = 1 open_files_limit = 65535  auto-increment-increment = 10 auto-increment-offset = 1  log_slave_updates= 1           allows the repository to record its replay events into its own binary log  read_only=0                  prevent any thread that does not have privileged permissions from modifying data  skip_slave_start [client]  socket = /data/3306/mysql.sock port = 3306

vim  /data/3307/my.cnf[mysqld]   #general  basedir = /usr/local/mysql  Datadir = /data/3307/data socket = /data/3307/mysql.sock user = mysql  port = 3307 server_id = 1 log-error = /data/3307/err.log  pid = /data/3307/mysql.pid  #binlog   log-bin  = /data/3307/mysql-bin  sync-binlog = 1  #InnoDB  innodb_flush_log_at_trx_commit innodb_support_xa =  1                        #other  character_set_server = utf8 default_storage_engine  =InnoDB ft_min_word_len = 1 open_files_limit = 65535  auto-increment-increment = 10 auto-increment-offset = 1  log_slave_updates= 1           allows the repository to record its replay events into its own binary log  read_only=0                  prevent any thread that does not have privileged permissions from modifying data  skip_slave_start [client]  socket = /data/3307/mysql.sock port = 3307

8. Create a multi-instance startup file.

Put to/data/3306 | /data/3307 under

script ellipsis (mysqld)

Startup of multi-instance startup files MySQL Service essence:

Mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &mysqld_safe--defaults-file=/data/3307 /MY.CNF 2>&1 >/dev/null &

Multi-instance startup file stop MySQL service in essence:

Mysqladmin-u root-p passwd-s/data/3306/mysql.sock shutdown mysqladmin-u root-p passwd-s/data/3306/mysql.sock Shutd Own smooth Stop

9, authorizing MySQL users, and groups to manage the entire multi-instance directory

Chown-r Mysql.mysql/data

10. Configure MySQL Global environment variables

echo "Export path= $PATH:/usr/local/mysql/bin/" >>/etc/profile source/etc/profile

11, Start initializing database

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

What initializes the database?

A, the primary purpose of initialization is to create a base database file, such as a library table that generates MySQL

b, after initialization, look at the directory, you can see some table files

12, Start | Stop MySQL Service

If there is a script, start with a script

/data/3306/mysql Start/data/3307/mysql Start

If there is no script, use the start

Mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &

Mysqld_safe--defaults-file=/data/3307/my.cnf 2>&1 >/dev/null & If there is no script, use start stop mysqladmin-u root-p Passwd-s/data/3306/mysql.sock shutdownmysqladmin-u root-p passwd-s/data/3306/mysql.sock shutdown Smooth Stop |

13, check

Landing:

Mysql-s/data/3306/mysql.sock

Management, the local use of-s/data/3306/mysql.sock, if the remote can be connected through different ports to sit management operations. Other and single-instance management is no different!

#######################################################################################

Then look at the second one through the official own mysqld_multi to achieve multi-instance combat:

MySQL installation here, as well as the initialization of the database and the previous steps, do not repeat it.

Configuration of the Mysqld_multi

vim /etc/my.cnf[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = / usr/local/mysql/bin/mysqladminuser = adminpassword = password[mysqld1]   #general  basedir = /usr/local/mysql/ datadir = /data/3306/data/ socket =  /data/3306/mysql.sock user = mysql port = 3306 server_id = 1  log-error = /data/3306/ pid = /data/3306/  #binlog   log-bin   = /data/3306/mysql-bin sync-binlog = 1  #InnoDB  innodb_flush_log_at_trx_ commit innodb_support_xa = 1                        #other  character_set_server =  utf8 default_storage_engine =innodb ft_min_word_len = 1 open_files_limit  = 65535 autO-increment-increment = 10 auto-increment-offset = 1  log_slave_updates=1            allows the repository to replay events into its own binary log as well  read_only=0                  prevent any thread that does not have privileged permissions from modifying data  skip_slave_start [client] socket = /data/3306/mysql.sock port = 3306[ mysqld2]  #general  basedir = /usr/local/mysql/ datadir = /data/3307/data/  socket = /data/3307/mysql.sock user = mysql port = 3307  server_id = 2 log-error = /data/3307/ pid = /data/3307/  #binlog   log-bin  = /data/3307/mysql-bin sync-binlog = 1  #InnoDB   innodb_flush_log_at_trx_commit innodb_support_xa = 1                        #other  character_set_server = utf8  default_storage_engine =innodb ft_min_word_len = 1 open_files_limit =  65535 auto-increment-increment = 10 auto-increment-offset = 1   log_slave_updates=1           allows the repository to record its replay events into its own binary log   read_only=0                 Prevent any thread that does not have privileged permissions from modifying the data  skip_slave_start [client] socket = /data/3307/mysql.sock port  = 3307[mysql]no-auto-rehashprompt=\\[email protected]\\d \\r:\\m>[mysqld_safe] open-files-limit = 8192

Mysqld_multi start

/usr/local/mysql/bin/mysqld_multi start 1/usr/local/mysql/bin/mysqld_multi start 2 stop MySQL instance mysqladmin-uroot-p-S/ Data/3306/mysql.sock shutdownmysqladmin-uroot-p-s/data/3307/mysql.sock shutdown change original password mysqladmin-uroot password ' 123456 '-s/data/3306/mysql.sockmysqladmin-uroot password ' 123456 '-s/data/3307/mysql.sock

Test Login

Mysql-uroot-p-s/data/3306/mysql.sock mysql-uroot-p-s/data/3307/mysql.sock

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1787516

MySQL multi-instance application configuration Deployment Guide