实践：mysql单机多实例部署（mac）

背景：在自己电脑搭建或测试分布式服务框架时，经常会用多个数据库实例模拟多个环境的情况，因此我把搭建多实例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