006：多实例安装

一. 多实例安装（可以同版本或异版本）

1. 多实例介绍

• 一台服务器上安装多个MySQL数据库实例
• 可以充分利用服务器的硬件资源（注意io资源）
• 通过mysqld_multi进行管理

2. 配置文件要点

• [mysqld_multi] 是否需要配置

  • my.cnf上直接配置[mysqld1]、[mysqld2]、[mysqld3] 实例标签，而不配置[mysqld_multi],使用mysqld_multi start 1也是可以启动数据库实例的，但是没有mysqld_safe的守护进程。所以该标签需要配置

  • 如果在[client]和[mysqld_multi]标签中同时存在user和password, 则在关闭数据库实例中会使用[mysqld_multi]中的user去关闭。

• (存在精确匹配的标签，则优先使用精确匹配标签下的配置项)

• multi_admin用户的作用
  通过官方文档中我们看到，'multi_admin'@'localhost'这个用户主要的作用是用来关闭数据库实例，因为文档中只授权了SHUTDOWN权限。所以在[mysqld_multi]标签下，我们需要配置user和password来进行关闭数据库实例。但是实际上有bug，还不如用（mysqld_multi --user=root --password=iforgot stop 1）授权有足够权限，这个权限还在摸索中，哪位有验证提供一下，谢谢！

3. 环境说明

• mysqld1 -- MySQL 5.7.18
• mysqld2 -- MySQL 5.7.18
• mysqld3 -- MySQL 5.6.36

4. 配置说明

• MySQL实例1

  • mysqld1 (MySQL 5.7.18)
  • port = 33016
  • datadir = /r2/mysqldata/mysql_33016
  • socket = /r2/mysqldata/mysql_33016/mysql.sock

• MySQL实例2

  • mysqld2 (MySQL 5.7.18)
  • port = 33026
  • datadir = /r2/mysqldata/mysql_33026
  • socket = /r2/mysqldata/mysql_33026/mysql.sock

• MySQL实例3

  • mysqld3 (MySQL 5.6.36)
  • port = 33036
  • datadir = /r2/mysqldata/mysql_33036
  • socket = /r2/mysqldata/mysql_33036/mysql.sock

这三个参数必须定制且必须不同 (port / datadir / socket)

• server-id和多数据库实例没有关系，和数据库复制有关系。

注意MySQL5.6.36的plugin_dir的路径

• 配置说明：

  • 1：配置的标签顺序没有关系，不会影响最终配置的有效性。
  • 2：同类型标签中的配置项会合并，形成一个大的配置项
  • 3：匹配度高的标签中的配置项的值，会覆盖掉匹配度低的标签中的配置项的值

[mysqldN]中的配置项会和[mysqld]中的配置项进行合并，并且[mysqldN]中已有的配置项的值，会覆盖掉[mysqld]中的配置项的值,如datadir, port等

• 配置文件

#==========================================================
# 多实例配置文件
#==========================================================

[client]            # 这个标签如果配置了用户和密码，
                    # 并且[mysqld_multi]下没有配置用户名密码，
                    # 则mysqld_multi stop时, 会使用这个密码
                    # 如果没有精确的匹配，则匹配[client]标签
user = root
password = iforgot

[mysqld_multi]
mysqld =/usr/local/mysql/bin/mysqld_safe
mysqladmin =/usr/local/mysql/bin/mysqladmin
log = /usr/local/mysql/mysqld_multi.log
user = multi_admin
pass = 123456  
            # 官方文档中写的password，但是存在bug，需要改成pass(v5.7.18)
            # 写成password，start时正常，stop时，报如下错误
            # Access denied for user 'multi_admin'@'localhost' (using password: YES)

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

[mysqld1]   # mysqld后面的数字为GNR, 是该实例的标识（mysqld_multi start 1）
server-id                       = 33016
mysqld                          = mysqld
mysqladmin                      = mysqladmin
group_concat_max_len            = 102400
user                            = mysql
port                            = 33016
socket                          = /r2/mysqldata/mysql_33016/mysql.sock
basedir                         = /usr/local/mysql
datadir                         = /r2/mysqldata/mysql_33016/data
pid_file                        = /r2/mysqldata/mysql_33016/data/mysql.pid
log_error                       = /r2/mysqldata/mysql_33016/logs/mysql_error.log
log_bin                         = /r2/mysqldata/mysql_33016/logs/binlog
slow_query_log_file             = /r2/mysqldata/mysql_33016/logs/slow.log
log_timestamps                  = system
################################################################
################################################################
[mysqld2]
server-id                       = 33026
mysqld                          = mysqld
mysqladmin                      = mysqladmin
user                            = mysql
port                            = 33026
socket                          = /r2/mysqldata/mysql_33026/mysql.sock
basedir                         = /usr/local/mysql
datadir                         = /r2/mysqldata/mysql_33026/data/
pid_file                        = /r2/mysqldata/mysql_33026/data/mysql.pid
log_error                       = /r2/mysqldata/mysql_33026/logs/mysql_error.log
log_bin                         = /r2/mysqldata/mysql_33026/logs/binlog
slow_query_log_file             = /r2/mysqldata/mysql_33026/logs/slow.log
log_timestamps                  = system

################################################################
################################################################
[mysqld3]
server-id                       = 33036
mysqld                          = /usr/local/mysql56/bin/mysqld
mysqladmin                      = /usr/local/mysql56/bin/mysqladmin
user                            = mysql
port                            = 33036
socket                          = /r2/mysqldata/mysql_33036/mysql.sock
basedir                         = /usr/local/mysql56
datadir                         = /r2/mysqldata/mysql_33036/data
pid_file                        = /r2/mysqldata/mysql_33036/data/mysql.pid
log_error                       = /r2/mysqldata/mysql_33036/logs/mysql_error.log
log_bin                         = /r2/mysqldata/mysql_33036/logs/binlog
slow_query_log_file             = /r2/mysqldata/mysql_33036/logs/slowlogs/slow.log
plugin_dir                      = /usr/local/mysql56/lib/plugin
################################################################
################################################################
[mysqld]

wait_timeout                    = 31536000
#interactive_timeout            = 600
sql_mode                        = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
skip_name_resolve               = 1
lower_case_table_names          = 0
character-set-server            = utf8
#auto_increment_increment       = 1
#auto_increment_offset          = 1
init_connect                    = 'set name utf8;'
#log_timestamps                  = system
tmpdir                          = /r2/mysqldata
#plugin_dir                      = /usr/local/mysql/lib/plugin
#==========================================================
#bin setting
#==========================================================
binlog_format                   = MIXED
log_bin_trust_function_creators = 1
binlog_cache_size               = 64M
max_binlog_cache_size           = 1G
max_binlog_size                 = 512M
expire_logs_days                = 7

#==========================================================
# replication relay-log
#==========================================================
log-slave-updates               = 1
slave-net-timeout               = 60
sync-master-info                = 1
sync-relay-log                  = 1
sync-relay-log-info             = 1
#==========================================================
#no need to sync database
#==========================================================
binlog-ignore-db                = test
binlog-ignore-db                = information_schema
binlog-ignore-db                = mysql
#==========================================================
#performance setting
#==========================================================
open_files_limit                = 1024000
max_connections                 = 1000
max_user_connections            = 9990
max_connect_errors              = 100000
table_open_cache                = 1024
max_allowed_packet              = 32M
thread_cache_size               = 64
max_heap_table_size             = 16M
query_cache_type                = 0
###global cache ###
key_buffer_size                 = 16M
query_cache_size                = 0
###session cache ###
sort_buffer_size                = 8M       #排序缓冲
join_buffer_size                = 4M       #表连接缓冲
read_buffer_size                = 8M       #顺序读缓冲
read_rnd_buffer_size            = 8M       #随机读缓冲
tmp_table_size                  = 32M      #内存临时表
binlog_cache_size               = 4M       #二进制日志缓冲
thread_stack                    = 256KB    #线程的堆栈的大小
#==========================================================
#innodb setting
#==========================================================
default-storage-engine          = InnoDB
innodb_buffer_pool_size         = 512M
innodb_open_files               = 1000
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = 1
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 128M
innodb_log_files_in_group       = 2
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_buffer_pool_instances    = 8
innodb_lock_wait_timeout        = 5
innodb_io_capacity              = 1000
innodb_io_capacity_max          = 20000
innodb_large_prefix             = 0
innodb_thread_concurrency       = 64
innodb_strict_mode              = OFF
innodb_sort_buffer_size         = 4194304
innodb_file_format              = Barracuda
innodb_file_format_max          = Barracuda
#==========================================================
#slow setting
#==========================================================
slow-query-log = on
long_query_time = 1

[mysqld-5.7]
innodb_buffer_pool_dump_pct     = 40
innodb_page_cleaners            = 4
innodb_undo_log_truncate        = 1
innodb_max_undo_log_size        = 1G   # 该参数减小到1G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery     = 1
log_timestamps                  = system
transaction_write_set_extraction= MURMUR32
show_compatibility_56           = on

5. 安装多实例

yum remove MariaDB-common MariaDB-compat MariaDB-server                  --rhel7删除mariadb
yum install gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel -y     --安装mysql环境包
tar zxvf  mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz -C /usr/local/       --解压mysql到/usr/local目录
tar zxvf  mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/       --同上
cd /usr/local/
ln -s mysql-5.7.18-linux-glibc2.5-x86_64/ mysql                          --创建软连接
ln -s mysql-5.6.36-linux-glibc2.5-x86_64/ mysql56                        --同上
groupadd mysql && useradd -r -g mysql -s /bin/false mysql                --添加用户
mkdir -p  /r2/mysqldata/                                                 --创建主目录
chown -R mysql:mysql /usr/local/mysql                                    --授权访问mysql-5.7.18-linux-glibc2.5-x86_64
chown -R mysql:mysql  /r2/mysqldata/                                     --同上
chmod 750 /r2/mysqldata/                                                 --添加权限
chmod 750 /usr/local/mysql                                               --同上
mv /tmp/multi_my.cnf  /etc/my.cnf                                        --multi_my.cnf配置文件上面已附上

--下面添加到环境变量
cat <<EOF >>/etc/profile
export PATH=$PATH:/usr/local/mysql/bin/    
EOF
source /etc/profile
echo $PATH

--开始创建并初始化mysqld1 （mysql5.7临时密码在日志输出上面有提示，下同）
mkdir -p /r2/mysqldata/mysql_33016/{logs,data}
chown -R mysql.mysql  /r2/mysqldata/mysql_33016
chmod 755 /r2/mysqldata/mysql_33016/
cd /usr/local/mysql/bin/
./mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/r2/mysqldata/mysql_33016/data/  


--开始创建并初始化mysqld2
mkdir -p /r2/mysqldata/mysql_33026/{logs,data}
chown -R mysql.mysql  /r2/mysqldata/mysql_33026
cd /usr/local/mysql/bin/
./mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/r2/mysqldata/mysql_33026/data/  


--开始创建并初始化mysqld3 （mysql5.6初始化密码是空）
mkdir -p /r2/mysqldata/mysql_33036/{logs,data}
chown  -R mysql.mysql  /r2/mysqldata/mysql_33036
cd /usr/local/mysql56/
./scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql56 --datadir=/r2/mysqldata/mysql_33036/data/ 

-- 安装后，需要检查mysql_error.log 确保没有错误出现

• 启动数据库，并修改root账号，授权其他账号

--使用mysqld_multi启动，mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]

[root@multi mysql56]# mysqld_multi start 1
[root@multi mysql56]# mysqld_multi start 2
[root@multi mysql56]# mysqld_multi start 3
[root@multi mysql56]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
--查看三个mysql端口
[root@multi mysql56]# netstat -tunlp | grep mysql
tcp6       0      0 :::33036                :::*                    LISTEN      35400/mysqld
tcp6       0      0 :::33016                :::*                    LISTEN      37735/mysqld
tcp6       0      0 :::33026                :::*                    LISTEN      37738/mysqld

-- 使用sock进行登录，并输入临时密码后，修改密码，如下：
root@multi mysqldata]# mysql -uroot -p  -S /r2/mysqldata/mysql_33016/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 20
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>alter user 'root'@'localhost' identified by 'iforgot' ; 
mysql>create user 'multi_admin'@'localhost' identified by '123456';
mysql>grant shutdown on *.* to 'multi_admin'@'localhost';   
mysql>flush privileges;
mysql>exit;

--mysqld2 同上；mysqld3 由于是mysql5.6.36版本直接使用mysql -S /r2/mysqldata/mysql_33036/mysql.sock登陆

[root@multi mysqldata]# mysql -S /r2/mysqldata/mysql_33036/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 147
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>set password=password('iforgot');
mysql>create user 'multi_admin'@'localhost' identified by '123456';
mysql>grant shutdown on *.* to 'multi_admin'@'localhost';   
mysql>flush privileges;
mysql>exit;

• 检测port和socket

[root@multi mysql56]#  ps -ef | grep mysqld
mysql     35400      1  0 14:56 pts/1    00:00:10 /usr/local/mysql56/bin/mysqld --server-id=33036 --user=mysql --port=33036 --socket=/r2/mysqldata/mysql_33036/mysql.sock --basedir=/usr/local/mysql56 --datadir=/r2/mysqldata/mysql_33036/data --pid_file=/r2/mysqldata/mysql_33036/data/mysql.pid --log_error=/r2/mysqldata/mysql_33036/logs/mysql_error.log --log_bin=/r2/mysqldata/mysql_33036/logs/binlog --slow_query_log_file=/r2/mysqldata/mysql_33036/logs/slowlogs/slow.log --plugin_dir=/usr/local/mysql56/lib/plugin
mysql     37735      1  0 16:33 pts/1    00:00:05 mysqld --server-id=33016 --group_concat_max_len=102400 --user=mysql --port=33016 --socket=/r2/mysqldata/mysql_33016/mysql.sock --basedir=/usr/local/mysql --datadir=/r2/mysqldata/mysql_33016/data --pid_file=/r2/mysqldata/mysql_33016/data/mysql.pid --log_error=/r2/mysqldata/mysql_33016/logs/mysql_error.log --log_bin=/r2/mysqldata/mysql_33016/logs/binlog --slow_query_log_file=/r2/mysqldata/mysql_33016/logs/slow.log --log_timestamps=system
mysql     37738      1  0 16:33 pts/1    00:00:04 mysqld --server-id=33026 --user=mysql --port=33026 --socket=/r2/mysqldata/mysql_33026/mysql.sock --basedir=/usr/local/mysql --datadir=/r2/mysqldata/mysql_33026/data/ --pid_file=/r2/mysqldata/mysql_33026/data/mysql.pid --log_error=/r2/mysqldata/mysql_33026/logs/mysql_error.log --log_bin=/r2/mysqldata/mysql_33026/logs/binlog --slow_query_log_file=/r2/mysqldata/mysql_33026/logs/slow.log --log_timestamps=system

-- 上面是mysqld_safe的进程

• 设置login-path

设置login-path主要为了能够简化登录，同时还可以让每个数据库的密码都不同，避免使用[client]下的统一用户名密码

[root@multi mysqldata]# mysql_config_editor  set -G mysqld1 -u root -p   -S /r2/mysqldata/mysql_33016/mysql.sock
Enter password:

[root@multi mysqldata]# mysql_config_editor  set -G mysqld2 -u root -p   -S /r2/mysqldata/mysql_33026/mysql.sock
Enter password:

[root@multi mysqldata]# mysql_config_editor  set -G mysqld3 -u root -p   -S /r2/mysqldata/mysql_33036/mysql.sock
Enter password:
[root@multi mysqldata]#

-- 然后可以使用mysql --login-path=mysql1 这种方式登录