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]

Advertisement

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

Command line instructions to create a database in MySQL

Still working on a way to port a sloppy Access database to MySQL. In the end it would be lovely to have a shell script doing all steps in a row:

1. Extract the tables of the database as .csv
2. Create a new (empty) MySQL database
3. Create all tables in the new MySQL db
4. Import the .csvs into those tables

One of the necessary steps is to create a new MySQL database:

$ mysql -u ''adminusername'' -p
mysql> CREATE DATABASE ''databasename'';
mysql> GRANT ALL PRIVILEGES ON ''databasename''.* TO "''username''"@"''hostname''" IDENTIFIED BY "''password''";
mysql> FLUSH PRIVILEGES;
mysql> EXIT


I just collected the code, did not try it out. Just not to forget….

Accessing an Access Database with mdbtools

Basically, what I wanted to do is accessing an MS Access database directly from R. This is probably possible and subject of another post.

Halfway from MS Access to R is mdbtools which can be added to Ubuntu in a terminal with:
sudo aptitude install mdbtools

MDB Tools comes with a set of command line utilties that allow mdb files to be used in shell scripting, extraction to another database, and similar functions. Each program is documented in its man page.

In order to extract the tables of the database into colon separated value (.csv) format create a file, say mdb-explode with the following content:

#! /bin/bash
mkdir $(echo $1|awk -F "." {'print $1'}).csvs
for i in $(mdb-tables $1); do
echo $i
mdb-export $1 $i > $(echo $1|awk -F "." {'print $1'}).csvs/$i.csv
done


Save the the file, open a terminal and make it executable:
chmod +x mdb-explode

If your MS Access database file (.mdb) is in the same directory you would
./mdb-explode YourDatabaseFile.mdb
and all tables of the database end up in a folder called YourDatabaseFile.csvs as .csv files which can be accessed by R via the read.table() function.

The idea is posted on Ubuntuforums by Skrynesaver but the script posted has some typos, which are corrected above. The script posted here worked with Ubuntu 9.04, mdbtools 0.5.99 and an Access 2003 database file.