MySQL – Backup of Big MyISAM Tables

If you ever dealt with backup of MySQL tables, you probably used mysqldump utility, which allows you to dump all data into some mytables.sql file, which you can backup somewhere, import to other MySQL server etc. I used this procedure for a thousands times so far, and generally it goes as follows:

export:

mysqldump -udinke -pmojpass -hhostname.of.server1 dbname table1 table2 tableN > dump_file.sql

mysql -udinke -pmojpass -hhostname.of.server2 dbname  < dump_file.sql

Sometimes there is a problem when you move data from new version of MySQL to old, and in that case you have to specify proper compability flag when running mysql dump (--compatible=name where name can be mysql323, mysql40, postgresql, oracle etc.).

Anyway, few days ago in order to move data from one MySQL to another, I had to dump some ... let say big mysql tables (about 10 tables, where each contained about 10 millions of records). After long lasted procedure (dump to file, gzip, scp to other server) I finally started import. However, after 3 hours (yes, three hours) instead of Linux prompt I got this:

[dinke@um-917 ~/public_html]$ mysql -udinke -p325ewfwt23rasf
keyword_discovery < es_miner_data.sql
ERROR 1582 (23000) at line 163833: Duplicate entry '1167548' for key
'PRIMARY'

WTF? I moved data from old server to new, which means import should run without compability problems. It could be indexes on old table were damaged, but instead to wait like 2 more hours in order to complete check & repair procedure, I've decided to create dump file, this time with ignore option, so all insert queries in dump file are "insert ignore", so in case of error like previous one, errors will be ignored. Not very smart, but those data are not really high sensitive, and I can afford to lose few records but can't afford to lose 10 hours for import!

So, dump, gzip, scp, import again ... which lasted long... loooooong .... so fucking long that after 3 hours after I started import I started to think about other solutions. And solution was dumb but effective. We simple moved all MySQL data files (*.MYI, *.MYD i *.frm) from one server to another, and then we run myisamchk in order to fix those tables because ... we didn't shutdown MySQL server during copy procedure which generally could cause some problems with data.

All in all, this procedure went very fast (the longest was actual copy from one host to another), and in less than half hour I got everything settled down.

At the end I came with conclusion that in case of really big tables, using tool like mysqldump is unaccepted as backup solution because it takes literally hours to complete. One of solution to that problem can be to copy MySQL data files like I did. That shouldn't be a problem, because tables are "platform safe" meaning, binary file created on one platform (ie *.MYI file on Linux) will work without problem when moved to other problem (ie. on Windows). The only one real problem is that if you copy data wihout MySQL shutdown, table files there are in some kind of "state of flux" which can be a problem, especialy if they are highly used in moment when you do copy. That's why you need to do myisamchk on it.