MariaDB Galera Cluster 是一套在 mysql innodb 存储引擎上面实现multi-master及数据实时同步的系统架构，业务层面无需做读写分离工作，数据库读写压力都能按照既定的规则分发到各个节点上去。在数据方面完全兼容 MariaDB 和 MySQL。
Galera Cluster 与传统的复制方式不同，不通过I/O_thread和sql_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_name和wsrep_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.本站所有内容只做学习和交流使用。 版权归原作者所有。
2.保证站内提供的所有可下载源码资源(软件等)都是按“原样”提供，本站未做过任何改动;但本网站不保证本站提供的下载资源的准确性、安全性和完整性;同时本网站也不承担用户因使用这些下载资源对自己和他人造成任何形式的损失或伤害。
3.本站部分内容均收集于网络!如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。请联系站长邮箱:admin#ibian.online(#换成@）处理！
