MySQL多实例

MySQL多实例

  • 为什么要使用多实例

  • MySQL启动方式之间的关系

  • MySQL多实例启动及关闭的方式

1. 为什么要使用多实例

  • 物理机性能强大，单个项目无法把资源使用完

  • 资源隔离，减少相互影响

  • 分担连接数
    MySQL随着连接数上升，性能会出现下降

  • 更充分的利用资源
    不同的业务错高峰混跑

2. MySQL启动方式之间的关系

2.1 MySQL读取配置文件的顺序

 # 从左往右读，后面的配置文件会把前面的进行覆盖

 /etc/my.cnf 》/etc/mysql/my.cnf 》/usr/local/mysql/etc/my.cnf 》~/.my.cnf 

 # mysqld --verbose --help|grep my.cnf
 /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

【提示】：启动mysql时，加上--defaults-file参数，可以指定只加载指定位置的配置文件

  • 通过--print-defaults输出mysqld加载的参数
# mysqld --print-defaults
mysqld would have been started with the following arguments:
--user=mysql --port=3306 --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --socket=/tmp/mysql3306.sock --pid-file=mysqldb1.pid ...

2.2 MySQL启动方式

标准方式

 service mysql start
 # or
 /etc/init.d/mysql start

以下三种方式，可用于启动多实例

mysqld_safe --defaults-file=/etc/my.cnf &
mysqld --defaults-file=/etc/my.cnf &
mysqld_multi start 3306

2.3 MySQL启动方式之间的关系

service mysqld start -> 实际上调用的是mysqld_safe

mysqld_safe -> 调用的是mysqld

service mysqld：主要是为了能够让mysql能够自启动，但实际环境中很少有环境使用自动启动的
mysqld_safe：实际上在mysqld上基础上多起了一个守护进程，如果mysqld异常挂掉了，他会自动再起一个mysqld

示例1：

 以mysqld_safe启动mysql，然后手动kill mysql进程，观察进程是否自动启动

 # mysqld_safe --defaults-file=/etc/my.cnf &

 [root@mysqldb1 15:20:51 /root]
 # ps -ef|grep mysql
 root      7992  7905  0 14:09 pts/2    00:00:00 mysql
 root     17206     1  1 15:20 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
 mysql    18722 17206 13 15:20 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql3306/error.log --open-files-limit=65535 --pid-file=/datamysql/mysql3306/data/mysqldb1.pid --socket=/tmp/mysql3306.sock --port=3306
 root     18772  7857  0 15:20 pts/0    00:00:00 grep --color=auto mysql

 [root@mysqldb1 15:20:56 /root]
 # kill -9 18722 

 [root@mysqldb1 15:21:04 /root]
 # ps -ef|grep mysql
 root      7992  7905  0 14:09 pts/2    00:00:00 mysql
 root     17206     1  0 15:20 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
 mysql    18798 17206  3 15:21 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql3306/error.log --open-files-limit=65535 --pid-file=/datamysql/mysql3306/data/mysqldb1.pid --socket=/tmp/mysql3306.sock --port=3306
 root     18852  7857  0 15:21 pts/0    00:00:00 grep --color=auto mysql

 【注意】：要实现守护进程，需先往系统中注册服务，chkconfig mysql off

 # chkconfig

 Note: This output shows SysV services only and does not include native
       systemd services. SysV configuration data might be overridden by native
       systemd configuration.

       If you want to list systemd services use 'systemctl list-unit-files'.
       To see services enabled on particular target use
       'systemctl list-dependencies [target]'.

 mysql              0:off   1:off   2:off   3:off   4:off   5:off   6:off
 netconsole         0:off   1:off   2:off   3:off   4:off   5:off   6:off
 network            0:off   1:off   2:on    3:on    4:on    5:on    6:off

2.4 mysqld_multi和其他启动方式关系

mysqld_multi：实际上调用的是mysqld_safe或者mysqld

3. MySQL多实例启动及关闭的方式

3.1 安装多实例注意事项

应当注意的参数：

port = 3306
socket = /tmp/mysql3306.sock
server-id = 1003306
datadir = /data/mysql/mysql3306/data
tmpdir = /data/mysql/mysql3306/tmp
log-bin = /data/mysql/mysql3306/logs/my3306_binlog

3.2 多实例启动方式

有三种方式：

mysqld_safe --defaults-file=/etc/my.cnf &

mysqld --defaults-file=/etc/my.cnf &

mysqld_multi

下面重点介绍mysqld_multi

  • mysqld_multi 是perl实现的通过mysqld_safe或mysqld来启动实例；通过调用mysqladmin实现多实例启动关闭的一组脚本程序

  • 需要在配置文件中声明：[mysqld_multi] ,[mysqldN],其中[mysqldN]这部分定义会覆盖[mysqld]

  • mysqld_multi启动的实例N的参数是[mysqld] + [mysqldN]两部分的组合

3.3 mysqld_multi配置文件和其他方式的区别


 mysqld_multi mysqld_safe
mysqld
配置文件位置 /etc/my.cnf
(可以指定)		 /path/filename.cnf
（可以指定）
配置文件结构区别 [client]
[mysql]
[mysqld_multi]
[mysqld]
[mysqldN1]
[mysqldN2]
[mysqldN..]
[mysqld_safe]		 [client]
[mysql]
[mysqld]
[mysqld_safe]

3.4 mysqld_multi需要的参数

参数

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root
password=mysql
log=/tmp/mysqld_multi.log

[mysqld3306]
port=3306
datadir=/data/mysql/mysql3306/data
tmpdir  = /data/mysql/mysql3306/tmp
socket=/tmp/mysql3306.sock
server-id=1003306
log-bin=/data/mysql/mysql3306/logs/my3306_binlog

调用方式

mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR...]

3.5 多实例启动与关闭示例

3.5.1 利用mysqld ， mysqld_safe分别启动与关闭实例3306，3307

#1. 初始化3307实例

# mkdir /data/mysql/mysql3307/{data,logs,tmp} -p
# chown -R mysql:mysql /data/mysql/mysql3307
# cp my3306.cnf my3307.cnf
# sed -i 's/3306/3307/g' /etc/my3307.cnf
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --initialize
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf &

#查看errorr日志，获取临时密码，并输入
# /usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -p

root [(none)] 12:41:41>alter user user() identified by 'mysql';
# mysqladmin -S /tmp/mysql3307.sock shutdown
# mysqladmin -S /tmp/mysql3306.sock shutdown


#2. 启动与关闭3306，3307

#启动
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf &

# ps -ef|grep mysql
avahi      667     1  0 09:19 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
mysql     7737  3021 14 12:45 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf
mysql     7781  3021 16 12:45 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf
root      7826  3021  0 12:45 pts/0    00:00:00 grep --color=auto mysql

#登陆测试
# mysql -S /tmp/mysql3306.sock 

root@localhost [(none)] 12:46:35>\s
--------------
mysql  Ver 14.14 Distrib 5.7.23, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:      2
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.23-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql3306.sock
Uptime:         1 min 18 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 16  Queries per second avg: 0.076



# mysql -S /tmp/mysql3307.sock 

root@localhost [(none)] 12:47:14>\s
--------------
mysql  Ver 14.14 Distrib 5.7.23, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:      2
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.23-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql3307.sock
Uptime:         1 min 51 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 16  Queries per second avg: 0.054
--------------

#关闭
# mysqladmin -S /tmp/mysql3307.sock shutdown
# mysqladmin -S /tmp/mysql3306.sock shutdown

# ps -ef|grep mysql
avahi      667     1  0 09:19 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
root      7934  3021  0 12:50 pts/0    00:00:00 grep --color=auto mysql

3.5.2 利用mysqld_multi启动与关闭实例3306，3307

#1. 修改/etc/my.cnf，添加以下内容

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user=root
password=mysql
log=/tmp/mysqld_multi.log

[mysqld3306]
port=3306
datadir=/data/mysql/mysql3306/data
socket=/tmp/mysql3306.sock
server-id=1003306
log-bin=/data/mysql/mysql3306/logs/my3306_binlog
slow_query_log_file = /data/mysql/mysql3306/slow.log
log-error = /data/mysql/mysql3306/error.log


[mysqld3307]
port=3307
datadir=/data/mysql/mysql3307/data
socket=/tmp/mysql3307.sock
server-id=1003307
log-bin=/data/mysql/mysql3307/logs/my3307_binlog
slow_query_log_file = /data/mysql/mysql3307/slow.log
log-error = /data/mysql/mysql3307/error.log


#2. 启动3306，3307

# mysqld_multi start 3306,3307

# ps -ef|grep mysql
avahi      667     1  0 09:19 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
root      8067     1  1 12:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3306 --datadir=/data/mysql/mysql3306/data --socket=/tmp/mysql3306.sock --server-id=1003306 --log-bin=/data/mysql/mysql3306/logs/my3306_binlog --slow_query_log_file=/data/mysql/mysql3306/slow.log --log-error=/data/mysql/mysql3306/error.log
root      8073     1  2 12:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3307 --datadir=/data/mysql/mysql3307/data --socket=/tmp/mysql3307.sock --server-id=1003307 --log-bin=/data/mysql/mysql3306/logs/my3307_binlog --slow_query_log_file=/data/mysql/mysql3307/slow.log --log-error=/data/mysql/mysql3307/error.log
mysql    11233  8067 24 12:59 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1003306 --log-bin=/data/mysql/mysql3306/logs/my3306_binlog --slow-query-log-file=/data/mysql/mysql3306/slow.log --log-error=/data/mysql/mysql3306/error.log --open-files-limit=65535 --pid-file=mysqldb1.pid --socket=/tmp/mysql3306.sock --port=3306
mysql    11237  8073 28 12:59 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1003307 --log-bin=/data/mysql/mysql3306/logs/my3307_binlog --slow-query-log-file=/data/mysql/mysql3307/slow.log --log-error=/data/mysql/mysql3307/error.log --open-files-limit=65535 --pid-file=mysqldb1.pid --socket=/tmp/mysql3307.sock --port=3307
root     11315  3021  0 13:00 pts/0    00:00:00 grep --color=auto mysql

#3. 用ss查看监听端口是否启用
# ss -nlp|grep mysqld
u_str  LISTEN     0      128    /tmp/mysql3306.sock 50212                 * 0                   users:(("mysqld",pid=11233,fd=122))
u_str  LISTEN     0      128    /tmp/mysql3307.sock 50215                 * 0                   users:(("mysqld",pid=11237,fd=119))
tcp    LISTEN     0      128      :::3306                 :::*                   users:(("mysqld",pid=11233,fd=121))
tcp    LISTEN     0      128      :::3307                 :::*                   users:(("mysqld",pid=11237,fd=118))


#4. 登陆
# mysql -S /tmp/mysql3307.sock
# mysql -S /tmp/mysql3306.sock


#5. 关闭mysql

# mysqld_multi stop 3306,3307

# ps -ef|grep mysql
avahi      667     1  0 09:19 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
root      8067     1  0 12:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3306 --datadir=/data/mysql/mysql3306/data --socket=/tmp/mysql3306.sock --server-id=1003306 --log-bin=/data/mysql/mysql3306/logs/my3306_binlog --slow_query_log_file=/data/mysql/mysql3306/slow.log --log-error=/data/mysql/mysql3306/error.log
root      8073     1  0 12:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3307 --datadir=/data/mysql/mysql3307/data --socket=/tmp/mysql3307.sock --server-id=1003307 --log-bin=/data/mysql/mysql3306/logs/my3307_binlog --slow_query_log_file=/data/mysql/mysql3307/slow.log --log-error=/data/mysql/mysql3307/error.log
mysql    11233  8067  0 12:59 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1003306 --log-bin=/data/mysql/mysql3306/logs/my3306_binlog --slow-query-log-file=/data/mysql/mysql3306/slow.log --log-error=/data/mysql/mysql3306/error.log --open-files-limit=65535 --pid-file=mysqldb1.pid --socket=/tmp/mysql3306.sock --port=3306
mysql    11237  8073  0 12:59 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --server-id=1003307 --log-bin=/data/mysql/mysql3306/logs/my3307_binlog --slow-query-log-file=/data/mysql/mysql3307/slow.log --log-error=/data/mysql/mysql3307/error.log --open-files-limit=65535 --pid-file=mysqldb1.pid --socket=/tmp/mysql3307.sock --port=3307
root     11581  3021  0 13:05 pts/0    00:00:00 grep --color=auto mysql

#观察发现进程还在，查看mysqld_multi.log

# tail -100f /tmp/mysqld_multi.log 

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

#提示密码错误，但是密码确实是对的，怎么会错呢？

#查看mysqld_multi.log源码

# which mysqld_multi 
/usr/local/mysql/bin/mysqld_multi

# vim /usr/local/mysql/bin/mysqld_multi
#搜索stop


#搜索get_mysqladmin_options


搜索defaults_for_group

发现最终调用my_print_defaults程序，查看程序帮助

# my_print_defaults --help
使用默认文件Usage：my_print_defaults [OPTIONS] groups ，输出某些程序提供的所有参数

Example usage:
my_print_defaults --defaults-file=example.cnf client mysql

# my_print_defaults --defaults-file=/etc/my.cnf client mysql mysqld_multi
--port=3306
--prompt=\U [\d] \R:\m:\s>
--no-auto-rehash
--mysqld=/usr/local/mysql/bin/mysqld_safe
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--user=root
--password=*****
--log=/tmp/mysqld_multi.log

#发现密码不是明文显示

#加参数-s，再次查询
# my_print_defaults -s --defaults-file=/etc/my.cnf client mysql mysqld_multi
--port=3306
--prompt=\U [\d] \R:\m:\s>
--no-auto-rehash
--mysqld=/usr/local/mysql/bin/mysqld_safe
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--user=root
--password=mysql
--log=/tmp/mysqld_multi.log

#修改mysqld_multi文件，在216行添加 -s                    

#vim里面输入set nu 显示行数


#再次尝试关闭数据库

# mysqld_multi stop 3306,3307

# ps -ef|grep mysql
avahi      667     1  0 09:19 ?        00:00:00 avahi-daemon: running [mysqldb1.local]
root     12152  4707  0 13:35 pts/2    00:00:00 tail -100f /tmp/mysqld_multi.log
root     17252  3021  0 13:41 pts/0    00:00:00 grep --color=auto mysql

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running

#已经能成功关闭数据库
posted @ 2018-08-02 14:09  DB-Engineer  阅读(289)  评论(0)  编辑  收藏  举报