Task: back up a database or all databases of a MySQL server.
We can go two ways:
- Stop the MySQL server and simply copy the databases
- Make a hot backup (without stopping the MySQL server)
Each method obviously has its pros and cons. For example, to perform a hot backup you need to know the login/password of a user with permission to back up the database, and the resulting backup file will be somewhat larger than if you just copy the database directly.
On the other hand, when copying on the fly you don't need to stop the MySQL server (which, obviously, would create a denial of service for the duration of the backup), and you don't need to know the path to the databases on the server. And one more thing - the database name doesn't always equal the name of its directory in the file system!
Way one: doing a proper hot backupFor this we'll need the "mysqldump" utility. It comes bundled with the MySQL server itself. Here's an example of calling this utility:
$ mysqldump -u root -p mydatabase > mydatabase.bak
In this example we use the utility as the root user (the -p parameter indicates that the password should be requested for the user) to create a backup of the database named "mydatabase". The backup result will be written as an SQL dump into the file mydatabase.bak
As you can see - this way we made a backup of a single database. In the next example we'll make a backup of all the databases on the server. Keep in mind that:
- all databases will be dumped into a single file, which, given their large total size, will turn into simply a huge lump of a dump;
- we need to run the backup as a user who has access rights to all databases - that is, as root or its equivalent.
$ mysqldump -u root -p --all-databases > alldatabases.bak
In this example all the databases will be dumped into a file named alldatabases.bak
Now let's make a backup of two or more databases. In our example, two databases (mydatabase1 and mydatabase2) will be backed up into a single file mydatabases.bak
$ mysqldump -u root -p --databases mydatabase1 mydatabase2 > alldatabases.bak
Doing a hot backup - all databases, but each one into its own fileThis technique isn't mine - it's taken from the site itblog.su and tweaked.
For this we write a little script with the following content:
#!/bin/sh
for i in `mysql -u root -pMysuperpassword -e'show databases;' | grep -v information_schema | grep -v Database`; do mysqldump -u root -pMysuperpassword $i > $i.bak; gzip -f $i.bak;done
Here, instead of -pMysuperpassword, substitute the correct root password (-p<password>, i.e. -p with the password stuck right after it, no space).
The -f flag in gzip makes it overwrite existing files without asking "Are you sure?".
Don't forget to make the script executable.
In this case, all databases on the MySQL server will be backed up, but separately and without stopping the server. Each database will be placed into its own file named after the database + a bak extension, and that file will then also be compressed with gzip (+ .gz).
Here's what we get (example):
mydatabase1.bak.gz
mydatabase2.bak.gz
...
Where mydatabase1 and so on will be the names of the databases (specifically the database names, not the names of the directories these databases are stored in).
Method two: just shut MySQL down and copy itNow for a less popular method - shut down the MySQL server and simply copy the databases. The thing is, MySQL stores all its databases as plain directories, each containing a set of table files. That means it's enough to pack the directory of the database you need into a tar file while the MySQL server is down - and you're done.
So, let's shut the server down.
for Debian/ubuntu:
$ sudo /etc/init.d/mysql stop
for CentOS/redhat:
$ sudo /etc/init.d/mysqld stop
for FreeBSD:
$ sudo /usr/local/etc/rc.d/mysql-server stop
Now let's copy. Go into the directory with the MySQL databases and create a tar file for the database you need.
# tar -cf mydatabase.bak.tar mydatabase
This example assumes the database is called mydatabase. You need to specify the name of the directory of the database you're backing up. And the result will show up as a single file, mydatabase.bak.tar
Now let's start the MySQL server back up.
for Debian/ubuntu:
$ sudo /etc/init.d/mysql start
for CentOS/redhat:
$ sudo /etc/init.d/mysqld start
for FreeBSD:
$ sudo /usr/local/etc/rc.d/mysql-server start
Heh, I made a backup by copying without stopping MySQL and it worked out fine for meThat's not "heh", that's "uh-oh", because everything's fine only as long as those databases don't change often. Under heavy load, you might time it so that the file gets copied while it's being modified and... well, long story short, you might as well throw that backup away afterward, and you'll most likely have no idea it's broken until the moment you actually need it and go "oh no".
Comments