mysql主从复制

最新推荐文章于 2025-04-17 13:09:25 发布

李二林

最新推荐文章于 2025-04-17 13:09:25 发布

阅读量548

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.youkuaiyun.com/qq_40132981/article/details/124512153

版权

本文详细介绍了如何在Mac上下载MySQL8的免安装压缩包，配置my.cnf文件以支持主从复制，并进行初始化。内容包括设置客户端连接参数、修改socket路径、调整内存设置，以及执行初始化操作和无密码登录。最后指导了主从节点的设置与同步过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、官网下载压缩包免安装形式的官网下载免装压缩包

2、复制两份

2.1在mysql8-master的support-files文件夹中创建 my.cnf

[client]
  default-character-set=outfit
  password   = 123456
  port        = 3307

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  #这是报错  Another process with pid 77346 is using unix socket file.
  #客户端也需要这个和服务端的一致
  socket      = /Users/lel/Downloads/mysql8-master/mysql.sock

[mysqld]
  event_scheduler=ON
  character-set-server=utf8
  init_connect='SET NAMES utf8'
  port        = 3307
  mysqlx_port = 33070
  #bind-address=127.0.0.1

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  socket       = /Users/lel/Downloads/mysql8-master/mysql.sock
  mysqlx_socket= /Users/lel/Downloads/mysql8-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/lel/Downloads/mysql8-master/mysql.sock
  #添加data文件的目录，存储各种数据和日志
  datadir=/Users/lel/Downloads/mysql8-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

2.2在mysql-slave的support-files文件夹中创建my.cnf

[client]
  default-character-set=outfit
  password   = 123456
  port        = 3307

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  #这是报错  Another process with pid 77346 is using unix socket file.
  #客户端也需要这个和服务端的一致
  socket      = /Users/lel/Downloads/mysql8-master/mysql.sock

[mysqld]
  event_scheduler=ON
  character-set-server=utf8
  init_connect='SET NAMES utf8'
  port        = 3307
  mysqlx_port = 33070
  #bind-address=127.0.0.1

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  socket       = /Users/lel/Downloads/mysql8-master/mysql.sock
  mysqlx_socket= /Users/lel/Downloads/mysql8-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/lel/Downloads/mysql8-master/mysql.sock
  #添加data文件的目录，存储各种数据和日志
[client]
  default-character-set=outfit
  #password   = 123456
  port        = 3308

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  #这是报错  Another process with pid 77346 is using unix socket file.
  #客户端也需要这个和服务端的一致
  socket      = /Users/lel/Downloads/mysql8-slave/mysql.sock

[mysqld]
  event_scheduler=ON
  character-set-server=utf8
  init_connect='SET NAMES utf8'
  port        = 3308
  mysqlx_port = 33080
  #bind-address=127.0.0.1

  #修改socket文件的位置，默认是走的/tmp下的mysql.sock会有冲突
  socket       = /Users/lel/Downloads/mysql8-slave/mysql.sock
  mysqlx_socket= /Users/lel/Downloads/mysql8-slave/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/lel/Downloads/mysql8-slave/mysql.sock
  #添加data文件的目录，存储各种数据和日志
  datadir=/Users/lel/Downloads/mysql8-slave/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

3、初始化

cd /Users/lel/Downloads/mysql8-master/bin
初始化
./mysqld --defaults-file=/Users/lel/Downloads/mysql8-master/support-files/my.cnf --initialize-insecure

cd /Users/lel/Downloads/mysql8-slave/bin
初始化
./mysqld --defaults-file=/Users/lel/Downloads/mysql8-slave/support-files/my.cnf --initialize-insecure

4、无密码登陆master

cd /Users/lel/Downloads/mysql8-master/bin
./mysql -uroot
#创建用户
create user 'master'@'%' identified by '123456';
#用户授权
grant replication slave on *.* to 'master'@'%';
#master用户允许远程访问
update user set host='%' where user='master';
#刷新
flush privileges;
#看主节点状态
show master status;

5、主节点状态

从节点设置

change master to master_host='127.0.0.1',master_port=3307,master_user='master',master_password='123456', master_log_file='mysql-bin.000008',master_log_pos=2047;
#刷新
flush privileges;
#从节点开始
start slave;
#查看从节点状态
show slave status;
#停止从节点
stop slave;