centos mysql5.7.24 多主多从集群搭建

待我称王封你为后i 2022-05-12 13:46 174阅读 0赞

**系统**

[root@i-ec0m1585 ~]# uname -a 
    Linux i-ec0m1585 3.10.0-327.4.5.el7.x86_64 #1 SMP Mon Jan 25 22:07:14 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

**主机名称**

<table> 
 <thead> 
  <tr> 
   <th>hostname</th> 
   <th>ip</th> 
   <th>mysql port</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>mysql-master-60</td> 
   <td>10.39.47.60</td> 
   <td>3307</td> 
  </tr> 
  <tr> 
   <td>mysql-master-61</td> 
   <td>10.39.47.61</td> 
   <td>3308</td> 
  </tr> 
  <tr> 
   <td>mysql-slave-62</td> 
   <td>10.39.47.62</td> 
   <td>3309</td> 
  </tr> 
  <tr> 
   <td>mysql-slave-63</td> 
   <td>10.39.47.63</td> 
   <td>3310</td> 
  </tr> 
 </tbody> 
</table>

`用的是ansible安装工具辅助安装`

**host文件如下**

➜  ansible cat hosts                                                                             
    [cache]
    10.39.47.60
    10.39.47.61
    10.39.47.62
    10.39.47.63

**设置静态IP例子，不需要设置固定IP的略过**

vi /etc/sysconfig/network-script/ifcfg-xxx
    IPADDR=10.39.47.60     #静态IP

**增加mysql yum源 以及部署mysql**

ansible -i hosts cache -u root -m shell -a 'cd /opt/;wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm'
    
    ansible -i hosts cache -u root -m shell -a 'cd /opt/;rpm -ivh mysql57-community-release-el7-8.noarch.rpm' 
    
    ansible -i hosts cache -u root -m shell -a 'yum update -y;yum -y install mysql-server'

关闭selinux

sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
    setenforce 0
    systemctl stop firewalld 
    systemctl disable firewalld
    
    mkdir /home/usr/local/mysql-5.7.24/data/3307/
    chown -R /home/usr/local/mysql-5.7.24/data

**查找密码方法**

[root@mysql-slave-63 ~]# grep "password"  /usr/local/mysql-5.7.18/data/3310/error.log 
    2018-10-23T15:29:30.152796Z 1 [Note] A temporary password is generated for root@localhost: 5K:j-*h&Pksu

--------------------

启动mysql

systemctl start mysqld
    systemctl status mysqld

--------------------

## 各个节点的配置 ##

[root@mysql-slave-63 ~]# cat /etc/my.cnf
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [client]
    
    port        = 3310
    
    socket      = /usr/local/mysql-5.7.18/data/3310/mysql.sock
    
    default-character-set=utf8
    
    
    
    [mysqld]
    
    port    = 3310
    
    socket  = /usr/local/mysql-5.7.18/data/3310/mysql.sock
    
    datadir = /usr/local/mysql-5.7.18/data/3310
    
    log-error = /usr/local/mysql-5.7.18/data/3310/error.log
    
    pid-file = /usr/local/mysql-5.7.18/data/3310/mysql.pid
    
    symbolic-links=1
    
    character-set-server=utf8
    
    lower_case_table_names=1
    log-bin=mysql-bin
    relay-log       = /usr/local/mysql-5.7.18/data/3310/mysqld-relay-bin
    server-id=3310
    autocommit = 1

--------------------

[root@mysql-slave-62 ~]# cat /etc/my.cnf
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [client]
    
    port        = 3309
    
    socket      = /usr/local/mysql-5.7.18/data/3309/mysql.sock
    
    default-character-set=utf8
    
    
    
    [mysqld]
    
    port    = 3309
    
    socket  = /usr/local/mysql-5.7.18/data/3309/mysql.sock
    
    datadir = /usr/local/mysql-5.7.18/data/3309
    
    log-error = /usr/local/mysql-5.7.18/data/3309/error.log
    
    pid-file = /usr/local/mysql-5.7.18/data/3309/mysql.pid
    
    symbolic-links=1
    
    character-set-server=utf8
    
    lower_case_table_names=1
    relay-log       = /usr/local/mysql-5.7.18/data/3309/mysqld-relay-bin
    log-bin=mysql-bin
    server-id=3309
    autocommit = 1

--------------------

[root@mysql-master-61 ~]# cat /etc/my.cnf
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [client]
    
    port        = 3308
    
    socket      = /usr/local/mysql-5.7.18/data/3308/mysql.sock
    
    default-character-set=utf8
    
    
    
    [mysqld]
    
    port    = 3308
    
    socket  = /usr/local/mysql-5.7.18/data/3308/mysql.sock
    
    datadir = /usr/local/mysql-5.7.18/data/3308
    
    log-error = /usr/local/mysql-5.7.18/data/3308/error.log
    
    pid-file = /usr/local/mysql-5.7.18/data/3308/mysql.pid
    
    symbolic-links=1
    
    character-set-server=utf8
    
    lower_case_table_names=1
    log-bin=mysql-bin
    server-id=3308
    autocommit = 1
    auto_increment_increment=2
    
    auto_increment_offset=2
    
    log-slave-updates
    
    sync_binlog=1

--------------------

[root@mysql-master-60 ~]# cat /etc/my.cnf
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [client]
    
    port        = 3307
    
    socket      = /usr/local/mysql-5.7.18/data/3307/mysql.sock
    
    default-character-set=utf8
    
     
    
    [mysqld]
    
    port    = 3307
    
    socket  = /usr/local/mysql-5.7.18/data/3307/mysql.sock
    
    datadir = /usr/local/mysql-5.7.18/data/3307
    
    log-error = /usr/local/mysql-5.7.18/data/3307/error.log
    
    pid-file = /usr/local/mysql-5.7.18/data/3307/mysql.pid
    
    symbolic-links=1
    
    character-set-server=utf8
    
    lower_case_table_names=1
    log-bin=mysql-bin
    server-id=3307
    autocommit = 1
    
    auto_increment_increment=2
    
    auto_increment_offset=1
    
    log-slave-updates
    
    sync_binlog=1

--------------------

修改密码

root@mysql-master-60 ~]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 17
    Server version: 5.7.24-log MySQL Community Server (GPL)
    
    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.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

修改密码命令

SET PASSWORD = PASSWORD('enN$#12345');
    alter user root@"localhost" identified by 'xxx';
    flush privileges;

在两个master创建复制用户

grant replication slave on *.* to 'copy'@'%' identified by 'xxx';
     flush privileges;

在master节点上重置master服务的状态

reset master;

在slave节点上

stop slave;
    
    reset slave;
    
    reset master;

在master上查看

如果已经执行，可以略过

SET PASSWORD = PASSWORD('xxx');
    alter user root@"localhost" identified by 'xxx';
    flush privileges;
    grant replication slave on *.* to 'copy'@'%' identified by 'xxx';
    flush privileges;

在Slave上设置Master（相当于是4台都需要设置）

设置从服务器3308、3309，他们的主均为3307，即在3308和3309上执行如下操作

change master to master_host='10.39.47.60',
    
    master_user='copy',
    
    master_password='xxx',
    
    master_port=3307,
    
    master_log_file='mysql-bin.000001',
    
    master_log_pos=154;

设置从服务器3310、3307，他们的主均为3308，即在3310和3307上执行如下操作

change master to master_host='10.39.47.61',
    
    master_user='copy',
    
    master_password='enN$#12345',
    
    master_port=3308,
    
    master_log_file='mysql-bin.000001',
    
    master_log_pos=154;

在所有的节点执行，确保这两个参数处于yes状态`Slave_IO_Running: Yes，Slave_SQL_Running: Yes` 即表示复制功能配置正常  
例如:

start slave;
    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.39.47.61
                      Master_User: copy
                      Master_Port: 3308
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 318
                   Relay_Log_File: couchbase-60-relay-bin.000002
                    Relay_Log_Pos: 320
            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: 318
                  Relay_Log_Space: 534
                  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: 3308
                      Master_UUID: 92c1778a-d6d6-11e8-90a1-525489ddae8b
                 Master_Info_File: /usr/local/mysql-5.7.18/data/3307/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: 
    1 row in set (0.00 sec)

--------------------

**经常用的命令**

stop slave;
    
    reset slave;
    
    reset master;
    show slave status \G

--------------------

遇到的问题

日志

2018-10-24T02:59:03.680461Z 25 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event 'mysql-bin.000001' at 154, the last event read from './mysql-bin.000001' at 318, the last byte read from './mysql-bin.000001' at 318.', Error_code: 1236

解决办法  
[http://blog.51cto.com/461205160/1737721][http_blog.51cto.com_461205160_1737721]  
[https://www.cnblogs.com/cobbliu/p/4311926.html][https_www.cnblogs.com_cobbliu_p_4311926.html]

mysqlbinlog --no-defaults /usr/local/mysql-5.7.18/data/3307/mysql-bin.000001 > test.txt
    
    mysql> stop slave;
    Query OK, 0 rows affected (0.16 sec)
    
    mysql> change master to master_log_file='mysql-bin.000001',master_log_pos=123;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> start slave;

经过测试成功

--------------------

## 高可用搭建 ##

安装mysql-mmm  
新增一台机器10.39.47.64

需要在这60，61，62，63，64台 上都安装 mysql-mmm

通过ansible安装mysql-mmm工具，先装epel-release源

ansible -i hosts cache -u root -m shell -a 'yum install epel-release -y'
     ansible -i hosts cache -u root -m shell -a 'yum update -y' 
      ansible -i hosts cache -u root -m shell -a 'yum install -y mysql-mmm* '

配置mmm代理和监控账号的权限

现在环境已经配置好，我没有配置忽略 mysql库和 user表，所以只要在任意一台主库上执行下面的操作，其他的库就都有这俩账号了。

mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'10.39.47.%' IDENTIFIED  BY 'xxx';
    
    mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'10.39.47.%' IDENTIFIED BY 'xxx';  
        
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges; 
    
    Query OK, 0 rows affected (0.06 sec)

注：60,61,62,63 是一样的  
**查看**

**新建mmm\_common.conf文件**

vi mmm_common.conf
    
    active_master_role      writer
    cluster_interface            eth0
    pid_path                      /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                      /usr/libexec/mysql-mmm/
    replication_user          copy
    replication_password     enN$#12345
    agent_user       mmm_agent      
    agent_password   enN$#12345
        ip      10.39.47.60
        mode    master
        peer    mysql-master-60
    
    
    
        ip      10.39.47.61
        mode    master
        peer    mysql-master-61
    
    
    
    
        ip      10.39.47.62
        mode    slave
        
        ip      10.39.47.63
        mode    slave
    
        hosts   mysql-master-60 mysql-master-61
        ips     10.39.47.65
        mode    exclusive
    
    
    
    
        hosts   db1, db2, db3
        ips      10.0.0.15, 10.0.0.16
        mode    balanced

由于issa不支持VIP高可用未完待续，  
高可用部署可参考  
[http://www.cnblogs.com/gomysql/p/3671896.html][http_www.cnblogs.com_gomysql_p_3671896.html]  
[http://www.cnblogs.com/chenmh/p/5563778.html][http_www.cnblogs.com_chenmh_p_5563778.html]  
[http://blog.51cto.com/lizhenliang/1354576][http_blog.51cto.com_lizhenliang_1354576]  
[http://mysql-mmm.org/doku.php][http_mysql-mmm.org_doku.php]

参考:  
[CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解][CentOS 7 _yum_MySQL5.7.20 _]  
[linux-installation-yum-repo 5.7][]  
[Mysql集群讲解(五) 多主多从环境搭建][Mysql_]  
[mysql 多主多从][mysql]  
[Mysql集群讲解(四) 一主多从环境搭建][Mysql_ 1]  
[de][http_blog.51cto.com_461205160_1737721]

[Linux下的MySQL主主复制和Mysql-MMM实现(Mysql双主多从高可用)][Linux_MySQL_Mysql-MMM_Mysql]

[http_blog.51cto.com_461205160_1737721]: http://blog.51cto.com/461205160/1737721
[https_www.cnblogs.com_cobbliu_p_4311926.html]: https://www.cnblogs.com/cobbliu/p/4311926.html
[http_www.cnblogs.com_gomysql_p_3671896.html]: http://www.cnblogs.com/gomysql/p/3671896.html
[http_www.cnblogs.com_chenmh_p_5563778.html]: http://www.cnblogs.com/chenmh/p/5563778.html
[http_blog.51cto.com_lizhenliang_1354576]: http://blog.51cto.com/lizhenliang/1354576
[http_mysql-mmm.org_doku.php]: http://mysql-mmm.org/doku.php
[CentOS 7 _yum_MySQL5.7.20 _]: https://blog.csdn.net/z13615480737/article/details/78906598
[linux-installation-yum-repo 5.7]: https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
[Mysql_]: https://www.cnblogs.com/liudongdong666666/p/7818240.html
[mysql]: https://blog.csdn.net/winsonyuan/article/details/44056751?utm_source=blogxgwz8
[Mysql_ 1]: https://www.cnblogs.com/liudongdong666666/p/7816659.html
[Linux_MySQL_Mysql-MMM_Mysql]: https://blog.csdn.net/chengxuyuanyonghu/article/details/71136172