Linux

2 posts in category

Extracting single table from huge MySQL dump

During last few weeks I had to work with relatively big MySQL dumps. I had to find interesting rows in about 400 files, each of them taking 40 minutes to import. In order to speed things up, I found simple tool that allowed me to extract only interesting tables.

The tool is actually single Perl script, named extract_sql.pl (available on Github). It allows extracting tables with simple command:

[email protected]:~$ extract_sql.pl -t TABLE_NAME -r DUMP_FILE.sql

This command will print dump to console output, so you may want to redirect it to some file:

[email protected]:~$ extract_sql.pl -t TABLE_NAME -r DUMP_FILE.sql > table_name.sql

Finally, extract_sql.pl is able to read input from stdin, so it is easy to extract and import single table from compressed dump file:

[email protected]:~$ zcat DUMP_FILE.sql | extract_sql.pl -t TABLE_NAME \
| mysql dest_database -u username -p

Automated MySQL backup on dedicated server or VPS

I just moved all my small projects to new dedicated server. I have to admit, until now I wasn't paying attention to regular backups. I simply ran mysqldump and copied everything to my laptop every few months. I didn't have any problems with that, as my data was not very critical. But, this time I decided to build something better - I wanted database backups to be generated automatically, at regular intervals.

I knew more or less what to do, I just had to put all pieces together. This tutorial shows necessary steps to build similar solution on your server.