centos7中建立配置galera cluster for mysql集群

àì夳堔傛蜴生んèń 2022-06-05 07:56 212阅读 0赞

galera安装文件我们可以在官网下载：[http://galeracluster.com/products/][http_galeracluster.com_products] 如果之前服务器上装过mysql需要先卸载，先检测一下有没有：`yum list installed | grep mysql` 卸载： `yum -y remove mysql-libs.x86_64`。  
然后将下载下来的文件上传之服务器，就可以安装了，和mysql一样也是有依赖关系的，安装顺序不要乱，先看一下文件（由于我的系统是刚安装的所以会缺少些文件）：

[root@testone ~]# ls
    galera-3-25.3.22-2.el7.x86_64.rpm            mysql-wsrep-client-5.7-5.7.20-25.13.el7.x86_64.rpm  mysql-wsrep-devel-5.7-5.7.20-25.13.el7.x86_64.rpm  mysql-wsrep-libs-compat-5.7-5.7.20-25.13.el7.x86_64.rpm  mysql-wsrep-test-5.7-5.7.20-25.13.el7.x86_64.rpm
    mysql-wsrep-5.7-5.7.20-25.13.el7.x86_64.rpm  mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64.rpm  mysql-wsrep-libs-5.7-5.7.20-25.13.el7.x86_64.rpm   mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64.rpm

现在开始安装：

[root@testone ~]# rpm -ivh mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
        file /usr/share/mysql/czech/errmsg.sys from install of mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64

安装的文件和系统现有的有冲突，搜到这个有冲突的库，卸载掉然后再安装：

[root@testone ~]# rpm -qa| grep mariadb
    mariadb-libs-5.5.56-2.el7.x86_64
    [root@testone ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
    [root@testone ~]# rpm -ivh mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-common-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-wsrep-common-5.7-5.7.20-25.################################# [100%]

可以看到，这次安装成功了，继续：

[root@testone ~]# rpm -ivh mysql-wsrep-libs-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-libs-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-wsrep-libs-5.7-5.7.20-25.13################################# [100%]

[root@testone ~]# rpm -ivh mysql-wsrep-client-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-client-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-wsrep-client-5.7-5.7.20-25.################################# [100%]

[root@testone ~]# rpm -ivh mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    错误：依赖检测失败：
        /usr/bin/perl 被 mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64 需要
        lsof 被 mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64 需要
        perl(Getopt::Long) 被 mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64 需要
        perl(strict) 被 mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64 需要
        socat 被 mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64 需要

这次又失败了，少些依赖文件，我们用yum安装一下：`yum -y install lsof && yum -y install socat && yum -y install perl` 也可以一次合起来写，将这三个文件安装好后就可以继续安装了：

[root@testone ~]# rpm -ivh mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-server-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-wsrep-server-5.7-5.7.20-25.################################# [100%]

[root@testone ~]# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.20-25.13.el7.x86_64.rpm 
    警告：mysql-wsrep-libs-compat-5.7-5.7.20-25.13.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:mysql-wsrep-libs-compat-5.7-5.7.2################################# [100%]

[root@testone ~]# rpm -ivh galera-3-25.3.22-2.el7.x86_64.rpm 
    警告：galera-3-25.3.22-2.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    错误：依赖检测失败：
        libboost_program_options.so.1.53.0()(64bit) 被 galera-3-25.3.22-2.el7.x86_64 需要

安装最后一个时，又失败了还是少依赖文件，yum安装一下：`yum -y install boost-devel` 然后再次安装：

[root@testone ~]# rpm -ivh galera-3-25.3.22-2.el7.x86_64.rpm 
    警告：galera-3-25.3.22-2.el7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID bc19ddba: NOKEY
    准备中...                          ################################# [100%]
    正在升级/安装...
       1:galera-3-25.3.22-2.el7           ################################# [100%]

好，安装完毕！接下来我们就开始编写配置文件了：`vi /etc/my.cnf` 将里面的内容全部注释掉，然后输入： `!includedir /etc/my.cnf.d/` 。接下来创建wsrep.cnf文件： `vi /etc/my.cnf.d/wsrep.cnf` 然后输入根据自己的需求输入配置信息：

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
    # *** default location during install, and will be replaced if you
    # *** upgrade to a newer version of MySQL.
    [mysqld]
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    # These are commonly set, remove the # and set as required.
    # basedir = .....
    # datadir = .....
    # port = .....
    # server_id = .....
    # socket = .....
    log_timestamps=SYSTEM                                  #这个是我们自己加的，防止日志时间和系统时间不一样
    port=3306
    server_id=11                                           #MySQL服务器的ID，必须是唯一的，集群各个节点也不同
    explicit_defaults_for_timestamp=true
    basedir=/usr/local/mysql
    datadir=/usr/local/mysql/data                     
    socket=/usr/local/mysql/data/mysql.sock
    pid_file=/run/mysqld/mysqld.pid
    log_error=/var/log/mysql.error
    wsrep_cluster_name='cs_cluster'                       #galera集群的名字，必须是统一的
    wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so   #wsrep提供者,必须配置(.so文件的路径在哪，就配置成哪,一般安装好后都是在这个目录下)
    wsrep_node_name = node1                               #wsrep节点的ID，必须是唯一的，集群各个节点也不同
    wsrep_cluster_address=gcomm://192.168.56.11,192.168.56.12,192.168.56.13 #集群中的其他节点地址，可以使用主机名或IP
    wsrep_node_address='192.168.56.11'                 #本机节点地址，可以使用主机名或IP
    wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.56.11:4567"   #指定wsrep启动端口号,4567为默认值
    wsrep_sst_donor='node1,node2,node3'                    #一个逗号分割的节点串作为状态转移源，比如wsrep_sst_donor=node1,node2,node3 如果node1可用，用node2,如果node2不可用，用node3，最后的逗号表明让提供商自己选择一个最优的。
    wsrep_sst_method=rsync                              #集群同步方式，我的系统没有可以用yum安装一下这个远程连接 yum -y install  rsync
    wsrep_sst_auth=test:123456                         #集群同步的用户名密码
    slow_query_log=on
    [client]
    default-character-set=utf8
    socket=/usr/local/mysql/data/mysql.sock
    [mysql]
    default-character-set=utf8
    socket=/usr/local/mysql/data/mysql.sock
    [mysqldump]
    max_allowed_packet = 512M
    [mysqld_safe]
    malloc-lib=/usr/lib64/libjemalloc.so.1             #这个我的系统里也没有可以用yum安装一下  yum -y install jemalloc  如果获取不到的话，下载一个数据源 rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

编辑好配置文件就可以初始化数据库了：

[root@testone etc]# mysqld --initialize --user=mysql
    mysqld: Can't create directory '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
    2017-11-19T09:52:36.312857Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
    2017-11-19T09:52:36.314273Z 0 [ERROR] Aborting

出现了两个错误，第一个是无法创建我们制定的文件目录是权限问题`mkdir /usr/local/mysql && chown mysql /usr/local/mysql && chgrp mysql /usr/local/mysql` 这样就好了，创建一个目录更改一下权限。第二个是在/usr/local/mysql/share/mysql/errmsg.sys目录下找不到文件，这和我们之前配置文件中basedir=/usr/local/mysql有关系，因为basedir指定是数据库的bin，lib之类的文件，我们rpm安装，这些文件默认的路径是在/usr/share/mysql下面，所以basedir这个参数可以不指定也可以，系统会自己找，或者直接指定到/usr/share/mysql/这个目录下面。所以可以将basedir注释掉或者`basedir=/usr/` 再次初始化：

[root@testone local]# mysqld --initialize --user=mysql
    2017-11-19T10:53:51.701879Z 0 [ERROR] Could not open file '/var/log/mysql.error' for error logging: Permission denied
    2017-11-19T10:53:51.701980Z 0 [ERROR] Aborting

还是权限问题，创建一个更改下属组，然后就可以了`touch /var/log/mysql.error && chown mysql /var/log/mysql.error && chgrp mysql /var/log/mysql.error`。初始化后会有一个随机密码在错误日志中我们找一下：

[root@testone log]# grep 'temporary password' /var/log/mysql.error
    2017-11-19T10:15:53.600562Z 1 [Note] A temporary password is generated for root@localhost: pq11aelad:Ho

然后就启动数据库了，我们先启动第一个节点：

/usr/sbin/mysqld --wsrep-new-cluster --user=mysql &

然后输入`systemctl status mysqld` 发现服务有启动成功，看下报错日志：

2017-11-23T10:56:39.704994+08:00 0 [Warning] Can't create test file /usr/local/mysql/data/testone.lower-test 2017-11-23T10:56:39.705308+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20-log) starting as process 8784 ... 2017-11-23T10:56:39.707338+08:00 0 [Warning] Can't create test file /usr/local/mysql/data/testone.lower-test 2017-11-23T10:56:39.707363+08:00 0 [Warning] Can't create test file /usr/local/mysql/data/testone.lower-test 2017-11-23T10:56:39.707631+08:00 0 [Note] WSREP: Read nil XID from storage engines, skipping position init 2017-11-23T10:56:39.707641+08:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera-3/libgalera_smm.so' 2017-11-23T10:56:39.712123+08:00 0 [Note] WSREP: wsrep_load(): Galera 3.22(r4d7d231) by Codership Oy <info@codership.com> loaded successfully. 2017-11-23T10:56:39.712148+08:00 0 [Note] WSREP: CRC-32C: using hardware acceleration. 2017-11-23T10:56:39.712457+08:00 0 [ERROR] WSREP: Could not open state file for writing: '/usr/local/mysql/data//grastate.dat'. Check permissions and/or disk space.: 13 (Permission denied) at galera/src/saved_state.cpp:SavedState():51 2017-11-23T10:56:39.712467+08:00 0 [ERROR] WSREP: wsrep::init() failed: 7, must shutdown 2017-11-23T10:56:39.712470+08:00 0 [ERROR] Aborting 2017-11-23T10:56:39.712484+08:00 0 [Note] Binlog end 2017-11-23T10:56:39.712536+08:00 0 [Note] /usr/sbin/mysqld: Shutdown complete

再网上查了一下这和selinux有关系，实现环境下我们先关闭它还有防火墙`setenforce 0 && systemctl stop firewalld`再重新输入命令启动一下，发现还是没成功，再看下日志：

2017-11-23T11:25:54.083657+08:00 0 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.50749S), skipping check
    2017-11-23T11:26:23.629382+08:00 0 [Note] WSREP: view((empty))
    2017-11-23T11:26:23.629665+08:00 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
         at gcomm/src/pc.cpp:connect():158
    2017-11-23T11:26:23.629698+08:00 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
    2017-11-23T11:26:23.630419+08:00 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'cs_cluster' at 'gcomm://192.168.56.12,192.168.56.13': -110 (Connection timed out)
    2017-11-23T11:26:23.630474+08:00 0 [ERROR] WSREP: gcs connect failed: Connection timed out
    2017-11-23T11:26:23.630490+08:00 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.56.12,192.168.56.13) failed: 7
    2017-11-23T11:26:23.630498+08:00 0 [ERROR] Aborting
    
    2017-11-23T11:26:23.630514+08:00 0 [Note] Giving 0 client threads a chance to die gracefully
    2017-11-23T11:26:23.630529+08:00 0 [Note] WSREP: Service disconnected.
    2017-11-23T11:26:24.630860+08:00 0 [Note] WSREP: Some threads may fail to exit.
    2017-11-23T11:26:24.630952+08:00 0 [Note] Binlog end
    2017-11-23T11:26:24.631085+08:00 0 [Note] /usr/sbin/mysqld: Shutdown complete

又去网上搜索了一下，说要将第一个启动节点的配置文件中的`wsrep_cluster_address=gcomm://192.168.56.11,192.168.56.12,192.168.56.13` 空着。照做后重新启动，等了几秒钟，看下状态：

[root@testone log]# systemctl status mysqld
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: active (running) since 四 2017-11-23 11:49:51 CST; 1min 34s ago
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
      Process: 16855 ExecStartPost=/usr/bin/mysqld_pre_systemd --post (code=exited, status=0/SUCCESS)
      Process: 16818 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS $MYSQLD_RECOVER_START (code=exited, status=0/SUCCESS)
      Process: 16765 ExecStartPre=/usr/bin/mysqld_pre_systemd --pre (code=exited, status=0/SUCCESS)
     Main PID: 16821 (mysqld)
       CGroup: /system.slice/mysqld.service
               └─16821 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
    
    11月 23 11:49:45 testone systemd[1]: mysqld.service holdoff time over, scheduling restart.
    11月 23 11:49:45 testone systemd[1]: Starting MySQL Server...
    11月 23 11:49:51 testone systemd[1]: Started MySQL Server.

看样子是起来了，根据日志密码进入数据库后修改密码，添加同步数据的账户，也就是配置文件中我们设置的，更改密码后，我的节点二和三也变了，好像是同步过去的，不过咱们还需要在创建一下，要不然节点一挂掉后，其他用户无法使用了。再看一下节点状态：

[root@testone log]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.20-log
    
    Copyright (c) 2000, 2017, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY '666666';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show status like "wsrep%";
    +------------------------------+--------------------------------------+
    | Variable_name                | Value                                |
    +------------------------------+--------------------------------------+
    | wsrep_local_state_uuid | 5a2ba584-d001-11e7-ae65-ea5a710753c5 |
    | wsrep_protocol_version | 7                                    |
    | wsrep_last_committed | 3                                    |
    | wsrep_replicated | 3                                    |
    | wsrep_replicated_bytes | 701                                  |
    | wsrep_repl_keys | 3                                    |
    | wsrep_repl_keys_bytes | 93                                   |
    | wsrep_repl_data_bytes | 416                                  |
    | wsrep_repl_other_bytes | 0                                    |
    | wsrep_received | 2                                    |
    | wsrep_received_bytes | 142                                  |
    | wsrep_local_commits | 0                                    |
    | wsrep_local_cert_failures | 0                                    |
    | wsrep_local_replays | 0                                    |
    | wsrep_local_send_queue | 0                                    |
    | wsrep_local_send_queue_max | 1                                    |
    | wsrep_local_send_queue_min | 0                                    |
    | wsrep_local_send_queue_avg | 0.000000                             |
    | wsrep_local_recv_queue | 0                                    |
    | wsrep_local_recv_queue_max | 2                                    |
    | wsrep_local_recv_queue_min | 0                                    |
    | wsrep_local_recv_queue_avg | 0.500000                             |
    | wsrep_local_cached_downto | 1                                    |
    | wsrep_flow_control_paused_ns | 0                                    |
    | wsrep_flow_control_paused | 0.000000                             |
    | wsrep_flow_control_sent | 0                                    |
    | wsrep_flow_control_recv | 0                                    |
    | wsrep_cert_deps_distance | 1.000000                             |
    | wsrep_apply_oooe | 0.000000                             |
    | wsrep_apply_oool | 0.000000                             |
    | wsrep_apply_window | 1.000000                             |
    | wsrep_commit_oooe | 0.000000                             |
    | wsrep_commit_oool | 0.000000                             |
    | wsrep_commit_window | 1.000000                             |
    | wsrep_local_state | 4                                    |
    | wsrep_local_state_comment | Synced                               |
    | wsrep_cert_index_size | 1                                    |
    | wsrep_causal_reads | 0                                    |
    | wsrep_cert_interval | 0.000000                             |
    | wsrep_incoming_addresses | 192.168.56.11:3306                   |
    | wsrep_desync_count | 0                                    |
    | wsrep_evs_delayed |                                      |
    | wsrep_evs_evict_list |                                      |
    | wsrep_evs_repl_latency | 0/0/0/0/0                            |
    | wsrep_evs_state | OPERATIONAL                          |
    | wsrep_gcomm_uuid | 5a2b5de1-d001-11e7-bbad-e6b3cf84d92e |
    | wsrep_cluster_conf_id | 1                                    |
    | wsrep_cluster_size | 1                                    |
    | wsrep_cluster_state_uuid | 5a2ba584-d001-11e7-ae65-ea5a710753c5 |
    | wsrep_cluster_status | Primary                              |
    | wsrep_connected | ON                                   |
    | wsrep_local_bf_aborts | 0                                    |
    | wsrep_local_index | 0                                    |
    | wsrep_provider_name | Galera                               |
    | wsrep_provider_vendor | Codership Oy <info@codership.com>    |
    | wsrep_provider_version | 3.22(r4d7d231)                       |
    | wsrep_ready | ON                                   |
    +------------------------------+--------------------------------------+
    57 rows in set (0.00 sec)
    
    mysql> quit
    Bye

成功，再配置启动其他节点，再查看状态就会发现节点个数增加啦！如果在生产环境下，我们需要启用防火墙，那就需要开放一些端口：

sudo firewall-cmd --add-port=3306/tcp --permanent
    sudo firewall-cmd --add-port=4567/tcp --permanent
    sudo firewall-cmd --add-port=4567/udp --permanent
    sudo firewall-cmd --add-port=4568/tcp --permanent
    sudo firewall-cmd --add-port=4444/tcp --permanent

[http_galeracluster.com_products]: http://galeracluster.com/products/