2、部署MariaDB Galeria集群
2.1 控制节点安装mariadb(controller1、controller2、controller3)
Galera是Galera Cluster，是一种为数据库设计的新型的、数据不共享的、高度冗余的高可用方案，galera mariadb就是集成了Galera插件的MariaDB集群，Galera本身是具有多主特性的，所以galera mariadb不是传统的主备模式的集群，而是多主节点架构。
2.1.1 控制节点添加mariadb-10.4.11的yum源
[root@controller1:/root]# vim /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://mirror.netinch.com/pub/mariadb//mariadb-10.4.11/yum/centos7-amd64/ gpgcheck = 0 enabled = 1 [root@controller1:/root]# yum makecache [root@controller2:/root]# cat > /etc/yum.repos.d/mariadb.repo << EOF [mariadb] name = MariaDB baseurl = http://mirror.netinch.com/pub/mariadb//mariadb-10.4.11/yum/centos7-amd64/ gpgcheck = 0 enabled = 1 EOF [root@controller2:/root]# yum makecache [root@controller2:/root]# scp /etc/yum.repos.d/mariadb.repo controller3:/etc/yum.repos.d/ [root@controller3:/root]# yum makecache
2.1.2 控制节点yum安装mariadb软件包并开启服务
[root@controller1:/root]# yum -y install MariaDB-server MariaDB-client galera-4 rsync xinetd wget ntpdate [root@controller1:/root]# systemctl enable mariadb && systemctl start mariadb [root@controller1:/root]# systemctl status mariadb [root@controller2:/root]# yum -y install MariaDB-server MariaDB-client galera-4 rsync xinetd wget ntpdate [root@controller2:/root]# systemctl enable mariadb && systemctl start mariadb [root@controller2:/root]# systemctl status mariadb [root@controller3:/root]# yum -y install MariaDB-server MariaDB-client galera-4 rsync xinetd wget ntpdate [root@controller3:/root]# systemctl enable mariadb && systemctl start mariadb [root@controller3:/root]# systemctl status mariadb
2.1.3 控制节点数据库安全设置、登陆数据库设置密码并授权
[root@controller1:/root]# mysql_secure_installation 注：Disallow root login remotely? [Y/n] n 密码：123456 [root@controller1:/root]# mysql MariaDB [(none)]> SET password=PASSWORD('123456'); //密码设置过的可以省略 Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> exit Bye [root@controller2:/root]# mysql_secure_installation 注：Disallow root login remotely? [Y/n] n 密码：123456 [root@controller2:/root]# mysql MariaDB [(none)]> SET password=PASSWORD('123456'); //密码设置过的可以省略 Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> exit Bye [root@controller3:/root]# mysql_secure_installation 注：Disallow root login remotely? [Y/n] n 密码：123456 [root@controller3:/root]# mysql MariaDB [(none)]> SET password=PASSWORD('123456'); //密码设置过的可以省略 Query OK, 0 rows affected (0.009 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'localhost' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.016 sec) MariaDB [(none)]> grant all privileges on *.* to 'mysql'@'%' identified by '123456' with grant option; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> exit Bye
2.1.4 配置MariaDB Galeria 集群 (控制节点全修改配置文件/etc/my.cnf.d/server.cnf)
[root@controller1:/root]# vim /etc/my.cnf.d/server.cnf [server] # this is only for the mysqld standalone daemon [mysqld] # # * Galera-related settings # [galera] # Mandatory settings ##wsrep_on=ON #开启全同步复制模式 wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.110.121,192.168.110.122,192.168.110.123" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 ##innodb_autoinc_lock_mode=2 #主键自增模式修改为交叉模式 ##wsrep_slave_threads=8 #开启并行复制线程，根据CPU核数设置 # # Allow server to accept connections on all interfaces. # bind-address=192.168.110.121 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 #事务提交每隔1秒刷盘 ##wsrep_certify_nonPK=ON #为没有显式申明主键的表生成一个用于certificationtest的主键，默认为ON ##query_cache_size=0 #关闭查询缓存 ##wsrep_provider_options="gcache.size=4G" #同步复制缓冲池 wsrep_provider_options="gcache.size=1G" wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_node_name=controller2 wsrep_node_address=192.168.110.121 wsrep_sst_method=rsync ##innodb_buffer_pool_size=2G # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.4 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.4] [root@controller2:/root]# vim /etc/my.cnf.d/server.cnf [server] # this is only for the mysqld standalone daemon [mysqld] # # * Galera-related settings # [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.110.122,192.168.110.121,192.168.110.123" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=192.168.110.122 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 wsrep_provider_options="gcache.size=1G" wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_node_name=controller1 wsrep_node_address=192.168.110.122 wsrep_sst_method=rsync # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.4 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.4] [root@controller3:/root]# vim /etc/my.cnf.d/server.cnf [server] # this is only for the mysqld standalone daemon [mysqld] # # * Galera-related settings # [galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.110.123,192.168.110.122,192.168.110.121" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=192.168.110.123 # # Optional setting #wsrep_slave_threads=1 #innodb_flush_log_at_trx_commit=0 wsrep_provider_options="gcache.size=1G" wsrep_cluster_name=MariaDB-Galera-Cluster wsrep_node_name=controller3 wsrep_node_address=192.168.110.123 wsrep_sst_method=rsync # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.4 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.4]
2.1.5 控制节点停止mariadb.service
[root@controller1:/root]# systemctl daemon-reload [root@controller1:/root]# systemctl stop mariadb.service [root@controller2:/root]# systemctl daemon-reload [root@controller2:/root]# systemctl stop mariadb.service [root@controller3:/root]# systemctl daemon-reload [root@controller3:/root]# systemctl stop mariadb.service
2.1.6 启动初始化集群
[root@controller1:/root]# mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql --wsrep-new-cluster & [root@controller2:/root]# systemctl start mariadb [root@controller2:/root]# systemctl status mariadb [root@controller2:/root]# systemctl enable mariadb [root@controller3:/root]# systemctl start mariadb [root@controller3:/root]# systemctl status mariadb [root@controller3:/root]# systemctl enable mariadb
注：两个节点启动成功后，在重新启动第一个节点：（删除第一个节点数据）
[root@controller1:/root]# pkill -9 mysql [1]+ Killed mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql --wsrep-new-cluster [root@controller1:/root]# rm -rf /var/lib/mysql/* [root@controller1:/root]# systemctl start mariadb.service [root@controller1:/root]# systemctl enable mariadb.service Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mariadb.service. Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mariadb.service. Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service. [root@controller1:/root]# systemctl status mariadb.service
注意：每次在第一台集群上，需要使用下面的命令来启动：
galera_new_cluster
2.2 登录数据库，查看集群状态
[root@controller1:/root]# mysql -uroot -p"123456" Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 10.4.11-MariaDB 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. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.002 sec) MariaDB [(none)]> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.002 sec) MariaDB [(none)]> show status like 'wsrep%'; +-------------------------------+---------------------------------------------------- | Variable_name | Value +-------------------------------+---------------------------------------------------- | wsrep_local_state_uuid | a2b7231d-383a-11ea-b086-83d5778583b5 | wsrep_protocol_version | 10 | wsrep_last_committed | 5 | wsrep_replicated | 0 | wsrep_replicated_bytes | 0 | wsrep_repl_keys | 0 | wsrep_repl_keys_bytes | 0 | wsrep_repl_data_bytes | 0 | wsrep_repl_other_bytes | 0 | wsrep_received | 3 | wsrep_received_bytes | 256 | wsrep_local_commits | 0 | wsrep_local_cert_failures | 0 | wsrep_local_replays | 0 | wsrep_local_send_queue | 0 | wsrep_local_send_queue_max | 1 | wsrep_local_send_queue_min | 0 | wsrep_local_send_queue_avg | 0 | wsrep_local_recv_queue | 0 | wsrep_local_recv_queue_max | 1 | wsrep_local_recv_queue_min | 0 | wsrep_local_recv_queue_avg | 0 | wsrep_local_cached_downto | 5 | wsrep_flow_control_paused_ns | 0 | wsrep_flow_control_paused | 0 | wsrep_flow_control_sent | 0 | wsrep_flow_control_recv | 0 | wsrep_cert_deps_distance | 0 | wsrep_apply_oooe | 0 | wsrep_apply_oool | 0 | wsrep_apply_window | 0 | wsrep_commit_oooe | 0 | wsrep_commit_oool | 0 | wsrep_commit_window | 0 | wsrep_local_state | 4 | wsrep_local_state_comment | Synced | wsrep_cert_index_size | 0 | wsrep_causal_reads | 0 | wsrep_cert_interval | 0 | wsrep_open_transactions | 0 | wsrep_open_connections | 0 | wsrep_incoming_addresses | AUTO,AUTO,AUTO | wsrep_cluster_weight | 3 | 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_gcomm_uuid | d5a23bb7-383a-11ea-b735-86c7a20615b4 | wsrep_applier_thread_count | 1 | wsrep_cluster_capabilities | | wsrep_cluster_conf_id | 18446744073709551615 | wsrep_cluster_size | 3 | wsrep_cluster_state_uuid | a2b7231d-383a-11ea-b086-83d5778583b5 | wsrep_cluster_status | Primary | wsrep_connected | ON | wsrep_local_bf_aborts | 0 | wsrep_local_index | 0 | wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: | | wsrep_provider_name | Galera | wsrep_provider_vendor | Codership Oy <info@codership.com> | wsrep_provider_version | 26.4.3(r4535) | wsrep_ready | ON | wsrep_rollbacker_thread_count | 1 | wsrep_thread_count | 2 +-------------------------------+--------------------------------------------- 65 rows in set (0.002 sec) MariaDB [(none)]> exit Bye
[root@controller1:/var/lib/mysql]# cat grastate.dat gvwstate.dat
# GALERA saved state version: 2.1 uuid: ab30c1f7-32df-11eb-8729-57b1b825e677 seqno: -1 safe_to_bootstrap: 0 my_uuid: fbebf07d-3901-11eb-bfa1-7e24330b874e #vwbeg view_id: 3 1804691c-32e0-11eb-8d84-9f6bddac679d 5 bootstrap: 0 member: 1804691c-32e0-11eb-8d84-9f6bddac679d 0 member: fbebf07d-3901-11eb-bfa1-7e24330b874e 0 member: b0bfcaa8-32df-11eb-b15e-12d2e6a72f24 0 #vwend
[root@controller2:/var/lib/mysql]# cat grastate.dat gvwstate.dat
# GALERA saved state version: 2.1 uuid: ab30c1f7-32df-11eb-8729-57b1b825e677 seqno: -1 safe_to_bootstrap: 0 my_uuid: d5a23bb7-383a-11ea-b735-86c7a20615b4 #vwbeg view_id: 3 1804691c-32e0-11eb-8d84-9f6bddac679d 5 bootstrap: 0 member: 1804691c-32e0-11eb-8d84-9f6bddac679d 0 member: fbebf07d-3901-11eb-bfa1-7e24330b874e 0 member: b0bfcaa8-32df-11eb-b15e-12d2e6a72f24 0 #vwend
[root@controller3:/var/lib/mysql]# cat grastate.dat gvwstate.dat
# GALERA saved state version: 2.1 uuid: ab30c1f7-32df-11eb-8729-57b1b825e677 seqno: -1 safe_to_bootstrap: 0 my_uuid: 1804691c-32e0-11eb-8d84-9f6bddac679d #vwbeg view_id: 3 1804691c-32e0-11eb-8d84-9f6bddac679d 5 bootstrap: 0 member: 1804691c-32e0-11eb-8d84-9f6bddac679d 0 member: fbebf07d-3901-11eb-bfa1-7e24330b874e 0 member: b0bfcaa8-32df-11eb-b15e-12d2e6a72f24 0 #vwend
2.3 每个控制节点/usr/bin 目录下面编辑心跳检测clustercheck脚本
[root@controller1:/root]# cd /usr/bin/ [root@controller1:/usr/bin]# wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck [root@controller1:/usr/bin]# chmod +x /usr/bin/clustercheck [root@controller1:/usr/bin]# ls -ld /usr/bin/clustercheck -rwxr-xr-x. 1 root root 3882 Jul 10 16:18 /usr/bin/clustercheck [root@controller2:/root]# cd /usr/bin/ [root@controller2:/usr/bin]# wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck [root@controller2:/usr/bin]# chmod +x /usr/bin/clustercheck [root@controller2:/usr/bin]# ls -ld /usr/bin/clustercheck -rwxr-xr-x. 1 root root 3882 Jul 10 16:19 /usr/bin/clustercheck [root@controller3:/root]# cd /usr/bin/ [root@controller3:/usr/bin]# wget https://raw.githubusercontent.com/olafz/percona-clustercheck/master/clustercheck [root@controller3:/usr/bin]# chmod +x /usr/bin/clustercheck [root@controller3:/usr/bin]# ls -ld /usr/bin/clustercheck -rwxr-xr-x. 1 root root 3882 Jul 10 16:20 /usr/bin/clustercheck