MySQL 单机多实例部署

MySQL 单机多实例部署

MySQL多实例两种部署方式
第一种：使用多个配置文件启动不同的进程来实现多实例。 优点：逻辑简单，配置简单。缺点：管理起来不太方便。
第二种：通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式， 优点：管理起来很方便，集中管理。缺点：定制每个实例的配置不太方面。

本文说明第二种方式部署mysql8.0
系统版本：
[root@LY-ZABBIX-APDB ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

下载安装包

下载地址：https://dev.mysql.com/downloads/mysql/
选择适合的版本

1.解压到指定目录
tar -zvxf mysql-8.0.18-el7-x86_64.tar.gz -C /
2. 重命名文件
mv /mysql-8.0.18-el7-x86_64 /mysql
3.新建配置文件
my.cnf配置文件生效优先级（由高到低）：
MySQL启动时指定的具体参数
MySQL启动时指定位置的配置文件
MySQL数据目录下的my.cnf文件
MySQL读取/etc/my.cnf文件
MySQL默认的参数
vim /etc/my.cnf
[client]
default-character-set=utf8mb4

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_bin
basedir=/mysql

[mysqld_multi]
mysqld=/mysql/bin/mysqld_safe
mysqladmin=/mysql/bin/mysqladmin
#user=mysql
#password=password
log=/mysql/log/mysql_multi.log

[mysqld3306]
port=3306
server_id=1
character_set_server=utf8mb4
collation_server=utf8mb4_bin
basedir=/mysql
datadir=/mysql/3306/data
socket=/tmp/mysql3306.sock
tmpdir=/mysql/3306/tmp
pid-file=/mysql/3306/mysql3306.pid
explicit_defaults_for_timestamp=true
log-output=file
slow_query_log=1
long_query_time=1
slow_query_log_file=/mysql/3306/log/slow.log
log-error=/mysql/3306/log/error3306.log
binlog_format=STATEMENT
log-bin=/mysql/3306/log/mysql3306_bin

[mysqld3307]
port=3307
server_id=2
character_set_server=utf8mb4
collation_server=utf8mb4_bin
basedir=/mysql
datadir=/mysql/3307/data
socket=/tmp/mysql3307.sock
tmpdir=/mysql/3307/tmp
pid-file=/mysql/3307/mysql3307.pid
explicit_defaults_for_timestamp=true
log-output=file
slow_query_log=1
long_query_time=1
slow_query_log_file=/mysql/3307/log/slow.log
log-error=/mysql/3307/log/error3307.log
binlog_format=ROW
log-bin=/mysql/3307/log/mysql3307_bin

[mysqld3308]
port=3308
server_id=3
character_set_server=utf8mb4
collation_server=utf8mb4_bin
basedir=/mysql
datadir=/mysql/3308/data
socket=/tmp/mysql3308.sock
tmpdir=/mysql/3308/tmp
pid-file=/mysql/3308/mysql3308.pid
explicit_defaults_for_timestamp=true
log-output=file
slow_query_log=1
long_query_time=1
slow_query_log_file=/mysql/3308/log/slow.log
log-error=/mysql/3308/log/error3308.log
binlog_format=mixed
log-bin=/mysql/3308/log/mysql3308_bin

4. 创建实例用户，目录,和授权
   useradd -M -s /sbin/nologin mysql
   mkdir /mysql/log -p
   mkdir /mysql/{3306,3307,3308}/data -p
   mkdir /mysql/{3306,3307,3308}/log -p
   mkdir /mysql/{3306,3307,3308}/tmp -p
   touch /mysql/3306/log/error3306.log 要创建文件，不然会报错
   touch /mysql/3307/log/error3307.log
   touch /mysql/3308/log/error3308.log
   chown -R mysql:mysql /mysql/

5.初始化mysql实例
/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/mysql/3306/data/ --initialize
/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/mysql/3307/data/ --initialize
/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/mysql/3308/data/ --initialize
记住初始化生成的临时密码，后面登陆使用
2020-01-07T13:31:23.319647Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q#yadhl9O+p6
初始化后会在对应data目录下生成文件，需要再次授权data目录
chown -R mysql:mysql /mysql/

6.添加环境变量
echo ‘export PATH=$PATH:/mysql/bin’ >> /etc/profile
source /etc/profile
7.启动mysql，指定配置文件
[root@LY-ZABBIX-APDB mysql]# mysqld_multi --defaults-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running

mysqld_multi --defaults-file=/etc/my.cnf start 3306
mysqld_multi --defaults-file=/etc/my.cnf start 3307
mysqld_multi --defaults-file=/etc/my.cnf start 3308

等几十秒显示
[root@LY-ZABBIX-APDB data]# mysqld_multi --defaults-file=/etc/my.cnf 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

7. 启动不了，就查看/mysql/3306/log/error3306.log和/mysql/log/mysql_multi.log

Starting MySQL servers

2020-01-08T02:07:43.571127Z mysqld_safe Logging to ‘/mysql/3308/log/error3308.log’.
2020-01-08T02:07:43.592690Z mysqld_safe Directory ‘/mysql/multi’ for UNIX socket file don’t exists.
mysqld_multi log file version 2.16; run: Wed Jan 8 10:09:42 2020
my.cnf配置文件socket路径写错了

2020-01-08T01:25:05.045652Z 0 [System] [MY-010116] [Server] /mysql/bin/mysqld (mysqld 8.0.18) starting as process 30729
2020-01-08T01:25:05.089779Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file ‘ibdata1’ must be writable
2020-01-08T01:25:05.090139Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file ‘ibdata1’ must be writable
初始化后data目录没有授权mysql用户