Compressed backup of MySQL database

Wrote several posts on this topic, but none was 100% right. The following is a blockquote from webcheatsheet.com and looks much better researched then my previous tries:

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine
Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql
To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

Advertisements

Remove empty lines with SED

Raw data is often delivered in Excel-sheets with a lot of noise and formating around. For analysis in R or other packages the real raw data is required. Scripting the “deformating” in plain text / csv files using shell tools like SED, AWK or Pearl to remove excess text in the datasheets makes it possible to rerun the procedure or track systematic errors.

Removing empty lines from a file containing code in plain text (like .csv, .html, .php, etc…) is very easy with SED in a UNIX/ MAC OS shell and even possible in the Windows CMD (after installing SED). The following is a blockquote from ZoneO-tips for Mandriva Linux which I found really useful and well written:

So, open up a konsole and move into the directory where your file resides (cd MyDirectory). And here we go with the two lines that’ll do the job

sed '/^$/d' myFile > tt
mv tt myFile

Here is what happens:

sed '/^$/d' myFile removes all empty lines from the file myFile and outputs the result in the console, > tt redirects the output into a temporary file called tt,
mv tt myFile moves the temporary file tt to myFile.

Now, you may have 100 html files to correct at the same time. That’s where foreach comes in… Let’s say you want to correct all files ending with .html, here is what you should do:

Open up a konsole, move into the directory where your html files reside, type the following commands:

foreach file (*html)
sed '/^$/d' $file > tt
mv tt $file
end

Finished!

Match and replace TAB

I am using the terminal inside emacs to modify and clean textfiles (colon separated) for import into MySQL – mostly with SED, GREP, AWK.
Replacing TAB in a textfile with SED was not as straightforward as I thought. So Google came up with an excellent post on macworld.com and here especially one post suggesting perl as a solution. It turned out perl behaves inside the pipe the same way as SED/AWK.

Replacing TAB ‘\t‘ with colon ‘,‘ in a textfile ‘SomeFile.txt‘ can be done with:

perl -pi -e 's/\t/,/g' SomeFile.txt

or using the pipe, which can be combined with grep, sed, awk, tr, head, tail, etc…

cat SomeFile.txt | perl -pi -e 's/\t/,/g'

*Very* similar to the sed invocation, except for the flags. “-pi” for “*print* every line out after making the changes, while editing *inplace*. That is, no backup file is kept. In reality, a temp file is written, and if all is well (ie the operation succeeds), the temp file is shifted “onto” the original file, giving the appearance of an in-place edit.

MySQL command line with OS X 10.6.

After installing MySQL 5.1.54 on Mac OS X 10.6.6 (Snow Leopard) I tried to access the server in the terminal and got:

-bash: mysql: command not found

Bash could not find the path, so I had to add it. I am not that familiar with the internals of the unix terminal anyway, so I found a highly informative thread on superuser.com.

Especially the post of Jim Logan solved the issue:

Take a look at the file /etc/paths, which is used by /usr/libexec/path_helper, which is used by /etc/profile. For MacPorts, use “sudo” to append “/opt/local/bin” to the end of the /etc/paths file and reopen the terminal window.

So I opened the terminal and used nano:
sudo nano /etc/paths

and appended the line
/usr/local/mysql/bin

to the file, saved, exited, restarted the terminal …. and voila, solved.