MariaDB集群搭建
一、环境简介
序列 | 主机名 | IP | OS版本 |
---|---|---|---|
1 | db136 | 192.168.142.136 | CentOS7.6 |
2 | db137 | 192.168.142.137 | CentOS7.6 |
3 | db138 | 192.168.142.138 | CentOS7.6 |
Galera版本:galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm
MariaDB版本:mariadb-10.5.0-linux-systemd-x86_64.tar.gz (注意,该版本是当前最新测试版本,请勿用于生产环境)
二、系统依赖包安装
由于笔者所使用的是vmware的虚拟机,故使用了最小化安装方式,所以需要安装大量的依赖包。
Centos最小化安装后续操作设置可参考:https://www.cnblogs.com/bjx2020/p/12125386.html
yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat
三、设置时间同步
yum -t install ntp
ntpdate asia.pool.ntp.org
# systemctl stop ntpd.service
cat >>/var/spool/cron/root<<"EOF"
*/10 * * * * /usr/sbin/ntpdate asia.pool.ntp.org >/dev/null
EOF
四、防火墙设置
建议直接关闭防火墙,如有特殊需求必须开启,请参照如下
# 关闭CentOS7自带的防火墙 firewall 启用 IPtable
systemctl stop firewalld
systemctl disable firewalld.service
#安装IPtables防火墙
yum install -y iptables-services
#开放443端口(HTTPS)
iptables -A INPUT -p tcp --dport 443 -j ACCEPT
#保存上述规则
service iptables save
#开启服务
systemctl restart iptables.service
systemctl enable iptables.service
# 修改iptables配置文件,开放以下端口 (默认开启了22端口,
# 以便putty等软件的连接,实例开启80端口和3306端口,
# 以便后期lamp环境使用,注:80 为Apache默认端口,3306为MySQL的默认端口)
iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables save
service iptables reload
# 自带的firwalld关闭方式
systemctl stop firewalld
systemctl disable firewalld
五、关闭selinux
# 关闭selinux
cp /etc/selinux/config /etc/selinux/config.bak
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
sed -i 's/SELINUXTYPE=targeted/# SELINUXTYPE=targeted/' /etc/selinux/config
setenforce 0
六、文件描述符修改
sed -i 's/4096/unlimited/' /etc/security/limits.d/20-nproc.conf
cat >>/etc/sysctl.conf <<"EOF"
################################################################
net.ipv4.tcp_keepalive_time =600
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout = 30
net.ipv4.ip_local_port_range = 32768 60999
net.ipv4.tcp_max_syn_backlog = 1024
net.core.somaxconn = 1024
net.ipv4.tcp_max_tw_buckets = 5000
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_synack_retries = 1
net.core.netdev_max_backlog = 1000
net.ipv4.tcp_max_orphans = 2000
net.nf_conntrack_max = 25000000
net.netfilter.nf_conntrack_max = 25000000
net.netfilter.nf_conntrack_tcp_timeout_established = 180
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
# 结合DDOS和TIME_WAIT过多,建议增加如下参数设置:
# Use TCP syncookies when needed
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_synack_retries=3
net.ipv4.tcp_syn_retries=3
net.ipv4.tcp_max_syn_backlog=2048
# Enable TCP window scaling
# net.ipv4.tcp_window_scaling: = 1
# Increase TCP max buffer size
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
# Increase Linux autotuning TCP buffer limits
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
# Increase number of ports available
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.ip_local_port_range = 5000 65000
################################################################
EOF
sysctl -p
cat >>/etc/bashrc<<"EOF"
ulimit -u 65536
EOF
source /etc/bashrc
cat >>/etc/security/limits.conf <<"EOF"
* hard nofile 1000000
* soft nofile 1000000
EOF
七、配置IP主机配置关系
# 配置主机对应关系
cat >>/etc/hosts<<"EOF"
192.168.142.136 db136
192.168.142.137 db137
192.168.142.138 db138
EOF
八、配置公钥认证(可选)
# 配置公钥认证(可选)
ssh-keygen # 一路回车即可(三台机器都需要操作)
ssh-copy-id -i ~/.ssh/id_rsa.pub db137 (只需要在第一台操作)
ssh-copy-id -i ~/.ssh/id_rsa.pub db138 (只需要在第一台操作)
# 验证登录
ssh root@db137
ssh root@db138
九、创建mysql用户
# 创建用户
useradd -M -r -s /bin/nologin mysql
echo "mysql"|passwd --stdin mysql
十、Galera参数概览
# node1
# node1
vim /etc/my.cnf 在 [galera]下面加入以下内容
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db136
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.136
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
# node2
# node2
vim /etc/my.cnf 在 [galera]下面加入以下内容
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db137
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.137
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
# node3
# node3
vim /etc/my.cnf 在 [galera]下面加入以下内容
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db138
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.138
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
# 注意要把 wsrep_node_name 和 wsrep_node_address 改成相应节点的 hostname 和 ip。
十一、下载安装
11-1、第一个节点:db136
cd /opt/wget http://yum.mariadb.org/10.5/centos74-amd64/rpms/galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpmrpm -ivh galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpmwget http://mariadb.mirror.nucleus.be//mariadb-10.5.0/bintar-linux-systemd-x86_64/mariadb-10.5.0-linux-systemd-x86_64.tar.gz
tar -zxvf mariadb-10.5.0-linux-systemd-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mariadb-10.5.0-linux-systemd-x86_64/ mysql
echo "PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
# 设置第一个节点的my.cnf
# db136
cat >/etc/my.cnf<<"EOF"
[client]
port = 3306
socket = /data/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="\u@MariaDB \R:\m:\s [\d]> "
no-auto-rehash
default-character-set=utf8mb4
[mysqld]
user = mysql
port = 3306
extra_port=13306
socket = /data/mysql/data/mysql.sock
basedir= /usr/local/mysql
datadir= /data/mysql/data
log-error=/data/mysql/logs/mysql-error.log
pid-file=/data/mysql/data/mariadb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1
log_queries_not_using_indexes =1
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/logs/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 30
log_slave_updates
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 5734M
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 8000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 1
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
#innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
symbolic-links=0
innodb_status_file = 1
#performance_schema
performance_schema = 1
# 字符集设定utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
# 优化
optimizer_switch = "mrr=on,mrr_cost_based=on,mrr_sort_keys=on"
deadlock_search_depth_short = 3
deadlock_search_depth_long = 10
deadlock_timeout_long = 10000000
deadlock_timeout_short = 5000
slave-parallel-threads=8
# gtid
gtid_strict_mode=1
wsrep_gtid_mode=1
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db136
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.136
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
[mysqld_safe]
nice=-19
open-files-limit=65535
[mysqldump]
quick
max_allowed_packet = 64M
EOF
SERVIER_ID=`date +%S`
sed -i "s/server-id = 3306/server-id = 3306"${SERVIER_ID}"/" /etc/my.cnf
mkdir -p /data/mysql/{data,logs}
chown mysql.mysql -R /data/mysql
chown mysql.mysql -R /usr/local/mariadb-10.5.0-linux-systemd-x86_64
chown mysql.mysql -R /usr/local/mysql
# 如果是拷贝my.cnf,只需要修改相应的参数值即可。
scp /etc/my.cnf root@db137:/etc/my.cnf
scp /etc/my.cnf root@db138:/etc/my.cnf
sed -i 's/wsrep_node_address=192.168.142.136/wsrep_node_address=192.168.142.137/' /etc/my.cnf
sed -i 's/wsrep_node_name=db136/wsrep_node_name=db137/' /etc/my.cnf
sed -i 's/wsrep_node_address=192.168.142.136/wsrep_node_address=192.168.142.138/' /etc/my.cnf
sed -i 's/wsrep_node_name=db136/wsrep_node_name=db138/' /etc/my.cnf
# 特别注意: 初始化第一台(另外两台不需要初始化)
# 初始化数据
cd /usr/local/mysql
./scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
# 第一个节点第一次启动:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --wsrep-new-cluster &
# 第一个节点第一次启动后再次启动的命令:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
# 启动之后登陆第一个实例后设置用户名和密码(仅在第一个实例)
mysql # 直接回车
delete from mysql.user where user='';
drop database test;
# 创建管理员
grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'rootpwd' with grant option;
alter user 'root'@'localhost' identified by 'rootpwd';
alter user 'mysql'@'localhost' identified by 'mysqlpwd';
-- grant all privileges on *.* to 'root'@'%' identified by 'rootpwd' with grant option;
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bakuser'@'%' identified by 'Mqh7pFCTLqaV';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost' identified by 'Mqh7pFCTLqaV';
flush privileges;
exit;
# 登录超级管理端口
mysql -h 127.0.0.1 -uroot -p'rootpwd' -P13306
# 查看服务
[root@db136 mysql]# netstat -anltp|grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 7440/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 7440/mysqld
tcp 0 0 0.0.0.0:13306 0.0.0.0:* LISTEN 7440/mysqld
[root@db136 mysql]#
# 启动第二和第三个实例,让后面两个实例加入到集群
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
11-2、第二个节点:db137
# 下载安装
cd /opt/
wget http://yum.mariadb.org/10.5/centos74-amd64/rpms/galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm
rpm -ivh galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm
wget http://mariadb.mirror.nucleus.be//mariadb-10.5.0/bintar-linux-systemd-x86_64/mariadb-10.5.0-linux-systemd-x86_64.tar.gz
tar -zxvf mariadb-10.5.0-linux-systemd-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mariadb-10.5.0-linux-systemd-x86_64/ mysql
echo "PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
# my.cnf配置
# 配置文件
# db137
cat >/etc/my.cnf<<"EOF"
[client]
port = 3306
socket = /data/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="\u@MariaDB \R:\m:\s [\d]> "
no-auto-rehash
default-character-set=utf8mb4
[mysqld]
user = mysql
port = 3306
extra_port=13306
socket = /data/mysql/data/mysql.sock
basedir= /usr/local/mysql
datadir= /data/mysql/data
log-error=/data/mysql/logs/mysql-error.log
pid-file=/data/mysql/data/mariadb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1
log_queries_not_using_indexes =1
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/logs/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 30
log_slave_updates
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 5734M
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 8000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 1
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
#innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
symbolic-links=0
innodb_status_file = 1
#performance_schema
performance_schema = 1
# 字符集设定utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
# 优化
optimizer_switch = "mrr=on,mrr_cost_based=on,mrr_sort_keys=on"
deadlock_search_depth_short = 3
deadlock_search_depth_long = 10
deadlock_timeout_long = 10000000
deadlock_timeout_short = 5000
slave-parallel-threads=8
# gtid
gtid_strict_mode=1
wsrep_gtid_mode=1
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db137
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.137
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
[mysqld_safe]
nice=-19
open-files-limit=65535
[mysqldump]
quick
max_allowed_packet = 64M
EOF
SERVIER_ID=`date +%S`
sed -i "s/server-id = 3306/server-id = 3306"${SERVIER_ID}"/" /etc/my.cnf
mkdir -p /data/mysql/{data,logs}
chown mysql.mysql -R /data/mysql
chown mysql.mysql -R /usr/local/mariadb-10.5.0-linux-systemd-x86_64
chown mysql.mysql -R /usr/local/mysql
# 特别注意:第二节点、第三节点不需要初始化
# 启动该节点
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
11-3、第三个节点:db138
# 下载安装
cd /opt/
wget http://yum.mariadb.org/10.5/centos74-amd64/rpms/galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm
rpm -ivh galera-4-26.4.3-1.rhel7.el7.centos.x86_64.rpm
wget http://mariadb.mirror.nucleus.be//mariadb-10.5.0/bintar-linux-systemd-x86_64/mariadb-10.5.0-linux-systemd-x86_64.tar.gz
tar -zxvf mariadb-10.5.0-linux-systemd-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mariadb-10.5.0-linux-systemd-x86_64/ mysql
echo "PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
# my.cnf配置
[](javascript:void(0)😉
# 配置文件
# db138
cat >/etc/my.cnf<<"EOF"
[client]
port = 3306
socket = /data/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysql]
prompt="\u@MariaDB \R:\m:\s [\d]> "
no-auto-rehash
default-character-set=utf8mb4
[mysqld]
user = mysql
port = 3306
extra_port=13306
socket = /data/mysql/data/mysql.sock
basedir= /usr/local/mysql
datadir= /data/mysql/data
log-error=/data/mysql/logs/mysql-error.log
pid-file=/data/mysql/data/mariadb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1
log_queries_not_using_indexes =1
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/logs/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 30
log_slave_updates
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 5734M
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话,可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
innodb_io_capacity = 8000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 1
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
#innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
symbolic-links=0
innodb_status_file = 1
#performance_schema
performance_schema = 1
# 字符集设定utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
# 优化
optimizer_switch = "mrr=on,mrr_cost_based=on,mrr_sort_keys=on"
deadlock_search_depth_short = 3
deadlock_search_depth_long = 10
deadlock_timeout_long = 10000000
deadlock_timeout_short = 5000
slave-parallel-threads=8
# gtid
gtid_strict_mode=1
wsrep_gtid_mode=1
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.142.136,192.168.142.137,192.168.142.138"
wsrep_node_name=db138
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address=192.168.142.138
wsrep_sst_method=rsync
wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=10240M
wsrep_sst_auth=bakuser:Mqh7pFCTLqaV
[mysqld_safe]
nice=-19
open-files-limit=65535
[mysqldump]
quick
max_allowed_packet = 64M
EOF
SERVIER_ID=`date +%S`
sed -i "s/server-id = 3306/server-id = 3306"${SERVIER_ID}"/" /etc/my.cnf
mkdir -p /data/mysql/{data,logs}
chown mysql.mysql -R /data/mysql
chown mysql.mysql -R /usr/local/mariadb-10.5.0-linux-systemd-x86_64
chown mysql.mysql -R /usr/local/mysql
# 第二节点、第三节点不需要初始化
# 启动该节点
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
十二、登录验证同步
# 测试验证同步
[root@db136 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| db138 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db136 mysql]#
[root@db137 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| db138 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db137 mysql]#
[root@db138 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| db138 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db138 mysql]#
# 删除库操作
[root@db138 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db138 mysql]#
[root@db137 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db137 mysql]#
[root@db136 mysql]# mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db136 |
| db137 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@db136 mysql]#
# 导入表测试
[root@db136 mysql]# mysql db136 < /opt/1.sql
[root@db137 mysql]# mysql -e "use db136; show tables;"
+-----------------+
| Tables_in_db136 |
+-----------------+
| t1 |
+-----------------+
[root@db137 mysql]#
[root@db137 mysql]# mysql -e "use db136; select * from t1 where id=10;"
+----+---------+---------+------------+---------+--------+----------+--------+
| id | pay_min | pay_max | grade_type | subject | period | discount | price |
+----+---------+---------+------------+---------+--------+----------+--------+
| 10 | 21 | 59 | 2 | 0 | 0 | 90 | 205.00 |
+----+---------+---------+------------+---------+--------+----------+--------+
[root@db137 mysql]#
[root@db137 mysql]# mysql -e "use db136; delete from t1 where id=10;"
[root@db138 mysql]# mysql -e "use db136; select * from t1 where id=10;"
十三、常用命令收录
# 这里应该显示集群里有3个节点
[root@db137 mysql]# mysql -e "show status like 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
# 这里应该显示ON
[root@db137 mysql]# mysql -e "show status like 'wsrep_connected'"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| wsrep_connected | ON |
+-----------------+-------+
# 这里应该显示 AUTO
[root@db137 mysql]# mysql -e "show status like 'wsrep_incoming_addresses'"
+--------------------------+----------------+
| Variable_name | Value |
+--------------------------+----------------+
| wsrep_incoming_addresses | AUTO,AUTO,AUTO |
+--------------------------+----------------+
# 这里节点的同步状态
[root@db137 mysql]# mysql -e "show status like 'wsrep_local_state_comment'"
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
[root@db137 mysql]#
十四、附录:断电异常处理
异常处理:当机房突然停电,所有galera主机都非正常关机,来电后开机,会导致galera集群服务无法正常启动。如何处理?
第1步:开启galera集群的群主主机的mariadb服务。
第2步:开启galera集群的成员主机的mariadb服务。
异常处理:galera集群的群主主机和成员主机的mysql服务无法启动,如何处理?
解决方法一:
第1步、删除garlera群主主机的/data/mysql/data/grastate.dat状态文件
/bin/galera_new_cluster启动服务。启动正常。登录并查看wsrep状态。
第2步:删除galera成员主机中的/data/mysql/data/grastate.dat状态文件
systemctl restart mariadb重启服务。启动正常。登录并查看wsrep状态。
解决方法二:
第1步、修改garlera群主主机的/data/mysql/data/grastate.dat状态文件中的0为1
/bin/galera_new_cluster启动服务。启动正常。登录并查看wsrep状态。
第2步:修改galera成员主机中的/data/mysql/data/grastate.dat状态文件中的0为1
重启服务。启动正常。登录并查看wsrep状态。
# find / -name grastate.dat
cat >>~/.bashrc <<"EOF"
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
alias mysql.galera_start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --wsrep-new-cluster &"
alias mysql.start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &"
EOF
source ~/.bashrc
2020-01-03 10:20:37 0 [Note] WSREP: (96c2caea, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-01-03 10:20:53 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
2020-01-03 10:20:53 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():220: Failed to open backend connection: -110 (Connection timed out)
2020-01-03 10:20:53 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1608: Failed to open channel 'MariaDB_Cluster' at 'gcomm://192.168.142.136,192.168.142.137,192.168.142.138': -110 (Connection timed out)
2020-01-03 10:20:53 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2020-01-03 10:20:53 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.142.136,192.168.142.137,192.168.142.138) failed: 7
2020-01-03 10:20:53 0 [ERROR] Aborting
#虚拟机关闭后,启动失败
# 第一台:
rm -rf /data/mysql/data/galera.cache
rm -rf /data/mysql/data/grastate.dat
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --wsrep-new-cluster &
# 第二台:
rm -rf /data/mysql/data/galera.cache
rm -rf /data/mysql/data/grastate.dat
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
# 第三台:
rm -rf /data/mysql/data/galera.cache
rm -rf /data/mysql/data/grastate.dat
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
十五、其他
# 模拟故障
[root@db138 mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.5.0-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@MariaDB0 16:19: [(none)]> shutdown;
Query OK, 0 rows affected (0.001 sec)
root@MariaDB0 16:19: [(none)]> exit
Bye
[root@db138 mysql]#
# 查看日志
[root@db137 ~]# tail -f /data/mysql/logs/mysql-error.log
......
......
2020-01-02 16:19:14 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-02 16:19:20 0 [Note] WSREP: cleaning up 0a38be73 (tcp://192.168.142.138:4567)
# 启动这个节点
[root@db138 mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 15714
# 可看到直接恢复正常
[root@db137 ~]# tail -f /data/mysql/logs/mysql-error.log
......
......
2020-01-02 16:20:59 0 [Note] WSREP: 1.0 (db138): State transfer from 0.0 (db137) complete.
2020-01-02 16:20:59 0 [Note] WSREP: Member 1.0 (db138) synced with group.
[root@db136 mysql]# mysql -e "show status like '%wsrep_cluster_size%';"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
[root@db136 mysql]#
# 关机测试
[root@db138 mysql]# shutdown
[root@db137 ~]# tail -f /data/mysql/logs/mysql-error.log
......
......
2020-01-02 16:23:01 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-02 16:23:06 0 [Note] WSREP: cleaning up c83f63d6 (tcp://192.168.142.138:4567)
[root@db136 mysql]# mysql -e "show status like '%wsrep_cluster_size%';"
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
[root@db136 mysql]#
# 启动故障机器
[root@db138 ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 6913
# 由于是测试环境,期间未有数据变化操作,启动服务后,发现集群还是自我恢复了。
# 再次,非常感谢创始人,为我等提供了伟大的产品。也让自己前行在自己喜欢的道路上。
[root@db136 mysql]# tail -f /data/mysql/logs/mysql-error.log
View:
id: d0943aae-2d2a-11ea-9103-4f3bbdb0b32e:29
status: primary
protocol_version: 4
capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
final: no
own_index: 2
members(3):
0: 9974d728-2d39-11ea-b3dc-fa279d9d7c6e, db138
1: c5ad2abf-2d2b-11ea-86c5-e3ff80386683, db137
2: d092d0cc-2d2a-11ea-b2ec-57ab1554759e, db136
=================================================
2020-01-02 16:26:43 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-02 16:26:44 0 [Note] WSREP: Member 0.0 (db138) requested state transfer from '*any*'. Selected 1.0 (db137)(SYNCED) as donor.
2020-01-02 16:26:45 0 [Note] WSREP: 1.0 (db137): State transfer to 0.0 (db138) complete.
2020-01-02 16:26:45 0 [Note] WSREP: Member 1.0 (db137) synced with group.
2020-01-02 16:26:45 0 [Note] WSREP: (d092d0cc, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-01-02 16:26:50 0 [Note] WSREP: 0.0 (db138): State transfer from 1.0 (db137) complete.
2020-01-02 16:26:50 0 [Note] WSREP: Member 0.0 (db138) synced with group.
# 另外,在本文档记录的环境下,如果系统重新安装或者其他严重异常后,需要重新加入集群中。
rm -rf /data/mysql/data/* 把文件给删除了。
那么把这个也删掉:
rm -rf /data/mysql/logs/*
然后启动该节点即可。
PLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
final: no
own_index: 2
members(3):
0: 9974d728-2d39-11ea-b3dc-fa279d9d7c6e, db138
1: c5ad2abf-2d2b-11ea-86c5-e3ff80386683, db137
2: d092d0cc-2d2a-11ea-b2ec-57ab1554759e, db136
=================================================
2020-01-02 16:26:43 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-02 16:26:44 0 [Note] WSREP: Member 0.0 (db138) requested state transfer from '*any*'. Selected 1.0 (db137)(SYNCED) as donor.
2020-01-02 16:26:45 0 [Note] WSREP: 1.0 (db137): State transfer to 0.0 (db138) complete.
2020-01-02 16:26:45 0 [Note] WSREP: Member 1.0 (db137) synced with group.
2020-01-02 16:26:45 0 [Note] WSREP: (d092d0cc, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-01-02 16:26:50 0 [Note] WSREP: 0.0 (db138): State transfer from 1.0 (db137) complete.
2020-01-02 16:26:50 0 [Note] WSREP: Member 0.0 (db138) synced with group.
# 另外,在本文档记录的环境下,如果系统重新安装或者其他严重异常后,需要重新加入集群中。
rm -rf /data/mysql/data/* 把文件给删除了。
那么把这个也删掉:
rm -rf /data/mysql/logs/*
然后启动该节点即可。