mysql 大表复制_Mysql千万级大表同步复制的一些问题

1 [root@iZ23eoaorbaZ ~]# pt-online-schema-change -u root -p xxxxxx -h localhost -A utf8 D=jinyuanbao,t=DistributionGoods --new-table-name DistributionGoods_0 --no-drop-trigger --no-swap-table --no-drop-new-table --execute2 No slaves found. See --recursion-method ifhost iZ23eoaorbaZ has slaves.3 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.4 Operation, tries, wait:5 copy_rows, 10, 0.25

6 create_triggers, 10, 1

7 drop_triggers, 10, 1

8 swap_tables, 10, 1

9 update_foreign_keys, 10, 1

10 Altering `jinyuanbao`.`DistributionGoods`...11 Creating new table...12 Created new table jinyuanbao.DistributionGoods_0 OK.13 2015-08-18T10:21:47Creating triggers...14 2015-08-18T10:21:47Created triggers OK.15 2015-08-18T10:21:47 Copying approximately 16259938rows...16 Copying `jinyuanbao`.`DistributionGoods`: 4% 11:05remain17 Copying `jinyuanbao`.`DistributionGoods`: 8% 11:18remain18 Copying `jinyuanbao`.`DistributionGoods`: 11% 12:02remain19 Copying `jinyuanbao`.`DistributionGoods`: 14% 12:16remain20 Copying `jinyuanbao`.`DistributionGoods`: 16% 12:18remain21 Copying `jinyuanbao`.`DistributionGoods`: 19% 12:25remain22 Copying `jinyuanbao`.`DistributionGoods`: 22% 12:05remain23 Copying `jinyuanbao`.`DistributionGoods`: 25% 11:44remain24 Copying `jinyuanbao`.`DistributionGoods`: 28% 11:07remain25 Copying `jinyuanbao`.`DistributionGoods`: 32% 10:27remain26 Copying `jinyuanbao`.`DistributionGoods`: 35% 09:50remain27 Copying `jinyuanbao`.`DistributionGoods`: 39% 09:13remain28 Copying `jinyuanbao`.`DistributionGoods`: 42% 08:37remain29 Copying `jinyuanbao`.`DistributionGoods`: 46% 08:02remain30 Copying `jinyuanbao`.`DistributionGoods`: 50% 07:27remain31 Copying `jinyuanbao`.`DistributionGoods`: 53% 06:54remain32 Copying `jinyuanbao`.`DistributionGoods`: 57% 06:24remain33 Copying `jinyuanbao`.`DistributionGoods`: 60% 05:51remain34 Copying `jinyuanbao`.`DistributionGoods`: 64% 05:19remain35 Copying `jinyuanbao`.`DistributionGoods`: 67% 04:47remain36 Copying `jinyuanbao`.`DistributionGoods`: 70% 04:19remain37 Copying `jinyuanbao`.`DistributionGoods`: 74% 03:48remain38 Copying `jinyuanbao`.`DistributionGoods`: 78% 03:14remain39 Copying `jinyuanbao`.`DistributionGoods`: 81% 02:47remain40 Copying `jinyuanbao`.`DistributionGoods`: 84% 02:18remain41 Copying `jinyuanbao`.`DistributionGoods`: 87% 01:48remain42 Copying `jinyuanbao`.`DistributionGoods`: 91% 01:18remain43 Copying `jinyuanbao`.`DistributionGoods`: 94% 00:48remain44 Copying `jinyuanbao`.`DistributionGoods`: 97% 00:20remain45 2015-08-18T10:38:03Copied rows OK.46 Not dropping old table because --no-drop-triggers was specified.47 Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute:48 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_del`;49 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_upd`;50 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_ins`;51 Not dropping the new table `jinyuanbao`.`DistributionGoods_0` because --no-drop-new-table was specified. To drop the new table, execute:52 DROP TABLE IF EXISTS `jinyuanbao`.`DistributionGoods_0`;53 Successfully altered `jinyuanbao`.`DistributionGoods`.54 [root@iZ23eoaorbaZ ~]# pt-online-schema-change -u root -p xxxxxxx -h localhost -A utf8 D=jinyuanbao,t=DistributionGoods_0 --new-table-name DistributionGoods_1 --no-drop-trigger --no-swap-table --no-drop-new-table --execute55 No slaves found. See --recursion-method ifhost iZ23eoaorbaZ has slaves.56 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.57 Operation, tries, wait:58 copy_rows, 10, 0.25

59 create_triggers, 10, 1

60 drop_triggers, 10, 1

61 swap_tables, 10, 1

62 update_foreign_keys, 10, 1

63 Altering `jinyuanbao`.`DistributionGoods_0`...64 Creating new table...65 Created new table jinyuanbao.DistributionGoods_1 OK.66 2015-08-18T10:46:06Creating triggers...67 2015-08-18T10:46:06Created triggers OK.68 2015-08-18T10:46:06 Copying approximately 16113957rows...69 Copying `jinyuanbao`.`DistributionGoods_0`: 4% 11:21remain70 Copying `jinyuanbao`.`DistributionGoods_0`: 8% 11:08remain71 Copying `jinyuanbao`.`DistributionGoods_0`: 12% 10:40remain72 Copying `jinyuanbao`.`DistributionGoods_0`: 16% 10:17remain73 Copying `jinyuanbao`.`DistributionGoods_0`: 19% 10:02remain74 Copying `jinyuanbao`.`DistributionGoods_0`: 23% 09:48remain75 Copying `jinyuanbao`.`DistributionGoods_0`: 27% 09:16remain76 Copying `jinyuanbao`.`DistributionGoods_0`: 30% 08:54remain77 Copying `jinyuanbao`.`DistributionGoods_0`: 34% 08:31remain78 Copying `jinyuanbao`.`DistributionGoods_0`: 37% 08:11remain79 Copying `jinyuanbao`.`DistributionGoods_0`: 41% 07:51remain80 Copying `jinyuanbao`.`DistributionGoods_0`: 44% 07:27remain81 Copying `jinyuanbao`.`DistributionGoods_0`: 48% 07:02remain82 Copying `jinyuanbao`.`DistributionGoods_0`: 51% 06:40remain83 Copying `jinyuanbao`.`DistributionGoods_0`: 54% 06:16remain84 Copying `jinyuanbao`.`DistributionGoods_0`: 58% 05:47remain85 Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute:86 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_0_del`;87 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_0_upd`;88 DROP TRIGGER IF EXISTS `jinyuanbao`.`pt_osc_jinyuanbao_DistributionGoods_0_ins`;89 Not dropping the new table `jinyuanbao`.`DistributionGoods_1` because --no-drop-new-table was specified. To drop the new table, execute:90 DROP TABLE IF EXISTS `jinyuanbao`.`DistributionGoods_1`;91 `jinyuanbao`.`DistributionGoods_0` was not altered.92 2015-08-18T10:54:31 Error copying rows from `jinyuanbao`.`DistributionGoods_0` to `jinyuanbao`.`DistributionGoods_1`: Threads_running=58 exceeds its critical threshold 50