Hi, one of my clients recently had an issue where the MYSQL database crashed due to a corrupted file. While I was able to recover most of the data, it’s clear they need a regular data backup running. The question i have is when the data content becomes so large (1/2TB), will running an MYSQL dump affect Ignition logging historical data to the database?
Running Ignition version 7.7.4 with MYSQL Version 5.6.
Any suggestions appreciated.
A little more work but the recommended approach when the main DB can’t be taken down for backups would be to create a second DB on a different (backup) PC and have it replicate the main db. This backup DB can then be started/stopped/backed up/etc at will.
See section here for more information.
Yeah I have looked into this already. Would I be right in thinking that data would not be logged to the database during a large data backup? It’s a little more work, but ultimatley probably the best option is the replication db.
One ineteresting question the client had on this was, what if the slave db has replicated the same corrupt file as on the master db. Then you are lefft with two corupt db files. We are not using MYSQL enterprise, I think that package offers fore warning that a db is starting the lag or have trouble. I’m not too familiar with this. Any views would be great.
I have done this in the past. There is a query you can run that tells you if the replicant is falling behind. We tied this to SMS system to send notices when it got beyond some limit.
Backing up the running replicant may cause it to slow down. The backup process is ACID if you are using innodb. Otherwise you should stop the replicant first.
As the replication works through SQL statements, file corruption on the main DB should not cause problems on the replicant. Bad SQL will cause problems. DROP DATABASE is replicated