我们在本篇文章将配置一个HA（高可用性）多主数据库群集。
VMware® Workstation 12 Pro
CentOS 7最小化的系统镜像
CentOS-7-x86_64-Minimal-1503-01.iso
hostname ip address node name node address cluster name
contoso21.com 192.168.10.21 db1 192.168.10.21 galera1_cluster
contoso22.com 192.168.10.22 db2 192.168.10.22 galera1_cluster
contoso23.com 192.168.10.23 db3 192.168.10.23 galera1_cluster
第1步: 建立主机IP地址与主机名称的映射关系
[root@contoso21 ~]# cat > /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 contoso21.com
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.21 contoso21.com
[root@contoso22 ~]# cat > /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 contoso22.com
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.22 contoso22.com
[root@contoso23 ~]# cat > /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 contoso23.com
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.10.23 contoso23.com
第2步: 添加MariaDB 软件包仓库
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
tee /etc/yum.repos.d/MariaDB.repo <<-'EOF'
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
第3步: – 设置selinux的disabled模式 setenforce 0，并且关闭防火墙服务
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
cat > /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
systemctl stop firewalld && systemctl disable firewalld
init 6 ## reboot
第4步: – 安装MariaDB Galera群集10.1版软件包
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
yum -y install socat deltarpm && yum install -y MariaDB-server MariaDB-client
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
mkdir -p /var/log/mariadb
touch /var/log/mariadb/mariadb-slow.log
touch /var/log/mariadb/queries.log
chmod 666 /var/log/mariadb/mariadb-slow.log
chmod 666 /var/log/mariadb/queries.log
第5步: 配置MariaDB数据库的安全性 systemctl status mysql -l 命令可以查看服务的完整运行状态
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
/usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 刚刚安装的MariaDB没有密码,此处直接回车便可成功连接数据库.
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 123456
Re-enter new password: 123456
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
第6步: 创建MariaDB Galera群集需要用到的多个用户
Now, we have to create some users that must be able to access the database.
The ‘sst_user’ is the user which a database node will use for authenticating to
another database node in the State Transfer Snapshot (SST) phase.
Run the following command on all nodes:
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令:
mysql -uroot -p123456 -h127.0.0.1
在db1 MariaDB [(none)]> 和 db2 MariaDB [(none)]> 还有 db2 MariaDB [(none)]>
上分别执行如下SQL命令:
USE mysql;
SHOW DATABASES;
SELECT host,user FROM user;
DELETE FROM mysql.user WHERE user='';
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
GRANT USAGE ON *.* to sst_user@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES on *.* to sst_user@'%';
FLUSH PRIVILEGES;
SELECT host,user FROM user;
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE '%collation%';
QUIT
建议你使用能够访问数据库的主机名称或者IP地址替代‘%’ ,因为‘%’ 的意思是允许用户
root或者sst_user从任何主机上访问数据库，因此安全性较差.
第7步: 修改MariaDB Galera群集所有节点的配置文件,设置MariaDB数据库的默认编码格式和表名称的小写格式，
配置查询日志和慢查询日志,关闭所有节点的mysql服务
在[root@contoso21 ~]# 和 [root@contoso22 ~]# 还有 [root@contoso23 ~]#
上分别执行如下终端命令：
service mysql stop
[root@contoso21 ~]# cat > /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower_case_table_names=1
general-log=ON
general-log-file=/var/log/mariadb/queries.log
log-output=file
slow-query-log=ON
slow-query-log-file=/var/log/mariadb/mariadb-slow.log
long_query_time=1
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera1_cluster'
wsrep_node_address='192.168.10.21'
wsrep_node_name='db1'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# 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.1 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.1]
[root@contoso22 ~]# cat > /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower_case_table_names=1
general-log=ON
general-log-file=/var/log/mariadb/queries.log
log-output=file
slow-query-log=ON
slow-query-log-file=/var/log/mariadb/mariadb-slow.log
long_query_time=1
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.10.21'
wsrep_cluster_name='galera1_cluster'
wsrep_node_address='192.168.10.22'
wsrep_node_name='db2'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# 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.1 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.1]
[root@contoso23 ~]# cat > /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower_case_table_names=1
general-log=ON
general-log-file=/var/log/mariadb/queries.log
log-output=file
slow-query-log=ON
slow-query-log-file=/var/log/mariadb/mariadb-slow.log
long_query_time=1
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.10.21'
wsrep_cluster_name='galera1_cluster'
wsrep_node_address='192.168.10.23'
wsrep_node_name='db3'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# 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.1 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.1]
第8步: 初始化MariaDB Galera群集中第一个节点
使用专门的参数项 ‘ --wsrep-new-cluster’ 来启动MariaDB Gelera群集,
只在db1节点（第1个节点）上如此操作,以便群集主要的节点被初始化.
[root@contoso21 ~]# /etc/init.d/mysql start --wsrep-new-cluster
[root@contoso21 ~]# mysql -uroot -p123456 -e"show status like 'wsrep%'"
wsrep_local_state_comment | Synced <-- cluster is synced
wsrep_incoming_addresses | 192.168.10.21:3306 <-- node db1 is a provider
wsrep_cluster_size | 1 <-- cluster consists of 1 node
wsrep_ready | ON <-- good :)
[root@contoso21 ~]# /etc/init.d/mysql start --wsrep-new-cluster
Starting mysql (via systemctl): [ OK ]
[root@contoso21 ~]# mysql -uroot -p123456 -e"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 | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 8.91e-07/6.818e-06/1.9412e-05/6.92271e-06/5 |
| 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 | b47891c2-1ad6-11e6-b7ef-66d1299a9d50 |
| wsrep_incoming_addresses | 192.168.10.21: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 | 0 |
| 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 | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.15(r3578) |
| wsrep_ready | ON |
| wsrep_received | 2 |
| wsrep_received_bytes | 140 |
| 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 | 2 |
+------------------------------+---------------------------------------------+
第9步: 向已经启动的MariaDB Galera群集添加另外两个节点db2和db3
检查和确认db2和db3两个节点的配置文件/etc/my.cnf.d/server.cnf 内部的[galera]
下面配置的所有参数是否正确，db2和db3两个节点的服务就如同像启动普通服务一样去启动MariaDB Galera节点服务.
[root@contoso22 ~]# service mysql start
通过在db1和db2节点上执行如下命令，观察群集状态中已经变化参数值内容:
[root@contoso22 ~]# mysql -uroot -p123456 -e"show status like 'wsrep%'"
至此，你会发现节点db2已被识别，群集的大小已经是2个节点，并且节点db2的IP地址已被列出：
wsrep_local_state_comment | Synced
wsrep_incoming_addre sses | 192.168.10.21:3306,192.168.10.22:3306
wsrep_cluster_size | 2
wsrep_connected | ON
wsrep_ready | ON
[root@contoso22 ~]# mysql -uroot -p123456 -e"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 | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0.000227382/0.000654379/0.00225715/0.000659168/7 |
| 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 | 8dc1b4b3-1ad7-11e6-8bf0-9a0cfaca8d40 |
| wsrep_incoming_addresses | 192.168.10.22:3306,192.168.10.21: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 | 0 |
| 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.333333 |
| wsrep_local_send_queue_max | 2 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.15(r3578) |
| wsrep_ready | ON |
| wsrep_received | 3 |
| wsrep_received_bytes | 216 |
| 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 | 2 |
+------------------------------+--------------------------------------------------+
同样地在节点db3上执行如下2个命令:
[root@contoso23 ~]# service mysql start #
[root@contoso23 ~]# mysql -uroot -p123456 -e"show status like 'wsrep%'"
至此，你应该会发现节点db3已被识别，群集的大小已经是3个节点，并且节点db3的IP地址已被列出：
wsrep_local_state_comment | Synced
wsrep_incoming_addresses | 192.168.10.22:3306,192.168.10.21:3306,192.168.10.23:3306
wsrep_cluster_size | 3
wsrep_connected | ON
wsrep_ready | ON
[root@contoso23 ~]# mysql -uroot -p123456 -e"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 | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| 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 | 23817fc4-1ad8-11e6-8ec5-3340a442db9a |
| wsrep_incoming_addresses | 192.168.10.23:3306,192.168.10.22:3306,192.168.10.21: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 | 0 |
| 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 | b47976ee-1ad6-11e6-b848-c6770ef480cb |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.15(r3578) |
| wsrep_ready | ON |
| wsrep_received | 3 |
| wsrep_received_bytes | 284 |
| 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 | 2 |
+------------------------------+----------------------------------------------------------+
第10步: 测试验证群集的复制功能
现在群集正在运行,让我们测试一下它是否能正常工作.在db1节点（contoso21）上创建一个‘clustertest’ 数据库，通过如下命令:
[root@contoso21 ~]# mysql -uroot -p123456 -e 'SELECT * FROM clustertest.mycluster;'
[root@contoso21 ~]# mysql -uroot -p123456 -e 'CREATE DATABASE clustertest;'
[root@contoso21 ~]# mysql -uroot -p123456 -e 'CREATE TABLE clustertest.mycluster ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), birth DATE, PRIMARY KEY(id));'
[root@contoso21 ~]# mysql -uroot -p123456 -e 'INSERT INTO clustertest.mycluster (name, birth) VALUES ("Tim", "1989-12-27");'
[root@contoso21 ~]# mysql -uroot -p123456 -e 'SELECT * FROM clustertest.mycluster;'
+----+------+------------+
| id | name | birth |
+----+------+------------+
| 3 | Tim | 1989-12-27 |
+----+------+------------+
[root@contoso22 ~]# mysql -uroot -p123456 -e 'SELECT * FROM clustertest.mycluster;'
+----+------+------------+
| id | name | birth |
+----+------+------------+
| 3 | Tim | 1989-12-27 |
+----+------+------------+
[root@contoso23 ~]# mysql -uroot -p123456 -e 'SELECT * FROM clustertest.mycluster;'
+----+------+------------+
| id | name | birth |
+----+------+------------+
| 3 | Tim | 1989-12-27 |
+----+------+------------+
从以上输出的结果我们能断定群集是正常工作的，群集通过了节点db1实现了与所有节点之间的复制操作.
当我们群集关闭群集时，按照逆向启动顺序去关闭节点
先关闭db3节点,，然后关闭db2节点，最后关闭db1节点
当我们再一次启动群集时，按照以下命令顺序启动群集：
[root@contoso21 ~]# /etc/init.d/mysql start --wsrep-new-cluster ## 第1步，第1个节点必须如此启动
[root@contoso22 ~]# service mysql start ## 第2步
[root@contoso23 ~]# service mysql start ## 第3步
[root@contoso21 ~]# systemctl status mysql -l
[root@contoso22 ~]# systemctl status mysql -l
[root@contoso23 ~]# systemctl status mysql -l
KeepAlived + HaProxy的高可用性方案配置
hostname ip address VIP
loadbalance1.com 192.168.100.55 192.168.10.200
loadbalance2.com 192.168.100.66 192.168.10.200
[root@loadbalance1 ~]# ss -anop | column
[root@loadbalance2 ~]# ss -anop | column
使用vi命令删除全部被编辑文件的内容
第1步：输入 G 光标跳到文件末尾
第2步：输入 :1,.d 回车
当我们分配一个IP地址192.168.10.200当作虚拟IP地址(VIP)来使用的时候.
我们需要做的第一件事情是告诉操作系统的内核,我们计划将IP地址绑定到没有被定义的接口上.
为了达到这个目的我们需要编辑配置文件/etc/sysctl.conf，
并且在该文件末尾添加如下行:
net.ipv4.ip_nonlocal_bind=1
[root@loadbalance1 ~]# vi /etc/sysctl.conf
net.ipv4.ip_nonlocal_bind=1
[root@loadbalance2 ~]# vi /etc/sysctl.conf
net.ipv4.ip_nonlocal_bind=1
然后，我们需要执行如下命令在不重新启动系统的情况下能够使VIP设置生效:
[root@loadbalance1 ~]# sysctl -p
[root@loadbalance2 ~]# sysctl -p
[root@loadbalance1 ~]# vi /etc/hosts
192.168.100.55 loadbalance1.com loadbalance1
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@loadbalance2 ~]# vi /etc/hosts
192.168.100.66 loadbalance2.com loadbalance2
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@loadbalance1 ~]# vi /etc/selinux/config
# disabled - No SELinux policy is loaded.
SELINUX=disabled
[root@loadbalance2 ~]# vi /etc/selinux/config
# disabled - No SELinux policy is loaded.
SELINUX=disabled
[root@loadbalance1 ~]# systemctl stop firewalld && systemctl disable firewalld
[root@loadbalance2 ~]# systemctl stop firewalld && systemctl disable firewalld
[root@loadbalance1 ~]# yum -y install keepalived haproxy
[root@loadbalance2 ~]# yum -y install keepalived haproxy
[root@loadbalance1 ~]# cat /etc/keepalived/keepalived.conf
[root@loadbalance2~]# cat /etc/keepalived/keepalived.conf
[root@loadbalance1 ~]# vi /etc/keepalived/keepalived.conf
global_defs {
notification_email {
14588019@qq.com
}
notification_email_from 14588019@qq.com
smtp_server localhost
smtp_connect_timeout 30
router_id loadbalance1.com
}
vrrp_script haproxy {
script "killall -0 haproxy"
interval 2
weight 2
}
vrrp_instance 50 {
virtual_router_id 50
advert_int 1
priority 101
state MASTER
interface eno16777736
virtual_ipaddress {
192.168.10.200 dev eno16777736
}
track_script {
haproxy
}
}
[root@loadbalance2 ~]# vi /etc/keepalived/keepalived.conf
global_defs {
notification_email {
14588019@qq.com
}
notification_email_from 14588019@qq.com
smtp_server localhost
smtp_connect_timeout 30
router_id loadbalance2.com
}
vrrp_script haproxy {
script "killall -0 haproxy"
interval 2
weight 2
}
vrrp_instance 50 {
virtual_router_id 50
advert_int 1
priority 99
state SLAVE
interface eno16777736
virtual_ipaddress {
192.168.10.200 dev eno16777736
}
track_script {
haproxy
}
}
[root@loadbalance1 ~]# vi /etc/haproxy/haproxy.cfg
global
chroot /var/lib/haproxy
daemon
group haproxy
log 192.168.100.55 local2
maxconn 4000
pidfile /var/run/haproxy.pid
stats socket /var/lib/haproxy/stats
user haproxy
defaults
log global
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
listen mariadb-cluster
bind 192.168.10.200:3306
mode tcp
balance roundrobin
server mariadb1 192.168.10.21:3306
server mariadb2 192.168.10.22:3306
server mariadb3 192.168.10.23:3306
[root@loadbalance2 ~]# vi /etc/haproxy/haproxy.cfg
global
chroot /var/lib/haproxy
daemon
group haproxy
log 192.168.10.66 local2
maxconn 4000
pidfile /var/run/haproxy.pid
stats socket /var/lib/haproxy/stats
user haproxy
defaults
log global
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
listen mariadb-cluster
bind 192.168.10.200:3306
mode tcp
balance roundrobin
server mariadb1 192.168.10.21:3306
server mariadb2 192.168.10.22:3306
server mariadb3 192.168.10.23:3306
[root@loadbalance1 ~]# vi /etc/default/haproxy
ENABLED=1
[root@loadbalance2 ~]# vi /etc/default/haproxy
ENABLED=1
[root@loadbalance1 ~]# systemctl enable keepalived
[root@loadbalance2 ~]# systemctl enable keepalived
[root@loadbalance1 ~]# systemctl restart keepalived
[root@loadbalance2 ~]# systemctl restart keepalived
[root@loadbalance1 ~]# systemctl enable haproxy
[root@loadbalance2 ~]# systemctl enable haproxy
[root@loadbalance1 ~]# systemctl restart haproxy
[root@loadbalance2 ~]# systemctl restart haproxy
show status like 'wsrep%';
show variables like 'wsrep_node%';
[root@loadbalance1 ~]# cat > /etc/yum.repos.d/MariaDB.repo
[root@loadbalance2 ~]# cat > /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@loadbalance1 ~]# yum -y install MariaDB-client
[root@loadbalance2 ~]# yum -y install MariaDB-client
[root@loadbalance1 ~]# mysql -uroot -p123456 -h192.168.10.200 -e"show variables like 'wsrep_node%'"
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.21 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db1 |
+-----------------------------+-----------------+
[root@loadbalance1 ~]# mysql -uroot -p123456 -h192.168.10.200 -e"show variables like 'wsrep_node%'"
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10 .22 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db2 |
+-----------------------------+-----------------+
[root@loadbalance1 ~]# mysql -uroot -p123456 -h192.168.10.200 -e"show variables like 'wsrep_node%'"
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.23 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db3 |
+-----------------------------+-----------------+
[root@loadbalance1 ~]# for i in `seq 1 6`
do
mysql -uroot -p123456 -h192.168.10.200 -e"show variables like 'wsrep_node%'"
done
[root@loadbalance1 ~]# for i in `seq 1 6`
> do
> mysql -uroot -p123456 -h192.168.10.200 -e"show variables like 'wsrep_node%'"
> done
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.21 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db1 |
+-----------------------------+-----------------+
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.22 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db2 |
+-----------------------------+-----------------+
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.23 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db3 |
+-----------------------------+-----------------+
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.21 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db1 |
+-----------------------------+-----------------+
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.22 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db2 |
+-----------------------------+-----------------+
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| wsrep_node_address | 192.168.10.23 |
| wsrep_node_incoming_address | AUTO |
| wsrep_node_name | db3 |
+-----------------------------+-----------------+
配置haproxy日志功能
[root@loadbalance1 ~]# vi /etc/rsyslog.conf
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514
local2.* /var/log/haproxy.log
[root@loadbalance1 ~]# vi /etc/sysconfig/rsyslog
SYSLOGD_OPTIONS="-r -m 0"
[root@loadbalance1 ~]# systemctl restart rsyslog
[root@loadbalance1 ~]# tail -f /var/log/haproxy.log
129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
