MySQL Database Restore

I want to restore a gateway backup to my laptop for some local development. I also have a MySQL database that needs to go along with it. I made a mysqldump of all tables except the history tables, and then restored the Ignition backup along with the dump, and Ignition is complaining about tag history DB connections.

So figured okay, maybe Ignition wants to create everything from scratch; I dropped the schema and added it again, and eventually some tables were indeed created, but no partitioned history tables. Eventually error messages went away.

So what is the correct way to accomplish what I’m trying to do?

Thanks!

Why didn’t you grab at least the current month history table?

Generally the idea is, you need to restore the DB first and then restore the gateway so when the gateway looks for the database it will find it.

So, you’re saying the correct way is to grab the latest history? What if I don’t care about history?

If you don’t care about the history you have to at least restore the table itself with no data in it. When Ignition sees that it has sqlth_te and other various tables, it will just expect the sqlt_data_xx tables to be there as well. That’s why you seen the errors on you log console.

Clean out the rows in the sqlth_* tables that refer to the data tables.

That makes sense. But it looks like, if the database connection is configured with a user of sufficient privileges, the schema required for (at least) tag history will be created. It makes sense that schema required for tag groups would also be created, but my little project didn’t have any.

So it looks like the answer to my question is this:

If there are NO custom tables, and historical data (history or groups) is NOT important, a database backup and restore IS NOT REQUIRED.

That’s correct. The historian will always try to create whatever tables are necessary for its operation, or refer to the existing ones if they exist. Since you had sqlth_partitions records pointing to some data tables that did not exist, that’s likely why you saw errors.

1 Like