理清思路
现在我们分为两大个步骤：
1、创建分库，并在分库中创建分表。
2、能指定用户的数据到特定的库和表。
现在我们有两个数据库了：
1、test库：里面存放了公共访问的数据表，因此在python我们需要有一个公共数据源。
2、test_1分库：里面存放的是需要分表的表和数据，因此我们需要一个用户原数据所在的数据源。
3、test_n分库：此库是用户的数据需要迁移到其他库的库，因此我们需要一个数据迁移的目录库数据源。
主执行过程
|
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迁移用户数据到指定的分库分表
流程图
主程序
|
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，如需转载请自行联系原作者
