理清思路

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

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

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

 

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

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

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

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

     


               mysql


主执行过程

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

执行后结果

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


主程序


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

迁移后结果展示

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/