[高可用]Ubuntu搭建高可用的MariaDB Galera数据库集群,mariadbgalera
基于OpenStack的搭建过程中,所有的组件都是存储在MySQL数据库中,既然要考虑到整个OpenStack的高可用性,我们需要对每一个组件、节点进行考虑,今天就先对MySQL数据库的集群进行实验。
搭建MySQL的集群,参考了比较常用的MariaDB Galera集群方案,由于Oracle公司收购了MySQL,很多开源爱好者担心Oracle公司随时有闭源的可能,所以MariaDB是MySQL开发的一个分支,在使用过程中可以把它当成MySQL,任何命令都没有任何变化,只是名称有所改变。
对于OpenStack来说,在Icehouse版本还沿用了MySQL数据库,但是在Juno版本,已经沿用了MariaDB,可见OpenStack也随时考虑自己的完全开源的问题。
关于MariaDB Galera集群的特点:
也具有下列的相关优势
一般情况下,大家看到的经典的MariaDB Galera集群架构图都是至少包含三个节点
为什么至少要三个节点呢?因为如果只有两个节点,一旦出现数据不统一,会发生脑裂现象,也就是系统并不知道到底以哪一个节点的结果为正确的结果,引入第三个节点,提供一个仲裁功能,确保了数据的准确性和统一性参考性。
我的实验测试环境也为三个节点,操作系统为Ubuntu 14.04
1、python1:192.168.3.108
2、Python2:192.168.3.107
3、Python3:192.168.3.133
需要注意的是:
1、服务器最好有交换分区,特别是在数据库状态转移期间,是需要缓存的
2、服务器设置的日志类型必须是文件类型,不能是CSV或者其他,而且Query cache必须禁用。
执行过程
1、安装Maria DB Galera软件包(分别在三个节点执行)
sudo apt-get install software-properties-common
然后添加MariaDB的仓库镜像,本例使用的清华的镜像,具体配置如下
sm@python1:~$ sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --homedir /tmp/tmp.jqXpEbuAw5 --no-auto-check-trustdb --trust-model always --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
gpg: requesting key 1BB943DB from hkp server keyserver.ubuntu.com
gpg: key 1BB943DB: public key "MariaDB Package Signing Key <package-signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg: imported: 1
添加配置源到/etc/apt/source.list文件中
sudo add-apt-repository 'deb http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.0/ubuntu trusty main'
查看一下添加后的源列表信息
sm@python1:~$ sudo cat /etc/apt/sources.list
deb http://mirrors.163.com/ubuntu/ trusty main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-security main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-updates main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ trusty-backports main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-security main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-updates main restricted universe multiverse
deb-src http://mirrors.163.com/ubuntu/ trusty-proposed main restricted universe multiverse
deb http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.0/ubuntu trusty main
# deb-src http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.0/ubuntu trusty main
deb-src http://mirrors.163.com/ubuntu/ trusty-backports main restricted universe multiverse
接下来,更新一下源列表信息
sudo apt-get update
安装MariaDB数据库和rsync软件
sudo apt-get install rsync mariadb-galera-server
注意:mariadb应该安装集成galera的版本,rsync是用来同步数据库所使用的工具。
2、设置Galera(三个节点都需要操作)
创建文件sudo vi /etc/mysql/conf.d/galera.cnf,该文件需要自己创建,原来不存在,然后输入以下内容
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address = 0.0.0.0
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="mytest_wsrep_cluster"
wsrep_cluster_address="gcomm://192.168.3.107,192.168.3.108,192.168.3.133"
wsrep_sst_method=rsync
注意:需要更新自己的MySQL集群节点IP地址和集群名称
启动集群
先将所有节点的集群mySQL服务停掉
sudo service mysql stop
1、启动第一个节点,第一个节点启动服务,必须添加-wsrep-new-cluster选项,以创建集群
sm@python1:~$ sudo service mysql stop
* Stopping MariaDB database server mysqld [ OK ]
sm@python1:~$ sudo service mysql start --wsrep-new-cluster
* Starting MariaDB database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
2、验证集群
sm@python1:~$ mysql -uroot -proot -e 'select variable_value from information_schema.global_status where variable_name="wsrep_cluster_size"'
+----------------+
| variable_value |
+----------------+
| 1 |
+----------------+
陆续启动其他节点集群,再次验证
sm@python1:~$ mysql -uroot -proot -e 'select variable_value from information_schema.global_status where variable_name="wsrep_cluster_size"'
+----------------+
| variable_value |
+----------------+
| 2 |
+----------------+
sm@python1:~$ mysql -uroot -proot -e 'select variable_value from information_schema.global_status where variable_name="wsrep_cluster_size"'
+----------------+
| variable_value |
+----------------+
| 3 |
+----------------+
写入测试
我们在节点1创建一个数据库
sm@python1:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
sm@python1:~$
sm@python1:~$ mysqladmin -uroot -proot create mytestdb
sm@python1:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
然后在节点2查看该数据库是否已经同步到节点2
sm@python2:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytestdb |
| performance_schema |
+--------------------+
然后在节点3删除这个数据库
sm@python3:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytestdb |
| performance_schema |
+--------------------+
sm@python3:~$ mysqladmin -uroot -proot drop mytestdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'mytestdb' database [y/N] y
Database "mytestdb" dropped
然后在节点2查看这个数据库
sm@python2:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
故障模拟
停止节点1的服务
sm@python1:~$ sudo service mysql stop
* Stopping MariaDB database server mysqld [ OK ]
在节点2查看集群节点数量
sm@python2:~$ mysql -uroot -proot -e 'select variable_value from information_schema.global_status where variable_name="wsrep_cluster_size"'
+----------------+
| variable_value |
+----------------+
| 2 |
+----------------+
节点3创建数据库
sm@python3:~$ mysqladmin -uroot -proot create mytestdb2
启动节点1服务,查看新创建的数据库是否同步过来
sm@python1:~$ sudo service mysql start
* Starting MariaDB database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
sm@python1:~$ mysql -uroot -proot -e 'select variable_value from information_schema.global_status where variable_name="wsrep_cluster_size"'
+----------------+
| variable_value |
+----------------+
| 3 |
+----------------+
sm@python1:~$ mysql -uroot -proot -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytestdb2 |
| performance_schema |
+--------------------+