Centos7搭建MySQL集群

悠悠 2022-04-03 08:14 400阅读 0赞

软件包下载地址：http://galeracluster.com/downloads/

安装前需卸载原有mysql，删除相关文件夹；卸载Postfix,这个可能跟MySQL配置有冲突: yum remove postfix –y  
先检测一下有没有：`yum list installed | grep mysql` 卸载： `yum -y remove mysql-libs.x86_64`

卸载系统自带的mariadb

[root@node0 ~]# rpm -qa| grep mariadb
    mariadb-libs-5.5.41-2.el7_0.x86_64
    [root@node0 ~]# rpm -e --nodeps mariadb-libs-5.5.41-2.el7_0.x86_64

建议先使用[阿里云][Link 1] yum 源安装基础依赖

离线情况下看这里→【如果你在离线状态下，建议先配置yum源的离线安装包，然后依次安装这些软件，详情请看我的另一篇博客：参考地址：[CentOS7本地yum源设置（断网情况下轻松安装各种依赖包）亲测有效！][CentOS7_yum]】

yum -y install gcc gcc-c++ openssl openssl-devel lsof socat perl boost-devel rsync jemalloc libaio libaio-devel

修改主机名：vi /etc/hosts   在打开的hosts文件中，添加修改主机名为：

192.168.190.132       node0

192.168.190.133       node1

192.168.190.134       node2

注意：集群中的每台主机都需要修改！

`1、所需安装包如下：`

[root@node0 ~]# ll
    -rw-r--r--  1 root root  12867764 12月 21 10:41 galera-3-25.3.25-2.el7.x86_64.rpm
    -rw-r--r--  1 root root     44852 12月 21 10:41 mysql-wsrep-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root  25405632 12月 21 10:41 mysql-wsrep-client-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root    283836 12月 21 10:41 mysql-wsrep-common-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root   3908032 12月 21 10:41 mysql-wsrep-devel-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root   2278988 12月 21 10:41 mysql-wsrep-libs-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root   2148744 12月 21 10:41 mysql-wsrep-libs-compat-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root 143030144 12月 21 10:41 mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64.rpm
    -rw-r--r--  1 root root  26199024 12月 21 10:41 mysql-wsrep-test-5.7-5.7.24-25.16.el7.x86_64.rpm

开始安装（安装过程中，若出现提示需要安装的依赖，就先把所需依赖安装了，然后继续安装软件）

[root@node0 ~]# rpm -ivh mysql-wsrep-common-5.7-5.7.24-25.16.el7.x86_64.rpm 
    
    [root@node0 ~]# rpm -ivh mysql-wsrep-libs-5.7-5.7.24-25.16.el7.x86_64.rpm 
    
    [root@node0 ~]# rpm -ivh mysql-wsrep-client-5.7-5.7.24-25.16.el7.x86_64.rpm

安装到mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86\_64.rpm时发需要依赖，用yum -y install lsof && yum -y install socat && yum -y install perl && yum -y install net-tools安装下依赖，然后继续安装软件包

离线情况下看这里→【如果是离线状态，请自行下载所需rpm依赖安装包，然后离线rpm安装】

[root@node0 ~]# rpm -ivh mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64.rpm 
    警告：mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64.rpm: 头V4 RSA/SHA512 Signature, 密钥 ID bc19ddba: NOKEY
    错误：依赖检测失败：
    	lsof 被 mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64 需要
    	net-tools 被 mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64 需要
    	socat 被 mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86_64 需要
    
    [root@node0 ~]# yum -y install lsof && yum -y install socat && yum -y install perl && yum -y install net-tools

依赖被安装以后，安装mysql-wsrep-server-5.7-5.7.24-25.16.el7.x86\_64.rpm成功了；继续安装···

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

[root@node0 ~]# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.24-25.16.el7.x86_64.rpm
    [root@node0 ~]# rpm -ivh mysql-wsrep-devel-5.7-5.7.24-25.16.el7.x86_64.rpm
    [root@node0 ~]# rpm -ivh mysql-wsrep-5.7-5.7.24-25.16.el7.x86_64.rpm
    [root@node0 ~]# rpm -ivh galera-3-25.3.25-2.el7.x86_64.rpm

好，安装完毕！接下来我们就开始编写配置文件了：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/share/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.190.133,192.168.190.134（第一次启动前把地址都注释掉，之后再启动节点时再次添加）集群中其他节点地址，可以使用主机名或IP
    wsrep_node_address='192.168.190.132'   #本机节点地址，可以使用主机名或IP
    wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.190.132: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

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

[root@node0 ~]# mysqld --initialize  --user=mysql
    mysqld: Can't create directory '/usr/local/mysql/data/' (Errcode: 2 - No such file or directory)
    2018-12-21T14:11:27.076527+08:00 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.
    2018-12-21T14:11:27.082187+08:00 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@node0 ~]# mysqld --initialize  --user=mysql
    2018-12-21T14:15:16.922554+08:00 0 [ERROR] Could not open file '/var/log/mysql.error' for error logging: Permission denied
    2018-12-21T14:15:16.922696+08:00 0 [ERROR] Aborting

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

[root@node0 ~]# touch /var/log/mysql.error && chown mysql /var/log/mysql.error && chgrp mysql /var/log/mysql.error
    [root@node0 ~]# mysqld --initialize  --user=mysql
    [root@node0 ~]# grep 'temporary password' /var/log/mysql.error
    2018-12-21T14:17:34.557895+08:00 1 [Note] A temporary password is generated for root@localhost: W#_jrIo<U9tG

然后就启动数据库了，我们先启动第一个节点，并查看数据库启动状态，如果数据库启动失败，看是否防火墙没有关闭等问题，可以事先执行`setenforce 0 && systemctl stop firewalld关闭防火墙，`再重新输入命令启动一下。

[root@node0 ~]# /usr/sbin/mysqld --wsrep-new-cluster --user=mysql &
    [1] 6660
    [root@node0 ~]# systemctl status mysqld
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: inactive (dead)
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
    [1]+  退出 1                /usr/sbin/mysqld --wsrep-new-cluster --user=mysql

数据库没有启动成功，将vi /etc/my.cnf.d/wsrep.cnf 第一个启动节点的配置文件中的`wsrep_cluster_address=gcomm:// 设置为这样。并且使用数据库启动命令：service mysqld start` 再次查看数据库启动状态：

如果启动不成功，再使用 `/usr/bin/mysqld_bootstrap` 进行启动试试，如果出线权限问题，自行更改权限

[root@node0 sbin]# systemctl status mysqld
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: activating (start-pre) since 五 2018-12-21 15:14:03 CST; 20s ago
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
      Control: 7474 (mysqld_pre_syst)
       CGroup: /system.slice/mysqld.service
               └─control
                 ├─7474 /bin/bash /usr/bin/mysqld_pre_systemd --pre
                 └─7491 /usr/sbin/mysqld --datadir=/usr/local/mysql/data --user=mysql --wsrep_recover
    
    12月 21 15:14:03 node0 systemd[1]: mysqld.service holdoff time over, scheduling restart.
    12月 21 15:14:03 node0 systemd[1]: Starting MySQL Server...

数据库会出现如下提示，根据失败提示查看数据库状态

[root@localhost ~]# service mysqld start
    Redirecting to /bin/systemctl start mysqld.service
    Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@localhost ~]# systemctl status mysqld.service
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: activating (start-pre) since 二 2018-12-25 10:32:22 CST; 13s ago
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
      Control: 3808 (mysqld_pre_syst)
       CGroup: /system.slice/mysqld.service
               └─control
                 ├─3808 /bin/bash /usr/bin/mysqld_pre_systemd --pre
                 └─3825 /usr/sbin/mysqld --datadir=/usr/local/mysql/data --user=mysql --wsrep_recover
    
    12月 25 10:32:22 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling rest
    12月 25 10:32:22 localhost.localdomain systemd[1]: Starting MySQL Server...
    [root@localhost ~]# journalctl -xe

# --------------以下是 journalctl -xe 最后几行内容---------------------------------
    12月 25 10:32:21 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exit
    12月 25 10:32:21 localhost.localdomain systemd[1]: Failed to start MySQL Server.
    -- Subject: Unit mysqld.service has failed
    -- Defined-By: systemd
    -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
    -- 
    -- Unit mysqld.service has failed.
    -- 
    -- The result is failed.
    12月 25 10:32:21 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
    12月 25 10:32:21 localhost.localdomain systemd[1]: mysqld.service failed.
    12月 25 10:32:22 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling rest
    12月 25 10:32:22 localhost.localdomain systemd[1]: Starting MySQL Server...
    -- Subject: Unit mysqld.service has begun start-up
    -- Defined-By: systemd
    -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
    -- 
    -- Unit mysqld.service has begun starting up.

最后会发现在journalctl -xe记录记录中，数据库实际是启动成功了。然后咱们再次运行systemctl status mysqld，启动成功

[root@localhost ~]# systemctl status mysqld
    ● mysqld.service - MySQL Server
       Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
       Active: activating (start-pre) since 二 2018-12-25 10:34:04 CST; 1min 1s ago
         Docs: man:mysqld(8)
               http://dev.mysql.com/doc/refman/en/using-systemd.html
      Control: 3848 (mysqld_pre_syst)
       CGroup: /system.slice/mysqld.service
               └─control
                 ├─3848 /bin/bash /usr/bin/mysqld_pre_systemd --pre
                 └─3865 /usr/sbin/mysqld --datadir=/usr/local/mysql/data --user=mysql --wsrep_recover...
    
    12月 25 10:34:04 localhost.localdomain systemd[1]: mysqld.service holdoff time over, schedulin...t.
    12月 25 10:34:04 localhost.localdomain systemd[1]: Starting MySQL Server...
    Hint: Some lines were ellipsized, use -l to show in full.

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

[root@localhost ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.7.24-log
    
    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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;FLUSH PRIVILEGES;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show status like "wsrep%";
    +------------------------------+--------------------------------------+
    | Variable_name                | Value                                |
    +------------------------------+--------------------------------------+
    | wsrep_local_state_uuid       | af4b7ecb-07ec-11e9-9d71-cfb0574baac9 |
    | wsrep_protocol_version       | 9                                    |
    | wsrep_last_committed         | 3                                    |
    | wsrep_replicated             | 3                                    |
    | wsrep_replicated_bytes       | 712                                  |
    | wsrep_repl_keys              | 3                                    |
    | wsrep_repl_keys_bytes        | 96                                   |
    | wsrep_repl_data_bytes        | 415                                  |
    | wsrep_repl_other_bytes       | 0                                    |
    | wsrep_received               | 2                                    |
    | wsrep_received_bytes         | 144                                  |
    | 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_open_transactions      | 0                                    |
    | wsrep_open_connections       | 0                                    |
    | wsrep_incoming_addresses     | 192.168.190.132:3306                 |
    | wsrep_cluster_weight         | 1                                    |
    | 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             | af4a65e4-07ec-11e9-9aac-76a760227320 |
    | wsrep_cluster_conf_id        | 1                                    |
    | wsrep_cluster_size           | 1                                    |
    | wsrep_cluster_state_uuid     | af4b7ecb-07ec-11e9-9d71-cfb0574baac9 |
    | 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.25(rddf9876)                       |
    | wsrep_ready                  | ON                                   |
    +------------------------------+--------------------------------------+
    60 rows in set (0.03 sec)
    
    
    mysql> flush privileges;  ##刷新
    mysql> exit;

最后不要忘了将配置文件/etc/my.cnf.d/wsrep.cnf中节点的设置修改回来（设置成各其他节点的IP地址）wsrep\_cluster\_address=gcomm://192.168.190.133,192.168.190.134

【剩下两台机群如此配置即可，注意几点：】

1、初次启动不需要使用启动脚本：/usr/bin/mysqld\_bootstrap，因为这个脚本中带有一个参数：--wsrep-new-cluster，代表新集群。  
2、配置文件 wsrep.cnf 中的参数 wsrep\_cluster\_address，一开始就要配置好了，全部配置成指向主节点的IP地址，如：wsrep\_cluster\_address=gcomm://192.168.190.132:4567  （192.168.190.132为主节点IP地址 ）不能为空。

3、配置文件 wsrep.cnf 中的参数 wsrep\_node\_address主节点为wsrep\_node\_address=node1，其他的个节点依次为：wsrep\_node\_address=node2  ， wsrep\_node\_address=node3 ，wsrep\_node\_address=node...

4、配置文件 wsrep.cnf 中的参数server\_id必须是唯一的，在配置各节点时，避免出现重复值！  
5、类似配置文件 wsrep.cnf 中的 wsrep\_node\_address 这种参数要配置成自己当前及其所在的IP地址信息。  
6、正式环境情况下，我们不要把防火墙关闭掉，把需要使用的端口开放给指定 IP 即可，更加安全。

都搭建成功后，可以看到，在任何一个节点上改动数据库（如创建数据库，改动数据表等操作），另外的节点都会有同步变化！

[https://blog.csdn.net/zhedanxc/article/details/78236033][https_blog.csdn.net_zhedanxc_article_details_78236033]

[Link 1]: https://blog.csdn.net/csdn_kou/article/details/81096744
[CentOS7_yum]: https://blog.csdn.net/u010463613/article/details/81196260
[https_blog.csdn.net_zhedanxc_article_details_78236033]: https://blog.csdn.net/zhedanxc/article/details/78236033