centos7安装mariadb集群

1740939793.jpg

环境准备

1、准备三台Centos7虚拟机环境
20190307182121.png

机器列表
192.168.48.211
192.168.48.212
192.168.48.213

2、设置主机名(设置三台虚拟机主机名分别为node1,node2,node3)

1
2
3
4
# vim /etc/hosts
192.168.48.211 node1
192.168.48.212 node2
192.168.48.213 node3

3、关闭SELINUX

1
2
# setenforce 0
# sed -i 's,^SELINUX=enforcing,SELINUX=disabled,g' /etc/selinux/config

4、关闭防火墙

1
2
systemctl stop firewalld.service
systemctl disable firewalld.service

5、使用以下命令快速添加YUM源

1
2
3
4
5
6
7
# 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

6、由于Mariadb服务器是在国外,速度较慢,可以使用国内镜像源替代,以USTC镜像源为例

1
# sed -i 's#yum\.mariadb\.org#mirrors.ustc.edu.cn/mariadb/yum#' /etc/yum.repos.d/mariadb.repo

7、刷新YUM缓存

1
# yum makecache

8、查看Mariadb相关的安装包,注意软件包版本和对应的YUM源名字

1
# yum list MariaDB* galera

20190307182935.png

部署MariaDB Galera集群

执行yum安装命令(执行节点:node1, node2, node3)

1
# yum install -y MariaDB-server MariaDB-client galera

待安装完成,启动数据库,并设置root账号权限密码(执行节点:node1, node2, node3)

1
2
# systemctl start mariadb
# mysql -uroot -e "grant all privileges on *.* to 'root'@'localhost' identified by 'root';flush privileges;"

也可使用如下方法设置数据库密码,测试情况使用 root % root

1
# /usr/bin/mysql_secure_installation

设置完成之后关闭数据库

1
# systemctl stop mariadb

配置node1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# cat /etc/my.cnf.d/server.cnf | grep -v '#'

[server]

[mysqld]

[galera]

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.48.211,192.168.48.212,192.168.48.213"
wsrep_node_name = node1
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.48.211
wsrep_sst_method=rsync

wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_auth=root:root

[embedded]

[mariadb]

[mariadb-10.1]

分别配置node2,node3,修改点为当前节点名称,当前节点ip地址:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# cat /etc/my.cnf.d/server.cnf | grep -v '#'

[server]

[mysqld]

[galera]

wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.48.211,192.168.48.212,192.168.48.213"
wsrep_node_name = node2
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.48.212
wsrep_sst_method=rsync

wsrep_slave_threads=1
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1024M
wsrep_sst_auth=root:root

[embedded]

[mariadb]

[mariadb-10.1]

20190307184819.png

在node1上执行如下命令,启动一个集群

1
# /usr/sbin/mysqld --wsrep-new-cluster --user=root &

查看集群状态

1
2
3
4
5
6
7
8
9
# mysql -uroot -proot

MariaDB [(none)]> show status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 1 |
+--------------------+-------+
1 row in set (0.01 sec)

在node2,node3节点上直接执行如下命令

1
systemctl start mariadb

再次查看集群状态,可以看到2,3节点已成功加入集群

1
2
3
4
5
6
7
MariaDB [(none)]> show  status like "wsrep_cluster_size";
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)

集群数据同步测试

在集群中任一一台主机执行如下命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [(none)]> create database marspie;
Query OK, 1 row affected (0.28 sec)

MariaDB [(none)]> use marspie;
Database changed
MariaDB [marspie]> create table user(id int primary key,name varchar(64));
Query OK, 0 rows affected (0.16 sec)

MariaDB [marspie]> show tables;
+-------------------+
| Tables_in_marspie |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)

MariaDB [marspie]> insert into user values(1,'alex');
Query OK, 1 row affected (0.02 sec)

MariaDB [marspie]>

查看另外两台主机,我们可以发现数据已经同步了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| marspie |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> use marspie;
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 [marspie]> show tables;
+-------------------+
| Tables_in_marspie |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)

MariaDB [marspie]> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | alex |
+----+------+
1 row in set (0.00 sec)

MariaDB [marspie]> insert into user values(2,'kiven');
Query OK, 1 row affected (0.21 sec)

MariaDB [marspie]>

常见问题解析

1、在生产环境中必须打开防火墙,如果只开放了 4567 和 针对指定ip开放3306 端口,会导致加入集群失败,需要开放
3306, 4444, 4567, 4568 四个端口才可以正常启动。

2、直接kill点创建集群节点,会导致此节点无法启动
20190307191741.png

  • 第一个启动的节点,在集群关闭数据库时需最后一个停止,再次启动集群是才可正常启动。
  • 若第一个启动的节点被kill, 停止所有节点,再次启动第一个节点还是会启动失败,这时需进入mysql数据目录删除galera缓存文件,方可启动集群
    1
    2
    3
    [root@node1 ~]# cd /var/lib/mysql/
    [root@node1 mysql]# rm -rf grastate.dat gvwstate.dat galera.cache
    [root@node1 mysql]# /usr/sbin/mysqld --wsrep-new-cluster --user=root

后续如还有坑再补充!

-------------本文结束感谢您的阅读-------------
Alex.Yao wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!