Backup and Restore using mysqlbackup from MySQL Enterprise

All this was done in a lab.  Don't do this on a production server.  There is my warning and disclaimer.

I did a full backup of my MySQL instance which resides under /var/lib/mysql.   I then delete all the data, all the ib* files, and mysql.   After deleting those files I ran a full recovers.  Here is how I did it.

Full Backup

Run the following command to do a full backup.   What this will do is backup everything in /var/lib/mysql and with the backup-and-apply-log it will go back and grab anything that has changed during the backup.   Also, that argument prepares the backup for a restore.

./mysqlbackup --user=root --password  --backup-dir=/storage/backups backup-and-apply-log

 

You need to specify a backup directory that is empty of mysqlbackup will complain.

When the backup completes you will have a message at the end telling you that it was successful.

I cd to the location of my backup and everything looked like it was there.   I ran a du -sh * and results looked the same.

Full Restore

./mysqlbackup --defaults-file=/storage/backups/backup-my.cnf --datadir=/var/lib/mysql --backup-dir=/storage/backups copy-back

Under "--default-file=" I originally put /etc/my.cnf thinking that I should use the my.cnf file I created.  However, part of the backup creates a "backup-my.cnf" with a few lines in it.  You should use this file for the restore or you could put these lines in your restore command.  It is much easier to just use the file that was created.

I started mysql /etc/init.d/mysql start, but it failed with the PID error.   I looked at my restored directory and noticed many of the files was owned by root, so I ran this command on the mysql directory.

chown -R mysql:mysql mysql

Now MySQL starts and every thing looks great.  

Move MySQL to a different Directory

Steps I use to install MySQL to a different directory.   The truth is I just use the rpm and let it install in the default /var/lib/mysql on Redhat and then I do the following to move it to another directory.

After the install I setup my my.cnf file and then start MySQL.   I don't think it is necessary to start it, but I do so that the ib files can be created.  I then stop MySQL.   I cd to /var/lib and move the mysql directory to wherever I want.  In my case I move it to my /data directory which is on a different filesystem.

So I issued this command mv mysql /data

Then I create a symbolic link to my new mysql location by using this command:

ln -s /data/mysql mysql

Now under /var/lib/ I have a symbolic link that points to /data/mysql.   If I cd to /var/lib/mysql I will be in /data/mysql.

One reason I do this is I don't like to have data in my root partition in case I fill it up.  Also, in my setup I don't have enough room in my root partition to host mysql.

After making this change I started to get this error:

starting mysql.the server quit without updating pid file failed.   I've seen this error before and it is usually a permission issue.   I checked my permissions and the permissions were fine.  

The problem is selinux the iptables or firewall.

To check run this command:
semodule -l 

Check and see if mysql is in the list?

Now temporarily disable selinux with this command:

echo > 0 /selinux/enforce

or 

service iptables stop

Now try to start MySQL.  Did it start?  Mine Did.

Now you know the problem is with selinux.  You have a few chooses:

1. you can disable selinux by adding SELINUX=disabled in /etc/selinux/config.  The lease secure, but fastest fix.

2. You can disable the mysql module in selinux.  To disable mysql run this command:

semodule -v -d mysql  or semodule --disable=mysql

To re-enable module run this command:

semodule -v -e mysql or semodule --enable=mysql

The "-v" is verbose and is not necessary.

3. You can keep it enabled, but follow the information in this link:

http://serverfault.com/questions/177061/how-do-i-assign-an-selinux-label-to-a-symlink-with-semanage-so-it-persists-after