CentOS7 单机配置mysql多实例运行

[root@localhost mysql]# uname -r
3.10.0-1160.108.1.el7.x86_64

NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

# 查看用户、组 是否存在
cat /etc/passwd |grep mysql 
cat /etc/group |grep mysql

# 删除用户
userdel mysql

# 创建组
groupadd mysql

# 创建一个名为mysql的系统用户，该用户属于mysql组，不能登录到系统，确保只有数据库服务器可以访问 MySQL 数据库
# -r 表示创建一个系统用户，该用户不能用于登录系统
# -g 指定用户所属的主组
# 指定用户的登录 shell 为 /sbin/nologin，这意味着该用户不能通过 SSH 或控制台登录到系统
useradd -r -g mysql mysql -s /sbin/nologin

# 创建安装目录
mkdir /usr/local/mysql 

# 下载mysql 安装包到root 目录
wget -P /root/  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

# 解压
cd /root/ && tar -zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

# 移动到mysql安装目录
mv /root/mysql-5.7.39-linux-glibc2.12-x86_64/*  /usr/local/mysql/

# 创建数据存储目录
mkdir -p /usr/local/mysql/data/{3306,3307,3308}

# 创建数据日志目录
mkdir -p /usr/local/mysql/log/

# 创建临时文件目录
mkdir -p /usr/local/mysql/tmp/{3306,3307,3308}

# 创建运行文件目录
mkdir -p /usr/local/mysql/run/

[mysqld_safe]
#mysqld=/usr/local/mysql/bin/mysqld_safe
#log-error=/usr/local/mysql/log/mysqld_safe_error.log


[mysqld]
user=mysql
basedir = /usr/local/mysql
explicit_defaults_for_timestamp=1
secure_file_priv=/usr/local/mysql_import_export
tls_version=TLSv1.2
skip-ssl=1


[mysqld_multi]
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/usr/local/mysql/log/mysqld_multi.log
user=root
password=nz131400

[mysqld3306]
server_id=3306
port=3306
log-bin=/usr/local/mysql/log/mysql-3306-bin
log-error = /usr/local/mysql/log/error-3306.log
tmpdir=/usr/local/mysql/tmp/3306
datadir=/usr/local/mysql/data/3306
socket=/usr/local/mysql/run/mysql-3306.sock
slow_query_log=on
slow_query_log_file =/usr/local/mysql/log/mysql-3306-slow.log
long_query_time=1
pid-file=/usr/local/mysql/mysql-3306.pid



[mysqld3307]
server_id=3307
port=3307
log-bin=/usr/local/mysql/log/mysql-3307-bin
log-error = /usr/local/mysql/log/error-3307.log
tmpdir=/usr/local/mysql/tmp/3307
datadir=/usr/local/mysql/data/3307
socket=/usr/local/mysql/run/mysql-3307.sock
slow_query_log=on
slow_query_log_file =/usr/local/mysql/log/mysql-3307-slow.log
long_query_time=1
pid-file=/usr/local/mysql/mysql-3307.pid
read_only=1

[mysqld3308]
server_id=3308
port=3308
log-bin=/usr/local/mysql/log/mysql-3308-bin
log-error = /usr/local/mysql/log/error-3308.log
tmpdir=/usr/local/mysql/tmp/3308
datadir=/usr/local/mysql/data/3308
socket=/usr/local/mysql/run/mysql-3308.sock
slow_query_log=on
slow_query_log_file =/usr/local/mysql/log/mysql-3308-slow.log
long_query_time=1
pid-file=/usr/local/mysql/mysql-3308.pid
read_only=1


[mysql]
prompt=\\u@\\d \\r:\\m:\\s>
default-character-set=utf8mb4
no-auto-rehash

# 递归授予安装目录所有权
chown -R mysql:mysql /usr/local/mysql
chmod 644 mysql

# 初始化数据库  记住初始化3306、3307、3308的数据库密码(如上图所示)
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3306

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3307

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/3308

# 重新递归授予安装目录所有权
chown -R mysql:mysql /usr/local/mysql

# 递归授予安装目录用户添加读写和执行权限
chmod u+wrx -R /usr/local/mysql

vim /etc/profile
# 在文件末尾添加下面信息
export PATH=/usr/local/mysql/bin:$PATH
# 刷新环境变量
source /etc/profile

# 使用mysqld_multi启动3306端口的实例 
mysqld_multi start 3306

# 使用mysqld_multi启动全部实例
mysqld_multi start

# 使用mysqld_multi查看实例状态
mysqld_multi report

# 登录三台mysql,输入初始密码

mysql -uroot -p -h127.0.0.1 -P3306 
mysql -uroot -p -h127.0.0.1 -P3307
mysql -uroot -p -h127.0.0.1 -P3307 

# 修改root密码
alter user root@localhost identified by '123456';

# 设置远程访问权限
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

# 防火墙放行MySQL监听的端口

# 查看防火墙服务服务的状态
systemctl status firewalld    

# 启用自启动防火墙服务
systemctl enable firewalld

# 开放3306/tcp，3307/tcp,3308/tcp 端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=3307/tcp --permanent
firewall-cmd --zone=public --add-port=3308/tcp --permanent

# 重启防火墙重
firewall-cmd --reload         

# 其他命令
systemctl start firewalld      # 手动启动防火墙服务

firewall-cmd --list-ports      # 查看已经开放的端口

mysqladmin -h127.0.0.1 -uroot -p -P3306 shutdown 
 mysqladmin -h127.0.0.1 -uroot -p -P3307 shutdown 
 mysqladmin -h127.0.0.1 -uroot -p -P3308 shutdown