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

MySQL backup

Just for the record: How to combine mysqldump and zip to archive all MySQL databases on the host. I am using a simple MySQL database server on localhost, to organise research tables before analysis.

mysqldump --all-databases | zip -9 allDB_backup_110415.sql.zip -

mysqldump --all-databases writes the content of all databases into the pipe and
zip -9 Filename - compresses the standard input (note the dash ‘-‘ at the end!) to ‘Filename’ (-9 gives maximum compression).

The reverse following the man page of ‘mysqldump’:

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

cwRsync – Transparent Backups in Windows XP

The Unix tool rsync turned out to be a fast and reliable way to backup my /home folder to an USB-disk.

ITeF!x provides this installation-how2. He seems to maintain the packages. The website is quite informative, though a bit confusing to me. The download link did not work today,   so I found another download location.

Installation

Supported platforms: Client – Windows 9x/NT/2000/XP/2003, Server – NT/2000/XP/2003.

[Download cwRsync.] cwRsync comes as a zip archive containing a Nullsoft Installer package. Unzip downloaded file and run cwRsync_x.x.x_Installer.exe or cwRsync_Server_x.x.x_Installer.exe (server version) :

  1. Click Next at Welcome-page
  2. View license agreement.
  3. Select components that varies depending on package type: Client package has an optional component (Secure Channel Wrapper & Wizard). It makes creation of secure channels to cwrsync servers an easy task.
  4. Specify an installation location.
  5. (cwRsyncServer only) Specify a service account.
  6. Installation starts. By clicking ‘Details’ button, you can get more detailed information about installation. Check if everything seems ok.

You’re DONE! cwRsync is installed on your machine.

Rsync’ing a USB-disk with a Windows XP folder

Use your text-editor-of-choice and paste the following line into it:
rsync -au --exclude '.*' --exclude 'Music/' "/cygdrive/e/" "/cygdrive/h/DATA/home/"

My USB-drive showed up in the windows explorer as E:/ therfore it says /cygdrive/e/. The Windows folder was H:/DATA/home/ which translates into /cygdrive/h/DATA/home/. Change the paths to your specific situation.

Save the file as syncUSB.bat. The .bat tells Windows to execute the script in the commandline.

A variation of the script is
cwRsync\bin\rsync -au --exclude '.*' --exclude 'Music/' "/cygdrive/e/" "/cygdrive/h/DATA/home/"
when both the syncUSB.bat and the installation folder of cwRsync are in the folder which gets updated from the USB-disk.

Manage Amazon S3 Buckets

Yeah, delight!

I was using crappy development scripts to fiddle with S3 buckets on Amazon Web Services (AWS). Creating, listing, deleting buckets and so on was not that straightforward and I found it not well documented… have a growing suspicion that I am just not capable of web-searches…

OK, there is an easy way:
My.Bucket

A graphical user interface.

Unfortunately it refused to work with Ubuntu-Firefox, but did work in Windows XP-IE5.

Ok, another tool just found is the S3 manager add-on to Firefox. This finally turned out to be the easiest way to connect to Amazones Web Services and create an online storage (“bucket”), edit or delete them.