MySQL hotcopy

• mysqlhotcopy.py script
  • MySQL Hotcopy wrapper
    • Installation
    • Pre-requisites for mysqlhotcopy.py
    • Pre-requisites for mysqlhotcopy
      • Perl module DBD::mysql
    • Features of mysqlhotcopy
    • Commands
    • Usage steps
      • Ownership issue FIXED
      • hotcopy, archive, transfer
      • extract dryrun
      • actual extraction
    • mysqlhotcopy options
    • Size estimation
    • Prepare directories on target for the transfer
    • Table-by-table hotcopy, to minimise table lock time ?
    • Behaviour with crashed tables
    • TODO:
• Locking used by mysqlhotcopy
  • Following mysql log
  • Table locking
    • Locking Timeouts
    • External (file level locking)
• Repair Table
  • References
  • MySQL Versions and USE_FRM option
  • Repairs and replication
  • MyISAM repairs
  • Following sections Stage 3: Difficult repair and Stage 2: Easy safe repair
    • move MYI and MYD into keep
    • truncate the moved table, recreating a 1024 byte MYI and empty MYD
    • Copy the MYD back from keep ontop of the empty MYD
    • Repopulate the index with myisamchk -r -q
    • Check the table survived this trauma
  • myisamcheck memory
  • Create a throwaway DB
  • Verify accessible before being detructive
  • Be destructive, delete the MYI index file for a table
  • Repairing the damage
• MySQL repair table live
  • History Timeline has Moved
    • hotcopy crash
    • all queries fail for crashed table
    • relevant INSERTs
  • Extraction of dybdb1.ihep.ac.cn tarball onto belle7 into tmp_ligs_offline_db_0
  • Repairing crashed DqChannelStatus table in tmp_ligs_offline_db_0
  • DqChannelStatus health checks
  • Make mysqldump with bad SEQNO excluded
    • Inspecting the dump file
    • Digest, compress, publish, test url and digest
    • Features of the dump tmp_ligs_offline_db_0.DqChannelStatus.sql
    • Recreate tables from the dump into tmp_ligs_offline_db_1
    • Compare the repaired with the recreated from dump
    • Validity/Payload divergence
    • Validity only writes, 308 SEQNO
  • Full Server backup
  • Extraction of DqChannel tarball into tmp_ligs_offline_db_0
    • basic check
    • establishing correspondence
      • Quick and dirty
      • Better but slower way
  • Concurrent DBI Writing
  • Four Table Dump/load, 8/70 min
    • Alternative dump/load technique, 8/32 min
  • Fake LOCALSEQNO
  • Append LOCALSEQNO onto the dump
  • Create DB _2 from the four table dump with faked LOCALSEQNO
    • try alternative load to check time
    • loading alternatives
    • dump alternatives
  • Deciding how to proceed
  • MySQL Server versions and repair table limitation
    • version shifting repair issue
    • what this means
  • Compare MySQL servers on belle1 and dybdb1
    • dybddb1
    • belle1
  • Load the dump into belle1
  • belle1 hotcopy
  • Test extraction of belle1 hotcopy onto belle7
    • basic checks OK
    • place tarball on cms01 where Qiumei has access
  • dybdb1 extraction instructions for Qiumei
    • replication config to exclude channelquality_db
    • Check free space on the server
    • Get the tarball and check digest
    • Remove empty channelquality_db
    • Perform extraction into mysql datadir
  • Basic checks of channelquality_db on belle7
  • Large Table Comparisons using grouped digests
  • Insitu repair and trim
  • Aside on MySQL comments
  • Review belle7 Databases
    • database summaries
    • tmp_ligs_offline_db_0
    • tmp_ligs_offline_db_1
    • tmp_ligs_offline_db_2
    • tmp_ligs_offline_db_3
    • tmp_ligs_offline_db_4
    • tmp_ligs_offline_db_5
    • channelquality_db
    • channelquality_db_0
    • testing DB diffing
    • dbdumpload.py is omitting auto increment settings ?
    • mysql bug wrt dumping AUTO_INCREMENT
      • create table and autoincrement
      • altering auto_increment
    • altering auto increment
  • Test use of partitioned dumplocal for large DB diffing
  • Validating Recovered DB
    • Procedure if had verified backups available
    • Our procedure without verified backups
    • Strategy
    • Insitu repair
    • Databases compared
    • Partitioning
• Lessons from MySQL corruption incident
• Next Steps