世界上只有一种英雄主义,就是看清生活的真相之后依然热爱生活!

CentOS 7 配置 MariaDB Galera Cluster

2019-07-24
55次查阅
2019/7/26

MariaDB Galera Cluster 是一套在 mysql innodb 存储引擎上面实现multi-master及数据实时同步的系统架构,业务层面无需做读写分离工作,数据库读写压力都能按照既定的规则分发到各个节点上去。在数据方面完全兼容 MariaDB 和 MySQL。

Galera Cluster 与传统的复制方式不同,不通过I/O_threadsql_thread进行同步,而是在更底层通过wsrep实现文件系统级别的同步,可以做到几乎实时同步。

试验环境:

  • 系统版本:CentOS Linux release 7.6.1810 (Core) 
  • 数据库版本:MariaDB-10.3.16
  • 服务器:192.168.0.3(node-1)、192.168.0.4(node-2)、192.168.0.5(node-3)

MariaDB安装和环境准备

1、MariaDB的安装和系统初始化(采用yum安装方式),这里直接用脚本完成:

#!/bin/bash
# sk(at)1987.name
# MariaDB yum安装脚本

DB_DATA_PATH='/data/mariadb'
DB_ROOT_PWD='123456'

# 初始化
init() {
    # 设置时区
    rm -rf /etc/localtime
    ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

    # 移除
    rpm -e mysql mysql-libs --nodeps
    yum -y remove mysql-server mysql mysql-libs
    yum -y install ntp lrzsz lsof

    # 同步时间
    ntpdate -u pool.ntp.org

    # 关闭防火墙和SELinux
    systemctl disable firewalld && systemctl stop firewalld
    sed -i 's/^SELINUX=.*$/SELINUX=disabled/g' /etc/selinux/config
    setenforce 0
    iptables -F
    iptables -X
    iptables -Z
    
}

# 生成配置文件
create_db_cnf() {
# 获取内存大小
mem_total=$(free -m | awk '/^Mem:/{print $2}')

# 生成配置文件
> /etc/my.cnf
cat > /etc/my.cnf <<EOF
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
datadir = ${DB_DATA_PATH}
log_error = ${DB_DATA_PATH}/mariadb.err
pid-file = ${DB_DATA_PATH}/mariadb.pid
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M

explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = ${DB_DATA_PATH}
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = ${DB_DATA_PATH}
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
EOF

# 参数调优
if [[ ${mem_total} -gt 1024 && ${mem_total} -lt 2048 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 32M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 128#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 768K#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 768K#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 8M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 16#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 16M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 32M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 128M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 32M#" /etc/my.cnf
elif [[ ${mem_total} -ge 2048 && ${mem_total} -lt 4096 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 64M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 256#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 1M#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 1M#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 16M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 32#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 32M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 64M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 256M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 64M#" /etc/my.cnf
elif [[ ${mem_total} -ge 4096 && ${mem_total} -lt 8192 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 128M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 512#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 2M#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 2M#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 32M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 64#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 64M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 64M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 512M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 128M#" /etc/my.cnf
elif [[ ${mem_total} -ge 8192 && ${mem_total} -lt 16384 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 256M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 1024#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 4M#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 4M#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 64M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 128#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 128M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 128M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 1024M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 256M#" /etc/my.cnf
elif [[ ${mem_total} -ge 16384 && ${mem_total} -lt 32768 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 512M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 2048#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 8M#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 8M#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 128M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 256#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 256M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 256M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 2048M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 512M#" /etc/my.cnf
elif [[ ${mem_total} -ge 32768 ]]; then
    sed -i "s#^key_buffer_size.*#key_buffer_size = 1024M#" /etc/my.cnf
    sed -i "s#^table_open_cache.*#table_open_cache = 4096#" /etc/my.cnf
    sed -i "s#^sort_buffer_size.*#sort_buffer_size = 16M#" /etc/my.cnf
    sed -i "s#^read_buffer_size.*#read_buffer_size = 16M#" /etc/my.cnf
    sed -i "s#^myisam_sort_buffer_size.*#myisam_sort_buffer_size = 256M#" /etc/my.cnf
    sed -i "s#^thread_cache_size.*#thread_cache_size = 512#" /etc/my.cnf
    sed -i "s#^query_cache_size.*#query_cache_size = 512M#" /etc/my.cnf
    sed -i "s#^tmp_table_size.*#tmp_table_size = 512M#" /etc/my.cnf
    sed -i "s#^innodb_buffer_pool_size.*#innodb_buffer_pool_size = 4096M#" /etc/my.cnf
    sed -i "s#^innodb_log_file_size.*#innodb_log_file_size = 1024M#" /etc/my.cnf
fi
}

# 安装数据库
db_install_main() {

# 初始化
init

cat > /etc/yum.repos.d/mariadb.repo <<EOF
[mariadb]
name = MariaDB
baseurl = http://mirrors.neusoft.edu.cn/mariadb/yum/10.3/centos7-amd64
gpgkey=http://mirrors.neusoft.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

yum -y install MariaDB-server MariaDB-client galera

#配置文件
create_db_cnf

#创建相关目录
mkdir -p $DB_DATA_PATH
chown -R mysql:mysql $DB_DATA_PATH

#初始化
/usr/bin/mysql_install_db \
--defaults-file=/etc/my.cnf \
--datadir=${DB_DATA_PATH} \
--user=mysql

/etc/init.d/mysql start

# 配置密码
/usr/bin/mysql -uroot -e "UPDATE mysql.user SET Password=PASSWORD('${DB_ROOT_PWD}') WHERE User='root';FLUSH PRIVILEGES;"
/etc/init.d/mysql restart

# 移除默认库和不安全账户
/usr/bin/mysql -uroot -p${DB_ROOT_PWD} -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
/usr/bin/mysql -uroot -p${DB_ROOT_PWD} -e "DROP DATABASE test;"
/usr/bin/mysql -uroot -p${DB_ROOT_PWD} -e "DELETE FROM mysql.user WHERE User='';"
/usr/bin/mysql -uroot -p${DB_ROOT_PWD} -e "DROP USER ''@'%';"
/usr/bin/mysql -uroot -p${DB_ROOT_PWD} -e "FLUSH PRIVILEGES;"
/etc/init.d/mysql restart
}

# 执行
db_install_main

2、编辑三台服务器的hosts文件:

vim /etc/hosts
192.168.0.3 node-1
192.168.0.4 node-2
192.168.0.5 node-3

3、在三台服务器上登录数据库,创建用于同步数据库的sst帐号:

grant all privileges on *.* to 'sst'@'%' identified by 'pwd123' with grant option;
flush privileges;

4、停止服务:

systemctl stop mariadb

Galera 集群配置

1、分别在三台服务器编辑/etc/my.cnf文件,添加如下配置:

[galera]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name="cluster"
wsrep_cluster_address="gcomm://192.168.0.3,192.168.0.4,192.168.0.5"
wsrep_node_name=node-1
wsrep_node_address=192.168.0.3
wsrep_slave_threads=2
wsrep_causal_reads=1
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=120M
wsrep_notify_cmd=
wsrep_sst_method=rsync
wsrep_sst_auth=sst:pwd123

node-2、node-3阶段和上述配置方法相同,修改wsrep_node_namewsrep_node_address为对应的主机名IP。如果有更多节点配置方法相同。

2、开启服务

首次启动在任意节点执行galera_new_cluster命令来创建集群,其他节点使用systemctl start mariadb开启服务,以后重启就和正常重启服务一样。

3、检查一下端口状态:

[root@node-1 ~]# netstat -natp | grep -e 3306 -e 4567 -e 4444 -e 4568
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      12115/mysqld        
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      12115/mysqld        
tcp        0      0 192.168.0.3:57674       192.168.0.5:4567        ESTABLISHED 12115/mysqld        
tcp        0      0 192.168.0.3:4567        192.168.0.3:50072       CLOSE_WAIT  12115/mysqld        
tcp        0      0 192.168.0.3:53010       192.168.0.4:4567        ESTABLISHED 12115/mysqld   
  • 3306 - MySQL端口
  • 4567 - 群集间通信端口
  • 4568 - IST(增量状态转移)端口
  • 4444 - SST(状态快照传输)端口

集群查看命令

1、查看集群功能是否开启

MariaDB [(none)]> show status like 'wsrep_ready';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+
1 row in set (0.001 sec)

2、初始化数据库显示情况

MariaDB [(none)]> show variables like 'wsrep_cluster_address';
+-----------------------+---------------------------------------------+
| Variable_name         | Value                                       |
+-----------------------+---------------------------------------------+
| wsrep_cluster_address | gcomm://192.168.0.3,192.168.0.4,192.168.0.5 |
+-----------------------+---------------------------------------------+
1 row in set (0.001 sec)

3、查看集群相关参数

MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+----------------------------------------------------+
| Variable_name                | Value                                              |
+------------------------------+----------------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                           |
| wsrep_apply_oool             | 0.000000                                           |
| wsrep_apply_window           | 0.000000                                           |
| wsrep_causal_reads           | 0                                                  |
| wsrep_cert_deps_distance     | 0.000000                                           |
| wsrep_cert_index_size        | 0                                                  |
| wsrep_cert_interval          | 0.000000                                           |
| wsrep_cluster_conf_id        | 15                                                 |
| wsrep_cluster_size           | 3                                                  |
| wsrep_cluster_state_uuid     | fb48e04c-ad4c-11e9-b61f-f659fc338c16               |
| wsrep_cluster_status         | Primary                                            |
| wsrep_cluster_weight         | 3                                                  |
| wsrep_commit_oooe            | 0.000000                                           |
| wsrep_commit_oool            | 0.000000                                           |
| wsrep_commit_window          | 0.000000                                           |
| wsrep_connected              | ON                                                 |
| 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_flow_control_paused    | 0.000000                                           |
| wsrep_flow_control_paused_ns | 0                                                  |
| wsrep_flow_control_recv      | 0                                                  |
| wsrep_flow_control_sent      | 0                                                  |
| wsrep_gcomm_uuid             | eea64c6d-adcd-11e9-926d-b634cab29f14               |
| wsrep_incoming_addresses     | 192.168.0.4:3306,192.168.0.5:3306,192.168.0.3:3306 |
| wsrep_last_committed         | 2                                                  |
| wsrep_local_bf_aborts        | 0                                                  |
| wsrep_local_cached_downto    | 18446744073709551615                               |
| wsrep_local_cert_failures    | 0                                                  |
| wsrep_local_commits          | 0                                                  |
| wsrep_local_index            | 2                                                  |
| wsrep_local_recv_queue       | 0                                                  |
| wsrep_local_recv_queue_avg   | 0.000000                                           |
| wsrep_local_recv_queue_max   | 1                                                  |
| wsrep_local_recv_queue_min   | 0                                                  |
| wsrep_local_replays          | 0                                                  |
| wsrep_local_send_queue       | 0                                                  |
| wsrep_local_send_queue_avg   | 0.000000                                           |
| wsrep_local_send_queue_max   | 1                                                  |
| wsrep_local_send_queue_min   | 0                                                  |
| wsrep_local_state            | 4                                                  |
| wsrep_local_state_comment    | Synced                                             |
| wsrep_local_state_uuid       | fb48e04c-ad4c-11e9-b61f-f659fc338c16               |
| wsrep_open_connections       | 0                                                  |
| wsrep_open_transactions      | 0                                                  |
| wsrep_protocol_version       | 9                                                  |
| wsrep_provider_name          | Galera                                             |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                  |
| wsrep_provider_version       | 25.3.26(r3857)                                     |
| wsrep_ready                  | ON                                                 |
| wsrep_received               | 2                                                  |
| wsrep_received_bytes         | 279                                                |
| wsrep_repl_data_bytes        | 0                                                  |
| wsrep_repl_keys              | 0                                                  |
| wsrep_repl_keys_bytes        | 0                                                  |
| wsrep_repl_other_bytes       | 0                                                  |
| wsrep_replicated             | 0                                                  |
| wsrep_replicated_bytes       | 0                                                  |
| wsrep_thread_count           | 3                                                  |
+------------------------------+----------------------------------------------------+
61 rows in set (0.001 sec)
  • wsrep_cluster_size集群规模节点数。
  • wsrep_cluster_state_uuid集群uuid。
  • wsrep_connected连接状态。
  • wsrep_ready启动状态。

4、查看时间

MariaDB [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-24 13:10:24 |
+---------------------+
1 row in set (0.000 sec)

1条评论

想说点什么?