################################################################################
## MariaDB群集安装 参考脚本 by johnlaw 2017-02-18 ##
CentOS7+MariaDB+ galera
################################################################################
一、规划机器列表 #############################################################################################
两台数据库实现双主
1) 192.168.170.156 --> MDB01
2) 192.168.170.157 --> MDB02
二、第一个节点安装及配置##########################################################################################
1、关闭SELINUX
修改/etc/selinux/config文件中的SELINUX="" 为 disabled ,然后重启。
关闭防火墙
[root@MDB01 ~]# systemctl stop firewalld.service
[root@MDB01 ~]# systemctl disable firewalld.service
2、设置mariadb的yum源并安装(所有节点都要)
修改yum源文件
vi /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
enabled=0
3、安装数据及服务组件
[root@MDB01 ~]# yum --enablerepo=mariadb -y install MariaDB-server galera MariaDB-client rsync
4、配置 第一个节点[192.168.170.156 MDB01]
--1、建立Cluster使用者,密码及用户(现用默认root用户,设置远程登录)
[root@MDB01 ~]# mysqladmin -u root flush-privileges password 'ayilian'
[root@MDB01 ~]# mysql -uroot -p
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> grant all privileges on *.* to root@'%' identified by 'ayilian';
[root@MDB01 ~]# vi /etc/my.cnf.d/server.cnf
--2、主节点的配置文件server.cnf
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON ##开启wsrep服务
wsrep_provider=/usr/lib64/galera/libgalera_smm.so ##加入/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
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
# add follows
# cluster name
wsrep_cluster_name="MariaDB_Cluster"
# own IP address(当前节点的IP)
wsrep_node_address="192.168.170.156"
wsrep_node_name='radiusone'
# replication provider
wsrep_sst_method=rsync
--3、启动集群:/bin/galera_new_cluster
[root@MDB01 ~]# /bin/galera_new_cluster
注:(第二次启动动用
[root@MDB01 ~]# systemctl restart mariadb)
--4、确认MySQL的3306端口和wsrep的4567端口处于监听状态
[root@MDB01 ~]# netstat -tulpn | grep -e 4567 -e 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2332/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2332/mysqld
主:4567是用来同步时用的很重要。#############
三、第二个节点安装及配置其它节点 ##########################################################################################
(192.168.170.157 )
1、关闭SELINUX
修改/etc/selinux/config文件中的SELINUX="" 为 disabled ,然后重启。
关闭防火墙
[root@MDB02 ~]# systemctl stop firewalld.service
[root@MDB02 ~]# systemctl disable firewalld.service
2、设置mariadb的yum源并安装(所有节点都要)
修改yum源文件
vi /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
enabled=0
3、安装数据及服务组件
[root@MDB02 ~]# yum --enablerepo=mariadb -y install MariaDB-server galera MariaDB-client rsync
4、配置 第一个 节点[192.168.170.156 MDB01]
--1、建立Cluster使用者,密码及用户(现用默认root用户,设置远程登录)
[root@MDB02 ~]# mysqladmin -u root flush-privileges password 'ayilian'
[root@MDB02 ~]# mysql -uroot -p
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> grant all privileges on *.* to root@'%' identified by 'ayilian';
[root@MDB02 ~]# vi /etc/my.cnf.d/server.cnf
--2、从节点的配置文件server.cnf
[mysqld]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON ##开启动wsrep功能
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.170.156:4567" ##配置指向主节点,端口很重要 4567
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
# add follows
# cluster name
wsrep_cluster_name="MariaDB_Cluster"
# own IP address(当前节点的主机名和IP)
wsrep_node_name='mdb02'
wsrep_node_address="192.168.170.157"
# replication provider
wsrep_sst_method=rsync
wsrep_sst_auth=root:ayilian
# this is only for embedded server
[embedded]
--3、启动集群数据库
[root@MDB02 ~]# systemctl restart mariadb
--4、确认MySQL的3306端口和wsrep的4567端口处于监听状态
[root@MDB02 ~]# netstat -tulpn | grep -e 4567 -e 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2332/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2332/mysqld
三、其它节点同第二节点安装一样 ################################################################################
以下非常重要
wsrep_cluster_address="gcomm://192.168.170.156:4567" ##配置指向主节点,端口很重要 4567
四、测试集群状态 ################################################################################
登录两个节点测试
[root@MDB02 ~]# mysql -uroot -p
MariaDB [(none)]> show status like 'wsrep%';
如果 "wsrep_local_state_comment" is "Synced" 则表明集群创建成功
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+-------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 2.000000 |
| wsrep_cert_index_size | 5 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | d10b0988-ed49-11e6-bbb5-163b5f87c5a0 |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.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 | 02e43ded-ee6b-11e6-a0bf-83c5163e6ed3 |
| wsrep_incoming_addresses | 192.168.170.157:3306,192.168.170.156:3306 |
| wsrep_last_committed | 13 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 11 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 1 |
| 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 | d10b0988-ed49-11e6-bbb5-163b5f87c5a0 |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.19(r3667) |
| wsrep_ready | ON |
| wsrep_received | 5 |
| wsrep_received_bytes | 648 |
| wsrep_repl_data_bytes | 99 |
| wsrep_repl_keys | 3 |
| wsrep_repl_keys_bytes | 47 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 210 |
| wsrep_thread_count | 2 |
+------------------------------+-------------------------------------------+
## MariaDB群集安装 参考脚本 by johnlaw 2017-02-18 ##
CentOS7+MariaDB+ galera
################################################################################
一、规划机器列表 #############################################################################################
两台数据库实现双主
1) 192.168.170.156 --> MDB01
2) 192.168.170.157 --> MDB02
二、第一个节点安装及配置##########################################################################################
1、关闭SELINUX
修改/etc/selinux/config文件中的SELINUX="" 为 disabled ,然后重启。
关闭防火墙
[root@MDB01 ~]# systemctl stop firewalld.service
[root@MDB01 ~]# systemctl disable firewalld.service
2、设置mariadb的yum源并安装(所有节点都要)
修改yum源文件
vi /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
enabled=0
3、安装数据及服务组件
[root@MDB01 ~]# yum --enablerepo=mariadb -y install MariaDB-server galera MariaDB-client rsync
4、配置 第一个节点[192.168.170.156 MDB01]
--1、建立Cluster使用者,密码及用户(现用默认root用户,设置远程登录)
[root@MDB01 ~]# mysqladmin -u root flush-privileges password 'ayilian'
[root@MDB01 ~]# mysql -uroot -p
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> grant all privileges on *.* to root@'%' identified by 'ayilian';
[root@MDB01 ~]# vi /etc/my.cnf.d/server.cnf
--2、主节点的配置文件server.cnf
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON ##开启wsrep服务
wsrep_provider=/usr/lib64/galera/libgalera_smm.so ##加入/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"
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
# add follows
# cluster name
wsrep_cluster_name="MariaDB_Cluster"
# own IP address(当前节点的IP)
wsrep_node_address="192.168.170.156"
wsrep_node_name='radiusone'
# replication provider
wsrep_sst_method=rsync
--3、启动集群:/bin/galera_new_cluster
[root@MDB01 ~]# /bin/galera_new_cluster
注:(第二次启动动用
[root@MDB01 ~]# systemctl restart mariadb)
--4、确认MySQL的3306端口和wsrep的4567端口处于监听状态
[root@MDB01 ~]# netstat -tulpn | grep -e 4567 -e 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2332/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2332/mysqld
主:4567是用来同步时用的很重要。#############
三、第二个节点安装及配置其它节点 ##########################################################################################
(192.168.170.157 )
1、关闭SELINUX
修改/etc/selinux/config文件中的SELINUX="" 为 disabled ,然后重启。
关闭防火墙
[root@MDB02 ~]# systemctl stop firewalld.service
[root@MDB02 ~]# systemctl disable firewalld.service
2、设置mariadb的yum源并安装(所有节点都要)
修改yum源文件
vi /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
enabled=0
3、安装数据及服务组件
[root@MDB02 ~]# yum --enablerepo=mariadb -y install MariaDB-server galera MariaDB-client rsync
4、配置 第一个 节点[192.168.170.156 MDB01]
--1、建立Cluster使用者,密码及用户(现用默认root用户,设置远程登录)
[root@MDB02 ~]# mysqladmin -u root flush-privileges password 'ayilian'
[root@MDB02 ~]# mysql -uroot -p
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> grant all privileges on *.* to root@'%' identified by 'ayilian';
[root@MDB02 ~]# vi /etc/my.cnf.d/server.cnf
--2、从节点的配置文件server.cnf
[mysqld]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON ##开启动wsrep功能
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.170.156:4567" ##配置指向主节点,端口很重要 4567
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
# add follows
# cluster name
wsrep_cluster_name="MariaDB_Cluster"
# own IP address(当前节点的主机名和IP)
wsrep_node_name='mdb02'
wsrep_node_address="192.168.170.157"
# replication provider
wsrep_sst_method=rsync
wsrep_sst_auth=root:ayilian
# this is only for embedded server
[embedded]
--3、启动集群数据库
[root@MDB02 ~]# systemctl restart mariadb
--4、确认MySQL的3306端口和wsrep的4567端口处于监听状态
[root@MDB02 ~]# netstat -tulpn | grep -e 4567 -e 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2332/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2332/mysqld
三、其它节点同第二节点安装一样 ################################################################################
以下非常重要
wsrep_cluster_address="gcomm://192.168.170.156:4567" ##配置指向主节点,端口很重要 4567
四、测试集群状态 ################################################################################
登录两个节点测试
[root@MDB02 ~]# mysql -uroot -p
MariaDB [(none)]> show status like 'wsrep%';
如果 "wsrep_local_state_comment" is "Synced" 则表明集群创建成功
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+-------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------+
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 2.000000 |
| wsrep_cert_index_size | 5 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | d10b0988-ed49-11e6-bbb5-163b5f87c5a0 |
| wsrep_cluster_status | Primary |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.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 | 02e43ded-ee6b-11e6-a0bf-83c5163e6ed3 |
| wsrep_incoming_addresses | 192.168.170.157:3306,192.168.170.156:3306 |
| wsrep_last_committed | 13 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 11 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 1 |
| 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 | d10b0988-ed49-11e6-bbb5-163b5f87c5a0 |
| wsrep_protocol_version | 7 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.19(r3667) |
| wsrep_ready | ON |
| wsrep_received | 5 |
| wsrep_received_bytes | 648 |
| wsrep_repl_data_bytes | 99 |
| wsrep_repl_keys | 3 |
| wsrep_repl_keys_bytes | 47 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 210 |
| wsrep_thread_count | 2 |
+------------------------------+-------------------------------------------+