MySQL 5.6 Multi-instance deployment

1. Install MySQL

# useradd-s/sbin/nologin-m MySQL

# TAR-XVF Mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz-c/usr/local

# mv Mysql-5.6.41-linux-glibc2.12-x86_64 MySQL

# Chown-r Mysql:mysql/usr/local/mysql

2. Configure the multi-instance profile directory

# Mkdir/data/{3306,3307}/data

# Chown-r Mysql:mysql/data

3.MySQL Multi-Instance 3306 configuration file

# VIM/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 = 3306

4.MySQL Multi-Instance 3307 configuration file

# VIM/DATA/3306/MY.CNF

[Client]
Port = 3307
Socket =/data/3307/mysql.sock

[Mysqld]
Port = 3306
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
Log-bin =/data/3307/mysql-bin
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 = 3307

5. initializing a multi-instance database

#/usr/local/mysql/scripts/mysql_install_db--basedir=/application/mysql/--datadir=/data/3306/data/--user=mysql

#/usr/local/mysql/scripts/mysql_install_db--basedir=/application/mysql/--datadir=/data/3307/data/--user=mysql

6.MySQL Multi-instance boot

#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &

#/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

7.MySQL Multi-instance setup password

#/usr/local/mysql/bin/mysqladmin-uroot Password 123456-s/data/3306/mysql.sock

#/usr/local/mysql/bin/mysqladmin-uroot Password 123456-s/data/3307/mysql.sock

8. test MySQL Multi-instance setup password

# mysql-uroot-p123456-s/data/3306/mysql.sock
# mysql-uroot-p123456-s/data/3307/mysql.sock

9. View MySQL Multi-instance listening port

# Netstat-lntup|grep 330
TCP 0 0::: 3306:::* LISTEN 3848/mysqld
TCP 0 0::: 3307:::* LISTEN 4885/mysqld

MySQL Multi-instance start command script

# Vim/etc/init.d/mysqld_multi

#!/bin/sh

Port=$3
Mysql_user= "Root"
Mysql_pwd= "123456"
Cmdpath= "/usr/local/mysql/bin"
mysql_sock= "/data/${port}/mysql.sock"
#startup function
Function_start_mysql ()
{
if [!-e "$mysql _sock"];then
printf "Starting mysql...\n"
/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
Else
printf "MySQL is running...\n"
Exit
Fi
}

#stop function
Function_stop_mysql ()
{
if [!-e "$mysql _sock"];then
printf "MySQL is stopped...\n"
Exit
Else
printf "stoping mysql...\n"
${cmdpath}/mysqladmin-u ${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown
Fi
}

#restart function
Function_restart_mysql ()
{
printf "Restarting mysql...\n"
Function_stop_mysql
Sleep 2
Function_start_mysql
}

Case $ in
Start
Function_start_mysql
;;
Stop
Function_stop_mysql
;;
Restart
Function_restart_mysql
;;
*)
printf "Usage:/etc/init.d/mysql_multi {start|stop|restart} {3306|3307}\n"
Esac

MySQL 5.6 Multi-instance deployment