爱威尔-mysql fabric高可用HA和分表(分片sharding)打通了各个环节

<img src="78/20161130164120_89.png" alt=""> 
 我想就不用解释这是什么玩意儿了吧。 
 说明没带虚拟机都需要装mysql，怎么安装我也不说了，我这边用的是5.7.15版本 算是比较高了，前面我也过自动化安装的文章，大家可以回头参考，我这里贴出来mysql的配置文件这个才是重点： 
 
<pre class="prettyprint">[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/home/mysql1
datadir=/home/mysql1/data
explicit_defaults_for_timestamp=true
lower_case_table_names=1
back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M

###***slowqueryparameters
long_query_time=1
slow_query_log = 1
slow_query_log_file=/home/mysql1/slowlog/slow.log

###***binlogparameters
log-bin=mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates=true
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=1024M
binlog_format=MIXED
expire_logs_days=7

###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1

#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M

#skip-name-resolve

###***master-slavereplicationparameters
server-id=3831
#slave-skip-errors=all

#***Innodbstorageengineparameters
innodb_buffer_pool_size=512M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/home/mysql1/data/error.log
pid-file=/home/mysql1/data/mysqld.pid
</pre> 这里有几个重点   端口要不同吧， 
 
 
 
 log-bin=mysql-bin 
 gtid-mode=on 
 enforce-gtid-consistency=true 
 log_slave_updates=true 这一个都必须有 大家一个就知道是怎么回事了，还有 
 server-id=3831  也要不同， 
 还有在mysql 的data目录中auto.cnf文件的server-uuid  也不能相同 
 如果相同处理  首先把这个文件删除或者重命名  然后重启mysql就可以了  auto.cnf会自动生成 
 上面这些都是主从HA必须要的，大家配置一下就行了，并不难。 
 MySQL Fabric  
 <img src="78/20161130164958_143.png" alt=""> 
 到目前为止1.5.6 对于fabric是最新的  
 <img src="78/20161130165200_789.png" alt=""> 
 不管哪个版本  打开要是能看到fabric就说明这个版本有fabric功能，因为mysql fabric是mysql utilities的一部分。 
 安装fabric的前提条件：MySQL5.6.10以上，python2.6以上  自己安装 这里不说了。 
 
 解压之后进入目录，只需要一个命令就行了： 
 python setup.py install 就是这么简单。上面的准备工作做完后 不废话开始： 
 
 
 1.修改配置文件： 
 安装成功，在这里就会找到fabric.cfg  这个文件 
 [root@h5 home]# cd /etc/mysql/ 
 [root@h5 mysql]# ll 
 总用量 20 
 -rw-r--r-- 1 root root  1139 11月 28 11:50 fabric.cfg 
 -rw-r--r-- 1 root root 13132 9月  15 2015 mysql-fabric-doctrine-1.4.0.zip 
 [root@h5 mysql]# 
   
 [DEFAULT] 
 prefix = 
 sysconfdir = /etc 
 logdir = /var/log 
   
 [storage] 
 address = localhost:3306 
 user = root 
 password = 336393 
 database = fabric 
 auth_plugin = mysql_native_password 
 connection_timeout = 6 
 connection_attempts = 6 
 connection_delay = 1 
   
 [servers] 
 user = root 
 password = 336393 
 backup_user = root 
 backup_password = 336393 
 restore_user = root 
 restore_password = 336393 
 unreachable_timeout = 5 
   
 [protocol.xmlrpc] 
 address = 192.168.38.15:32274 
 threads = 5 
 user = admin 
 password = 
 disable_authentication = no 
 realm = MySQL Fabric 
 ssl_ca = 
 ssl_cert = 
 ssl_key = 
   
 [protocol.mysql] 
 address = 192.168.38.15:32275 
 user = admin 
 password = 
 disable_authentication = no 
 ssl_ca = 
 ssl_cert = 
 ssl_key = 
   
 [executor] 
 executors = 5 
   
 [logging] 
 level = INFO 
 url = file:///var/log/fabric.log 
   
 [sharding] 
 mysqldump_program = /usr/bin/mysqldump 
 mysqlclient_program = /usr/bin/mysql 
 prune_limit = 10000 
   
 [statistics] 
 prune_time = 3600 
   
 [failure_tracking] 
 notifications = 300 
 notification_clients = 50 
 notification_interval = 60 
 failover_interval = 0 
 detections = 3 
 detection_interval = 6 
 detection_timeout = 1 
 prune_time = 3600 
   
 [connector] 
 ttl = 1 
 改动就这几处， 
   
 [storage] 这里要配置的是和fabric同在一个服务器的mysql数据库用户名密码和要创建的数据库，fabric会用到。 
 [servers] 里面配置的是需要管理的mysql数据库用户名和密码，这里是统一的，也就是说不管是主从还是高可用的  还是分片的所有数据库的用户名和密码都要统一。 
 其他地方就默认 
 2.初始化fabric，创建fabric数据库和相关表 
   
 mysqlfabric --config=/etc/mysql/fabric.cfg manage setup --param=storage.user=root --param=storage.password=336393 
   
 这个地方需要输入管理员的密码，其实就是[protocol.xmlrpc]和[protocol.mysql] 
 这两个地方的password 的密码。 
   
 成功之后就可以看到： 
<img src="78/20161130165813_794.png" alt=""> 
 
 
 
 这就是fabric管理所需要的数据库。 
   
 3.启动fabric 
   
 mysqlfabric --config=/etc/mysql/fabric.cfg manage start --daemonize 
   
 --daemonize  是为了进程在后台运行 
<img src="78/20161130165858_204.png" alt=""> 
 
 
 
 4.HA开始 
 创建一个组： 
 mysqlfabric group create oa_g1 
   
 oa_g1名字随意自己取 
 往这个组里面加服务器： 
   
 mysqlfabric group add oa_g1192.168.38.31:3306 
 mysqlfabric group add oa_g1192.168.38.32:3306 
 mysqlfabric group add oa_g1192.168.38.33:3306 
   
   
 选举主库： 
 mysqlfabric group promote weir 
<pre class="prettyprint">[root@h5 mysql]# mysqlfabric group lookup_servers oa_g1
Password for admin: 
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ------------------ --------- ---------- ------
4d7208d9-b31f-11e6-9752-000c29417632 192.168.38.32:3306 PRIMARY READ_WRITE 1.0
4d7208d9-b31f-11e6-9752-000c29417633 192.168.38.33:3306 SECONDARY READ_ONLY 1.0
4d7208d9-b31f-11e6-9752-000c29417683 192.168.38.31:3306 SECONDARY READ_ONLY 1.0
</pre> 
 接着启动 故障自动切换： 
   
 mysqlfabric group activate weir 
   
 到此HA就搞定了 大家就可以测试了。 
 我这里把结论写出来： 
 主机挂了可以自动切换主机 
 挂掉的机器恢复正常就无法自动加入HA，需要手动完成： 
 就是先删除之前挂掉的服务器： 
 mysqlfabric group remove weir 192.168.38.31:3306 
   
 然后再重新加入： 
   
 mysqlfabric group add weir 192.168.38.31:3306 
   
 虽然没有那么智能但是可以接受。 
 HA其实就这些，并不复杂。而且可以配置多个组，组成多个HA都可以 
   
 这个时候我们就可以创建数据库进行测试了，你可以用命令也可以用第三方的管理工具。 
 如果不放心可以检查一下HA是否生效。用： 
<pre class="prettyprint">mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.38.32
 Master_User: root
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000011
 Read_Master_Log_Pos: 1983
 Relay_Log_File: m1-relay-bin.000007
 Relay_Log_Pos: 2196
 Relay_Master_Log_File: mysql-bin.000011
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: 
 Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
 Skip_Counter: 0
 Exec_Master_Log_Pos: 1983
 Relay_Log_Space: 2440
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
 Master_SSL_Cipher: 
 Master_SSL_Key: 
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 3832
 Master_UUID: 4d7208d9-b31f-11e6-9752-000c29417632
 Master_Info_File: /home/mysql1/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 4d7208d9-b31f-11e6-9752-000c29417632:1-71
 Executed_Gtid_Set: 4d7208d9-b31f-11e6-9752-000c29417632:1-71,
4d7208d9-b31f-11e6-9752-000c29417683:1-66
 Auto_Position: 1
 Replicate_Rewrite_DB: 
 Channel_Name: 
 Master_TLS_Version: 
1 row in set (0.00 sec)

mysql>
</pre> 
 看到这个就是成功了。在主数据库上面创建数据库和表，马上就可以在从数据库上面看到同样的数据库和表，你在主数据库表里面添加数据 从数据库也会有。这就是HA主从。 
 
 
 下面我们配置分片： 
   
 我们从上面的基础上做分片 
   
 我有加入了一个服务器192.168.38.34，我准备用这一台做分片。 
 首先我再创建一个组，这个组里面只有34这个mysql服务器，如果只是做分片不做HA可以是单台一组，创建组的命令跟上面一样： 
   
 [root@h5 mysql]# mysqlfabric group lookup_groups 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
 group_id description failure_detector                          master_uuid 
 -------- ----------- ---------------- ------------------------------------ 
    oa_g1        None                1 4d7208d9-b31f-11e6-9752-000c29417632 
    oa_g2        None                1 4d7208d9-b31f-11e6-9752-000c29417634 
   
   
 [root@h5 mysql]# 
   
 看到有两个组 
   
 [root@h5 mysql]# mysqlfabric group lookup_servers oa_g2 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                          server_uuid            address  status       mode weight 
 ------------------------------------ ------------------ ------- ---------- ------ 
 4d7208d9-b31f-11e6-9752-000c29417634 192.168.38.34:3306 PRIMARY READ_WRITE    1.0 
   
   
 [root@h5 mysql]# 
   
 oa_g2组只有一个 
   
 现在开始分片： 
   
 首先定义分片是用什么规则： 
 mysqlfabric sharding create_definition RANGE oa_g1 
   
 HASH：在分片键上执行一个哈希函数生成分片号。如果作为分片键的列只有很少的重复值，那么哈希函数的结果会平均分布在多个分片上。 RANGE：管理员显式定义分片键的取值范围和分片之间的映射关系。这可以尽可能让用户控制数据分片，并确定哪一行被分配到哪一个分片。 
 目前fabric只有这两种 
   
 [root@h5 ~]# mysqlfabric sharding create_definition RANGE oa_g1 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                                 uuid finished success result 
 ------------------------------------ -------- ------- ------ 
 b35a1ba3-706f-472c-963b-9129b7ffda30        1       1      1 
   
 state success          when                                                   description 
 ----- ------- ------------- ------------------------------------------------------------- 
     3       2 1480343750.72 Triggered by <mysql.fabric.events.Event object at 0x294a950>. 
     4       2 1480343750.73                     Executing action (_define_shard_mapping). 
     5       2 1480343750.76                      Executed action (_define_shard_mapping). 
   
 添加要分片的表和字段： 
   
 mysqlfabric sharding add_table 1 oa.users id 
   
 [root@h5 ~]# mysqlfabric sharding add_table 1 oa.user id 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                                 uuid finished success result 
 ------------------------------------ -------- ------- ------ 
 b2903f23-6ca7-4451-bf4f-f1cb74c15c44        1       1      1 
   
 state success          when                                                   description 
 ----- ------- ------------- ------------------------------------------------------------- 
     3       2 1480343773.62 Triggered by <mysql.fabric.events.Event object at 0x294aa50>. 
     4       2 1480343773.63                        Executing action (_add_shard_mapping). 
     5       2  1480343773.7                         Executed action (_add_shard_mapping). 
   
 解释mysqlfabric sharding add_table 1 oa.users id 中的1  就是上面的result的1 
   
 然后对两个组定义规则： 
 1-10000在组oa_g1  10000以上在oa_g2组 
   
 mysqlfabric sharding add_shard 1 "oa_g1/1,oa_g2/10000" --state=ENABLED 
   
 但是执行这句话会报错： 
   
 [root@h5 ~]# mysqlfabric sharding add_shard 1 "oa_g1/1,oa_g2/10000" --state=ENABLED 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
 DatabaseError: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work 
 (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 
   
 这个地方是可以解决的，我现在继续往下写，我们去掉--state=ENABLED 
   
 [root@h5 ~]# mysqlfabric sharding add_shard 1 "oa_g1/1,oa_g2/10000" 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                                 uuid finished success result 
 ------------------------------------ -------- ------- ------ 
 d7eb4970-d358-4df1-9b23-725cf61c4d1f        1       1      1 
   
 state success          when                                                   description 
 ----- ------- ------------- ------------------------------------------------------------- 
     3       2 1480343866.65 Triggered by <mysql.fabric.events.Event object at 0x2af5110>. 
     4       2 1480343866.66                                Executing action (_add_shard). 
     5       2 1480343866.72                                 Executed action (_add_shard). 
   
 然后到fabric的数据库里面： 
 
 <img src="78/20161130170448_140.png" alt=""> 
 
 
 在数据库里面改一下 ，当然现在我还没有测试正确的方式。就先这样做。 
 到此我们测试一下可不可以： 
   
 [root@h5 ~]# mysqlfabric sharding lookup_servers oa.user 2000 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                          server_uuid            address    status       mode weight 
 ------------------------------------ ------------------ --------- ---------- ------ 
 4d7208d9-b31f-11e6-9752-000c29417632 192.168.38.32:3306   PRIMARY READ_WRITE    1.0 
 4d7208d9-b31f-11e6-9752-000c29417633 192.168.38.33:3306 SECONDARY  READ_ONLY    1.0 
 4d7208d9-b31f-11e6-9752-000c29417683 192.168.38.31:3306 SECONDARY  READ_ONLY    1.0 
   
   
 [root@h5 ~]# mysqlfabric sharding lookup_servers oa.user 200000 
 Password for admin: 
 Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e 
 Time-To-Live: 1 
   
                          server_uuid            address  status       mode weight 
 ------------------------------------ ------------------ ------- ---------- ------ 
 4d7208d9-b31f-11e6-9752-000c29417634 192.168.38.34:3306 PRIMARY READ_WRITE    1.0 
   
 这样看是可以了。到此分片也就完成了，下面是jdbc怎么操作了： 
 mysql-connector-java-5.1.40.jar  这是5.1的最新版本 不要用6.X版本  里面没有才做fabric的接口 
 高可用的： 
 
<pre class="prettyprint lang-java">package com.aurora.mysql.test;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.lang3.RandomStringUtils;

import com.mysql.fabric.jdbc.JDBC4FabricMySQLConnection;

public class Main {
	private static String URL = "jdbc:mysql:fabric://192.168.38.15:32274/oa?fabricUsername=admin&fabricPassword=11&fabricServerGroup=oa_g1";
	private static String USERNAME = "root";
	private static String PWD = "336393";
	private static String SQL = "INSERT INTO `user` VALUES ('4', 'weir4', '4111')";

public static void main(String[] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
		long start = System.currentTimeMillis();
		testInsert();
		long end = System.currentTimeMillis();
		System.out.println((end - start));
	}

private static JDBC4FabricMySQLConnection getConnection() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.fabric.jdbc.FabricMySQLDriver");
//		Connection con = DriverManager.getConnection(URL,USERNAME,PWD);
		JDBC4FabricMySQLConnection con = (JDBC4FabricMySQLConnection) DriverManager.getConnection(URL,USERNAME,PWD);
		return con;
	}

private static void testInsert() throws ClassNotFoundException, SQLException {
		Connection con = getConnection();
		con.setAutoCommit(false);
		con.setReadOnly(false);
		PreparedStatement pt = con.prepareStatement(SQL);
		
		pt.executeUpdate();
		con.commit();
		
		con.close();
	}

private static void testInsertAutoCommit() throws ClassNotFoundException, SQLException {
		Connection con = getConnection();
		con.setAutoCommit(true);
		PreparedStatement pt = con.prepareStatement(SQL);
		
		pt.executeUpdate();
		
		con.close();
	}</pre> 
 
 
 分片的： 
 
<pre class="prettyprint lang-java">package com.aurora.mysql.test1;
import java.io.UnsupportedEncodingException; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
 
import com.mysql.fabric.jdbc.FabricMySQLConnection; 
 
public class Main { 
 private static String URL = "jdbc:mysql:fabric://192.168.38.15:32274/oa?fabricShardTable=user&fabricUsername=admin&fabricPassword=11"; 
 private static String USERNAME = "root"; 
 private static String PWD = "336393"; 
 private static String SQL = "INSERT INTO `user` VALUES ('1000012', 'weir100002', '4111')";
 
 public static void main(String[] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException { 
 long start = System.currentTimeMillis();
 testInsert(); 
 long end = System.currentTimeMillis(); 
 System.out.println((end - start)); 
 }
 
 private static Connection getConnection() throws SQLException, ClassNotFoundException { 
 Class.forName("com.mysql.fabric.jdbc.FabricMySQLDriver"); 
 Connection con = DriverManager.getConnection(URL,USERNAME,PWD); 
 return con; 
 } 
 
 private static void testInsert() throws ClassNotFoundException, SQLException { 
 Connection con = getConnection(); 
 long start = System.currentTimeMillis();
 con.setAutoCommit(false); 
 
 FabricMySQLConnection fcon=(FabricMySQLConnection) con;
 fcon.setShardKey(String.valueOf(1000012));
 PreparedStatement pt = con.prepareStatement(SQL);
 pt.executeUpdate();
 fcon.commit();
 
 con.close();
 
 long end = System.currentTimeMillis(); 
 System.out.println("testInsert----"+(end - start)); 
 } 
} </pre> 关键点： 
 
 FabricMySQLConnection fcon=(FabricMySQLConnection) con;         fcon.setShardKey(String.valueOf(1000012)); 
 <img src="78/20161130171242_537.png" alt=""> 
 结束。 
 
 
<pre class="prettyprint">HA配置常用命令
1：mysqlfabric group create hagroup1：创建HA组
2：mysqlfabric group destroy hagroup1：删除HA组
3：mysqlfabric group add hagroup1 192.168.1.101:3306：添加组成员
4：mysqlfabric group remove hagroup1 9f93533a-3f39-11e51：移出组成员
5：mysqlfabric group lookup_servers hagroup1：查看组成员
6：mysqlfabric group promote hagroup1：选举master
7：mysqlfabric groupactivate hagroup1：激活自动故障转移
8：mysqlfabric group deactivatehagroup1：禁用自动故障转移
9：mysqlfabric server set_status server_uuid status：变更服务器状态
（primary,secondary,spare,faulty ）
10：mysqlfabric help manage：manage命令帮助
11：mysqlfabric help group：group命令帮助
12：mysqlfabric help server：server命令帮助
</pre> 其他可以去官网看看