select TABLE_NAME from tables where TABLE_SCHEMA='dbname' and TABLE_NAME not in (从库执行出的结果);
+------------------------------+
| TABLE_NAME |
+-------------------------------+
| gwk_merchant |
| high_category |
| high_comm_product |
| import_cpa_confirm_666 |
| liujuntest |
| test_1501 |
| test_product_1501 |
+-------------------------------+
7 rows in set (0.01 sec)
这样查出来是主库上有而从库上没有的表,,需要在从库上创建,
主库执行导出数据:mysqldump -pxxx --default-character-set=utf8 --quick --single-transaction --master-data=2 --routines --triggers --events dbname gwk_merchant high_category high_comm_product import_cpa_confirm_666 liujuntest test_1501 test_product_1501 | gzip --fast > /data2/5.sql.gz
导出来都 放到从库上,
[root@host8~]# zcat /data/5.sql.gz | mysql -pxxx --socket=/data/mysql/mysql.sock dbname
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'dbname' GROUP BY table_schema;
在主库从库上查看表是不是一样,从库上表多的话,查找出来要drop掉
查出来主库上的所有表:
mysql -pXXX -uroot
use information_schema;
set group_concat_max_len=5000;
select GROUP_CONCAT(concat("'",TABLE_NAME,"'")) from tables where TABLE_SCHEMA='dbname'\G
在从库上过滤不在主库的所有表:
mysql -pxxx -uroot
use information_schema;
select GROUP_CONCAT(concat("'",TABLE_NAME,"'")) from tables where TABLE_SCHEMA='dbname' and TABLE_NAME not in (主库执行出的结果);
| GROUP_CONCAT(concat("'",TABLE_NAME,"'")) 'board_stat_1411_bak','board_stat_1501','board_stat_1502','board_stat_1503','board_stat_1504','board_stat_1505','board_stat_1506','board_stat_1507','board_stat_1508','board_stat_1509','board_stat_1510','board_stat_1511','board_stat_1512','cps_effect_confirm_log_1408_6712_invalid_bak','cps_effect_confirm_log_1411_4018','cps_effect_confirm_log_1506','cps_effect_confirm_log_1507','cps_effect_confirm_log_1508','cps_effect_confirm_log_1509','cps_effect_confirm_log_1510','cps_effect_confirm_log_1511','cps_effect_confirm_log_1512','cps_effect_confirm_log_rollback_15','cps_order_status_log_1502','cps_order_status_log_1503','cps_order_status_log_1504','cps_order_status_log_1505','cps_order_status_log_1506','cps_order_status_log_1507','cps_order_status_log_1508','cps_order_status_log_1509','cps_order_status_log_1510','cps_order_status_log_1511','cps_order_status_log_1512','datasum_1506','datasum_1507','datasum_1508','datasum_1509','datasum_1510','datasum_1511','datasum_1512','datasum_part20150513_bak','datasum_part_huatong_like','flow_stat_1411_bak','flow_stat_1501','flow_stat_1502','flow_stat_1503','flow_stat_1504','flow_stat_1505','flow_stat_1506','flow_stat_1507','flow_stat_1508','flow_stat_1509','flow_stat_1510','flow_stat_1511','flow_stat_1512','hangzhoutest'
1 row in set (0.00 sec)
在从库上删除这些表:
Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name in ('board_stat_1411_bak','board_stat_1501','board_stat_1502','board_stat_1503','board_stat_1504','board_stat_1505','board_stat_1506','board_stat_1507','board_stat_1508','board_stat_1509','board_stat_1510','board_stat_1511','board_stat_1512','cps_effect_confirm_log_1408_6712_invalid_bak','cps_effect_confirm_log_1411_4018','cps_effect_confirm_log_1506','cps_effect_confirm_log_1507','cps_effect_confirm_log_1508','cps_effect_confirm_log_1509','cps_effect_confirm_log_1510','cps_effect_confirm_log_1511','cps_effect_confirm_log_1512','cps_effect_confirm_log_rollback_15','cps_order_status_log_1502','cps_order_status_log_1503','cps_order_status_log_1504','cps_order_status_log_1505','cps_order_status_log_1506','cps_order_status_log_1507','cps_order_status_log_1508','cps_order_status_log_1509','cps_order_status_log_1510','cps_order_status_log_1511','cps_order_status_log_1512','datasum_1506','datasum_1507','datasum_1508','datasum_1509','datasum_1510','datasum_1511','datasum_1512','datasum_part20150513_bak','datasum_part_huatong_like','flow_stat_1411_bak','flow_stat_1501','flow_stat_1502','flow_stat_1503','flow_stat_1504','flow_stat_1505','flow_stat_1506','flow_stat_1507','flow_stat_1508','flow_stat_1509','flow_stat_1510','flow_stat_1511','flow_stat_1512','hangzhoutest');
+----------------------------------------------------------+
| CONCAT( 'drop table ', table_name, ';' ) |
+----------------------------------------------------------+
| drop table board_stat_1411_bak; |
| drop table board_stat_1501; |
| drop table board_stat_1502; |
| drop table board_stat_1503; |
| drop table board_stat_1504; |
| drop table board_stat_1505; |
| drop table board_stat_1506; |
| drop table board_stat_1507; |
| drop table board_stat_1508; |
| drop table board_stat_1509; |
| drop table board_stat_1510; |
| drop table board_stat_1511; |
| drop table board_stat_1512; |
| drop table cps_effect_confirm_log_1408_6712_invalid_bak; |
| drop table cps_effect_confirm_log_1411_4018; |
| drop table cps_effect_confirm_log_1506; |
| drop table cps_effect_confirm_log_1507; |
| drop table cps_effect_confirm_log_1508; |
| drop table cps_effect_confirm_log_1509; |
| drop table cps_effect_confirm_log_1510; |
| drop table cps_effect_confirm_log_1511; |
| drop table cps_effect_confirm_log_1512; |
| drop table cps_effect_confirm_log_rollback_15; |
| drop table cps_order_status_log_1502; |
| drop table cps_order_status_log_1503; |
| drop table cps_order_status_log_1504; |
| drop table cps_order_status_log_1505; |
| drop table cps_order_status_log_1506; |
| drop table cps_order_status_log_1507; |
| drop table cps_order_status_log_1508; |
| drop table cps_order_status_log_1509; |
| drop table cps_order_status_log_1510; |
| drop table cps_order_status_log_1511; |
| drop table cps_order_status_log_1512; |
| drop table datasum_1506; |
| drop table datasum_1507; |
| drop table datasum_1508; |
| drop table datasum_1509; |
| drop table datasum_1510; |
| drop table datasum_1511; |
| drop table datasum_1512; |
| drop table datasum_part20150513_bak; |
| drop table datasum_part_huatong_like; |
| drop table flow_stat_1411_bak; |
| drop table flow_stat_1501; |
| drop table flow_stat_1502; |
| drop table flow_stat_1503; |
| drop table flow_stat_1504; |
| drop table flow_stat_1505; |
| drop table flow_stat_1506; |
| drop table flow_stat_1507; |
| drop table flow_stat_1508; |
| drop table flow_stat_1509; |
| drop table flow_stat_1510; |
| drop table flow_stat_1511; |
| drop table flow_stat_1512; |
| drop table hangzhoutest; |
+----------------------------------------------------------+
57 rows in set (0.06 sec)
执行这些drop语句就可以删除了