mysql5.7单机多实例环境搭建

目标

在一台虚拟机上搭建三个mysql5.7.30实例

1. 准备工作

1.1 安装包

1. wget 直接下载

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz /root

2. 本地下载的包，rz -be 上传到Linux

1.2 安装环境

1.2.1 权限用户

# 建立MySQL用户"mysql"
    useradd -g mysql -u 2000 -s /sbin/nologin -d /usr/local/mysql -MN mysql

1.2.2 目录规划

安装目录

1. 安装目录：程序文件目录
2. 对应配置文件 my.cnf 的basedir参数

mkdir /application/ # 创建mysql的安装位置

数据目录

1. 数据目录：数据文件目录
2. 对应配置文件 my.cnf 的datadir参数

mkdir -p /data/mysql/330{6..9}/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/*

1.2.3 环境配置

    1）修改sysctl.conf
    echo "vm.swappiness=1" >> /etc/sysctl.conf  # 设置内核少用交换 https://cloud.tencent.com/developer/article/1503835
    sysctl -p                                                           # sysctl 命令被用于在内核运行时动态地修改内核的运行参数  -p   载入sysctl配置文件  如-p后未指定路径，则载入 /etc/sysctl.conf

    2）配置limits.conf
    echo '* soft nofile 65535' >> /etc/security/limits.conf    # 任何用户可以打开的最大的文件描述符数量    https://cloud.tencent.com/developer/article/1403636
    echo '* hard nofile 65535' >> /etc/security/limits.conf   
    
    3）配置ulimit
    echo "ulimit -n 65535" >> /etc/rc.local       # rc.local脚本是在系统初始化级别脚本运行之后再执行https://www.huaweicloud.com/articles/c1d9caaa0619eabf224e98c6a0aefb7a.html
    ulimit -n 65535
   
   4）配置IO调度
    echo deadline > /sys/block/sda/queue/scheduler  # deadline只分读写两个队列，对于磁盘I/O，Linux提供了cfq, deadline和noop三种调度策略 https://www.cnblogs.com/bamanzi/p/linux-disk-io-scheduler.html
    grubby --update-kernel=ALL --args="elevator=deadline"
  
   5）关闭NUMA
    sed -i '/^numa/c numa=off'  /etc/default/grub
    grub2-mkconfig -o /etc/grub2.cfg >/dev/null
   
   6）关闭SELinux
   vi /etc/selinux/config  # 修改内容为SELINUX=disabled

1.3 配置文件

修改配置文件
vim /data/mysql/3307/my.cnf
vim /data/mysql/3308/my.cnf
vim /data/mysql/3309/my.cnf

3307

[mysqld]
user                                =mysql
basedir                             = /application/mysql-5.7.30-linux-glibc2.12-x86_64/
datadir                             =/data/mysql/3307/data/
server_id                           =7
port                                =3307
# bind-address                        =127.0.0.1
character_set_server                =utf8mb4
explicit_defaults_for_timestamp     =on
log_timestamps                      =system
lower_case_table_names              =1
default_time_zone                   ='+08:00'
socket                              =/data/mysql/3307/tmp/mysql.sock
secure_file_priv                    =/data/mysql/3307/tmp/
binlog_format                       =row
log_bin                             =/data/mysql/3307/logs/mysql-bin
binlog_rows_query_log_events        =on
log_slave_updates                   =on
log_error                           =/data/mysql/3307/logs/error.log
general_log                         =off
general_log_file                    =/data/mysql/3307/logs/general.log
slow_query_log                      =on
slow_query_log_file                 =/data/mysql/3307/logs/slow_query.log
log_queries_not_using_indexes       =on
long_query_time                     =1.000000
gtid_mode                           =on
enforce_gtid_consistency            =on

3308

[mysqld]
user                                =mysql
basedir                             = /application/mysql-5.7.30-linux-glibc2.12-x86_64/
datadir                             =/data/mysql/3308/data/
server_id                           =8
port                                =3308
# bind-address                        =127.0.0.1
character_set_server                =utf8mb4
explicit_defaults_for_timestamp     =on
log_timestamps                      =system
lower_case_table_names              =1
default_time_zone                   ='+08:00'
socket                              =/data/mysql/3308/tmp/mysql.sock
secure_file_priv                    =/data/mysql/3308/tmp/
binlog_format                       =row
log_bin                             =/data/mysql/3308/logs/mysql-bin
binlog_rows_query_log_events        =on
log_slave_updates                   =on
log_error                           =/data/mysql/3308/logs/error.log
general_log                         =off
general_log_file                    =/data/mysql/3308/logs/general.log
slow_query_log                      =on
slow_query_log_file                 =/data/mysql/3308/logs/slow_query.log
log_queries_not_using_indexes       =on
long_query_time                     =1.000000
gtid_mode                           =on
enforce_gtid_consistency            =on

3309

[mysqld]
user                                =mysql
basedir                             = /application/mysql-5.7.30-linux-glibc2.12-x86_64/
datadir                             =/data/mysql/3309/data/
server_id                           =9
port                                =3309
# bind-address                        =127.0.0.1
character_set_server                =utf8mb4
explicit_defaults_for_timestamp     =on
log_timestamps                      =system
lower_case_table_names              =1
default_time_zone                   ='+08:00'
socket                              =/data/mysql/3309/tmp/mysql.sock
secure_file_priv                    =/data/mysql/3309/tmp/
binlog_format                       =row
log_bin                             =/data/mysql/3309/logs/mysql-bin
binlog_rows_query_log_events        =on
log_slave_updates                   =on
log_error                           =/data/mysql/3309/logs/error.log
general_log                         =off
general_log_file                    =/data/mysql/3309/logs/general.log
slow_query_log                      =on
slow_query_log_file                 =/data/mysql/3309/logs/slow_query.log
log_queries_not_using_indexes       =on
long_query_time                     =1.000000
gtid_mode                           =on
enforce_gtid_consistency            =on

2. 安装

解压安装包

tar zxf /root/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /application/

ls /opt/mysql-5.7.30-linux-glibc2.12-x86_64 # 检查一下

3. 启动

3.1 启动前环境检查及准备

检查依赖

 ldd /opt/mysql-5.7.30-linux-glibc2.12-x86_64/bin/mysqld

创建软连接

ln -sf /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql

配置path

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile

使生效

 . /etc/profile

 which mysql

3.2 初始化

初始化命令：
./bin/mysqld --defaults-file=/data/mysql/3307/my.cnf --initialize
./bin/mysqld --defaults-file=/data/mysql/3308/my.cnf --initialize
./bin/mysqld --defaults-file=/data/mysql/3309/my.cnf --initialize-insecure

3.3 启动

启动服务命令：
 ./bin/mysqld --defaults-file=/data/mysql/3307/my.cnf &
 ./bin/mysqld --defaults-file=/data/mysql/3308/my.cnf &
 ./bin/mysqld --defaults-file=/data/mysql/3309/my.cnf &

3.4 登录

查看初始密码：
cat /data/mysql/3307/logs/error.log
cat /data/mysql/3308/logs/error.log

登录命令：
 ./bin/mysql -u root -p --socket=/data/mysql/3307/tmp/mysql.sock 
 ./bin/mysql -u root -p --socket=/data/mysql/3308/tmp/mysql.sock 
 ./bin/mysql -u root -p --socket=/data/mysql/3309/tmp/mysql.sock 
 passwd: hellomysql

show databases失败：
修改初始随机密码即可

alter user user() indentified by "你的密码"

4. 主从搭建

5.6,5.7步骤基本差不多 https://blog.csdn.net/weixin_43224710/article/details/109329553

参考文献：
【1】主从复制 https://blog.csdn.net/weixin_43224710/article/details/109329553
【2】5.7安装 https://mp.weixin.qq.com/s/Y1BRn7Wvt3nC3QTeJOsbXw
[3] 远程无法连接 https://www.cnblogs.com/isme-zjh/p/11540442.html