It seems it's usefull to keep the migrate tables while you are working on migration, in case you need to see the mapping and revert/redoing stuff.
But actually I was having some troubling while using the migrate module for other pursings. This SQL error:
SQLSTATE[HY000]  No such file or directory did keeping show up when I did try to use drush migrate:... till delete those tables.
With this query you can delete all table with prefix
SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)
WHERE TABLE_SCHEMA = 'db'
AND TABLE_NAME LIKE 'migrate\%');
SET @delStmt = CONCAT('DROP TABLE ', @tbls);
-- SELECT @delStmt;
PREPARE stmt FROM @delStmt;
DEALLOCATE PREPARE stmt;