背景:在自己电脑搭建或测试分布式服务框架时,经常会用多个数据库实例模拟多个环境的情况,因此我把搭建多实例mysql的过程记录下来,方便互相学习和沟通。
1.搭建环境
1) mac 电脑,版本 10.15.6
2) mysql版本 8.0.20
官网下载:https://downloads.mysql.com/archives/community/
2.搭建过程如下
2.1 步骤
1) 官网下载mysql安装tar包,注意dmg不行
mysql-8.0.20-macos10.15-x86_64.tar.gz
2)解压及创建mysql包路径
```
tar-xfv mysql-8.0.20-macos10.15-x86_64.tar.gz
cp -rf mysql-8.0.20-macos10.15-x86_64/* /usr/local/mysql8.0/
```
3)创建mysql数据文件存放路径
mkdir -p /usr/local/mysql8.0/{3306,3307,3308,3309}/data
##授权用户组mysql的权限
chown -R mysql:mysql /usr/local/mysql8.0
##创建日志文件
touch /usr/local/mysql8.0/mysqld_multi.log
4)编辑脚本 my.cnf
vim /etc/my.cnf
###文件内容如下
[mysqld]
user=mysql
basedir =/usr/local/mysql8.0
datadir=/usr/local/mysql8.0/data
[mysqld_multi]
mysqld=/usr/local/mysql8.0/bin/mysqld_safe
mysqladmin=/usr/local/mysql8.0/bin/mysqladmin
log=/usr/local/mysql8.0/mysqld_multi.log
[mysqld3306]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/local/mysql8.0/3303/data
port=3306
user=mysql
server_id=3306
socket=/tmp/mysql_3306.sock
log-error =/usr/local/mysql8.0/3306/error_3306.log
[mysqld3307]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/local/mysql8.0/3307/data
port=3307
user=mysql
server_id=3307
socket=/tmp/mysql_3307.sock
log-error =/usr/local/mysql8.0/3307/error_3307.log
[mysqld3308]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/local/mysql8.0/3308/data
port=3308
user=mysql
server_id=3308
socket=/tmp/mysql_3308.sock
log-error =/usr/local/mysql8.0/3308/error_3308.log
[mysqld3309]
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/local/mysql8.0/3309/data
port=3309
user=mysql
server_id=3309
socket=/tmp/mysql_3309.sock
log-error =/usr/local/mysql8.0/3309/error_3309.log
5)分别初始化3306、3307、3308、3309端口,注意,初始化实例的最后一行记录了root的初始密码
/usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3307/data
/usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3308/data
/usr/local/mysql8.0/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql8.0/ --datadir=/usr/local/mysql8.0/3309/data
6)设置环境变量
vim ~/.bash_profile
##添加如下配置
export PATH=/usr/local/mysql/bin:$PATH
##生效配置
source ~/.bash_profile
7) mysql命令创建别名,不然后面这三个命令识别不出来 会报错
alias mysqld=/usr/local/mysql8.0/bin/mysqld
alias mysqld_multi=/usr/local/mysql8.0/bin/mysqld_multi
alias mysqladmin=/usr/local/mysql8/bin/mysqladmin
8) 启动实例
mysqld_multi --defaults-extra-file=/etc/my.cnf --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql start 3306
mysqld_multi --defaults-extra-file=/etc/my.cnf --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql start 3307
mysqld_multi --defaults-extra-file=/etc/my.cnf --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql start 3308
mysqld_multi --defaults-extra-file=/etc/my.cnf --log=/usr/local/mysql8.0/mysqld_multi.log --user=mysql start 3309
9)验证实例
mysqld_multi --defaults-extra-file=/etc/my.cnf report
2.2 修改密码
详见:
https://blog.csdn.net/lkr_lkr/article/details/105931513
##先登录
mysql -u root -p -S /tmp/mysql_3307.sock
##修改密码
ALTER USER USER() IDENTIFIED BY '123456';
exit;
##验证
附录图:
2.3 客户端连接问题
1.出现Authentication plugin 'caching_sha2_password' 错误时,是因登录密码级别设置的高了。
2.解决方法:
1)管理员权限运行命令提示符,登陆MySQL
2)修改账户密码加密规则并更新用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码
3)刷新权限并重置密码
FLUSH PRIVILEGES; #刷新权限
alter user 'root'@'localhost' identified by '123456'; #再重置下密码
4)现在再次打开Navicat Premium 12连接MySQL问题数据库就会发现可以连接成功了
详见:https://blog.csdn.net/u011182575/article/details/80821418
网友评论