mysql fabric sharding_MySQL Sharding DB （基于Fabric）

系统拓扑

Server id

Gtid

Shard1

10.0.0.201:3306

Root/chenhf123&*(

1

Auto.cnf

/usr/local/mysql3306/

10.0.0.201:3307

2

/usr/local/mysql3307

Shard2

10.0.0.202:3306

3

/usr/local/mysql3306/

10.0.0.202:3307

4

/usr/local/mysql3307

global

10.0.0.203:3306

5

/usr/local/mysql3306/

10.0.0.203:3307

6

/usr/local/mysql3307

Backing store

10.0.0.203:3308

/usr/local/mysqlbackingstore

Fabric manage

10.0.0.203:32274

10.0.0.201:3306

scripts/mysql_install_db --basedir=/usr/local/mysql3306--datadir=/usr/local/mysql3306/data --user=mysql

编辑/usr/local/mysql3306/my.cnf

Bin/mysqld_safe –user=mysql &

Bin/mysql –uroot –socket=./mysql.sock

Bin/mysqladmin –uroot shutdown

10.0.0.201:3307

scripts/mysql_install_db --basedir=/usr/local/mysql3307--datadir=/usr/local/mysql3307/data --user=mysql

编辑/usr/local/mysql3307/my.cnf

Bin/mysqld_safe –user=mysql &

Bin/mysql –uroot –socket=./mysql.sock

Bin/mysqladmin –uroot shutdown

10.0.0.202:3306

Server-id=3

10.0.0.202:3307

Server-id=4

10.0.0.203:3306

Server-id=5

10.0.0.203:3307

Server-id=6

Backing store

scripts/mysql_install_db--basedir=/usr/local/mysqlbackingstore --datadir=/usr/local/mysqlbackingstore/data--user=mysql

CREATE USER 'fabric'@'%' IDENTIFIED BY 'fabric';

GRANT ALL ON fabric.* TO 'fabric'@'%';

Fabric

/etc/mysql/fabric.cfg

Mysqlfabric manage setup

所有节点创建用户fabric

CREATE USER 'fabric'@'%' IDENTIFIED BY 'fabric';

GRANTALL ON *.* TO 'fabric'@'%';

Mysqlfabric manage start

mysqlfabric group create shard-grp-1

mysqlfabric group create shard-grp-2

mysqlfabric group create shard-grp-global

mysqlfabricgroup add shard-grp-1 10.0.0.201:3306

mysqlfabricgroup add shard-grp-1 10.0.0.201:3307

mysqlfabricgroup add shard-grp-2 10.0.0.202:3306

mysqlfabricgroup add shard-grp-2 10.0.0.202:3307

mysqlfabricgroup add shard-grp-global 10.0.0.203:3306

mysqlfabricgroup add shard-grp-global 10.0.0.203:3307

mysqlfabricgroup promote shard-grp-global

mysqlfabricgroup promote shard-grp-1

mysqlfabricgroup promote shard-grp-2

mysqlfabricgroup lookup_servers shard-grp-global

mysqlfabricgroup lookup_servers shard-grp-1

mysqlfabricgroup lookup_servers shard-grp-2

mysqlfabric group health shard-grp-global

mysqlfabric group health shard-grp-1

mysqlfabric group health shard-grp-2

定义分片策略

mysqlfabricsharding create_definition RANGE shard-grp-global

添加分片表和字段

mysqlfabricsharding add_table 2 test.account account_id

mysqlfabricsharding add_table 2 account account_id

mysqlfabricsharding add_table 1 employees.employees emp_no

定义分片信息

mysqlfabricsharding add_shard 1 “shard-grp-1/1, shard-grp-2/1000“ --state=ENABLED

mysqlfabricsharding add_shard 2 ”shard-grp-1/1, shard-grp-2/1000“ --state=ENABLED

验证

mysqlfabricsharding lookup_servers test.account 1

mysqlfabricsharding lookup_servers account 1

mysqlfabricsharding lookup_servers employees.employees 1

停止顺序

Mysqlfabric manage stop

停止所有的mysql实例

启动顺序

启动所有的mysql master实例

启动所有的mysql slave实例

Mysqlfabric manage start