1

I have a large database that needs to be rebuilt every 24h. The database is built using a custom script on a server that puls data from different files. The problem is that the whole process takes 1 min to complete and that is 1 min downtime because we need to drop the whole database in order to rebuild it (there is no other way than to drop it).

At first, we planned to build a temporary database, and drop the original and then rename the temporary to the original name but MySQL doesn't support database renaming.

The second approach was to dump .sql file from temp database and import it to main(original) database but that also causes downtime.

What is the best way to do this?

John
  • 313
  • 3
  • 14
  • 1
    try this: https://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name – Rahul Jain Apr 28 '18 at 11:37
  • "I have a large ... rebuilt every 24h" What do you mean with rebuild? Do you mean adding new indexes/columns, removing indexes/columns in that case check InnoDB engines online DDL which is available from MySQL 5.6 -> https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid Add column, Remove column (both In Permits Concurrent DML? = Yes) can be done without locking the table (for other inserts, updates or deletes queries) which means no downtime..also read https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-syntax.html – Raymond Nijland Apr 28 '18 at 11:57
  • It means adding, removing and updating rows and adding/removing tables and their definition. The process it to complex so we need to drop the database. – John Apr 28 '18 at 12:00
  • ok i think you should take a look into Gorden's answer makes alot off sense. – Raymond Nijland Apr 28 '18 at 12:05

1 Answers1

5

Here is something that I do. It doesn't result in zero downtime but could finish in less than a second.

Create a database that only has interface elements to your real database. In my case, it only contains view definitions, and all user queries go through this database.

Create a new database each night. When it is done, then update the view definitions to refer to the new database. I would recommend either turning off user access to the database containing the views while you are updating them or deleting all of the views and recreating them -- this prevents partial access to the old database. Because creating views is fast, this should be a very fast operation.

We do all of this through a job. In fact, before changing the production views, we test the view creation on another database to be sure they are all working.

Obviously, if you use alter view instead of requiring consistency across all the views, then there is no downtime, just a brief period of inconsistency.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786