mac 配置mysql 主从
一、配置过程
我们这里借鉴网上的文章进行搭建，该网上的帖子里面也有一些问题，然后我们这里记录一下搭建过程以及中间的所有的问题。https://blog.csdn.net/andyvera/article/details/93140839
我们这里采用的主从配置是采用[mysqld_multi]进行主从配置
1.下载
https://dev.mysql.com/downloads/mysql/
下载下面这个即可
image.png
解压并拷贝到一个位置
tar -zxvf mysql-8.0.20-macos10.15-x86_64.tar.gz
cp -r mysql-8.0.20-macos10.15-x86_64/* /Users/zhouzhenyong/software/mysql-8.0.6
创建连接
// 首先创建路径：-p是强制创建路径
sudo mkdir -p /usr/local/mysql
// 创建关联
sudo ln -s /Users/zhouzhenyong/software/mysql-8.0.6 /usr/local/mysql
2.配置环境变量
sudo vi ~/.bash_profile
添加：export PATH=$PATH:/usr/local/mysql/mysql-8.0.6/bin
source ~/.bash_profile
注意：如果是在fish中，会有异常，记得请先退出fish
3.创建主从mysql
首先创建数据的目录
mkdir -p /Users/zhouzhenyong/mysql-cluster/master/data
mkdir -p /Users/zhouzhenyong/mysql-cluster/slave1/data
然后执行
mysqld --datadir=/Users/zhouzhenyong/mysql-cluster/master/data --initialize --initialize-insecure
mysqld --datadir=/Users/zhouzhenyong/mysql-cluster/slave1/data --initialize --initialize-insecure
注意：加上–initialize-insecure参数则生成的root用户没有密码，否则mysql初始化时随机生成一个密码并输入到日志文件中
zhouzhenyong@shizi-2 ~> mysqld --datadir=/Users/zhouzhenyong/mysql-cluster/master/data --initialize --initialize-insecure
2020-05-26T17:58:48.648103Z 0 [System] [MY-013169] [Server] /Users/zhouzhenyong/software/mysql-8.0.6/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 407
2020-05-26T17:58:48.650377Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /Users/zhouzhenyong/mysql-cluster/master/data/ is case insensitive
2020-05-26T17:58:48.669113Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-05-26T17:58:48.923259Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-05-26T17:58:49.577968Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
4.配置文件
1.首先查找我们本机的my.cnf路径
注意：其实mac中是没有对应的my.cnf路径的，这个文件也是我们创建的在控制台输入命令
mysqld --help --verbose | more
然后在下面就可以看到这样的一句话
mysqld Ver 8.0.12 for osx10.13 on x86_64 (Homebrew)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starts the MySQL database server.
Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-8.0
上面其中红色部分就是mysql配置文件所在的位置，除了上面的位置之外，也要看下这些位置是否确实有对应的配置，比如我的就在如下
/usr/local/etc/my.cnf
2.创建主从共用的文件
看到我们之前的文件在这个目录，我们就在这个目录中创建对应的主从共用文件
sudo touch /usr/local/etc/cluster.conf
下面一些配置基本是参考对应的网上配置，并做了一些修改，让自己这里能够成功运行
[mysqld_multi]
mysqld = /usr/local/mysql/mysql-8.0.6/bin/mysqld
mysqladmin = /usr/local/mysql/mysql-8.0.6/bin/mysqladmin
user = root
password = root1234
[mysqld3307]
server-id=3307
port=3307
#以下为binlog配置，备灾及从机复制
# binlog的日志文件名字
log-bin=mysql-bin
binlog_format=MIXED
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size = 512m #最大binlog缓存大小
log-error=/Users/zhouzhenyong/mysql-cluster/master/mysqld.log
tmpdir=/Users/zhouzhenyong/mysql-cluster/master
slow_query_log=on
slow_query_log_file =/Users/zhouzhenyong/mysql-cluster/master/mysql-slow.log
long_query_time=1
socket=/Users/zhouzhenyong/mysql-cluster/master/mysql_3307.sock
pid-file=/Users/zhouzhenyong/mysql-cluster/master/mysql.pid
basedir=/Users/zhouzhenyong/mysql-cluster/master
datadir=/Users/zhouzhenyong/mysql-cluster/master/data
[mysqld3308]
server-id=3308
port=3308
log-bin=mysql-bin
log-error=/Users/zhouzhenyong/mysql-cluster/slave1/mysqld.log
tmpdir=/Users/zhouzhenyong/mysql-cluster/slave1
slow_query_log=on
slow_query_log_file =/Users/zhouzhenyong/mysql-cluster/slave1/mysql-slow.log
long_query_time=1
socket=/Users/zhouzhenyong/mysql-cluster/slave1/mysql_3308.sock
pid-file=/Users/zhouzhenyong/mysql-cluster/slave1/mysql.pid
basedir=/Users/zhouzhenyong/mysql-cluster/slave1
datadir=/Users/zhouzhenyong/mysql-cluster/slave1/data
read_only=1
[mysqld]
character_set_server=utf8
5.启动主从实例
mysqld_multi --defaults-file=/usr/local/etc/cluster.conf start
查看状态
mysqld_multi --defaults-file=/usr/local/etc/cluster.conf report
zhouzhenyong@shizi-2 ~/m/master> mysqld_multi --defaults-file=/usr/local/etc/cluster.conf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: 三 5 27 02:11:33 2020
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
6.登录主机，创建从机账号和权限
sudo mysql -S /Users/zhouzhenyong/mysql-cluster/master/mysql_3307.sock
注意：这里的要求输入密码，是我们自己机器的密码，因为root账号，mysql我们设置的无密码登录
给从库授权获取二进制文件权限
create user 'slave'@'%' identified by 'slave1234';
grant replication slave on . to 'slave'@'%';
flush privileges;
7.查看主库节点
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 858 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
8.配置从库
登录
sudo mysql -S /Users/zhouzhenyong/mysql-cluster/slave1/mysql_3308.sock
关闭从库
stop slave;
配置从库同步（同步的位置）
change master to master_host='127.0.0.1',master_port=3307,master_user='slave',master_password='slave1234',master_log_file='mysql-bin.000001',master_log_pos=858;
开启从库
start slave;
检查从库状态
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4946
Relay_Log_File: shizi-2-relay-bin.000002
Relay_Log_Pos: 1059
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4946
Relay_Log_Space: 1270
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3307
Master_UUID: 8d40fc5c-9f7a-11ea-9e31-4cae848a2a31
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
从库状态为如下就可以了
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果不是，则可能有对应的一些问题
9.从库创建只读账号
create user 'reader'@'%' identified by '123456';
grant select on . to 'reader'@'%';
flush privileges;
二、使用
在主库中添加表，以及添加对应的数据，在从库中都会有数据了
三、问题：
1.启动失败
zhouzhenyong@shizi-2 /u/l/etc> mysqld_multi --defaults-file=/usr/local/etc/cluster.conf start
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: 三 5 27 01:26:31 2020
Starting MySQL servers
Installing new database in /Users/zhouzhenyong/mysql-cluster/master/data
FATAL ERROR: Tried to start mysqld under group [mysqld3307],
but no data directory was found or could be created.
data directory used: /Users/zhouzhenyong/mysql-cluster/master/data
这里执行失败，是由于自己本机还有一个mysql实例，然后看了下
which mysql
发现并不是自己配置的这个mysql，而是brew 下载的mysql@5.6，我这里将这个brew 下载的删除了
1.删除流程：
brew uninstall mysql@5.6
brew remove mysql@5.6
2.删除完毕后又遇到mysqld不识别的情况
image.png
通过路径，进入到对应的目录中
然后点击鼠标，点击对应的mysqld，然后会弹出告警框，这里点击打开，然后再重新输入命令就可以识别了cd /usr/local/mysql/mysql-8.0.6/bin
open .
image.png
由于图片没有截图，参考网上的一个，点击打开即可
image.png
2.启动失败
zhouzhenyong@shizi-2 ~/m/master> mysqld_multi --defaults-file=/usr/local/etc/cluster.conf start
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: 三 5 27 02:06:48 2020
Starting MySQL servers
FATAL ERROR: Tried to start mysqld under group [mysqld3307], but no mysqld binary was found.
Please add "mysqld=..." in group [mysqld_multi], or add it to group [mysqld3307] separately.
这里是要求将其中的mysqld配置上，上面其实是配置上了，在参考网上配置中mysqld是没有配置的，这里放开了mysqld的配置为，但是网上的配置是这样，是mysqld_safe，但是这个mysqld_safe还是有问题的，启动会失败
mysqld = /usr/local/mysql/bin/mysqld_safe
最后修改为如下就没问题了，也就是上面自己的这个配置
mysqld = /usr/local/mysql/mysql-8.0.6/bin/mysqld
参考：
mysql官方对于主从的支持
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-master-slave-replication-connection.html
https://blog.csdn.net/qq_21153619/article/details/81529880
主要借鉴
https://blog.csdn.net/andyvera/article/details/93140839
https://www.cnblogs.com/kylinlin/p/5258719.html
https://blog.csdn.net/weixin_43184819/article/details/84000936
