MySQL Fabric 是一个用于管理 MySQL 服务器群的可扩展框架。该框架实现了两个特性 — 高可用性 (HA) 以及使用数据分片的横向扩展。这两个特性既可以单独使用,也可以结合使用。本文只测试高可用性。
测试环境
centos 6.5
192.168.17.177 mysql数据库
192.168.17.178 Fabric服务器和mysql数据库
192.168.17.179 mysql数据库
192.168.17.180 mysql数据库
防火墙
重启后永久性生效:
开启:chkconfig iptables on
关闭:chkconfig iptables off
即时生效,重启后失效:
开启:service iptables start
关闭:service iptables stop
1、安装mysql,4台服务器安装相同版本mysql和相同配置
先删除centos6.5自带mysql
rpm -qa | grep mysql
rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
下载mysql,http://dev.mysql.com/downloads/mysql/
MySQL-client-5.6.28-1.el6.x86_64.rpm
MySQL-devel-5.6.28-1.el6.x86_64.rpm
MySQL-server-5.6.28-1.el6.x86_64.rpm
MySQL-shared-5.6.28-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.28-1.el6.x86_64.rpm
安装mysql
yum localinstall –-nogpgcheck *.rpm
配置mysql
more /root/.mysql_secret //查看默认自带密码
service mysql start //启动mysql
/usr/bin/mysql_secure_installation //初始化mysql
fabric是基于GTID主从复制,所以这些实例中必须要启用GTID,在mysql的配置文件要加上下边参数:
编辑mysql配置文件
vim /usr/my.cnf
在文件中加上
gtid_mode=ON
log-bin
log-slave-updates
enforce-gtid-consistency
server_id=1 //各个服务器的值不同
创建mysql管理员帐号,fabric需要使用mysql管理员级别帐号来管理数据库,4台mysql服务器创建相同的帐号
GRANT ALL PRIVILEGES ON *.* TO 'fabric'@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION;
FLUSH PRIVILEGES;
2、在192.168.17.178服务器安装mysql fabric
Fabric已经被打包到了MySQL Utilities中,下载MySQL Utilities就可以了,另外fabric操作mysql需要使用python连接器,需要同时下载mysql-utilities-1.5.6-1.el6.noarch.rpm(http://dev.mysql.com/downloads/utilities/)、
mysql-connector-python-2.1.3-1.el6.x86_64.rpm(http://dev.mysql.com/downloads/connector/python/)安装。
安装fabric
yum localinstall –-nogpgcheck *.rpm
vim /etc/mysql/fabric.cfg // 修改fabric配置
[DEFAULT]
prefix =
sysconfdir = /etc
logdir = /var/log
[statistics]
prune_time = 3600
[logging]
url = file:///var/log/fabric.log
level = INFO
[storage]
auth_plugin = mysql_native_password
database = fabric
user = fabric //与上边创建的帐号一致
address = 192.168.17.178:3306 // fabric Backing Store用的mysql
connection_delay = 1
connection_timeout = 6
password = secret //与上边创建的帐号一致
connection_attempts = 6
[failure_tracking]
notification_interval = 60
notification_clients = 50
detection_timeout = 1
detection_interval = 6
notifications = 300
detections = 3
failover_interval = 0
prune_time = 3600
[servers] // 192.168.17.180、192.168.17.179、192.168.17.177三台mysql的管理员帐号
restore_user = fabric
unreachable_timeout = 5
backup_password = secret
backup_user = fabric
user = fabric
restore_password = secret
password = secret
[connector]
ttl = 1
[protocol.xmlrpc]
disable_authentication = yes // 关闭验证
ssl_cert =
realm = MySQL Fabric
ssl_key =
ssl_ca =
threads = 5
user = admin
address = 192.168.17.178:32274
password =
[executor]
executors = 5
[sharding]
prune_limit = 10000
mysqldump_program = /usr/bin/mysqldump
mysqlclient_program = /usr/bin/mysql
[protocol.mysql]
disable_authentication = yes // 关闭验证
ssl_cert =
ssl_key =
ssl_ca =
user = admin
address = 192.168.17.178:32275
password =
初始化Backing Store数据,初始化完成后,可以在192.168.17.178的mysql中看到fabric数据表,这个数据库主要用来Backing Store使用。
mysqlfabric manage setup
启动fabric
mysqlfabric manage start
建立HA服务器组
mysqlfabric group create my_group
将mysql服务器加入HA组中
mysqlfabric group add my_group 192.168.17.177:3306
mysqlfabric group add my_group 192.168.17.179:3306
mysqlfabric group add my_group 192.168.17.180:3306
添加完成后,默认情况不会自己选出主库(PRIMARY),可以让fabric自动选出主库,也可以手动选出主库
1)执行下边命令让fabric选出主库
mysqlfabric group promote my_group
2)手动指定主库
mysqlfabric server set_status 192.168.17.179:3306 primary // set_status可手工指定4个状态primary, secondary,spare, or faulty.
添加完成后,可以使用使用下边命令查看当前my_group组中的服务器状况
mysqlfabric group lookup_servers my_group
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- ---------- ------
086d9569-a78a-11e5-9205-005056ab3497 192.168.17.180:3306 SECONDARY READ_ONLY 1.0
452ee193-a4a7-11e5-bf33-005056ab482f 192.168.17.179:3306 PRIMARY READ_WRITE 1.0
edcbd377-a4a7-11e5-bf37-005056ab131b 192.168.17.177:3306 SECONDARY READ_ONLY 1.0
fabric常用相关命令
mysqlfabric group create my_group #创建HA组
mysqlfabric group destroy my_group #删除HA组
mysqlfabric group add my_group 192.168.17.180:3306 #添加组成员
mysqlfabric group remove my_group xxxxxxxx #移出组成员
mysqlfabric group lookup_servers my_group #查看组成员
mysqlfabric group promote my_group #选举master
mysqlfabric groupactivate my_group #激活自动故障转移
mysqlfabric group deactivate my_group #禁用自动故障转移
mysqlfabric serverset_status server_uuid status #变更服务器状态
mysqlfabric help manage #manage命令帮助
mysqlfabric help group #group命令帮助
mysqlfabric help server #server命令帮助
3、java测试代码建库,建表,插入数据,查询操作
操作数据库的顺序改为先连接Fabric,再由Fabric内部操作具体的数据库,这里使用java例子,测试代码来自http://dev.mysql.com/downloads/connector/j/ java驱动包中的例子
package demo.fabric;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.fabric.jdbc.FabricMySQLConnection;
/**
* Demonstrate working with employee data in MySQL Fabric with Connector/J and the JDBC APIs.
*/
public class EmployeesTest {
public static void main(String args[]) throws Exception {
String hostname = "192.168.17.178";
String port = "32274";
String database = "employees";
String user = "fabric";
String password = "secret";
String baseUrl = "jdbc:mysql:fabric://" + hostname + ":" + Integer.valueOf(port) + "/";
// Load the driver if running under Java 5
if (!com.mysql.jdbc.Util.isJdbc4()) {
Class.forName("com.mysql.fabric.jdbc.FabricMySQLDriver");
System.out.println("load driver");
}
Connection rawConnection = DriverManager.getConnection(baseUrl + "mysql?fabricServerGroup=my_group", user, password);
Statement statement = rawConnection.createStatement();
statement.executeUpdate("create database if not exists employees");
System.out.println("create database if not exists employees");
statement.close();
rawConnection.close();
// The 1-st way is to set it's name explicitly via the "fabricServerGroup" connection property
rawConnection = DriverManager.getConnection(baseUrl + database + "?fabricServerGroup=my_group", user, password);
statement = rawConnection.createStatement();
statement.executeUpdate("create database if not exists employees");
statement.close();
rawConnection.close();
rawConnection = DriverManager.getConnection(baseUrl + database + "?fabricServerGroup=my_group", user, password);
statement = rawConnection.createStatement();
statement.executeUpdate("drop table if exists employees");
statement.executeUpdate("create table employees (emp_no int not null, first_name varchar(50), last_name varchar(50), primary key (emp_no))");
// 2. Insert data
// Cast to a Fabric connection to have access to specific methods
FabricMySQLConnection connection = (FabricMySQLConnection) rawConnection;
// example data used to create employee records
Integer ids[] = new Integer[] { 1, 2, 10001, 10002 };
String firstNames[] = new String[] { "John", "Jane", "Andy", "Alice" };
String lastNames[] = new String[] { "Doe", "Doe", "Wiley", "Wein" };
// insert employee data
PreparedStatement ps = connection.prepareStatement("INSERT INTO employees.employees VALUES (?,?,?)");
for (int i = 0; i < 4; ++i) {
ps.setInt(1, ids[i]);
ps.setString(2, firstNames[i]);
ps.setString(3, lastNames[i]);
ps.executeUpdate();
}
System.out.println("Querying employees");
System.out.format("%7s | %-30s | %-30s%n", "emp_no", "first_name", "last_name");
System.out.println("--------+--------------------------------+-------------------------------");
ps = connection.prepareStatement("select emp_no, first_name, last_name from employees where emp_no = ?");
for (int i = 0; i < 4; ++i) {
ps.setInt(1, ids[i]);
ResultSet rs = ps.executeQuery();
rs.next();
System.out.format("%7d | %-30s | %-30s%n", rs.getInt(1), rs.getString(2), rs.getString(3));
rs.close();
}
ps.close();
// connection.setServerGroupName("my_group");
// statement.executeUpdate("drop table if exists employees");
statement.close();
connection.close();
}
}
如果当前PRIMARY为192.168.17.179:3306,直接连接192.168.17.179进行增加修改删除,数据能自动同步到192.168.17.177和192.168.17.180,完整的主从模式。
如果当前PRIMARY为192.168.17.179:3306,直接连接192.168.17.177或192.168.17.180,会造成PRIMARY主库的修改无法同步到从数据,需要把从数据库冲突数据删除,重启冲突的从数据库,通过mysqlfabric server set_status修改从数据库的状态为secondary,数据会重新同步。
参考
http://www.csdn.net/article/2014-08-20/2821300
http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html