backupbackup-restorationdatabasedatabase-backupMySQL
In the process of creating an automated solution for backing up and restoring an entire MySQL server, I've come across the mysql database which seems to contain user accounts, permissions, metadata, that kind of thing. Should this database be backed up? Will backing up and attempting to restore it break things?
I've had a heck of a time Googling for "mysql backing up mysql database" as you can imagine.
Found someone asking a similar question: MySQL > Table doesn't exist. But it does (or it should).
Mike Dacre had the answer that solved my problem. The problem was that the ib_logfile0 and ib_logfile1 (and maybe some of the other ib* files in the mysql/ root directory) were inconsistent with my new installation of mysql. You can't just drop in db files from the old mysql/ directory and expect it to work.
What I did to recover the database was to backup my current /var/lib/mysql/ on the fresh installation:
$ sudo service mysql stop # Stop mysql. Command could be different on different distros
$ sudo mv /var/lib/mysql ~/mysql.bku
Then copy the emergency backup directory to /var/lib
$ sudo cp -R /media/NAS/Backup/mysql /var/lib/
Then set the permissions appropriately (refer to ~/mysql.bku/ for reference if needed). There may be more efficient commands for this but I'm including what I know for completeness in case someone with less experience may need it.
$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo find /var/lib/mysql/ -type d -exec chmod 700 {} \;
$ sudo find /var/lib/mysql/ -type f -exec chmod 660 {} \;
$ sudo chmod 644 /var/lib/mysql/debian-5.1.flag # Not sure what this is but the permissions were a bit different so include it just in case
And start mysql again
$ sudo service mysql start # Again command might be different on different distros
Then I backed up the databases I needed:
$ mysqldump -u root -p mediawiki-1_19_1 -c | gzip -9 > wiki.2012-11-15.sql.gz
When I was finished I put the mysql/ directory back and then imported the databases from the dump files.
$ sudo service mysql stop
$ sudo mv /var/lib/mysql ~/mysql-discard # Too nervous to start typing "sudo rm -r /" for /var/lib/mysql, so move it away instead
$ sudo mv ~/mysql.bku /var/lib/mysql
$ sudo service mysql start
Problem solved, I have a proper export of the database now and mysql is running properly too. All that's left is following the Restoring a wiki from backup guide.
/var/runAs you already noticed, excluding /var/run during a complete restore of a CentOS 6 system causes problems, because it also excludes directories created by installed packages. Excluding /var/lock can also cause similar problems, because some packages create subdirectories there too.
(There may be no such issues on more recent Linux distributions which use systemd — on such distributions /var/lock and /var/run (really /run) may be placed on tmpfs, and any required subdirectories are created during every boot; however, CentOS 6 is much older and does not have any support for automatic creation of subdirectories in /var/lock or /var/run.)
However, actually excluding /var/run and /var/lock is not needed for a proper restore, because the /etc/rc.d/rc.sysinit script on CentOS 6 includes the following command:
find /var/lock /var/run ! -type d -exec rm -f {} \;
This command will remove all stale lock or pid files (or any other non-directory files, such as sockets and symlinks) during the system boot. Therefore you should remove /var/lock and /var/run from the restore exclusion list.
You already exclude /etc/sysconfig/network* when restoring the backup; this should match both the /etc/sysconfig/network file (global networking configuration) and the /etc/sysconfig/network-scripts directory (per-interface configuration files ifcfg-*). However, these files are used only by the old-style network configuration scripts included in the initscripts package, and CentOS 6 has another network configuration system — NetworkManager, configuration for which is stored in /etc/NetworkManager. Try also excluding that directory when you restore the backup.
If you see that symbolic links were replaced with plain files after the restore, this means that either your backup/restore program was not configured correctly, or (if there is no option for saving and restoring actual symlinks) the program you used is not suitable for Linux system backup/restore at all. You can get away with a program which does not support symlinks only if the program is used to backup and restore only some specific data which definitely will not contain symlinks. Note that you may find symlinks in places where you did not expect them — e.g., in some cases symlinks may be used in MySQL database directories (to store some parts of data on a different device), therefore relying on the “no symlinks” assumption may be dangerous.
If your backup program simply copies files from a running server, your backup is not really “crash consistent“, because different files (and even different blocks of a same file) are copied at different times, therefore you will not actually get a consistent snapshot of the database in your backup. (This applies to any kind of database, not just MySQL.)
There are several ways to backup MySQL databases using just a file-level backup:
Use mysqldump to create a SQL dump before starting the file-level backup; backup the dump file instead of the database directory. This is the most portable backup format, but both dumping and restoring may be slow.
Stop the MySQL server before starting the backup, make a file-level backup, then start the MySQL server again. To restore, just restore all files on the new server, then start the server normally. This kind of backup is fast, but requires a significant downtime during the backup.
To reduce the MySQL server downtime required by the previous method, you can create a filesystem snapshot after stopping the server, then start the MySQL server again, and then mount the snapshot, perform a file level backup and delete the snapshot. You need to have the filesystem on an LVM volume with some free space in the volume group for the snapshot.
To reduce the downtime even further, you can use FLUSH TABLES WITH READ LOCK before taking the snapshot instead of stopping the server, as described here; in this case the snapshot will contain MyISAM tables in a consistent state, and InnoDB tables in a crash-consistent state (InnoDB recovery will be needed after a file level restore).
Read this documentation for more information about MySQL backup.
Best Answer
Here is something interesting to consider: Backing up the
mysqldatabase limits you greatly in that you can only restore such a database to the same version of mysql you ran the backup from. Here is why:Here is mysql.user from MySQL 5.0.45
Here is mysql.user from MySQL 5.1.32
Here is mysql.user from MySQL 5.5.12
If you attempt a restore of mysql.user to a version of MySQL it was not meant for, it will produce random permission problems. The way to backup the mysql user permissions in a version-agnostic way is to dump the user grants in SQL. That way, the user grants are completely portable. There are two ways to accomplish this:
OPTION #1 : Using MAATKIT
mk-show-grants will generate the SQL needed whatever mysql instance you connect to. (Keep in mind that MAATKIT is being migrated to the Percona Toolkit. This tool will most likely be called pt-show-grants).
OPTION #2 : Script the dumping of the SQL GRANTS
I have written my own emulation of mk-show-grants. It will leave out anonymous users. It looks like this:
Using either of these options will create a more stable backup of user grants.
ON A SEPARATE NOTE
Now if you are using this log-output option
the mysql database will populate the slow log (if enabled) as a mysql table in the mysql schema rather than a text file. So, doing physical backups will include such mysql table-based logs. Believe me, it is not worth the diskspace if the general log and slow query log are enabled and piling up in the mysql schema. Just stick with the MySQL Grants Dump Options.
UPDATE 2011-09-19 15:54 EDT
There is one very important factor in maintaining backups of MySQL permissions via SQL Grants:
Each user comes out with their password in some modified MD5 format. For mysql 4.0 and back, it is a 16-character hexadecimal string. For mysql 4.1+, it is 41 characters (An asterisk followed by a 40-character hexadecimal string).
Before you restore a SQL Grants dump, check the SQL Grants dump file for any 16-character hexadecimal passwords. If you see even one, you must add the following to /etc/my.cnf (or my.ini for Windows) on the mysql server you will restore to:
The old_password directive permits 16-char and 41-char passwords to coexist and correctly authenticate in the same running mysql instance. Any passwords created going forward will be 16-characters.
MySQL restart is not required. Just run this: