0

Split Large MySQL Dump Files

Posted (Updated ) in Database, Linux

I’ve been using my Cloud Database Backup script for a few months now for weekly scheduled backups of my MySQL databases to Google Docs. Everything has been going smoothly, however I’m starting to run low on quota. For this reason I decided to look into splitting the SQL dumps into chunks small enough to be convertible and doing an upload-convert rather than a zip upload which will result in literally unlimited, quote free database backups as frequently as I like! The focus of this post though is the actual splitting script which splits a given MySQL dump into chunks of x characters.

As always, download it here.

The standard MySQL dumps with grouped INSERT statements won’t work here, as if you have thousands or more rows in your table, the statement may be contain more characters than the chunk size resulting in a file not able to be converted. So use –skip-extended-insert like so:

mysqldump -u<username> -p --databases <dbname(s)> --skip-extended-insert > dbdump.sql

Use the splitter script like so:

./flynsarmy_dbchunk.py --filepath=/path/to/dump.sql

The following arguments are accepted:

--filepath=/path/to/file
--max_chunk_size [1024000 by default]
--debug [False by default]

The script will split on the closest INSERT or blank line before the specified chunk size limit is reached. It’ll use the input filename as the output names appending .<num>.txt on the end and output the names of the chunks. Here’s an example:

Input:

$ ./flynsarmy_dbchunk.py --filepath=tmp/dbdump.sql

Output:

tmp/mydump.sql.0.txt
tmp/mydump.sql.1.txt
tmp/mydump.sql.2.txt
tmp/mydump.sql.3.txt
tmp/mydump.sql.4.txt
tmp/mydump.sql.5.txt
tmp/mydump.sql.6.txt
tmp/mydump.sql.7.txt
tmp/mydump.sql.8.txt
tmp/mydump.sql.9.txt
tmp/mydump.sql.10.txt
tmp/mydump.sql.11.txt
tmp/mydump.sql.12.txt
tmp/mydump.sql.13.txt
tmp/mydump.sql.14.txt
tmp/mydump.sql.15.txt
tmp/mydump.sql.16.txt
tmp/mydump.sql.17.txt
tmp/mydump.sql.18.txt
tmp/mydump.sql.19.txt
tmp/mydump.sql.20.txt
tmp/mydump.sql.21.txt
tmp/mydump.sql.22.txt
tmp/mydump.sql.23.txt
tmp/mydump.sql.24.txt
tmp/mydump.sql.25.txt
tmp/mydump.sql.26.txt
tmp/mydump.sql.27.txt
tmp/mydump.sql.28.txt
tmp/mydump.sql.29.txt
tmp/mydump.sql.30.txt
tmp/mydump.sql.31.txt

This script will be uploaded into my Cloud Database Backup tool once some weird issues have been worked out.