理清思路
现在我们分为两大个步骤：
1、创建分库，并在分库中创建分表。
2、能指定用户的数据到特定的库和表。
现在我们有两个数据库了：
1、test库：里面存放了公共访问的数据表，因此在python我们需要有一个公共数据源。
2、test_1分库：里面存放的是需要分表的表和数据，因此我们需要一个用户原数据所在的数据源。
3、test_n分库：此库是用户的数据需要迁移到其他库的库，因此我们需要一个数据迁移的目录库数据源。
分库流程图
主执行过程
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迁移用户数据到指定的分库分表
流程图
主程序
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 |
+---------------------+---------+---------------+
205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
