This one worked for me in Magento ver. 2.1.0
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE catalog_category_entity;
TRUNCATE TABLE catalog_category_entity_datetime;
TRUNCATE TABLE catalog_category_entity_decimal;
TRUNCATE TABLE catalog_category_entity_int;
TRUNCATE TABLE catalog_category_entity_text;
TRUNCATE TABLE catalog_category_entity_varchar;
TRUNCATE TABLE catalog_category_product;
TRUNCATE TABLE catalog_category_product_index;
INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES ('1', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');
INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES
('1', '69', '0', '1', '1'),
('2', '46', '0', '2', '1'),
('3', '69', '0', '2', '1');
INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES
('1', '45', '0', '1', 'Root Catalog'),
('2', '45', '0', '2', 'Default Category');
SET FOREIGN_KEY_CHECKS = 1;
DELETE FROM url_rewrite WHERE entity_type = 'category';
Seem that we need to increase the memory limit when running re-index a large of number database:
1) Reset db: open your PHP Admin or Mysql GUI tool
update indexer_state set status = 'invalid' where status != 'valid';
2) Re-index again:
php -dmemory_limit=2048M bin/magento indexer:reindex
Best Answer
Use Magento's import/export feature... I found nice article here
http://lero9.co.nz/magento-tips/how-to-export-your-magento-product-database-using-using-magentos-importexport-dataflow-profiles/