Today I’ve been fighting a 10+ gigabyte mysql import. I’ve come across and solved a few problems that might be handy to someone in the future.
/etc/my.cnf, it specifies the maximum packet size. I had the problem where some of the insert statements exceeded 1 megabyte in size, so increasing this limit to 64M solved my first problem.
Resuming from mid-way through an import
Next up, I wanted to
cat the sql import, but skip all the tables up to table
xyz. This I accomplished with a little bit of sed magic:
cat dump.sql | sed -n -e '/`some_table_name`/,$p' | mysql --init-command="SET foreign_key_checks = 0;" database_name
set foreign_key_checks parameter to mysql, this is normally set at the top of your dump file, because we’re truncating the front off the dump file, you need to disable foreign key checks explicitly.
Using pipe viewer
PV is a tool to graph your progress. Instead of running
cat and hoping for the best, you get graphical feedback on how long your database restore process will take.