Mysql 主从配置

操作系统 mysql版本 IP 主从关系 简称
Win8.1-64位 5.7.14 192.168.50.87 主【Master】 Master
虚拟机【Centos7 64位】 5.7.18 192.168.227.128 从【Slave】 Slave

Master 配置

  1. 配置 my.ini, 配置后需重启

    1
    2
    3
    4
    5
    6
    [mysqld]
    server-id=1 #标识唯一的数据库,这里设置为1,在设置从库的时候就需要设置为其他值
    log-bin=mysql-bin #打开日志
    binlog-ignore-db=mysql #指定不需要同步的数据库[可以添加多条binlog-ignore-db命令]
    binlog-do-db=test #指定需要同步的数据库[可以添加多条binlog-do-db命令]
    #expire_log_days=1 #自动清理1天前的log文件
  2. 创建从库权限账号 test, 密码 test,设置 FileREPLICATION SALVE 权限, 并只允许来源地址为 192.168.227.128

    1
    2
    GRANT FILE,REPLICATION SLAVE ON *.* TO 'test'@'192.168.50.82' IDENTIFIED BY 'test';
    FLUSH PRIVILEGES;
  3. 查看主库信息,这里的 File, Position 在配置 Slave 时会用到

    1
    2
    3
    4
    5
    6
    7
    mysql> show master status;
    +------------------+----------+---------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+---------------+------------------+-------------------+
    | mysql-bin.000001 | 154 | test | mysql | |
    +------------------+----------+---------------+------------------+-------------------+
    1 row in set (0.20 sec)
  1. Slave 连接 Master 的 Mysql,测试是否能连接

    1
    2
    3
    4
    5
    mysql -h 192.168.50.87 -u test -p
    #若 虚拟机 的网络连接方式为 NAT 模式【桥接模式不需要】,则需要在 Master 运行如下命令:
    UPDATE `user` SET Host='%' WHERE user = 'test';
    FLUSH PRIVILEGES;
    #若还是无法连接,配置主机的防火墙->高级设置->入站规则->端口->TCP、特定本地端口、3306
  2. 主库相关命令:

    1
    2
    3
    4
    5
    6
    #查看主机二进制Log文件列表
    show binary logs;
    #提交日志(生成新的二进制日志文件)
    flush logs;
    #清除之前所有的二进制Log文件,并且新的Log文件后缀从 000001 开始
    reset master;

Slave 配置

  1. 配置 /etc/my.cnf, 配置后需重启

    1
    2
    3
    4
    5
    6
    7
    8
    [mysqld]
    server-id=2
    log-bin=mysql-bin
    replicate-ignore-db=mysql #指定不需要从主机同步的数据库
    replicate-do-db=test #指定需要从主机同步的数据库
    log-slave-updates #从库从主库复制的数据会写入log-bin日志文件,若未开启,则不会写入log-bin日志文件
    slave-skip-errors=all #跳过错误
    slave-net-timeout=60 #若60秒主库没有推送数据,则会重连并且追赶这段时间主库的数据
  2. 运行如下命令: [master_log_file 对应配置 Master 时的File, master_log_pos 对应配置 Master 时的 Position]

    1
    2
    3
    stop slave;
    change master to master_host='192.168.50.87',master_user='test',master_password='test',master_log_file='mysql-bin.000001',master_log_pos=617;
    start slave;
  3. 查看从库信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.50.87
    Master_User: test
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000015
    Read_Master_Log_Pos: 154
    Relay_Log_File: localhost-relay-bin.000002
    Relay_Log_Pos: 286
    Relay_Master_Log_File: mysql-bin.000015
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: test
    Replicate_Ignore_DB: mysql
    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: 154
    Relay_Log_Space: 463
    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: 1
    Master_UUID: 3e9c0cca-984e-11e7-82aa-1c1b0d252d4a
    Master_Info_File: /alidata/server/mysql-5.6.21/data/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    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
    1 row in set (0.00 sec)
  4. 从库相关命令:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    #在从机上查询主机二进制Log文件列表
    show master logs;
    #如果从库同步出错,解决方法如下:
    #第一种
    stop slave;
    change master to master_host='192.168.50.87',master_user='test',master_password='test',master_log_file='mysql-bin.000001',master_log_pos=617;
    start slave;
    #第二种
    stop slave;
    reset slave;
    start slave;

0%