mysql怎么部署多实例_MySQL多实例部署

MySQL多实例搭建

toc

一、什么是MySQL多实例

MySQL的多实例就是在一台机器上开启多个不同的服务端口，运行多个MySQL服务进程，使用不同的socket来监听这多个不同的端口以此提供服务，这一点和Oracle的多实例类似。这些MySQL的实例共用相同的MySQL但是使用的参数文件是不一样的，相应的数据文件也不同。提供服务的时候从逻辑上看各自独立，各自获取的硬件资源可以灵活设定。

二、MySQL多实例优劣势

有效的利用服务器资源。当单个服务器的资源有剩余的时候可以将多余的资源有效的利用起来，而且还实现了资源的逻辑隔离。

节约经济消耗。例如需要多个数据库来搭建主从，但是又只有一台服务器。

当单个数据库并发很高或计算资源需求很高时。整个实例会消耗大量系统的CPU，IO等资源。这样其他实例的可利用资源就会变少产生问题。无法实现实例资源的绝对隔离。

三、如何部署MySQL多实例

部署的方式有两种：1. 使用mysqld_multi工具，用单独的配置文件实现多实例配置复杂但是管理方便。

​ 2.设置多个配置文件启动，这样启动不同进程实现多实例。原理简单，但是不易管理。

1. 使用mysqld_multi搭建

1.1 环境规划

系统版本

数据库版本

主机名

端口

CentOS Linux release 7.6.1810 (Core)

mysql-5.7.30-linux-glibc2.12-x86_64

mysqlmulti

3306,3307,3308

MySQL安装包下载：

系统初始化完成，selinux关闭，防火墙关闭，主机名设置等

1.2 创建用户组以及相关目录和授权

[root@mysqlmulti ~]# groupadd mysql

[root@mysqlmulti ~]# useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}

[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3307/{data,logs,tmp}

[root@mysqlmulti ~]# mkdir -p /data/mysql/mysql_3308/{data,logs,tmp}

[root@mysqlmulti ~]# chown -R mysql.mysql /data/

1.3 安装需要的包

[root@mysqlmulti ~]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

1.4 修改系统限制参数

[root@mysqlmulti ~]# vim /etc/security/limits.conf

#追加以下内容

* soft nofile 20480

* hard nofile 65535

* soft nproc 20480

* hard nproc 65535

1.5 修改内核参数

[root@mysqlmulti ~]# sysctl -p

vm.swappiness = 0

net.ipv4.tcp_max_syn_backlog = 65535

net.ipv4.tcp_max_tw_buckets = 8000

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_fin_timeout = 10

net.ipv4.ip_local_port_range = 1024 65535

net.ipv4.tcp_max_syn_backlog = 4096

net.ipv4.tcp_fin_timeout = 30

net.core.somaxconn = 65535

net.core.netdev_max_backlog = 65535

net.core.wmem_default = 87380

net.core.wmem_max = 16777216

net.core.rmem_default = 87380

net.core.rmem_max = 16777216

1.6 解压安装包并建立链接

[root@mysqlmulti ~]# tar -xJf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz -C /opt/

[root@mysqlmulti ~]# ln -s /opt/mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

1.7 改写配置文件

[root@mysqlmulti ~]# cat /etc/my.cnf

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /data/mysql/mysqld_multi.log

[mysqld]

basedir = /usr/local/mysql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#3306数据库

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3306/data

port=3306

server_id=3306

socket=/tmp/mysql_3306.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3306/log/slow.log

log-error = /data/mysql/mysql_3306/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3306/log/mysql3306_bin

#3307数据库

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3307/data

port=3307

server_id=3307

socket=/tmp/mysql_3307.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3307/log/slow.log

log-error = /data/mysql/mysql_3307/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3307/log/mysql3307_bin

#3308数据库

[mysqld3308]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3308/data

port=3308

server_id=3308

socket=/tmp/mysql_3308.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3308/log/slow.log

log-error = /data/mysql/mysql_3308/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3308/log/mysql3308_bin

1.8 进行初始化

[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3306/data --initialize-insecure --user=mysql &

2020-08-06T09:07:57.185748Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed

[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3307/data --initialize-insecure --user=mysql &

2020-08-06T09:15:04.283372Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed

[root@mysqlmulti bin]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3308/data --initialize-insecure --user=mysql &

2020-08-06T09:15:51.033914Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.15) initializing of server has completed

1.9 查看数据库是否初始化成功

3306数据库

[root@mysqlmulti data]# pwd

/data/mysql/mysql_3306/data

[root@mysqlmulti data]# ls

auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001

ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002

ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

3307数据库

[root@mysqlmulti data]# pwd

/data/mysql/mysql_3307/data

[root@mysqlmulti data]# ls

auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001

ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002

ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

3308数据库

[root@mysqlmulti data]# pwd

/data/mysql/mysql_3308/data

[root@mysqlmulti data]# ls

auto.cnf client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001

ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002

ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

1.10 设置启动

[root@mysqlmulti ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

1.11 使用mysql_multi进行多实例管理

[root@mysqlmulti ~]# /usr/local/mysql/bin/mysqld_multi start ##启动全部实例

[root@mysqlmulti ~]# /usr/local/mysql/bin/mysqld_multi report ##查看全部实例状态

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

四、需要注意的问题

配置文件安装路径不能相同

数据库目录不能相同

启动脚本不能同名

端口不能相同

socket文件的生成路径不能相同