注:该离线部署完全处于离线的情况部署的,是通过自己在电脑上虚拟机测试通过的。
完整的资源位于mysql galera集群离线部署,所有需要用的配置文件、软件包都在这个压缩包里面,且里面有自己编写的自动化部署脚本,可以直接运行安装,可供学习交流。
前期准备
服务器环境
系统版本 | Mariadb版本 | 主机IP | 节点名称 |
CentOS 7.9 | Mariadb-10.5.12 | 192.168.247.10 | galera1 |
CentOS 7.9 | Mariadb-10.5.12 | 192.168.247.20 | galera2 |
CentOS 7.9 | Mariadb-10.5.12 | 192.168.247.30 | galera3 |
环境准备
提前在一台可以联网的服务器下载好rsync-*.rpm包,只需要一个文件就行
1. 在可以连接网络的服务器上修改rpm包存储
vi /etc/yum.conf ###CentOS默认没有安装vim,如果有可以使用vim,两者效果一样
修改以下内容,将0修改成1
或者使用命令行直接修改
sed -i "s/keepcache=0/keepcache=1/g" /etc/yum.conf
2. 下载rsync-*.rpm
yum install rsync -y
3. 导出rsync-*.rpm(使用服务器操作工具进行)
进入rpm保存的路径,导出备用
### x86_64/7 是下载的时候自动生成的,每台机器可能不一样
### x86_64/7 下面一共有三个文件夹,三个顺序找就能找到,但基本就在下面两个其中一个
cd /var/cache/yum/x86_64/7/base/packages/
或
cd /var/cache/yum/x86_64/7/update/packages/
服务器远程操作管理软件推荐
或者我会将文章所有需要的资料,即直接运行的压缩包放置在文章最后
Mariadb 离线部署
纯离线,无需任何网络即可完成Mariadb的安装,采用二进制安装
1. 存储目录
数据文件存储位置:/mysql_data/data
日志文件存储位置:/mysql_data/logs/
binlog文件存储位置:/mysql_data/mysql
缓存目录位置:/mysql_data/tmp
根据自己的需要改变
2. 创建目录
#创建相应的路径 -p表示不存在时创建
mkdir -p /mysql_data/data
mkdir -p /mysql_data/logs
mkdir -p /mysql_data/tmp
mkdir -p /mysql_data/mysql
#增加mysql用户
useradd mysql
#授权
chown -R mysql:mysql /mysql_data/data
chown -R mysql:mysql /mysql_data/logs
chown -R mysql:mysql /mysql_data/tmp
chown -R mysql:mysql /mysql_data/mysql
3. 解压文件
tar -zxvf ${mariadb_bag} -C /usr/local/ #解压至指定路径
mv /usr/local/mari* /usr/local/mysql #修改名字
4. 设置mysql的配置文件
[root@xxxxx]# vim /etc/my.cnf
#配置mysql配置文件
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
datadir = /mysql_data/data
pid-file = /mysql_data/mysql/mysql.pid
server-id = 10
relay_log =/mysql_data/logs/mysql_relay.log
bind-address = 0.0.0.0
tmpdir=/mysql_data/tmp
init_connect ='SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
log_bin = /mysql_data/mysql/mysql_bin.log
binlog_format = ROW
expire_logs_days = 7
log_error = /mysql_data/logs/mysql_error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /mysql_data/logs/mysql_slow.log
performance_schema = 0
skip-external-locking #跳过外部锁定,避免external locking
根据需要修改相应的参数,主要修改ip地址及路径
5. 初始化数据库
#初始化数据库
/usr/local/mysql/scripts/mariadb-install-db --basedir=/usr/local/mysql --datadir=/mysql_data/data --user=mysql
#设置启动文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mariadb
/etc/init.d/mariadb start
#添加环境变量
sed -i '$a\export MYSQL_HOME=/usr/local/mysql' /etc/profile
sed -i '$a\export PATH=$MYSQL_HOME/bin:$PATH' /etc/profile
#使变量生效
source /etc/profile
#登录mysql
mysql #注:mariadb一开始登录没有密码,可以直接使用mysql进入
#若需要用密码登录,使用mysql -uroot -p
至此,mariadb的离线安装完成,可以正常使用
Galera 离线部署
安装软件包
安装rsync
rpm -ivh rsync*.rpm #安装之前提前准备好的rpm文件
修改my.cnf配置文件
192.168.247.10 配置文件内容
[root@xxxxxxx]# vim /etc/my.cnf
##需要修改的部分
#server-id 建议换成ip地址最后一节
#wsrep_cluster_address 所有节点ip地址
#wsrep_node_name 当前节点ip地址
#wsrep_node_address 当前节点ip地址
#配置mysql配置文件
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
datadir = /mysql_data/data
pid-file = /mysql_data/mysql/mysql.pid
server-id = 10
relay_log =/mysql_data/logs/mysql_relay.log
bind-address = 0.0.0.0
tmpdir=/mysql_data/tmp
init_connect ='SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
log_bin = /mysql_data/mysql/mysql_bin.log
binlog_format = ROW
expire_logs_days = 7
log_error = /mysql_data/logs/mysql_error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /mysql_data/logs/mysql_slow.log
performance_schema = 0
skip-external-locking #跳过外部锁定,避免external locking
[galera]
wsrep_on=ON
wsrep_provider=/usr/local/mysql/lib/galera/libgalera_smm.so
wsrep_cluster_name=galera_cluster
wsrep_cluster_address="gcomm://192.168.247.10,192.168.247.20,192.168.247.30"
wsrep_node_name=192.168.247.10
wsrep_node_address=192.168.247.10
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
192.168.247.20 配置文件内容
##需要修改的部分
#server-id 建议换成ip地址最后一节
#wsrep_cluster_address 所有节点ip地址
#wsrep_node_name 当前节点ip地址
#wsrep_node_address 当前节点ip地址
#配置mysql配置文件
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
datadir = /mysql_data/data
pid-file = /mysql_data/mysql/mysql.pid
server-id = 20
relay_log =/mysql_data/logs/mysql_relay.log
bind-address = 0.0.0.0
tmpdir=/mysql_data/tmp
init_connect ='SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
log_bin = /mysql_data/mysql/mysql_bin.log
binlog_format = ROW
expire_logs_days = 7
log_error = /mysql_data/logs/mysql_error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /mysql_data/logs/mysql_slow.log
performance_schema = 0
skip-external-locking #跳过外部锁定,避免external locking
[galera]
wsrep_on=ON
wsrep_provider=/usr/local/mysql/lib/galera/libgalera_smm.so
wsrep_cluster_name=galera_cluster
wsrep_cluster_address="gcomm://192.168.247.10,192.168.247.20,192.168.247.30"
wsrep_node_name=192.168.247.20
wsrep_node_address=192.168.247.20
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
192.168.247.30 配置文件内容
##需要修改的部分
#server-id 建议换成ip地址最后一节
#wsrep_cluster_address 所有节点ip地址
#wsrep_node_name 当前节点ip地址
#wsrep_node_address 当前节点ip地址
#配置mysql配置文件
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
datadir = /mysql_data/data
pid-file = /mysql_data/mysql/mysql.pid
server-id = 30
relay_log =/mysql_data/logs/mysql_relay.log
bind-address = 0.0.0.0
tmpdir=/mysql_data/tmp
init_connect ='SET NAMES utf8'
character-set-server = utf8
skip-name-resolve
back_log = 300
log_bin = /mysql_data/mysql/mysql_bin.log
binlog_format = ROW
expire_logs_days = 7
log_error = /mysql_data/logs/mysql_error.log
slow_query_log = 1
long_query_time = 1
log_slow_verbosity=query_plan
slow_query_log_file = /mysql_data/logs/mysql_slow.log
performance_schema = 0
skip-external-locking #跳过外部锁定,避免external locking
[galera]
wsrep_on=ON
wsrep_provider=/usr/local/mysql/lib/galera/libgalera_smm.so
wsrep_cluster_name=galera_cluster
wsrep_cluster_address="gcomm://192.168.247.10,192.168.247.20,192.168.247.30"
wsrep_node_name=192.168.247.30
wsrep_node_address=192.168.247.30
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
有几个需要修改的参数都在配置文件开头处说明,即server ID、IP地址、主机名
初始化集群
主节点初始化操作:(以192.168.247.10为例)
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --wsrep-new-cluster &
除主节点外的其他节点
/etc/init.d/mariadb start
查看集群状态
[root@xxxxxxx]# mysql -uroot -p
Enter password:
MariadDB [(none)] > show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> show global status like 'ws%'; #查看具体的集群配置信息
#这里的数据以网上样例展示,具体以实际为主
+-------------------------------+----------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------------------------------------+
| wsrep_applier_thread_count | 8 |
| 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 | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | a4d25ee0-515c-11eb-b451-66b7a1b050e2 |
| 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.000308077/0.00394484/0.0110307/0.00501106/3 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_active | false |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_requested | false |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 4fd60274-523f-11eb-b7dd-476f9c9f0398 |
| wsrep_gmcast_segment | 0 |
| wsrep_incoming_addresses | 192.168.40.60:3306,192.168.40.70:3306,192.168.40.50:3306 |
| wsrep_last_committed | 0 |
| 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 | a4d25ee0-515c-11eb-b451-66b7a1b050e2 |
| 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.31(r0ede97d) |
| wsrep_ready | ON |
| wsrep_received | 2 |
| wsrep_received_bytes | 306 |
| 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_rollbacker_thread_count | 1 |
| wsrep_thread_count | 9 |
+-------------------------------+----------------------------------------------------------+
66 rows in set (0.001 sec)
MariaDB [(none)]>
使用脚本完成一键安装
具体的脚本内容如下,配置文件为Galera中的内容
#!/bin/bash
if [ ! -d "/mysql_data" ]; then
echo "Please create mysql_data disk"
exit 0
fi
echo "1. Install MariaDB offline"
echo "2. Start the Mariadb service"
read -ep "Please input the action you want to perform: " exenum
mariadb_bag="mariadb-10.5.12-linux-systemd-x86_64.tar.gz"
rsync_name="rsync-3.1.2-12.el7_9.x86_64.rpm"
now_dir="/root/dep_mariadb"
#安装MariaDB
if [[ ${exenum} = 1 ]] && [[ ${exenum} =~ ^[12]$ ]]; then
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
systemctl stop firewalld.service
systemctl disable firewalld.service
sysctl -p
#创建目录和授权
mkdir -p /mysql_data/data
mkdir -p /mysql_data/logs
mkdir -p /mysql_data/tmp
mkdir -p /mysql_data/mysql
useradd mysql
chown -R mysql:mysql /mysql_data/data
chown -R mysql:mysql /mysql_data/logs
chown -R mysql:mysql /mysql_data/tmp
chown -R mysql:mysql /mysql_data/mysql
# chmod -R 775 /mysql_data/data
# chmod -R 775 /mysql_data/logs
# chmod -R 775 /mysql_data/tmp
# chmod -R 775 /mysql_data/mysql
#解压文件
if [[ -e ${mariadb_bag} ]]; then
tar -zxvf ${mariadb_bag} -C /usr/local/
mv /usr/local/mari* /usr/local/mysql
rm -rf ${mariadb_bag}
else
echo "======================="
echo "mariadb is not exist!!!"
echo "======================="
exit 1
fi
\mv -f my.cnf /etc/
#初始化数据库
/usr/local/mysql/scripts/mariadb-install-db --basedir=/usr/local/mysql --datadir=/mysql_data/data --user=mysql
#设置启动文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mariadb
# /etc/init.d/mariadb start
#添加环境变量
sed -i '$a\export MYSQL_HOME=/usr/local/mysql' /etc/profile
sed -i '$a\export PATH=$MYSQL_HOME/bin:$PATH' /etc/profile
#使变量生效
source /etc/profile
#安装rsync
if [[ -e ${rsync_name} ]]; then
rpm -ivh rsync*.rpm
rm -rf ${rsync_name}
else
echo "===================="
echo "rsync is not exit!!!"
echo "===================="
fi
elif [[ ${exenum} = 2 ]] && [[ ${exenum} =~ ^[12]$ ]]; then
echo "==============================================="
echo "Make sure that the primary database is started!!"
echo "Make sure that the /etc/my.cnf file has changed the corresponding address"
echo "If not, use ctrl+C to exit"
echo "================================================"
read -ep "Whether it is the first one to start? [y/n] " yn
case "$yn" in
[Yy] )
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --wsrep-new-cluster &
;;
[Nn] )
/etc/init.d/mariadb start
;;
* )
echo "please input y/n to express yes or no"
esac
#修改数据库密码,MariaDB安装后默认没有密码
# mysql << EOF
#alter user 'root'@'localhost' identified by 'Server@v01';
#flush privileges;
#grant all privileges on *.* to 'root'@'%' identified by 'Server@v01' with grant option;
#EOF
else
echo "Have a mistake in your entered, please re-enter it!"
fi
测试及可能出现的问题
如果数据库全部关机,再次启动可能会报错,具体如下:
[root@xxxxxx]# cd /mysql_data/logs/
[root@xxxxxx /mysql_data/logs/]# tail -50f mysql_error.log
2021-01-09 13:49:33 0 [Note] WSREP: Read nil XID from storage engines, skipping position init
2021-01-09 13:49:33 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/galera/libgalera_smm.so'
2021-01-09 13:49:33 0 [Note] WSREP: wsrep_load(): Galera 25.3.31(r0ede97d) by Codership Oy <info@codership.com> loaded successfully.
2021-01-09 13:49:33 0 [Note] WSREP: CRC-32C: using 64-bit x86 acceleration.
2021-01-09 13:49:33 0 [Note] WSREP: Found saved state: a4d25ee0-515c-11eb-b451-66b7a1b050e2:0, safe_to_bootstrap: 0
2021-01-09 13:49:33 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/data/; base_host = 192.168.40.50; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/data/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 2G; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout =
2021-01-09 13:49:33 0 [Note] WSREP: Assign initial position for certification: 0, protocol version: -1
2021-01-09 13:49:33 0 [Note] WSREP: wsrep_sst_grab()
2021-01-09 13:49:33 0 [Note] WSREP: Start replication
2021-01-09 13:49:33 0 [Note] WSREP: 'wsrep-new-cluster' option used, bootstrapping the cluster
2021-01-09 13:49:33 0 [Note] WSREP: Setting initial position to a4d25ee0-515c-11eb-b451-66b7a1b050e2:0
2021-01-09 13:49:33 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
2021-01-09 13:49:33 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.247.10,192.168.247.20,192.168.247.30) failed: 7
2021-01-09 13:49:33 0 [ERROR] Aborting
解决办法
修改主节点内容如下,其他节点正常重启即可
[root@xxxxxx]# cd /mysql_data/logs/
[root@xxxxxx /mysql_data/data]# cat grastate.dat
# GALERA saved state
version: 2.1
uuid: a4d25ee0-515c-11eb-b451-66b7a1b050e2
seqno: -1
safe_to_bootstrap: 0
[root@xxxxxx /mysql_data/data]# vim grastate.dat
# GALERA saved state
version: 2.1
uuid: a4d25ee0-515c-11eb-b451-66b7a1b050e2
seqno: -1
safe_to_bootstrap: 1 #把 safe_to_bootstrap: 0 修改为 1
#主节点重新启动,其他节点正常启动
[root@MariaDB-Node1 /data/mysql/data]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --wsrep-new-cluster &