MySQL多实例的本质
在一台机器上开启多个不同的MySQL实例,也就是各实例监听不同的端口,提供不同的服务。
多个实例公用一套MySQL安装程序,启动程序和配置文件可以是一个也可以是多个(推荐多个);各自的数据文件隔离;逻辑上各实例彼此隔离。
为什么要使用多实例?优缺点?
- 物理机性能强大,单个实例无法充分利用硬件资源
- 资源隔离,减少相互影响
- 分担连接数,MySQL随着连接数的上升,性能会下降
- 更充分的利用资源,不同业务错高峰混跑
- 有优点,也有缺点,比如多个实例会存在资源相互抢占的问题,当某个实例的并发较高或者存在慢查询时,它会消耗更多的硬件资源,这就可能影响到别的实例的性能
多实例的应用场景
- 资金比较紧张的公司
- 并发访问不大的业务
MySQL多实例常见配置方案
- (推荐)通过多个配置文件及多个启动程序来实现多实例。
- 单一配置文件方案,即一个配置文件中写多个实例的配置。
1.目录规划
/opt/software/mysql # MySQL的安装目录 /data/mysql/ # 所有的MySQL实例的数据目录、备份目录、日志目录,都在该目录下,各个实例以端口号命名 /etc/my.conf # MySQL 3306实例的默认配置文件
2.依赖下载
# 如果你的系统曾经安装过mariadb,请先卸载 yum remove -y mariadb* yum install -y epel-release yum update -y yum install -y cmake gcc-c++ ncurses-devel perl-Data-Dumper boost-doc boost-devel libaio-devel yum install -y net-tools tree bash-completion lrzsz
停止单实例的运行,并且备份单实例的配置文件(防止多实例初始化时读取这个单实例配置文件),后续多实例配置完事后,再重新还原这个配置文件
[root@cs software]# systemctl stop mysqld [root@cs software]# mv /etc/my.cnf /etc/my.cnf.bak
准备多实例的数据目录
由于多实例的数据目录已经在单实例那里创建成功了,这一步就可以略过:
每台实例以端口命名,端口名目录下存放配置文件,其中的data目录存放各自的数据。
为每个实例创建配置文件
我们将每个实例(3306的可配置也可不配置,因为它默认使用的是/etc/my.cnf)的配置文件都放在各自端口目录的下面,视情况修改下面参数,然后直接拷贝运行即可:
cat > /data/mysql/3307/my.cnf <<EOF [mysqld] basedir=/opt/software/mysql datadir=/data/mysql/3307/data socket=/data/mysql/3307/mysql.sock log-error=/data/mysql/3307/logs/errorlog/mysql.log port=3307 server_id=7 [client] socket=/data/mysql/3307/mysql.sock EOF cat > /data/mysql/3308/my.cnf <<EOF [mysqld] basedir=/opt/software/mysql datadir=/data/mysql/3308/data socket=/data/mysql/3308/mysql.sock log-error=/data/mysql/3308/logs/errorlog/mysql.log port=3308 server_id=8 [client] socket=/data/mysql/3308/mysql.sock EOF cat > /data/mysql/3309/my.cnf <<EOF [mysqld] basedir=/opt/software/mysql datadir=/data/mysql/3309/data socket=/data/mysql/3309/mysql.sock log-error=/data/mysql/3309/logs/errorlog/mysql.log port=3309 server_id=9 [client] socket=/data/mysql/3309/mysql.sock EOF cat > /data/mysql/3310/my.cnf <<EOF [mysqld] basedir=/opt/software/mysql datadir=/data/mysql/3310/data socket=/data/mysql/3310/mysql.sock log-error=/data/mysql/3310/logs/errorlog/mysql.log port=3310 server_id=10 [client] socket=/data/mysql/3310/mysql.sock EOF
完事之后,各自的数据目录下就有了配置文件了:
[root@cs software]# ls /data/mysql/3307/ backup data logs my.cnf [root@cs software]# cat /data/mysql/3307/my.cnf [mysqld] basedir=/opt/software/mysql datadir=/data/mysql/3307/data socket=/data/mysql/3307/mysql.sock log-error=/data/mysql/3307/logs/errorlog/mysql.log port=3307 server_id=7 [client] socket=/data/mysql/3307/mysql.sock
授权:
[root@cs software]# chown -R mysql.mysql /data/mysql/*
现在,配置文件完事了,就可以着手进行初始化了。
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data --basedir=/opt/software/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3309/data --basedir=/opt/software/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3310/data --basedir=/opt/software/mysql
实际上,下面这几个配置文件内容都是来自于mysqld.service
文件,然后修改了各自实例的配置文件路径:
cat > /etc/systemd/system/mysqld3307.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf LimitNOFILE = 5000 EOF cat > /etc/systemd/system/mysqld3308.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf LimitNOFILE = 5000 EOF cat > /etc/systemd/system/mysqld3309.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf LimitNOFILE = 5000 EOF cat > /etc/systemd/system/mysqld3310.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3310/my.cnf LimitNOFILE = 5000 EOF
多实例的配置基本完毕,可以尝试启动了:
systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service systemctl start mysqld3310.service # 下面两个命令等价 systemctl start mysqld3307.service systemctl start mysqld3307
验证:
[root@cs software]# find / -name mysql.sock /data/mysql/3307/mysql.sock /data/mysql/3308/mysql.sock /data/mysql/3309/mysql.sock /data/mysql/3310/mysql.sock [root@cs software]# netstat -lnp|grep 33 tcp6 0 0 :::3307 :::* LISTEN 100746/mysqld tcp6 0 0 :::3308 :::* LISTEN 100753/mysqld tcp6 0 0 :::3309 :::* LISTEN 100760/mysqld tcp6 0 0 :::3310 :::* LISTEN 100768/mysqld unix 2 [ ACC ] STREAM LISTENING 411727 100760/mysqld /data/mysql/3309/mysql.sock unix 2 [ ACC ] STREAM LISTENING 411405 100746/mysqld /data/mysql/3307/mysql.sock unix 2 [ ACC ] STREAM LISTENING 411408 100768/mysqld /data/mysql/3310/mysql.sock unix 2 [ ACC ] STREAM LISTENING 411730 100753/mysqld /data/mysql/3308/mysql.sock
标签:配置文件,--,安装,Mysql,实例,mysqld,mysql,data From: https://www.cnblogs.com/jjjyyylll/p/18399836