Tag: bash

4 posts

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.

Wysyłanie plików na serwer - rsync

RSYNC musi być zainstalowany po stronie klienta i na serwerze.

strona/scripts/upload.sh

	#!/bin/sh

SRC='/home/mateusz/projekty/strona/'

rsync -ave 'ssh' \
        --filter='merge .rsync-filter' \
        $SRC \
        [email protected]:DIRECTORY

strona/scripts/.rsync-filter

	- /scripts
- /tests
- /nbproject
- /.hg
- /.hgignore
- /.zfproject.xml
- /application/configs/site.xml
- /application/configs/application.ini
- /public/media
- /public/images/captcha
- /public/index.php
- /data/cache
- /library/Zend
- /library/ZendX

użycie:

	cd /home/mateusz/projekty/strona/
./upload.sh