MySQL高可用架构之Galera Cluster
1、实验准备及拓扑
至少需要三个节点
node1 192.168.150.137
node2 192.168.150.138
node3 192.168.150.139mariadb版本为mariadb的支持galera cluster的分支版本
MariaDB-Galera-server-5.5.46实验前准备：
1、HA环境首要条件：时间同步
三个节点添加对时脚本
[root@localhost ~]# crontab -l
*/5 * * * * /usr/sbin/ntpdate 1.cn.pool.ntp.org
2、三个几点均配置MariaDB-Galera的本地yum仓库，我尝试使用mariadb官方提供的yum仓库，天朝的网会气死你
[root@localhost ~]# cat /etc/yum.repos.d/galera.repo
[galera]
name=galera
baseurl=file:///root/galera_cluster
gpgcheck=0
enable=1
3、yum安装，仅需安装MariaDB-Galera-server，其余的均会依赖安装
yum -y install Mariadb-Galera-server2、配置
1、查看galera所需调用的库的位置
rpm -ql galera | grep -i smm.so
/usr/lib64/galera/libgalera_smm.so
2、修改配置文件，三节点同步修改
[root@localhost yum.repos.d]# 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]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.150.137,192.168.150.138,192.168.150.139"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name='mycluster'
#
# 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-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]
# These two groups are 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]
[mariadb-5.5]
3、节点1进行mysql及cluster开启
[root@localhost ~]# /etc/rc.d/init.d/mysql start --wsrep-new-cluster
Starting MySQL.... SUCCESS!
4、其它两个节点进行正常的mysql开启
[root@localhost ~]# service mysql start
Starting MySQL....SST in progress, setting sleep higher. SUCCESS!
此时已配置完成。。。。。。3、功能验证
1、节点1创建数据库，节点2 3均可正常查看
节点1：[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.46-MariaDB-wsrep MariaDB Server, wsrep_25.12.r4f81026
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
节点2 3：
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.46-MariaDB-wsrep MariaDB Server, wsrep_25.12.r4f81026
Copyright (c) 2000, 2015, 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 |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
2、节点2数据库中创建表，节点1 2均可正常查看
节点2：
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> CREATE TABLE tb1 (id int,name char(10));
Query OK, 0 rows affected (0.01 sec)
节点1 3：
MariaDB [(none)]> use mydb
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 [mydb]> SHOW TABLES
-> ;
+----------------+
| Tables_in_mydb |
+----------------+
| tb1 |
+----------------+
1 row in set (0.00 sec)
MariaDB [mydb]> DESC tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
3、自增栏位的测试，每个几点会跳着进行自增，同时插入时例如1节点1 4 7；2节点2 5 8；三节点3 6 9。
节点1：
MariaDB [mydb]> CREATE TABLE tb2(id int UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,name char(30)
uery OK, 0 rows affected (0.01 sec)
MariaDB [mydb]> INSERT INTO tb2 (name) VALUES ('void'),('yao');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
节点2：
MariaDB [mydb]> select * from tb2;
+----+------+
| id | name |
+----+------+
| 1 | void |
| 4 | yao |
+----+------+
2 rows in set (0.01 sec)
MariaDB [mydb]> INSERT INTO tb2 (name) VALUES ('amy'),('apple');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from tb2;
+----+-------+
| id | name |
+----+-------+
| 1 | void |
| 4 | yao |
| 5 | amy |
| 8 | apple |
+----+-------+
4 rows in set (0.00 sec)
