在服务器资源有限的情况下,可利用该方案快速搭建各类 mysql 架构方案.各 MySQL 实例共享一个 mysqld 主程序,但各实例数据目录是独立的,存放在不同的文件夹中；好了、废话不多说,直接上干货,具体搭建步骤如下
原文链接
①.、通过官方二进制包解压安装
wget https://cdn.mysql.com/archives/mysql-⑧0/mysql-⑧0.30-el7-x86_6④tar.gz
tar xzf mysql-⑧0.30-el7-x86_6④tar.gz
mv mysql-⑧0.30-el7-x86_64 /usr/local/mysql
# vim /etc/profile 添加环境变量,执行 source /etc/profile 使配置在当前 shell 下生效
export PATH=$PATH:/usr/local/mysql/bin/
# 创建各 mysql 服务器实例的数据目录 datadir
useradd -r -M mysql -s /bin/false
mkdir -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_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = root # 后续变更各 mysql 实例 root 账号的初始随机密码为简单密码 root,因为停止各 mysql 实例时需使用此密码
log = /var/log/mysql_multi.log
# mysql 实例一
[mysqld1]
server-id = 1
socket = /var/lib/mysql1/mysql.sock
port = 3306
bind_address = 0.0.0.0
datadir = /var/lib/mysql1
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql1/mysql.pid
mysqlx = 1 # 设置 0 则禁用 mysqlx, 其默认监听端口 33060
mysqlx-port = 33060
# mysql 实例二
[mysqld2]
server-id = 2
socket = /var/lib/mysql2/mysql.sock
port = 3307
bind_address = 0.0.0.0
datadir = /var/lib/mysql2
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql2/mysql.pid
mysqlx = 1
mysqlx-port = 33070
# mysql 实例三
[mysqld3]
server-id = 3
socket = /var/lib/mysql3/mysql.sock
port = 3308
bind_address = 0.0.0.0
datadir = /var/lib/mysql3
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
skip_name_resolve = 1
log_error = error.log
pid-file = /var/lib/mysql3/mysql.pid
mysqlx = 1
mysqlx-port = 33080
EOF
①.、初始化 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 ⑧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:5⑤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_multid
chmod ◆x /etc/init.d/mysqld_multid
# 启动 各 mysql 实例
/etc/init.d/mysqld_multid start
chkconfig 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 running
netstat -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 stop
①.、使用初始随机密码登录各 MySQL 实例
mysql -u root -p -P3306 -S /var/lib/mysql1/mysql.sock
mysql -u root -p -P3307 -S /var/lib/mysql2/mysql.sock
mysql -u root -p -P3308 -S /var/lib/mysql3/mysql.sock
-- 做任何操作前,需按照默认安全策略配置一个密码,才允许后续操作
alter user user() identified by 'Admin@123';
-- 设置密码策略,否则报错提示不满足现有密码策略,如 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
show 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 = 0
set global validate_password.mixed_case_count = 0;
set global validate_password.number_count=0;
set global validate_password.special_char_count=0;
-- 修改 root 账号为简单密码 root
alter user user() identified by 'root';
update mysql.user set host='%' where user='root';
flush privileges;
exit;
以上就是土嘎嘎小编大虾米为大家整理的相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用，就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！