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-server 2、配置 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)