The company I'm currently working for has a script that runs at night that takes a MySQL dump of the production environment and imports it into the staging environment. This is a very basic setup - No replication, no fancy backup or migration utilities. Literally just a mysqldump and import.
Were occasionally running into issues with discrepancies in the data. Upon looking for some solutions to verify the data integrity, I found the CHECKSUM TABLE command.
If I wanted to use the CHECKSUM TABLE, when should it be ran on the server? Before or after the mysqldump? Or is it a bad idea to use rely on the checksum if the backup was taken while the table wasn't locked? The origin of the data is the active production mysql server. So id like to find a way to confirm data integrity without locking the tables in the production database, if possible.
Thank you
I'm with @younix on this - fix the problem don't work around it. Your backups are inconsistent because you are not locking the database/table. Find a way to get a consistent backup and your problems will be solved.
I have had good results with LVM snapshots but other solutions exist.