Our main database (MySQL) has about 50 tables with about 2 million records each, many of the columns are indexed. Before doing any major change, we either copy the entire DB under a new name like "DB_MAIN_Nov112018" and just leave it there as an online backup/copy or we copy individual tables and use the same principle. Does this hurt the performance of the database server as it has to do the indexing of the original DB as well as the many tables and databases that are used as backups?
As long as the database and its tables and many indexes are just sitting there, there is essentially no harm:
- The extra db and tables have no impact on your ordinary queries.
- Some meta operation involving the
information_schemawill notice them.
- The copy takes disk space. (So would a dump.)
An advantage over a dump: You can quickly perform a query on the old copy.