mysql 分库 实现_MySQL分库分表python实现分库(7th)

理清思路

现在我们分为两大个步骤:

1、创建分库,并在分库中创建分表。

2、能指定用户的数据到特定的库和表。

现在我们有两个数据库了:

1、test库:里面存放了公共访问的数据表,因此在python我们需要有一个公共数据源。

2、test_1分库:里面存放的是需要分表的表和数据,因此我们需要一个用户原数据所在的数据源。

3、test_n分库:此库是用户的数据需要迁移到其他库的库,因此我们需要一个数据迁移的目录库数据源。

分库流程图

21bf0f782182caeaf303a5a92cc357a6.png

主执行过程

if __name__=='__main__':

# 设置默认的数据库链接参数

db_config_common = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test'

}

# 配置用户数据所在数据库源

db_config_from = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test_1'

}

# 配置用户数据迁移目标数据目录

db_config_from = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

}

sharding = ShardingDatabase()

# 设置公共数据库配置

sharding.get_conn_cursor(db_config_common, 'common')

# 设置用户原数据数据库配置

sharding.get_conn_cursor(db_config_from, 'from')

# 设置用户目标数据库配置

sharding.get_conn_cursor(db_config_to, 'to')

# 创建分库

db_config_to.pop('database')

sharding.create_db(db_config_to)

# 向分库中创建分表

max_num = sharding.get_max_sharding_table_num()

sharding.create_tables(begin = 1, offset = max_num, force=True)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

if __name__=='__main__':

# 设置默认的数据库链接参数

db_config_common = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test'

}

# 配置用户数据所在数据库源

db_config_from = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test_1'

}

# 配置用户数据迁移目标数据目录

db_config_from = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

}

sharding = ShardingDatabase()

# 设置公共数据库配置

sharding.get_conn_cursor(db_config_common, 'common')

# 设置用户原数据数据库配置

sharding.get_conn_cursor(db_config_from, 'from')

# 设置用户目标数据库配置

sharding.get_conn_cursor(db_config_to, 'to')

# 创建分库

db_config_to.pop('database')

sharding.create_db(db_config_to)

# 向分库中创建分表

max_num = sharding.get_max_sharding_table_num()

sharding.create_tables(begin = 1, offset = max_num, force=True)

执行分库程序

python sharding_database.py

python sharding_database.py

1

2

pythonsharding_database.py

pythonsharding_database.py

执行后结果

SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| test               |

| test_1             |

| test_2             |

| test_3             |

+--------------------+

SELECT * FROM test.system_setting;

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

| system_setting_id | name                      | value                                                                                         |

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

|                18 | max_sharding_database_num | 3                                                                                             |

|                19 | sharding_database         | test_1                                                                                        |

|                20 | test_1                    | {'user':'root','password':'root','host':'127.0.0.1','port':3306,'database':'test_1'}          |

|                21 | sharding_database_prefix  | test                                                                                          |

|                38 | harding_database          | test_2                                                                                        |

|                39 | test_2                    | {"port": 3306, "host": "127.0.0.1", "password": "root", "user": "root", "database": "test_2"} |

|                40 | harding_database          | test_3                                                                                        |

|                41 | test_3                    | {"port": 3306, "host": "127.0.0.1", "password": "root", "user": "root", "database": "test_3"} |

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

USE test_2

SHOW TABLES;

+------------------+

| Tables_in_test_2 |

+------------------+

| buy_order_1      |

| buy_order_10     |

| buy_order_2      |

| buy_order_3      |

| buy_order_4      |

| buy_order_5      |

...

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| test               |

| test_1             |

| test_2             |

| test_3             |

+--------------------+

SELECT * FROM test.system_setting;

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

| system_setting_id | name                      | value                                                                                         |

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

|                18 | max_sharding_database_num | 3                                                                                             |

|                19 | sharding_database         | test_1                                                                                        |

|                20 | test_1                    | {'user':'root','password':'root','host':'127.0.0.1','port':3306,'database':'test_1'}          |

|                21 | sharding_database_prefix  | test                                                                                          |

|                38 | harding_database          | test_2                                                                                        |

|                39 | test_2                    | {"port": 3306, "host": "127.0.0.1", "password": "root", "user": "root", "database": "test_2"} |

|                40 | harding_database          | test_3                                                                                        |

|                41 | test_3                    | {"port": 3306, "host": "127.0.0.1", "password": "root", "user": "root", "database": "test_3"} |

+-------------------+---------------------------+-----------------------------------------------------------------------------------------------+

USE test_2

SHOW TABLES;

+------------------+

| Tables_in_test_2 |

+------------------+

| buy_order_1      |

| buy_order_10     |

| buy_order_2      |

| buy_order_3      |

| buy_order_4      |

| buy_order_5      |

...

python迁移用户数据到指定的分库分表

流程图

65ab0fe4d73527504c05ab86d8a5bf18.png

主程序

if __name__=='__main__':

# 设置公共库配置

db_config_common = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test'

}

sharding = ShardingDatabase()

# 设置公共数据库配置

sharding.get_conn_cursor(db_config_common, 'common')

# 指定用户数据到 哪个库 哪个表,如:用户username3数据迁移到 test_3库 10号表

sharding.move_data('username3', 'test_3', 10)

sharding.move_data('username7', 'test_2', 3)

sharding.move_data('username55', 'test_2', 6)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

if __name__=='__main__':

# 设置公共库配置

db_config_common = {

'user'    : 'root',

'password': 'root',

'host'    : '127.0.0.1',

'port'    : 3306,

'database': 'test'

}

sharding = ShardingDatabase()

# 设置公共数据库配置

sharding.get_conn_cursor(db_config_common, 'common')

# 指定用户数据到 哪个库 哪个表,如:用户username3数据迁移到 test_3库 10号表

sharding.move_data('username3', 'test_3', 10)

sharding.move_data('username7', 'test_2', 3)

sharding.move_data('username55', 'test_2', 6)

上面程序展示了将三位用户的数据迁移到指定的分库和分表中:

1、用户:username3 -> 库:test_3 -> 表:*_10

2、用户:username7 -> 库:test_2 -> 表:*_3

3、用户:username55 -> 库:test_2 -> 表:*_6

迁移后结果展示

SELECT * FROM user;

+---------+-------------+-------------+------------+---------+

| user_id | username    | password    | table_flag | db_name |

+---------+-------------+-------------+------------+---------+

|       3 | username3   | password3   |         10 | test_3  |

|       7 | username7   | password7   |          3 | test_2  |

|      55 | username55  | password55  |          6 | test_2  |

...

USE test_3

SELECT * FROM sell_order_10 LIMIT 0, 1;

+---------------------+---------------+---------+---------+--------+

| sell_order_id       | user_guide_id | user_id | price   | status |

+---------------------+---------------+---------+---------+--------+

| 3792112071144902657 |             7 |      10 | 9720.00 |      1 |

+---------------------+---------------+---------+---------+--------+

SELECT * FROM buy_order_10 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974680104961 |       3 |             1 |

+---------------------+---------+---------------+

SELECT * FROM goods_10 LIMIT 0, 1;

+---------------------+------------+--------+----------+

| goods_id            | goods_name | price  | store_id |

+---------------------+------------+--------+----------+

| 3792111953670836225 | goods1     | 370.00 |        3 |

+---------------------+------------+--------+----------+

SELECT * FROM order_goods_10 LIMIT 0, 1;

+---------------------+---------------------+---------------------+---------------+--------+------+

| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price  | num  |

+---------------------+---------------------+---------------------+---------------+--------+------+

| 3792112350317776897 | 3792112071144902657 | 3792111953670836225 |             7 | 370.00 |    1 |

+---------------------+---------------------+---------------------+---------------+--------+------+

USE test_2

SELECT * FROM sell_order_3 LIMIT 0, 1;

+---------------------+---------------+---------+---------+--------+

| sell_order_id       | user_guide_id | user_id | price   | status |

+---------------------+---------------+---------+---------+--------+

| 3792112052236980225 |             6 |      10 | 7790.00 |      1 |

+---------------------+---------------+---------+---------+--------+

SELECT * FROM buy_order_3 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974399086593 |       7 |             1 |

+---------------------+---------+---------------+

SELECT * FROM order_goods_3 LIMIT 0, 1;

+---------------------+---------------------+---------------------+---------------+---------+------+

| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |

+---------------------+---------------------+---------------------+---------------+---------+------+

| 3792112312489349121 | 3792112052236980225 | 3792111952869724161 |             6 | 6368.00 |    2 |

+---------------------+---------------------+---------------------+---------------+---------+------+

USE test_2

SELECT * FROM buy_order_3 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974399086593 |       7 |             1 |

+---------------------+---------+---------------+

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

SELECT * FROM user;

+---------+-------------+-------------+------------+---------+

| user_id | username    | password    | table_flag | db_name |

+---------+-------------+-------------+------------+---------+

|       3 | username3   | password3   |         10 | test_3  |

|       7 | username7   | password7   |          3 | test_2  |

|      55 | username55  | password55  |          6 | test_2  |

...

USE test_3

SELECT * FROM sell_order_10 LIMIT 0, 1;

+---------------------+---------------+---------+---------+--------+

| sell_order_id       | user_guide_id | user_id | price   | status |

+---------------------+---------------+---------+---------+--------+

| 3792112071144902657 |             7 |      10 | 9720.00 |      1 |

+---------------------+---------------+---------+---------+--------+

SELECT * FROM buy_order_10 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974680104961 |       3 |             1 |

+---------------------+---------+---------------+

SELECT * FROM goods_10 LIMIT 0, 1;

+---------------------+------------+--------+----------+

| goods_id            | goods_name | price  | store_id |

+---------------------+------------+--------+----------+

| 3792111953670836225 | goods1     | 370.00 |        3 |

+---------------------+------------+--------+----------+

SELECT * FROM order_goods_10 LIMIT 0, 1;

+---------------------+---------------------+---------------------+---------------+--------+------+

| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price  | num  |

+---------------------+---------------------+---------------------+---------------+--------+------+

| 3792112350317776897 | 3792112071144902657 | 3792111953670836225 |             7 | 370.00 |    1 |

+---------------------+---------------------+---------------------+---------------+--------+------+

USE test_2

SELECT * FROM sell_order_3 LIMIT 0, 1;

+---------------------+---------------+---------+---------+--------+

| sell_order_id       | user_guide_id | user_id | price   | status |

+---------------------+---------------+---------+---------+--------+

| 3792112052236980225 |             6 |      10 | 7790.00 |      1 |

+---------------------+---------------+---------+---------+--------+

SELECT * FROM buy_order_3 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974399086593 |       7 |             1 |

+---------------------+---------+---------------+

SELECT * FROM order_goods_3 LIMIT 0, 1;

+---------------------+---------------------+---------------------+---------------+---------+------+

| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |

+---------------------+---------------------+---------------------+---------------+---------+------+

| 3792112312489349121 | 3792112052236980225 | 3792111952869724161 |             6 | 6368.00 |    2 |

+---------------------+---------------------+---------------------+---------------+---------+------+

USE test_2

SELECT * FROM buy_order_3 LIMIT 0, 1;

+---------------------+---------+---------------+

| buy_order_id        | user_id | user_guide_id |

+---------------------+---------+---------------+

| 3792111974399086593 |       7 |             1 |

+---------------------+---------+---------------+

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值