mac 配置mysql 主从

最新推荐文章于 2024-07-09 20:22:26 发布

老柿子

最新推荐文章于 2024-07-09 20:22:26 发布

阅读量705

点赞数

分类专栏： java 教程技术探索文章标签： mysql 主从读写分离

本文链接：https://blog.csdn.net/zhouzhenyong/article/details/106445378

版权

java 同时被 3 个专栏收录

19 篇文章 3 订阅

订阅专栏

教程

12 篇文章 1 订阅

订阅专栏

技术探索

3 篇文章 0 订阅

订阅专栏

一、配置过程

我们这里借鉴网上的文章进行搭建，该网上的帖子里面也有一些问题，然后我们这里记录一下搭建过程以及中间的所有的问题。https://blog.csdn.net/andyvera/article/details/93140839
我们这里采用的主从配置是采用[mysqld_multi]进行主从配置

1.下载

https://dev.mysql.com/downloads/mysql/
下载下面这个即可
解压并拷贝到一个位置

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 © 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不识别的情况

通过路径，进入到对应的目录中

cd /usr/local/mysql/mysql-8.0.6/bin
open .

然后点击鼠标，点击对应的mysqld，然后会弹出告警框，这里点击打开，然后再重新输入命令就可以识别了由于图片没有截图，参考网上的一个，点击打开即可

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

老柿子

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
mac 配置mysql 主从

一、配置过程我们这里借鉴网上的文章进行搭建，该网上的帖子里面也有一些问题，然后我们这里记录一下搭建过程以及中间的所有的问题。https://blog.csdn.net/andyvera/article/details/93140839我们这里采用的主从配置是采用[mysqld_multi]进行主从配置1.下载https://dev.mysql.com/downloads/mysql/下载下面这个即可解压并拷贝到一个位置tar -zxvf mysql-8.0.20-macos10.15-x86
复制链接

扫一扫

专栏目录