MySQL Fabric实验（二）Sharding

最新推荐文章于 2023-03-29 11:33:30 发布

wzy0623

最新推荐文章于 2023-03-29 11:33:30 发布

阅读量2k

点赞数

分类专栏： MySQL MySQL高可用方案

本文链接：https://blog.csdn.net/wzy0623/article/details/50441091

版权

MySQL 同时被 2 个专栏收录

147 篇文章 42 订阅

订阅专栏

MySQL高可用方案

34 篇文章 74 订阅

订阅专栏

一、概述
MySQL Fabric这一新的架构为MySQL提供了高可用和向外扩展的特性。本实验专注于使用Fabric对多个MySQL服务器进行读写实现向外扩展。当单个MySQL服务器（或HA组）的写性能达到极限时，可以使用Fabric把数据分布到多个MySQL服务器组。注意这里说的组可以是单一服务器，也可以是HA组。管理员通过建立一个分片映射定义数据如何在多个服务中分片。一个分片映射作用于一个或多个表，由管理员指定每个表上的哪些列作为分片键，MySQL Fabric使用分片键计算一个表的特定行应该存在于哪个分片上。当多个表使用相同的映射和分片键时，这些表上包含相同列值（用于分片的列）的数据行将存在于同一个分片。单一事务可以访问一个分片中的所有数据。目前Fabric提供两种用分片键计算分片号的方法：

HASH：在分片键上执行一个哈希函数生成分片号。如果作为分片键的列只有很少的重复值，那么哈希函数的结果会平均分布在多个分片上。
RANGE：管理员显式定义分片键的取值范围和分片之间的映射关系。这可以尽可能让用户控制数据分片，并确定哪一行被分配到哪一个分片。

应用程序访问分片的数据库时，它设置一个连接属性指定分片键。Fabric连接器会应用正确的范围或哈希映射，并将事务路由到正确的分片。当需要更多的分片时，MySQL Fabric可以把现有的一个分片分成两个，同时修改状态存储和连接器中缓存的路由数据。类似地，一个分片可以从一个HA组迁移到另一个。

注意单一的事务或查询只能访问一个单一的分片，所以基于对数据的理解和应用的访问模式选择一个分片键是非常重要的。并不是对所有表分片都有意义。对于当前不能交叉分片查询的限制，将某些小表的全部数据存储到每一个组中可能会更好。这些全局表被写入到‘全局组’，表中数据的任何改变都会自动复制到所有其它非全局组中。全局组中模式（结构）的改变也会复制到其它非全局组中以保证一致性。为了得到做好的映射，在没有‘自然选择’的分片键时可能需要修改模式。

二、安装与配置

1. 使用VirtualBox安装两个CentOS release 6.4虚拟机，安装Python 2.6或以上版本，关闭iptables和selinux。虚拟机和网卡说明如下表所示。

主机名	内部网络IP	说明
fab_connector	192.168.56.101	安装Fabric和MySQL，建立一个MySQL实例，使用缺省的3306端口，存储MySQL实例的状态和路由信息
fab_group1	192.168.56.102	安装MySQL，建立三个MySQL数据库实例，端口分别是3326、3327、3328，每个实例自成一组

虚拟机名称	网卡	连接方式	说明
fab_connector	网卡1	网络地址转换（NAT）	用于虚拟机访问宿主机和外网
	网卡2	桥接网卡（192.168.16.119）	用于宿主机访问虚拟机
	网卡3	内部网络	用于Fabric组内互联
fab_group1	网卡1	网络地址转换（NAT）	用于虚拟机访问宿主机和外网
fab_group1	网卡3	内部网络	用于Fabric组内互联

因为只是出于实验的目的，所以使用root用户安装MySQL和Fabric，下载的软件包分别是mysql-5.7.10-linux-glibc2.5-x86_64.tar和mysql-utilities-1.5.6.tar.gz。实验环境如下图所示。

2. 在两个虚拟机上安装MySQL

cd /root
tar xvf mysql-5.7.10-linux-glibc2.5-x86_64.tar
tar zxvf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
ln -s mysql-5.6.13-linux-glibc2.5-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql .

3. 在fab_connector上安装Fabric

cd /root
tar zxvf mysql-utilities-1.5.6.tar.gz
cd mysql-utilities-1.5.6
sudo python setup.py install

4. 在fab_connector上配置并启动MySQL

[root@fab_connector ~]# cat /root/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/root/mysql/bin

export PATH

[root@fab_connector ~]# cat /etc/my_fabric.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab_connector
report-port=3306
server-id=1
log-bin=fab-bin.log

[root@fab_connector ~]# mysqld --defaults-file=/etc/my_fabric.cnf --user=mysql &
[root@fab_connector ~]# mysql -h 127.0.0.1 -P3306 -u root -p -e "CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'secret';GRANT ALL ON fabric.* TO 'fabric'@'localhost'";

5. 在fab_group1上配置三个MySQL实例

[root@fab_group1 ~]# cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/root/mysql/bin

export PATH

[root@fab_group1 bin]# cat /etc/my_group1_1_init.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_1
port=3326
socket=/var/lib/group1_1/mysql.sock

[root@fab_group1 bin]# cat /etc/my_group1_2_init.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_2
port=3327
socket=/var/lib/group1_2/mysql.sock

[root@fab_group1 bin]# cat /etc/my_group1_3_init.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_3
port=3328
socket=/var/lib/group1_3/mysql.sock

[root@fab_group1 ~]# cat /etc/my_group1_1.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_1
port=3326
socket=/var/lib/group1_1/mysql.sock
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=fab_group1
report-port=3326
server-id=11
log-bin=fab1a-bin.log

[root@fab_group1 ~]# cat /etc/my_group1_2.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_2
port=3327
socket=/var/lib/group1_2/mysql.sock
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=fab_group1
report-port=3327
server-id=12
log-bin=fab1a-bin.log
 
[root@fab_group1 ~]# cat /etc/my_group1_3.cnf
[mysqld]
basedir=/root/mysql
datadir=/var/lib/group1_3
port=3328
socket=/var/lib/group1_3/mysql.sock
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=fab_group1
report-port=3328
server-id=13
log-bin=fab1a-bin.log

[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_1_init.cnf --initialize
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_2_init.cnf --initialize
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_3_init.cnf --initialize
# 记下初始化生成的临时密码
[root@fab_group1 ~]# chown -R mysql /var/lib/group1_1
[root@fab_group1 ~]# chown -R mysql /var/lib/group1_2
[root@fab_group1 ~]# chown -R mysql /var/lib/group1_3
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_1_init.cnf --user=mysql &
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_2_init.cnf --user=mysql &
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_3_init.cnf --user=mysql &
[root@fab_connector ~]# mysql -h 127.0.0.1 -P3326 -u root -p
ALTER USER USER() IDENTIFIED BY 'new_password';
CREATE USER 'fabric'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON *.* TO 'fabric'@'%';
[root@fab_connector ~]# mysql -h 127.0.0.1 -P3327 -u root -p
ALTER USER USER() IDENTIFIED BY 'new_password';
CREATE USER 'fabric'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON *.* TO 'fabric'@'%';
[root@fab_connector ~]# mysql -h 127.0.0.1 -P3328 -u root -p
ALTER USER USER() IDENTIFIED BY 'new_password';
CREATE USER 'fabric'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON *.* TO 'fabric'@'%';
# 修改初始密码，添加fabric用户
[root@fab_group1 ~]# mysqladmin -u root --protocol=tcp -h127.0.0.1 -P3326 -p shutdown
[root@fab_group1 ~]# mysqladmin -u root --protocol=tcp -h127.0.0.1 -P3327 -p shutdown
[root@fab_group1 ~]# mysqladmin -u root --protocol=tcp -h127.0.0.1 -P3328 -p shutdown
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_1.cnf --user=mysql &
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_2.cnf --user=mysql &
[root@fab_group1 ~]# mysqld --defaults-file=/etc/my_group1_3.cnf --user=mysql &
# 重启三个实例

6. 在fab_connector上配置并启动Fabric

[root@fab_connector ~]# cat /etc/mysql/fabric.cfg
[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 = localhost:3306
connection_delay = 1
connection_timeout = 6
password = secret
connection_attempts = 6

[servers]
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 = no
ssl_cert = 
realm = MySQL Fabric
ssl_key = 
ssl_ca = 
threads = 5
user = admin
address = 192.168.16.119:32274
password = secret

[executor]
executors = 5

[sharding]
prune_limit = 10000
mysqldump_program = /root/mysql/bin/mysqldump
mysqlclient_program = /root/mysql/bin/mysql

[protocol.mysql]
disable_authentication = no
ssl_cert = 
ssl_key = 
ssl_ca = 
user = admin
address = 192.168.16.119:32275
password = secret

[root@fab_connector ~]# mysqlfabric manage setup
# 在状态存储（MySQL数据库实例）中建立Fabric库，执行显示如下图。

[root@fab_connector ~]# mysqlfabric manage start --daemonize
# 启动MySQL Fabric进程，执行显示如下图。

[root@fab_connector ~]# mysqlfabric manage ping
# 检查fabric进程是否运行，执行显示如下图。

7. 在fab_connector上建立HA Group（my_group1，并在其中添加三个MySQL实例）
警告：在执行这步前要确认组中所有的server-uuid都不相同。否则在将服务器添加到组中时会报错。

[root@fab_group1 bin]# cat /var/lib/group1_1/auto.cnf 
[auto]
server-uuid=d87cd922-ae99-11e5-b34b-080027a5c938
[root@fab_group1 bin]# cat /var/lib/group1_2/auto.cnf 
[auto]
server-uuid=2682c4bb-ae9a-11e5-b6f6-080027a5c938
[root@fab_group1 bin]# cat /var/lib/group1_3/auto.cnf 
[auto]
server-uuid=347229df-ae9a-11e5-b931-080027a5c938

[root@fab_connector ~]# mysqlfabric group create global-group
# 建立名为global-group的全局组，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group create my_group1
# 建立名为my_group1的组，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group create my_group2
# 建立名为my_group2的组，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group add global-group 192.168.56.102:3326
# 将实例1添加到global-group，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group add my_group1 192.168.56.102:3327
# 将实例2添加到my_group1，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group add my_group2 192.168.56.102:3328
# 将实例3添加到my_group2，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group lookup_groups
# 查看组，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group lookup_servers global-group
# 查看global-group中的服务器，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group lookup_servers my_group1
# 查看my_group1中的服务器，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group lookup_servers my_group2
# 查看my_group2中的服务器，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group promote global-group
# 提升global-group中的实例为primary，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group promote my_group1
# 提升my_group1中的实例为primary，执行显示如下图。

[root@fab_connector ~]# mysqlfabric group promote my_group2
# 提升my_group2中的实例为primary，执行显示如下图。

[root@fab_connector ~]# mysqlfabric sharding create_definition RANGE global-group
# 定义分片映射方法，执行显示如下图。

[root@fab_connector ~]# mysqlfabric sharding add_table 1 test.chat_message src_userid
# 定义分片表，执行显示如下图。

[root@fab_connector ~]# mysqlfabric sharding add_shard 1 "my_group1/1, my_group2/30000000, my_group2/60000000" --state=ENABLED
# 定义分片范围，执行显示如下图。

三、测试

1. 在3326上建立测试表

create database test;
use test;
CREATE TABLE chat_message (
  src_userid bigint(20) NOT NULL,
  target_userid bigint(20) NOT NULL,
  message varchar(200) DEFAULT NULL
);

因为3326是全局组里的MySQL服务器实例，所以上面命令建立的库和表将会自动复制到3327和3328两个实例。
运行一个Java应用程序，代码如下：

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.SQLException;

import org.apache.commons.lang3.RandomStringUtils;
import com.mysql.fabric.jdbc.FabricMySQLConnection;


public class Main {
    private static String USERNAME = "fabric";
    private static String PWD = "secret";
    private static int MAX = 2000;
    private static String SQL = "insert into chat_message(src_userid,target_userid,message) values(?,?,?)";

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

    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 {
                int i = 0;
                Connection con = getConnection();
                con.setAutoCommit(false);
                while (i < MAX) {
                        int srcUserid = (int) (Math.random() * 100000000);
                        if(i==1999){
                                srcUserid=-1;
                        }
                        FabricMySQLConnection fcon=(FabricMySQLConnection) con;
                        fcon.setShardKey(String.valueOf(srcUserid));
                        PreparedStatement pt = con.prepareStatement(SQL);
                        pt.setLong(1, srcUserid);
                        pt.setLong(2, srcUserid);
                        pt.setString(3, RandomStringUtils.randomAscii(200));
                        pt.executeUpdate();
                        fcon.commit();
                        i++;
                        //fcon.close();
                        System.out.println(i);

                }
                con.close();

    }


    private static void testInsertAutoCommit() throws ClassNotFoundException, SQLException {
        Connection con = getConnection();
        con.setAutoCommit(true);
        PreparedStatement pt = con.prepareStatement(SQL);
        int i = 0;
        while (i < MAX) {
            pt.setLong(1, 1 + (int) (Math.random() * 100000000));
            pt.setLong(2, 1 + (int) (Math.random() * 100000000));
            pt.setString(3, RandomStringUtils.randomAscii(200));
            pt.setInt(4, 1);
            pt.setInt(5, 1);
            pt.setInt(6, 1);
            pt.setInt(7, 1);
            pt.executeUpdate();
            i++;
        }
        con.close();
    }

    private static void testBatchInsert(int batchSize) throws ClassNotFoundException, SQLException {
        Connection con = getConnection();
        con.setAutoCommit(false);
        PreparedStatement pt = con.prepareStatement(SQL);
        int i = 0;
        while (i < MAX) {
            pt.setLong(1, 1 + (int) (Math.random() * 100000000));
            pt.setLong(2, 1 + (int) (Math.random() * 100000000));
            pt.setString(3, RandomStringUtils.randomAscii(200));
            pt.setInt(4, 1);
            pt.setInt(5, 1);
            pt.setInt(6, 1);
            pt.setInt(7, 1);
            pt.addBatch();
            if (i % batchSize == 1) {
                pt.executeBatch();
                con.commit();
            }
            i++;
        }
        pt.executeBatch();
        con.commit();
        con.close();
    }

    private static void testLoadFile(int batchSize)
            throws ClassNotFoundException, SQLException, UnsupportedEncodingException {
        String fieldsterminated = "\t\t";
        String linesterminated = "\t\r\n";
        String loadDataSql = "LOAD DATA LOCAL INFILE 'sql.csv' INTO TABLE chat_message FIELDS TERMINATED BY '"
                + fieldsterminated + "'  LINES TERMINATED BY '" + linesterminated
                + "' (src_userid,target_userid,message,s1,s2,s3,s4) ";
        Connection con = getConnection();
        con.setAutoCommit(false);
        PreparedStatement pt = con.prepareStatement(loadDataSql);
        com.mysql.jdbc.PreparedStatement mysqlStatement = null;
        if (pt.isWrapperFor(com.mysql.jdbc.Statement.class)) {
            mysqlStatement = pt.unwrap(com.mysql.jdbc.PreparedStatement.class);
        }

        int i = 0;
        StringBuilder sb = new StringBuilder(10000);
        while (i < MAX) {
            sb.append(1 + (int) (Math.random() * 100000000));
            sb.append(fieldsterminated);
            sb.append(1 + (int) (Math.random() * 100000000));
            sb.append(fieldsterminated);
            sb.append(RandomStringUtils.randomAscii(200).replaceAll("\\\\", " "));
            sb.append(fieldsterminated);
            sb.append(1);
            sb.append(fieldsterminated);
            sb.append(1);
            sb.append(fieldsterminated);
            sb.append(1);
            sb.append(fieldsterminated);
            sb.append(1);
            sb.append(linesterminated);
            if (i % batchSize == 1) {
                byte[] bytes = sb.toString().getBytes();
                InputStream in = new ByteArrayInputStream(bytes);
                mysqlStatement.setLocalInfileInputStream(in);
                mysqlStatement.executeUpdate();
                con.commit();
                sb = new StringBuilder(10000);
            }

            i++;
        }
        byte[] bytes = sb.toString().getBytes();
        InputStream in = new ByteArrayInputStream(bytes);
        mysqlStatement.setLocalInfileInputStream(in);
        mysqlStatement.executeUpdate();
        con.commit();

        con.close();
    }
}

该程序持续向test.chat_message表里添加2000条数据。其中最后一条插入一条没有对应范围的分片键。程序执行最后因为没有找到分片会报错，如下图所示。

这时查询3326、3327、3328三个实例，命令和结果如下图所示。

从查询结果可以看到，global-group中没有数据，my_group1和my_group2上按照预设的范围定义，分别插入了616条和1383条数据，一共插入了1999条数据。

手工在3326上执行下面的DDL和DML语句：

alter table test.chat_message add column c3326 int;
insert into test.chat_message (src_userid,target_userid,c3326) values(1,10,1);
insert into test.chat_message (src_userid,target_userid,c3326) values(30000001,11,1);
insert into test.chat_message (src_userid,target_userid,c3326) values(-1,12,1);
commit;

这时查询3326、3327、3328三个实例，命令和结果如下图所示。

从查询结果可以看到，global-group中添加了3条数据，并且把结构和数据的修改复制到了my_group1和my_group2。

手工在3327上执行DDL和DML

alter table test.chat_message add column c3327 int;
insert into test.chat_message (src_userid,target_userid,c3327) values(1,20,2);
insert into test.chat_message (src_userid,target_userid,c3327) values(30000001,21,2);
insert into test.chat_message (src_userid,target_userid,c3327) values(-1,22,2);
commit;

手工在3328上执行DDL和DML

alter table test.chat_message add column c3328 int;
insert into test.chat_message (src_userid,target_userid,c3328) values(1,30,3);
insert into test.chat_message (src_userid,target_userid,c3328) values(30000001,31,3);
insert into test.chat_message (src_userid,target_userid,c3328) values(-1,32,3);
commit;

这时查询3326、3327、3328三个实例可以看到，my_group1和my_group2中的DDL和DML只会影响本组，变化不会复制到别的组中。

总结：
1. 程序中要用setShardKey方法设置连接的分片键。如果不用，所有的修改或查询将会在global-group中执行，并且修改将会复制到其它组。
2. 如果按定义分片键没有找到对应的分片，程序会报错。
3. 不要在分片的表中使用auto_increament，结果会出人意料。

参考：
MySQL Fabric官方文档
http://mysqlhighavailability.com/mysql-fabric-add-scaling-to-mysql/

wzy0623

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL Fabric实验（二）Sharding

一、概述 MySQL Fabric这一新的架构为MySQL提供了高可用和向外扩展的特性。本实验专注于使用Fabric对多个MySQL服务器进行读写实现向外扩展。当单个MySQL服务器（或HA组）的写性能达到极限时，可以使用Fabric把数据分布到多个MySQL服务器组。注意这里说的组可以是单一服务器，也可以是HA组。管理员通过建立一个分片映射定义数据如何在多个服务中分片。一个分片
复制链接

扫一扫