CentOS 6.9 搭建mysql 主从服务器并读写分离

一、环境

    virtualBox centOS6.9 mysql5.6.38
     这里我用的是virtualBox虚拟机搭建的三台服务器，这里的mysql版本尽量一致，如果不一致那么从服务器版本不能高于主服务器版本
    网上经验：
              线上的项目导出数据的时候，保证主库上锁，等slave导入、slave start之后在解锁； slave重启的时候也一样，重启之前，主库上锁，重启完毕，解锁
              主库上锁 flushtables withread lock 主库解锁 unlocktables

二、配置主数据库

    ①编辑配置文件 vim /etc/my.cnf 

    log-bin=mysql-bin #启用二进制日志[主库必须启用,主服务器发送变更记录到从服务器依赖的时二进制日志]
    server-id=1 #这是数据库ID，此ID是唯一的，主库默认为1，其他从库以此ID进行递增，ID值不能重复，否则会同步出错
    binlog-do-db=test # 需要同步的数据库；
    # binlog-do-db = slaveDB  #需要同步的数据库，如果需要同步多个数据库；则继续添加此项。
    # binlog-do-db = slaveDB1
    # binlog-do-db = slaveDB2
    binlog-ignore-db = mysql # 不需要同步的数据库；
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1
    # 提示1：如果你不配置server-id或者配置值为0，那么主服务器将拒绝所有从服务器的连接。
    # 提示2：在使用InnoDB的事务复制，为了尽可能持久和数据一致，你应该在my.cnf里配置innodb_flush_log_at_trx_commit=1 和sync_binlog=1；
    # 提示3：确保主服务器里的skip-networking选项未启用，如果网络被禁用，你的从服务器将不能与主服务器通信并且复制失败。

     ② 重启mysql使配置生效
        service mysql restart;
     ③ 进入mysql终端

        新建复制账号
      CREATE USER 'slaveUser'@ '192.168.30.%' IDENTIFIED BY'123456';
        赋予权限
        grant replication slave on *.* to'slaveUser'@'192.168.30.%' identified by'123456'       
这里我的用户是slaveUser，因为后面两台服务器都在30网段，所以我这里新建用户也限制在30网段连接，如果不想限制可以写成@'%'形式

    ④查看master状态

         show master status;

        记录file 和 position的值，从服务器配置用到。这个时候不要去动主数据库，会影响position的值

三、配置从服务器

   ①编辑配置文件

    vim /etc/my.cnf 
    server-id=2
    replicate-do-db=test # 需要同步的数据库
    replicate-ignore-db=mysql # 需要忽略的数据库
    read-only
    # 提示1：如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
    # 提示2：一般你不需要在从服务器上启用二进制日志，如果你在从服务器上启用二进制日志，那你可用它来做数据备份和崩溃恢复，或者做更复杂的事情(比如这个从服务器用来当作其它从服务器的主服务器)。

    ②重启mysql使配置生效

    service mysql restart;

    ③进入mysql终端修改slave参数
        停止slave

            stop slave;
        修改slave

      CHANGE MASTER TO
      MASTER_HOST='192.168.30.73',
      MASTER_USER='slaveUser',
      MASTER_PASSWORD='123456',
      MASTER_LOG_FILE='mysql-bin.000007',
      MASTER_LOG_POS=120;

这里的MASTER_LOG_FILE和MASTER_LOG_POS就是主服务器的file 和 position的值
            启用slave

        start slave;
    ④查看是否生效
        show slave status\G;
        
        这里如果两项都是Yes代表设置成功
    ⑤验证
        在主服务器需要同步的数据库里新建表和数据，从库会自动复制
如果需要新增第二台从库，请重复从库配置

四、错误排查

    这里主要是后面可能双yes中第一个出不来上面yes出不来主要是三种原因

        ①用户名或密码不对，这里仔细核对复制账号的用户名或密码

        ②POS不对，这里可以返回去看看主库master的状态，有主库重启后POS会改变

        ③网络不通,这里特别需要注意一下，首先必须要主从服务器可以ping通，第二除了开放tcp3306端口外，还需要开放udp3306端口，我这里给出开放udp3306端口的命令

iptables -A INPUT -p udp -m udp --dport 3306 -j ACCEPT