多实例安装
1. 多实例介绍
- 一台服务器上安装多个MySQL数据库实例
- 可以充分利用服务器的硬件资源
- 通过mysqld_multi进行管理
2. 安装要求
-
MySQL实例1 -
mysql1
- port = 3306
- datadir = /data1
- socket = /tmp/mysql.sock1
-
MySQL实例2 -
mysql2
- port = 3307
- datadir = /data2
- socket = /tmp/mysql.sock2
-
MySQL实例3 -
mysql3
- port = 3308
- datadir = /data3
- socket = /tmp/mysql.sock3
-
MySQL实例4 -
mysql4
- port = 3309
- datadir = /data4
- socket = /tmp/mysql.sock4
该三个参数必须定制,且必须不同 (port / datadir / socket)
server-id
和多数据库实例没有关系,和数据库复制有关系。
3. 安装操作
#
# 多实例配置文件,可以mysqld_multi --example 查看例子
#
[root@MyServer /]> cat /etc/my.cnf
#[client] # 这个标签如果配置了用户和密码,
# 并且[mysqld_multi]下没有配置用户名密码,
# 则mysqld_multi stop时, 会使用这个密码
# 如果没有精确的匹配,则匹配[client]标签
#user = root
#password = 123
#-------------
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
pass = 123 # 官方文档中写的password,但是存在bug,需要改成pass(v5.7.9)
# 写成password,start时正常,stop时,报如下错误
# Access denied for user 'multi_admin'@'localhost' (using password: YES)
log = /var/log/mysqld_multi.log
[mysqld1] # mysqld后面的数字为GNR, 是该实例的标识
# mysqld_multi start 1, mysqld_multi start 2-4
server-id = 11
socket = /tmp/mysql.sock1
port = 3306
bind_address = 0.0.0.0
datadir = /data1
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data1/mysql.pid1
[mysqld2]
server-id = 12
socket = /tmp/mysql.sock2
port = 3307
bind_address = 0.0.0.0
datadir = /data2
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data2/mysql.pid2
[mysqld3]
server-id = 13
socket = /tmp/mysql.sock3
port = 3308
bind_address = 0.0.0.0
datadir = /data3
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data3/mysql.pid3
[mysqld4]
server-id = 14
socket = /tmp/mysql.sock4
port = 3309
bind_address = 0.0.0.0
datadir = /data4
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /data4/mysql.pid4
#
# 准备好数据目录,并初始化安装
#
[root@MyServer ~]> mkdir /data1
[root@MyServer ~]> mkdir /data2
[root@MyServer ~]> mkdir /data3
[root@MyServer ~]> mkdir /data4
[root@MyServer ~]> chown mysql.mysql /data{1..4}
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data1
#
# 一些日志输出,并提示临时密码,下同
#
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data2
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data3
[root@MyServer ~]> mysqld --initialize --user=mysql --datadir=/data4
# 安装后,需要检查error.log 确保没有错误出现
[root@MyServer ~]> cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multid
# 拷贝启动脚本,方便自启
[root@MyServer ~]> chkconfig mysqld_multid on
[root@MyServer ~]> mysqld_multi start
[root@MyServer ~]> 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 server from group: mysqld4 is running
[root@MyServer ~]> netstat -tunlp | grep mysql
[root@MyServer ~]> netstat -tunlp | grep mysql
tcp 0 0 :::3307 :::* LISTEN 6221/mysqld
tcp 0 0 :::3308 :::* LISTEN 6232/mysqld
tcp 0 0 :::3309 :::* LISTEN 6238/mysqld
tcp 0 0 :::3306 :::* LISTEN 6201/mysqld
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock1 -p -P3306
#
# 使用-S /tmp/mysql.sock1 进行登录,并输入临时密码后,修改密码,下同
#
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock2 -p -P3307
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock3 -p -P3308
[root@MyServer ~]> mysql -u root -S /tmp/mysql.sock4 -p -P3309
--
-- mysql1
--
mysql> show variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "socket";
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| socket | /tmp/mysql.sock1 |
+---------------+------------------+
1 row in set (0.01 sec)
mysql> show variables like "datadir";
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| datadir | /data1/ |
+---------------+---------+
1 row in set (0.00 sec)
--
-- 这样才能进行关闭数据库的操作
-- 和[mysqld_multi]中的user,pass(注意在5.7.9中不是password)对应起来 (类比[client]标签)
-- 一会测试federated链接,需要增加federated参数,并重启mysql2
--
mysql> create user 'multi_admin'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant shutdown on *.* to 'multi_admin'@'localhost';
--
-- mysql2, mysql3, mysql4 类似。可以看到与my.cnf中对应的port和socket
--