I’ll show you how to restore the backup, too!
I like to use simple bash scripts to do various tasks. Backing up MySQL is one of them.
I recently decided to start compressing my MySQL backups, as I started including all databases in one fell swoop. I use bzip2 to compress the .sql files produced by mysqldump. bzip2 is standard on pretty much every *nix operating system, so you likely won’t need to install it.
I’m also using Tarsnap for backups now, which is a great service, btw. So cutting the size down on the backups sent to Tarsnap will save me a bit of money. I’ll be doing an article later on that focuses entirely on Tarsnap.. I’m pretty in love with it. You can find an article about installing and using Tarsnap, right here at longren.io.
Anyway, here’s the command I use to backup and compress all databases on my MySQL server:
mysqldump -uroot -p --opt --all-databases | bzip2 -cq9 > /home/tyler/mysql-backups/backupname.sql.bz2
That will create a backup of all databases. The -cq9 piece in the bzip2 command uses stdin for input and tells bzip2 to be quiet. The number, 9, specifies the compression level that bzip2 should use.
The script embedded in the Gist below is what I use to all my databases to /home/tyler/mysql-backups/, and then that folder gets backed up to Tarsnap.
https://gist.github.com/tlongren/85e0d7d04cd507b1ec53
To restore the database, you’ll want to bunzip2 the .sql.bz2 file first:
bunzip2 backupname.sql.bz2
That will leave you with a backupname.sql file. Then bring the resulting .sql file into MySQL like so:
mysql -uroot -pyourpasswordfornoprompt < backupname.sql
The databases will need to either already exist, or there will need to be CREATE DATABASE statements in the .sql file. It’s up to you. I like to create my databases before hand, but it’s just personal preference.
That’s all there is to it. How do you take care of your backups? I’d love to hear how others are doing it. Comments are open.
There’s a thread going on at Hacker News, too.
Update: Made a slight modification to the code and gist suggested by sluggo.
Update 2: HackerNews user Nanzikambe suggested the method above will destroy disk I/O on your server. He suggests using ZFS snapshots instead. The example he posted is in the Gist below, and includes the ability to send the backup to a remote server. A good tutorial on backing up MySQL using ZFS can be found here.
Well, now what?
Work with Me
I'm available for hire and always taking new clients, big and small. Got a project or an idea you'd like to discuss? Startup plan but no developer to make it happen? Just get in touch, I'd love to see if I can help you out!
Leave some Feedback
Got a question or some updated information releavant to this post? Please, leave a comment! The comments are a great way to get help, I read them all and reply to nearly every comment. Let's talk. 😀
Longren.io is proudly hosted by DigitalOcean
nice post! Though at least in version 5.6.19 –opt includes –lock-tables which can’t be used with –single-transaction, since the first locks all tables and the second fetches a “snapshots” allowing updates/inserts during the course of the transaction (given that the tables are transactional). –single-transaction should, especially in multi-user environments, be used with caution since a ALTER/CREATE/RENAME/TRUNCATE/DROP TABLE query during the process could mess it up.
// sluggo
Thanks for the tip sluggo! Gist and post updated to use no –single-transaction.
absolutely, thanks again for a good article!
Just to clarify, you CAN actually use –lock-tables and –single-transaction together but since options are processed first to last whichever is given first will effectively be ignored.
Thanks for clarifying that! I was a little unclear exactly how it worked and was going to ask.
I agree on the –single-transaction suggestion, just wanted to provide one caveat: it’s unsafe if you have MyISAM tables. The resulting backup file will be inconsistent.
FYI–compressing your DB backups isn’t really what you want to do if you’re sending them to Tarsnap. Tarsnap is an incremental backup system, meaning once you have an initial set of backups, Tarsnap will thereafter only do additional backups of the parts of the file that changed. If you compress your DB backup, then Tarsnap will think the entire thing changed. If you leave it uncompressed, Tarsnap can pick out which file blocks changed and backup only those changed sectors. Most databases only have a small percentage of the content that changes each day. Ultimately, this means Tarsnap can have one backup with incremental revisions/changelog, vs if you compress it, you’re suddenly storing multiple copies of the same data in Tarsnap, which adds up really quickly for multi-GB databases.
Tarsnap also handles compression automatically before actually uploading the file, so you’re not paying extra by not compressing the data. The important thing though is make sure you’re piping files into the Tarsnap program in an uncompressed state.
Here the simple intro, how my script works. Please read the script fully before you execute. Actually there is no harm there but you have to do what you doing..
Ref: https://vvcares.com/blog/post/backup-all-mysql-databases-compress
backup each mysql db into a different file, rather than one big file
as with –all-databases. This will make restores easier.
To backup a single database simply add the db name as a parameter (or multiple dbs)
mkdir -p /vv_files/backups
useradd –home-dir /var/backups/mysql –gid backup –no-create-home mysql-backup
Remember to make the script executable, and unreadable by others
chown -R mysql-backup:backup /var/backups/mysql
chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh
crontab entry - backup every 6 hours
sudo crontab -e [ set your desired timing. 6 hrs is safer for sysadmins]