一、MySQL安装部署-多实例（Linux）

在数据库服务运行过程中，在一个系统环境中，可以运行多个相同的服务程序信息，并且产生不同的进程和网络端口信息，称为多实例；

多个数据库服务实例信息中存储的数据库信息是相互隔离和独立的，并且利用数据库服务多实例可以实现测试与分布式架构需求；

企业数据库服务多实例应用架构设计：（主要用于支持多套业务场景）

1.多实例部署环境规划

实例信息编号	实例服务端口	实例存储路径	实例配置文件	套接字文件
mysql-01	端口信息：3307	/data/3307/data	/data/3307/data/my.cnf	/tmp/mysql3307.sock
mysql-02	端口信息：3308	/data/3308/data	/data/3308/data/my.cnf	/tmp/mysql3308.sock
mysql-03	端口信息：3309	/data/3309/data	/data/3309/data/my.cnf	/tmp/mysql3309.sock

2.多实例部署环境准备

下载和校验

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.43-linux-glibc2.28-x86_64.tar
md5sum mysql-8.0.43-linux-glibc2.28-x86_64.tar

YUM源

mkdir -p /etc/yum.repos.d/backup
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/backup/
cat >/etc/yum.repos.d/kylin_aarch64.repo<<EOF
[ks10-adv-os]
name = Kylin Linux Advanced Server 10 - OS
baseurl = https://update.cs2c.com.cn/NS/V10/V10SP3-2403/os/adv/lic/base/x86_64/
gpgcheck = 0
enabled = 1

[ks10-adv-updates]
name = Kylin Linux Advanced Server 10 - Updates
baseurl = https://update.cs2c.com.cn/NS/V10/V10SP3-2403/os/adv/lic/updates/x86_64/
gpgcheck = 0
enabled = 1
EOF

yum clean all
yum makecache
yum repolist

相关软件

yum remove  -y mysql*
yum search libaio  # search for info
yum install -y libaio # install library
yum install -y ncurses-compat-libs

3.多实例初始数据信息

# 多实例初始化配置前调整操作
mv /etc/my.cnf /tmp

id mysql
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

cd /usr/local
tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar
tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
ln -s mysql-8.0.43-linux-glibc2.28-x86_64 mysql

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

cd mysql
mkdir -p /data/330{7..9}/{logs,binlog,relaylog,tmp,data}
chown -R mysql. /data/*

# 多实例初始化操作过程
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data
#初始化完毕后可以检查数据库目录中，是否已经存在了数据库初始化产生的数据文件信息；

4.多实例配置文件编写

3307配置

cat >/data/3307/data/my.cnf<<'EOF'
[mysql]
socket=/tmp/mysql3307.sock

[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock

log_timestamps=SYSTEM  
general_log=ON
general_log_file=/data/3307/logs/general.log

log_error=/data/3307/logs/mysql_3307.err
log_error_verbosity=3

log_bin=ON
log_bin=/data/3307/binlog/mysql-bin
log_bin_index=/data/3307/binlog/binlog.index
sync_binlog=1
binlog_checksum=NONE


gtid_mode=1
enforce_gtid_consistency=1
log_replica_updates=1 

slow_query_log=1
slow_query_log_file=/data/3307/logs/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1

server_id=3307

report_host=192.168.0.101
report_port=3307

EOF
chown -R mysql. /data/3307/data/my.cnf

3308配置

cat >/data/3308/data/my.cnf<<'EOF'
[mysql]
socket=/tmp/mysql3308.sock

[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock

log_timestamps=SYSTEM  
general_log=ON
general_log_file=/data/3308/logs/general.log

log_error=/data/3308/logs/mysql_3308.err
log_error_verbosity=3

log_bin=ON
log_bin=/data/3308/binlog/mysql-bin
log_bin_index=/data/3308/binlog/binlog.index
sync_binlog=1
binlog_checksum=NONE


gtid_mode=1
enforce_gtid_consistency=1
log_replica_updates=1 

slow_query_log=1
slow_query_log_file=/data/3308/logs/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1

server_id=3308

report_host=192.168.0.101
report_port=3308

EOF
chown -R mysql. /data/3308/data/my.cnf

3309配置

cat >/data/3309/data/my.cnf<<'EOF'
[mysql]
socket=/tmp/mysql3309.sock

[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock

log_timestamps=SYSTEM  
general_log=ON
general_log_file=/data/3309/logs/general.log

log_error=/data/3309/logs/mysql_3309.err
log_error_verbosity=3

log_bin=ON
log_bin=/data/3309/binlog/mysql-bin
log_bin_index=/data/3309/binlog/binlog.index
sync_binlog=1
binlog_checksum=NONE


gtid_mode=1
enforce_gtid_consistency=1
log_replica_updates=1 

slow_query_log=1
slow_query_log_file=/data/3309/logs/slow.log
long_query_time=0.01
log_queries_not_using_indexes=1

server_id=3309

report_host=192.168.0.101
report_port=3309

EOF
chown -R mysql. /data/3309/data/my.cnf

5.创建systemctl启动文件

3307启动服务文件

cat <<EOF> /etc/systemd/system/mysqld3307.service
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/data/my.cnf
LimitNOFILE=65535
EOF

chown -R mysql. /data/3307/data/my.cnf

3308启动服务文件

cat <<EOF> /etc/systemd/system/mysqld3308.service
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/data/my.cnf
LimitNOFILE=65535
EOF

chown -R mysql. /data/3308/data/my.cnf

3309启动服务文件

cat <<EOF> /etc/systemd/system/mysqld3309.service
[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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/data/my.cnf
LimitNOFILE=65535
EOF

chown -R mysql. /data/3309/data/my.cnf

配置开机自启动

systemctl daemon-reload

systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3300

systemctl enable mysqld3307
systemctl enable mysqld3308
systemctl enable mysqld3309

本地连接

非安全数据初始化，是空密码，本地通过不同的socket连接，进去就可以设置密码，创建相关用户。

mysql -S /tmp/mysql3307.sock
mysql -S /tmp/mysql3308.sock
mysql -S /tmp/mysql3309.sock