MySQL分库分表python实现分库(7th)

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL分库分表python实现分库(7th)

技术小胖子 2017-11-08 17:48:00 浏览1742
展开阅读全文

理清思路

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

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

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

 

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

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

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

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

     


               mysql


主执行过程

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)

执行分库程序

1
2
python sharding_database.py
python sharding_database.py

执行后结果

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迁移用户数据到指定的分库分表

流程图

    

         mysql


主程序


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

分库分表迁移数据python程序:sharding_database.py

迁移后结果展示

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 |
+---------------------+---------+---------------+

文章出自:http://www.ttlsa.com/mysql/mysql-distributed-database-and-table-python-example/





      本文转自027ryan  51CTO博客,原文链接:http://blog.51cto.com/ucode/1746038,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注