MySQL多实例

mac2022-06-30 89

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_multimysqld_safemysqld配置文件位置/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 #已经能成功关闭数据库

转载于:https://www.cnblogs.com/wanbin/p/9514663.html