0

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

Justin
  • 137
  • 2
  • 9
  • 2
    `backup was taken while the table wasn't locked` <-- there's the source of your inconsistencies. If you want to use checksum table, you'd have to run it before the backup, take a backup and restore that backup to somewhere and run another checksum to verify they match. Personally, I would attack the problem, not the symptoms. –  Sep 22 '17 at 18:07
  • 1
    "there's the source of your inconsistencies". It's not clear on what grounds you make this statement. While it can be true it takes quite an effort to get an inconsistent mysqldump. I doubt an average MySQL user can do it. – akuzminsky Sep 27 '17 at 03:28

1 Answers1

0

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.

user9517
  • 115,471
  • 20
  • 215
  • 297