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_main2、编辑三台服务器的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)
THE END
暂无评论内容