mysql多实例

`前提需要`

1. mysql8.0

配置my.cnf

创建目录

可以在/user/local/mysql下创建两个目录

sudo mkdir mysql_3306
sudo mkdir mysql_3307

chown mysql:mysql mysql_3306
chown mysql:mysql mysql_3307

更改配置文件

可以在/etc/下找到my.cnf

sudo chmod 777 my.cnf # 大概率my.cnf是只读文件，要让文件变得可写
vim my.cnf

可以直接复制下面的内容

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = 12345678
log = multi_mysql.log

[mysqld3306]
port = 3306
socket = /tmp/mysql_3306.sock
pid-file = /usr/local/mysql/mysql_3306/mysqld.local.pid
datadir = /usr/local/mysql/mysql_3306
skip-grant-tables

[mysqld3307]
port = 3307
socket = /tmp/mysql_3307.sock
pid-file = /usr/local/mysql/mysql_3307/mysqld.local.pid
datadir = /usr/local/mysql/mysql_3307
skip-grant-tables

先不需要密码登录

sudo chmod 644 my.cnf # 大概率my.cnf是只读文件，要让文件变得可写

初始化两个目录

sudo mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/mysql_3306

sudo mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/mysql_3307

初始化过程中会有如下情况

mysqld: Can't create directory '/usr/local/mysql/data2/' (OS errno 13 - Permission denied)
2023-01-02T12:52:18.271775Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.28-macos11-arm64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 32963
2023-01-02T12:52:18.272964Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/mysql/data2/ is unusable. You can remove all files that the server added to it.
2023-01-02T12:52:18.272995Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-01-02T12:52:18.273124Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.28-macos11-arm64/bin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.

以上情况是没有加sudo

如果是一下情况，则是初始化成功

2023-01-02T12:52:24.044753Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.28-macos11-arm64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 32965
2023-01-02T12:52:24.045814Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data2/ is case insensitive
2023-01-02T12:52:24.049373Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-01-02T12:52:24.106741Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-01-02T12:52:24.827055Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: XBE+msf&4/*#
2023-01-02T12:52:25.089437Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.28).

会生成一个临时的密码，因为已经设置了`skip-grant-tables`，所以可以忽略

启动mysql服务

start

sudo mysqld_multi start

report

如果出现一下情况，是因为my.cnf必须是只读文件，所以使用` sudo chmod 644 my.cnf`就好了。

my_print_defaults: [Warning] World-writable config file '/etc/my.cnf' is ignored.

启动的日志在multi_mysql.log下

stop

如果想知道当前mysql服务的状态，可以使用如下命令

sudo mysqld_multi stop

登录mysql

mysql -S /tmp/mysql_3306.sock
mysql -S /tmp/mysql_3307.sock

修改密码

use mysql; 

select host, user, plugin from user where user='root';
# 大概率你会看到如下内容
| localhost | root | caching_sha2_password |
# 可以将plugin改成mysql_native_password
update user set plugin='mysql_native_password' where user='root';
# 修改密码
update user set authentication_string='' where user='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
flush privileges;

exit;

修改my.cnf文件

sudo mysqld_multi stop
sudo chmod 777 my.cnf

将my.cnf中的skip-grant-tables删除

sudo mysqld_multi start
sudo mysqld_multi report
# 应该看到两个服务启动

登录mysql again

mysql -S /tmp/mysql_3306.sock -uroot -p12345678
mysql -S /tmp/mysql_3307.sock -uroot -p12345678

`修改成功`