Linux operation and architecture-mysql multi-instance

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