Home Profile Fun

#158 Linux 07.01.2008

Full backup of web servers with MySQL databases

I have thoroughly tested this tutorial on my own Debian system. Nevertheless, please keep your eyes open and adapt the steps according to your system. Thanks.

Backup
Restoration
Tips
Extract a single database from a full MySQL dump

Backup
There are many ways to do a full backup of a Linux web server including all MySQL databases. If the overall data of the web server isn't too big, one of the easiest methods is to create a stage4 tar archive. This means we just create an archive of all files of the whole system. Additionally we backup the MBR (master boot record) and the partition table. In case of a hard drive failure it is possible to restore the entire system by simply rebuilding the disk layout and unpacking the stage4 tar archive. Of course also single files can be extracted and restored from the archive. We will make sure that the MySQL databases are in a consistent state. And at last the most important step of the whole backup process: the verification that the data on the backup medium is correct and can be restored. The advantage here is that the overall amount of data for the full backup is relatively small as we backup only the real data and not the partitions as a whole.

In this Linux tutorial I use a simple scenario with one hard disk that contains two partitions: sda1 which is mounted to / and sda2 which is mounted to /boot. There are no LVMs (logical volumes) or other kinds of virtualization. Also note that the backup of SELinux information is beyond the scope of this article.

The first part is to backup the disk layout. The following three steps produce only very small files. These files can be stored on the original system and can then be downloaded to the backup system. We start with the backup of the master boot record.

dd if=/dev/sda of=/backup_sda_mbr count=1 bs=512

Then download backup_sda_mbr to the backup medium. To verify that the data on the backup medium is the same as the data on the MBR compare their md5sums. Both results must be exactly the same.
On the original system:

dd if=/dev/sda count=1 bs=512 | md5sum

On the backup medium:

md5sum backup_sda_mbr

The backup of the partition table follows.

sfdisk -d /dev/sda > /backup_sda_pt.sfdisk

This is just a text file. Open it and verify that it contains valid data. The MBR and the partition table are crucial data. Without them it can be very difficult to access the hard disk.

During a restore the symbolic links in / have to be recreated manually. So it's a good idea to save an overview.

ls -lha / > /symlinks.txt

Also remember the file system types of the two partitions. In this example it's ext3:

df -hT
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.4G  789M  6.2G  12% /
...
/dev/sda2     ext3     92M   66M   21M  77% /boot
...

Copy these two files to the backup medium and remove them from original system.

Now we have a backup of the disk layout. In case of a hardware failure the master boot record and the partition table can be restored within seconds.
It's time to backup the actual data. We create a MySQL dump to backup all databases. Additionally the binary files of the databases will be included in the backup. If for any reason (e.g. character set issues) the import of a MySQL dump fails the binary files can be used to restore the MySQL databases. In fact, in this example we restore the MySQL databases with the binary files as we restore the entire file system.
The first step is to get an overview about the size of the entire system.

df -h

We will not compress the tar archives. The reason is that if a bit error occures in a compressed archive you might lose the complete backup. Whereas with an uncompressed archive it is more likely that you lose only the file where the bit error occured.

Now, let's continue with the MySQL dump of all databases. However, we cannot just create the dump because changes of the data may happen during the creation of the dump which could result in inconsistent backup data. Therefore we login as root to MySQL first and flush the tables with a read lock so that the data cannot be changed. This lock is global accross all databases.

mysql> flush tables with read lock;

It is important that you keep this session open, because the table lock is being removed immediately if you quit MySQL!
We move the process to the backround.

Press ctrl-z

The next step is to actually create the dump of all MySQL databases. mysqldump could be used here without any additional options, because we have already set the global read lock at the MySQL command-line. But it is possible to set a global read lock just for the duration of the dump: mysqldump uses --opt by default and thus locks databases individually only. For a global lock accross all databases --lock-all-tables has to be used.

mysqldump --opt --lock-all-tables --all-databases -u root -p > /root/all-databases_yyyymmdd.sql

Ok, so far so good. We have the disk layout and all databases as a MySQL dump. The last step is to create backups for the two partitions. Again, the result is uncompressed to be on the safe side.
Backup all folders you find in / but skip /dev, /proc, /sys (and /selinux).

cd /
tar cfvp /fullbackup-sda1-yyyymmdd.tar /bin /etc /home /initrd /lib /media /mnt /opt /root /sbin /srv /tmp /usr /var

Backup the /boot folder which resides on sda2.

cd /boot
tar cfvp /fullbackup-sda2-yyyymmdd.tar *

If you don't have enough disk space left for the archives you can copy them directly via SSH to the backup system. But then you can't verify that the content hasn't been changed during the network transfer.

cd /
tar cfvp - /bin /etc /home /initrd /lib /media /mnt /opt /root /sbin /srv /tmp /usr /var | ssh \
 user@backupsystem "cat > /backups/fullbackup-sda1-yyyymmdd.tar"

Backup the /boot folder which resides on sda2.

cd /boot
tar cfvp - * | ssh user@backupsystem "cat > /backups/fullbackup-sda2-yyyymmdd.tar"

The MySQL databases still have the read lock. So we have got also the binary files of the databases and tables in a consistent state. Now it's time to unlock the MySQL tables again. We do this by first putting the MySQL command-line process to the foreground again.

fg
[Hit return]
mysql> unlock tables;
mysql> quit

You may want to burn the backup data on a DVD. To burn a folder use

growisofs -Z /dev/cdrom -R -J /backups/

or burn just a single file

growisofs -Z /dev/cdrom -R -J /backups/fullbackup-sda1-yyyymmdd.tar

To verify that the data on the backup medium is the same as on the server we run md5sum again on both sides. The results must be exactly the same. (As mentioned before, this step is possible only if you haven't created the archives directly on the backup system via SSH)
Server:

md5sum fullbackup-sda1-yyyymmdd.tar fullbackup-sda2-yyyymmdd.tar

Backup medium:

md5sum fullbackup-sda1-yyyymmdd.tar fullbackup-sda2-yyyymmdd.tar

As a last check we extract one single file from each archive on the backup medium and open it in a text editor:

tar xfvp fullbackup-sda1-yyyymmdd.tar var/www/domain/htdocs/index.html
tar xfvp fullbackup-sda2-yyyymmdd.tar grub/menu.lst

Finally the MySQL dump and the backup files on the server can be removed.

rm /root/all-databases_yyyymmdd.sql
rm /fullbackup-sda1-yyyymmdd.tar
rm /fullbackup-sda2-yyyymmdd.tar

If you use different character sets than UTF-8 in MySQL make at least once a test import of the MySQL dump. There may be character set problems which block the entire import or result in corrupted data. If possible test the import on the same system in a test database or use a test system with exactly the same MySQL setup and configuration as on the original system. Then import the dump and verify that the data is correct, especially look for non-standard ASCII signs and umlauts.

That's it. We have a verified full backup of the entire web server including all MySQL databases.

Restoration
To restore the entire system from a disk crash you can boot the rescue system or liveCD, recreate the disk layout, format the partitions and extract the tar archives directly over the network. I assume the worst case here which means a blank hard drive.
MySQL and all databases will be restored automatically because we restore all binaries, thus the binary files of the MySQL databases too.
But just to mention it: if you want to import the MySQL dump use the following command. The MySQL daemon must be running.

mysql -u root -p < /root/all-databases_yyyymmdd.sql

The first step is to log into the rescue/liveCD system and set the correct time.

date MMDDhhmm[YYYY]

Then copy backup_sda_mbr and backup_sda_pt.sfdisk from the backup system to / on the original system.
And restore the master boot record

cd /
dd if=/backup_sda_mbr of=/dev/sda count=1 bs=512

Restore the partition table

sfdisk /dev/sda < /backup_sda_pt.sfdisk

sfdisk may complain about the disk being used. If this is the case try to find the partition the liveCD uses for swapping.

dmesg | grep swap

Then turn of swapping and try again to write the partition table.

swapoff /dev/sdaX

Format both paritions with the original file system. Here in this tutorial we use ext3.

mke2fs -j /dev/sda1
mke2fs -j /dev/sda2

Create and activate the swap area on the swap partition

mkswap /dev/sda3
swapon /dev/sda3

Now we have restored the disk layout and proceed with mounting the file systems.

Mount the file systems to the rescue/liveCD system

mount /dev/sda1 /mnt/sda
mkdir /mnt/sda/boot
mount /dev/sda2 /mnt/sda/boot

One important note here: before you extract any archives on the original system please extract only /etc/passwd from fullbackup-sda1-yyyymmdd.tar temporarily to a safe place.

tar xfvp fullbackup-sda1-yyyymmdd.tar etc/passwd

Now compare the UIDs and GUIDs in this passwd with the UIDs and GUIDs in /etc/passwd on the liveCD or rescue system! At least the main users like mysql, sshd etc. must have identical UIDs and GUIDs. If necessary change them in /etc/passwd on the liveCD / rescue system. If you skip this step it can happen that SSH or other daemons won't start later and you have to manually correct the owners and groups.

Copy the backup of sda1 from the backup system and extract it in /mnt/sda1.

cd /mnt/sda; ssh user@backupsystem "cat /backups/fullbackup-sda1-yyyymmdd.tar" | tar xfvp -

Create missing folders

mkdir proc
mkdir dev
mkdir sys
(mkdir selinux)

Copy the backup of sda2 from the backup system and extract it in /mnt/sda1/boot.

cd /mnt/sda/boot; ssh user@backupsystem "cat /backups/fullbackup-sda2-yyyymmdd.tar" | tar xfvp -

Now look in the file symlinks.txt and recreate the symbolic links in /.

That's it. You can reboot the machine after unmounting the partitions.
Check for errors during the boot process.

cd /
umount /mnt/sda/boot
umount /mnt/sda
reboot

Tips
With partprobe (part of the parted package) you can let the kernel reread the partition table without having to reboot the server. parteprobe is in the parted package.

partprobe /dev/sda

If you have trouble with grub, e.g. grub does not display the menu or can't find the kernel file etc.: reinstall grub in the MBR. You must again boot into the rescue/liveCD system and perform the following steps:

mount /dev/sda1 /mnt/sda1
mount /dev/sda2 /mnt/sda1/boot
mount -t proc none /mnt/sda1/proc
mount -o bind /dev /mnt/sda1/dev
chroot /mnt/sda1 /bin/bash
source /etc/profile
grep -v rootfs /proc/mounts > /etc/mtab
/usr/sbin/grub-install /dev/sda
exit
cd /
umount /mnt/sda1/dev
umount /mnt/sda1/proc
umount /mnt/sda1/boot
umount /mnt/sda1
reboot

Then all should be up and running.

For large systems or entire networks have a look at one of the greatest backup software available: Bacula
Another possibility for creating backups is rsync.
If you use LVMs you can create snapshots.

Extract a single database from a full MySQL dump
Create a copy of the original dump.
Open the dump with vi.
Search for the CREATE DATABASE statement that belongs to your desired database bei typing

/^CREATE DATABASE

Press 'n' until have found it.
Then press 'n' one more time so that you are at the following database.
Delete everything from here until near the end of the file where the variables get their old values.
And also delete everything from the first create database statement to your desired one unless it is the first one already.
Now you have only the header lines, the variables at the beginning and at the end of the file and your database statements left. Save it and run

mysql -u root -p < ./modified_dump.sql