Linux operation and architecture-mysql multi-instance

Source: Internet
Author: User

First, MySQL multi-instance Introduction

Open several different service ports (3306,33307,3308) on one server, run multiple MySQL service processes, share a set of MySQL installers, multi-instance MySQL is logically independent

1, multi-instance master-slave replication schematic diagram

2. Advantages

① Efficient use of server resources
② Conserve server resources
③ resource preemption problem, one instance is high or slow query, other instances are affected

3. Application Scenario

① Company funds shortage
② concurrent access is not a particularly large business
The ③ portal applies MySQL multi-instance scenarios, typically from libraries

4, MySQL multi-instance common configuration scheme

① Multi-configuration file, multi-boot program (common)
② single configuration file, single launcher multi-instance scenario (one profile, bad management, high coupling)

Second, MySQL multi-instance deployment

1. Environment

[Email protected] ~]# cat/etc/redhat-6.9~]# uname-R2.6. -696~]# hostname-I172.19. 5.54 172.16. 1.54

2. Install MySQL

Useradd-s/sbin/nologin-/server/toolstar XF mysql-5.6. -LINUX-GLIBC2. 5-x86_64.tar.gzmv mysql-5.6. -LINUX-GLIBC2. 5-x86_64/application/mysql-5.6.  $  -s/application/mysql-5.6. //application/-R mysql.mysql/application/mysql

3. Configure multi-instance startup command and configuration file directory

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

4, Unzip data.zip in/under direct decompression overwrite/data directory (note: Data.zip is packaged multi-instance configuration file, data, start command)

[Email protected] data]# tree ├──3306│    ├──data         #3306实例数据文件目录 │    ├──my.cnf       #3306实例配置文件 │    └── MySQL        #3306实例启动命令 └──3307    ├──data          #3307实例数据文件目录    ├──my.cnf        #3307实例配置文件    └──mysql         #3307实例启动命令

5, authorized MySQL user management/data directory

Chown-r Mysql.mysql/data

6. Authorize MySQL command to execute permissions

" MySQL "|xargs chmod +x

7. Configure MySQL command to start environment variables

Method One:
Ln-s/application/mysql/bin /* Method Two:echo ' export path=/application/mysql/bin: $PATH ' >>/ Etc/profilesource/etc/profile

8. Initialize multi-instance database

cd/application/mysql/scripts/. /mysql_install_db--basedir=/application/mysql/--datadir=/data/3306/data/--user=MySQL # Initializes the database 3306. /mysql_install_db--basedir=/application/mysql/--datadir=/data/3307/data/--user=mysql #初始化数据库3307

9, MySQL multi-instance service start

    #启动服务   /data/< Span style= "COLOR: #800080" >3306 /mysql start /data/3307 /mysql start[[email protected] data]# netstat -lntup| grep 330  tcp  0  0 ::: 3306 :::* LISTEN 3848 /mysqld tcp  0  0 ::: 3307 :::* LISTEN 4885 /mysqld 

10. mysql Multi-instance setup password and login

#设置密码: 123456 -s/data/3306/123456 -s/data/3307/mysql.sock#登录数据库, Designation sock-uroot-p123456-s/data/3306/-uroot-p123456-s/data/3307/ Mysql.sock

Iii. MySQL Multi-instance configuration file

/data/3306/my.cnf

[Client]

Port = 3306

Socket =/data/3306/mysql.sock

[Mysqld]

Port = 3306

Socket =/data/3306/mysql.sock

DataDir =/data/3306/data

Open_files_limit = 1024

Back_log = 600

Max_connections = 800

Max_connect_errors = 3000

Table_open_cache = 512

external-locking = FALSE

Max_allowed_packet =8m

Sort_buffer_size = 1M

Join_buffer_size = 1M

thread_cache_size = 100

Thread_concurrency = 2

Query_cache_size = 2M

Query_cache_limit = 1M

Query_cache_min_res_unit = 2k

Thread_stack = 192K

Tmp_table_size = 2M

Max_heap_table_size = 2M

Long_query_time = 1

Pid-file =/data/3306/mysql.pid

Log-bin =/data/3306/mysql-bin

Relay-log =/data/3306/relay-bin

Relay-log-info-file =/data/3306/relay-log.info

Binlog_cache_size = 1M

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

Expire_logs_days = 7

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_rnd_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors = 1032,1062

Replicate-ignore-db=mysql

Server-id = 4

/data/3307/my.cnf

[Client]

Port = 3307

Socket =/data/3307/mysql.sock

[Mysqld]

Port = 3307

Socket =/data/3307/mysql.sock

DataDir =/data/3307/data

Open_files_limit = 1024

Back_log = 600

Max_connections = 800

Max_connect_errors = 3000

Table_open_cache = 512

external-locking = FALSE

Max_allowed_packet =8m

Sort_buffer_size = 1M

Join_buffer_size = 1M

thread_cache_size = 100

Thread_concurrency = 2

Query_cache_size = 2M

Query_cache_limit = 1M

Query_cache_min_res_unit = 2k

Thread_stack = 192K

Tmp_table_size = 2M

Max_heap_table_size = 2M

Long_query_time = 1

Pid-file =/data/3307/mysql.pid

Relay-log =/data/3307/relay-bin

Relay-log-info-file =/data/3307/relay-log.info

Binlog_cache_size = 1M

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

Expire_logs_days = 7

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_rnd_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors = 1032,1062

Replicate-ignore-db=mysql

Server-id = 5

Note: After comparing two configuration files, it can be found that in addition to the port, Server-id is not the same, 3306 turned on the Log-bin function, so as to do the MySQL master copy

Iv. MySQL Multi-instance startup command script

#!/bin/Sh#initport=3306Mysql_user="Root"mysql_pwd="123456"Cmdpath="/application/mysql/bin"Mysql_sock="/data/${port}/mysql.sock"#startup Functionfunction_start_mysql () {if[!-E"$mysql _sock"];then printf"starting mysql...\n"/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/NULL&Elseprintf"MySQL is running...\n"exit fi} #stop Functionfunction_stop_mysql () {if[!-E"$mysql _sock"];then printf"MySQL is stopped...\n"ExitElseprintf"stoping mysql...\n"${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown fi} #restart Functionfunction_restart_mysql () {printf"restarting mysql...\n"Function_stop_mysql Sleep2Function_start_mysql} Case$1 inchstart) function_start_mysql;; stop) Function_stop_mysql;; restart) function_restart_mysql;;*) printf"Usage:/data/${port}/mysql {start|stop|restart}\n"Esac

V. Errors encountered

1. [ERROR] Plugin ' InnoDB ' init function returned error

Remove the IB_LOGFILE0 and ib_logfile1 two files from the MySQL directory to solve the problem

2.5.5.32-->5.6.36 configuration parameter does not cause multiple instances to start

Table_cache-->table_open_cache = 512 (5.6 renamed)

Six, production environment multi-instance configuration environment

1, generally in 1-4 instances between the majority, 1-2 most, large business occupy more machines, machine R510 majority, CPU is e5210,48g memory, hard disk 12*300g SAS,RAID10

2, Memory 32G, dual cpu8 core, hard disk 6x600g, run 2-3 instances

Linux operation and architecture-mysql multi-instance

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.