(资料图)
在服务器资源有限的情况下，可利用该方案快速搭建各类 mysql 架构方案。各 MySQL 实例共享一个 mysqld 主程序，但各实例数据目录是独立的，存放在不同的文件夹中；好了、废话不多说，直接上干货，具体搭建步骤如下
环境介绍| 实例 | 主机 | mysql port | mysqlx port | datadir |
|---|---|---|---|---|
| mysql1 | 192.168.31.100 | 3306 | 33060 | /var/lib/mysql1/ |
| mysql2 | 192.168.31.100 | 3307 | 33070 | /var/lib/mysql2/ |
| mysql3 | 192.168.31.100 | 3308 | 33080 | /var/lib/mysql3/ |
1、通过官方二进制包解压安装
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.30-el7-x86_64.tar.gztar xzf mysql-8.0.30-el7-x86_64.tar.gzmv mysql-8.0.30-el7-x86_64 /usr/local/mysql# vim /etc/profile 添加环境变量，执行 source /etc/profile 使配置在当前 shell 下生效export PATH=$PATH:/usr/local/mysql/bin/# 创建各 mysql 服务器实例的数据目录 datadiruseradd -r -M mysql -s /bin/falsemkdir -p /var/lib/mysql{1..3} && chown mysql.mysql /var/lib/mysql{1..3}设置各 mysql 实例的配置文件
cat > /etc/my.cnf << EOF[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = rootpass = root # 后续变更各 mysql 实例 root 账号的初始随机密码为简单密码 root，因为停止各 mysql 实例时需使用此密码log = /var/log/mysql_multi.log# mysql 实例一[mysqld1]server-id = 1socket = /var/lib/mysql1/mysql.sockport = 3306bind_address = 0.0.0.0datadir = /var/lib/mysql1user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mskip_name_resolve = 1log_error = error.logpid-file = /var/lib/mysql1/mysql.pidmysqlx = 1 # 设置 0 则禁用 mysqlx, 其默认监听端口 33060mysqlx-port = 33060# mysql 实例二[mysqld2]server-id = 2socket = /var/lib/mysql2/mysql.sockport = 3307bind_address = 0.0.0.0datadir = /var/lib/mysql2user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mskip_name_resolve = 1log_error = error.logpid-file = /var/lib/mysql2/mysql.pidmysqlx = 1mysqlx-port = 33070# mysql 实例三[mysqld3]server-id = 3socket = /var/lib/mysql3/mysql.sockport = 3308bind_address = 0.0.0.0datadir = /var/lib/mysql3user = mysqlperformance_schema = offinnodb_buffer_pool_size = 32Mskip_name_resolve = 1log_error = error.logpid-file = /var/lib/mysql3/mysql.pidmysqlx = 1mysqlx-port = 33080EOF1、初始化 MySQL 实例，记录各实例的初始随机密码
/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql1#> 2023-03-15T01:22:52.092218Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 15026#> 2023-03-15T01:22:52.119703Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.#> 2023-03-15T01:22:55.237170Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.#> 2023-03-15T01:23:00.616679Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lqYujqcue7*_# 同理，初始化其它 mysql 实例（mysql2、mysql3）/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql2/usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql3设置多实例启动程序
cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multidchmod +x /etc/init.d/mysqld_multid# 启动 各 mysql 实例/etc/init.d/mysqld_multid startchkconfig mysqld_multid on # 可选，设置开机启动/etc/init.d/mysqld_multid report # 查看运行情况#> Reporting MySQL servers#> MySQL server from group: mysqld1 is running#> MySQL server from group: mysqld2 is running#> MySQL server from group: mysqld3 is runningnetstat -ntlp | grep mysqld#> Active Internet connections (only servers)#> Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name#> tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19867/mysqld#> tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 19869/mysqld#> tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 19876/mysqld#> tcp6 0 0 :::33080 :::* LISTEN 19876/mysqld#> tcp6 0 0 :::33060 :::* LISTEN 19867/mysqld#> tcp6 0 0 :::33070 :::* LISTEN 19869/mysqld# 停止 各 mysql 实例，需先更新 root 账号密码为 root，因为其实现原理是通过 mysqladmin 登录到各 mysql 实例执行 shutdown/etc/init.d/mysqld_multid stop1、使用初始随机密码登录各 MySQL 实例
mysql -u root -p -P3306 -S /var/lib/mysql1/mysql.sockmysql -u root -p -P3307 -S /var/lib/mysql2/mysql.sockmysql -u root -p -P3308 -S /var/lib/mysql3/mysql.sock2、设置 MySQL root 账号为简单密码 root，且允许从任意主机访问数据库
-- 做任何操作前，需按照默认安全策略配置一个密码，才允许后续操作alter user user() identified by "Admin@123";-- 设置密码策略，否则报错提示不满足现有密码策略，如 ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsshow global variables like "validate_password%";set global validate_password.policy=0; -- validate_password_policy 设置 0 低级 1 中级 2 高级set global validate_password.length=4;set global validate_password.check_user_name = 0set global validate_password.mixed_case_count = 0;set global validate_password.number_count=0;set global validate_password.special_char_count=0;-- 修改 root 账号为简单密码 rootalter user user() identified by "root";update mysql.user set host="%" where user="root";flush privileges;exit;MySQL-5.7.x 二进制包，官方下载地址
关键词：
