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