Mac MySQL 8.0 (免安装版) 主从集群搭建

2021-07-30 21:28:25

一、下载解压包

打开 MySQL 官网地址：https://dev.mysql.com/downloads/mysql/ ，选择面安装版本。

二、解压文件

下载到合适文件夹，解压压缩包。

解压 mysql-8.0.22-macos10.15-x86_64.tar.gz 三份，分别命名文件夹为 master、slave1、slave2

三、编辑数据库配置文件

在 master 文件夹下的 support-files 文件夹下面新建 my.conf 配置文件

[client]

  default-character-set=utf8

  #password   = your_password

  port        = 3306

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  #这是报错  Another process with pid 77346 is using unix socket file.

  #客户端也需要这个和服务端的一致

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/master/mysql.sock

[mysqld]

  event_scheduler=ON

  character-set-server=utf8

  init_connect='SET NAMES utf8

  port        = 3306

  mysqlx_port = 33060

  bind-address=127.0.0.1

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/master/mysql.sock

  mysqlx_socket= /Users/zeyangg/SynologyDrive/ee/mysql/master/mysqlx.sock

  skip-external-locking

  key_buffer_size = 16M

  max_allowed_packet = 1M

  table_open_cache = 64

  sort_buffer_size = 512K

  net_buffer_length = 8K

  read_buffer_size = 256K

  read_rnd_buffer_size = 512K

  myisam_sort_buffer_size = 8M

  character-set-server=utf8

  init_connect='SET NAMES utf8'

  #修改mysql的主目录

  basedir=/Users/zeyangg/SynologyDrive/ee/mysql/master

  #添加data文件的目录，存储各种数据和日志

  datadir=/Users/zeyangg/SynologyDrive/ee/mysql/master/data

  log-bin=mysql-bin

  binlog_format=mixed

  server-id   = 1

  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

  quick

  max_allowed_packet = 16M

[mysql]

  no-auto-rehash

  # Remove the next comment character if you are not familiar with SQL

  #safe-updates

  default-character-set=utf8

[myisamchk]

  key_buffer_size = 20M

  sort_buffer_size = 20M

  read_buffer = 2M

  write_buffer = 2M

[mysqlhotcopy]

  interactive-timeout

在 slave1 文件夹下的 support-files 文件夹下面新建 my.conf 配置文件

[client]

  default-character-set=utf8

  #password   = your_password

  #修改端口号不要和主库一致

  port        = 3316

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  #这是报错  Another process with pid 77346 is using unix socket file.

  #客户端也需要这个和服务端的一致

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/slave1/mysql.sock

[mysqld]

  event_scheduler=ON

  character-set-server=utf8

  init_connect='SET NAMES utf8

  #修改端口号

  port        = 3316

  mysqlx_port = 33061

  bind-address=127.0.0.1

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/slave1/mysql.sock

  mysqlx_socket= /Users/zeyangg/SynologyDrive/ee/mysql/slave1/mysqlx.sock

  skip-external-locking

  key_buffer_size = 16M

  max_allowed_packet = 1M

  table_open_cache = 64

  sort_buffer_size = 512K

  net_buffer_length = 8K

  read_buffer_size = 256K

  read_rnd_buffer_size = 512K

  myisam_sort_buffer_size = 8M

  character-set-server=utf8

  init_connect='SET NAMES utf8'

  #修改mysql的主目录

  basedir=/Users/zeyangg/SynologyDrive/ee/mysql/slave1

  #添加data文件的目录，存储各种数据和日志

  datadir=/Users/zeyangg/SynologyDrive/ee/mysql/slave1/data

  log-bin=mysql-bin

  binlog_format=mixed

  #不要和主库一致

  server-id   = 2

  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

  quick

  max_allowed_packet = 16M

[mysql]

  no-auto-rehash

  # Remove the next comment character if you are not familiar with SQL

  #safe-updates

  default-character-set=utf8

[myisamchk]

  key_buffer_size = 20M

  sort_buffer_size = 20M

  read_buffer = 2M

  write_buffer = 2M

[mysqlhotcopy]

  interactive-timeout

在 slave2 文件夹下的 support-files 文件夹下面新建 my.conf 配置文件

[client]

  default-character-set=utf8

  #password   = your_password

  #修改端口号不要和主库一致

  port        = 3326

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  #这是报错  Another process with pid 77346 is using unix socket file.

  #客户端也需要这个和服务端的一致

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/slave2/mysql.sock

[mysqld]

  event_scheduler=ON

  character-set-server=utf8

  init_connect='SET NAMES utf8

  #修改端口号

  port        = 3326

  mysqlx_port = 33062

  #修改的socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突

  socket      = /Users/zeyangg/SynologyDrive/ee/mysql/slave2/mysql.sock

  mysqlx_socket= /Users/zeyangg/SynologyDrive/ee/mysql/slave2/mysqlx.sock

  skip-external-locking

  key_buffer_size = 16M

  max_allowed_packet = 1M

  table_open_cache = 64

  sort_buffer_size = 512K

  net_buffer_length = 8K

  read_buffer_size = 256K

  read_rnd_buffer_size = 512K

  myisam_sort_buffer_size = 8M

  character-set-server=utf8

  init_connect='SET NAMES utf8'

  #修改mysql的主目录

  basedir=/Users/zeyangg/SynologyDrive/ee/mysql/slave2

  #添加data文件的目录，存储各种数据和日志

  datadir=/Users/zeyangg/SynologyDrive/ee/mysql/slave2/data

  log-bin=mysql-bin

  binlog_format=mixed

  #不要和主库一致

  server-id   = 3

  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]

  quick

  max_allowed_packet = 16M

[mysql]

  no-auto-rehash

  # Remove the next comment character if you are not familiar with SQL

  #safe-updates

  default-character-set=utf8

[myisamchk]

  key_buffer_size = 20M

  sort_buffer_size = 20M

  read_buffer = 2M

  write_buffer = 2M

[mysqlhotcopy]

  interactive-timeout

四、数据库初始化

使用命令行进入对应目录，执行以下语句

# 进入 master 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/master/bin

# 执行 master 初始化

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/master/support-files/my.cnf --initialize-insecure

# 进入 slave1 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave1/bin

# 执行 slave1 初始化

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/slave1/support-files/my.cnf --initialize-insecure

# 进入 slave2 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave2/bin

# 执行 slave2 初始化

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/slave2/support-files/my.cnf --initialize-insecure

执行过程中可能出现提示 mysqld 不安全

选择 Cancel，然后进入系统安全里面，选择 allow anyway，然后再次执行上述命令，选择 open 就可以继续运行了。

五、启动主数据库

分别启动对应数据库

# 进入 master 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/master/bin

# 启动 master

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/master/support-files/my.cnf 

# 进入 slave1 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave1/bin

# 启动 slave1

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/slave1/support-files/my.cnf 

# 进入 slave2 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave2/bin

# 启动 slave2

./mysqld --defaults-file=/Users/zeyangg/SynologyDrive/ee/mysql/slave2/support-files/my.cnf

启动 master 截图如下

六、创建用户

# 进入 master 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/master/bin

# 进入主 master 库

./mysql -h 127.0.0.1 -uroot -P3306

# 创建用户（此处使用随机生成密码，需要记住密码）

# create user 'holddie'@'%' identified by '123456';

create user 'holddie'@'%' IDENTIFIED BY RANDOM PASSWORD;

# 授权远程同步

grant replication slave on *.* to 'holddie'@'%';

# 保存刷新

flush privileges;

# 查看主库的状态

show master status\G;

查看主库的状态

从库连接主库

# 进入 slave1 从库的状态

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave1/bin

# 在从库使用刚才主库创建的账号，连接一次，看否账号可用（使用刚才创建账号对应的密码）

./mysql -h 127.0.0.1 -uholddie -P3306

# 如果登录没有问题则 quit 退出，进入 slave1 自己数据库

./mysql -h 127.0.0.1 -uroot -P3316

# 使用命令 （注意此处替换自己创建数据库账号，以及修改对应 master_log_file 名称以及偏移位置）

change master to master_host='127.0.0.1',master_port=3306,master_user='holddie',master_password='VrQ>-YtFPGw&-sJ,hI2Q', master_log_file='mysql-bin.000004',master_log_pos=156;

# 启动 slave1 同步

start slave;

# 查看同步状态

show salve status;

注意观察 Slave_IO_Running 和 Slave_SQL_Running 的状态值，只有都为 Yes 的时候才表明同步 ok，同理 Slave2 也是相同的操作步骤。

查看同步状态

# 进入 slave1 目录

cd /Users/zeyangg/SynologyDrive/ee/mysql/slave1/bin

./mysql -h 127.0.0.1 -uroot -P3316

show salve status;