MySQL Like for Like – 100% accurate Backup/ Restore

backupMySQLperformance-testingrestore

I've struggled to find the answer to get a perfect backup and restore for MySQL.

I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation – the whole things is like for like – 100% preserved.

I tried copying the raw files (.frm, .ibd, .trn, .myi etc) files across but get

Table '' doesn't exist in engine.

I also tried dBForge to copy the database which worked, but after looking at the fragmentation levels I can see differences, so I think this isn't a like for like (although I'm copying from production so perhaps I saw some anomaly there).

mysqldump – Does this do it?

Best Answer

It's not safe to copy data files unless mysqld is stopped.

I would suggest Percona XtraBackup for a physical backup of the database. This should be close to preserving the exact pages, including any fragmentation.

Another alternative is filesystem snapshots. You could revert to the snapshot after each of your tests.

Related Question