Here is a quick tip that will show how you can get a small sample dataset from a mysql database using mysqldump. We frequently need to get a small snapshot from a very big production database to import it into a development or staging database that will not need all the original data; let’s say we need 1,000,000 records from all the tables in the database; we will just use the option –where=”true LIMIT X”, with X the number of records we want mysqldump to stop after.

Simply we will run something like (add whatever other options you need to mysqldump):

mysqldump --opt --where="true LIMIT 1000000" mydb > mydb1M.sql
Read the rest of this entry »

MySQL 5 has introduced some new interesting features, like stored procedures and triggers.
I will show in this small post how we can backup and restore these components using mysqldump.

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

• –routines – FALSE by default
• –triggers – TRUE by default

Read the rest of this entry »

The result of mysqldump is a flat text file containing the sql commands used to restore the mysql databases/tables that were dumped. Normally the result of mysqldump is compressed (in regular backups) because the size of the resulted file is normally big and good compression rates are achieved on such text files.
To do this manually using gzip we just run:

gzip -v outputfile.sql

This quick tip shows how you can run the same thing on the fly with only one command directly compressing the resulted file. This might be useful in situations where space is a problem and the full dump can’t be saved on the available storage directly because of its size. Also this might be useful to not run it in 2 commands and have one compact command (maybe used also in some backup scripts, etc.) Read the rest of this entry »

Backing up MySQL databases normally involves running mysqldump (either manually or from a script). If you have worked with very big mysql tables (let’s say over 10GB) you already know that dumping such a table will take a while (of course this will depend from many things like hardware, mysql configuration, etc. but still whatever you do it will still take a long time for such big tables).

Read the rest of this entry »

If you site relies on MySQL and stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (manual mistake to delete some data, software errors, hardware errors, server compromise, etc.). In a previous post I have presented that MySQL provides the basic tool (mysqldump) to perform database backups. This is required because backing up a database is a little different than backing up regular files.

With mysqldump anyone can write a small shell script and running it from cron, it will achieve an automatic backup solution. There are many such scripts already available freely and also many commercial solutions also (I assume as I have not tested any really ). The script that I liked the most is AutoMySQLBackup, because it doesn’t have any real requirements (mysqldump of course is needed – in any mysql client package – and gzip or bzip2 to compress the resulting file) and has all the features I was looking for in such a script.

AutoMySQLBackup has all the features I needed: it can backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.). It has also some other features (check the project homepage for full details), that I am not using myself (like email logs for example), but other peoples might find interesting. Read the rest of this entry »

I often need to make a quick backup of one mysql database. This happens right before there will be some major changes to the database, or before performing an upgrade on the mysql server software. Now I don’t recommend this to be done regularly, but still, I use this extensively in this kind of situations… If you are looking for an automated solution to backup your databases you might want to check my other post: “Backup your MySQL databases automatically”
MySQL provides us the tool required to do this: mysqldump. As the name implies, this can make a dump of one database or even to all the databases on the server. You can see all the options on its help manual or on the mysql site.

Basically this is used like:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

Read the rest of this entry »