linux mysql8.0多实例部署

1.前期准备

1.1首先先从官网下载安装包

https://dev.mysql.com/downloads/mysql/

1.2 创建软件目录，解压迁移软件

[root@db03 opt]# tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz mysql
[root@db03 opt]# ls
mysql

1.3 创建 mysql 用户

[root@db03 opt]# groupadd -g 1002 mysql
[root@db03 opt]# useradd -u 1002 -g 1002 -M -s /sbin/nologin mysql
[root@db03 opt]# id mysql
uid=1002(mysql) gid=1002(mysql) groups=1002(mysql)

1.4 添加环境变量

echo 'export PATH=$PATH:/opt/mysql/bin' >>  /etc/profile 
source /etc/profile

2.部署

2.1 创建多实例目录

[root@db03 ~]# mkdir -p /data/330{7,8,9}/data

2.2 准备多配置文件

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

2.3 初始化三套数据字典

mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql

2.4 systemd管理多实例，准备三个启动文件

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/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

[root@db03 ~]# cd /etc/systemd/system/
[root@db03 system]# cp mysqld3307.service mysqld3308.service 
[root@db03 system]# cp mysqld3307.service mysqld3309.service

vim mysqld3308.service
# 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
vim mysqld3309.service
# 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

[root@db03 system]# grep 'ExecStart' mysqld3307.service
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
[root@db03 system]# grep 'ExecStart' mysqld3308.service
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
[root@db03 system]# grep 'ExecStart' mysqld3309.service
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

2.5 授权目录权限

[root@db03 ~]# chown -R mysql.mysql /opt/mysql
[root@db03 ~]# chown -R mysql.mysql /data/330*

2.6 启动多实例

[root@db03 ~]# systemctl start mysqld3307.service
[root@db03 ~]# systemctl start mysqld3308.service
[root@db03 ~]# systemctl start mysqld3309.service

2.7查看状态

[root@db03 mysql]# netstat -tlnap | grep mysql
tcp6       0      0 :::33060                :::*                    LISTEN      31303/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      31303/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      31359/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      31414/mysqld  

3.连接

3.1进入mysql 目录

[root@db03 mysql] #  ./bin/mysql -uroot -p -P3307 -S /data/3307/mysql.sock

3.2修改密码，允许远程连接

mysql> use mysql;
mysql> select user,host from user;
mysql>update user set host= '%' where user = 'root'; 
mysql>update user set plugin='mysql_native_password' where user ='root';
mysql>flush privileges;
mysql>ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
mysql>flush privileges;
mysql> exit;

3.3 重启mysql

[root@db03 ~] systemctl restart mysqld3307.service