Backing up with Percona's Xtrabackup

 Percona's backup allows for full and incremental backups and it is open source.  I tried it on a Redhat 6.2 server called Redhat62 and it worked great.   I installed a new version of MySQL and did a restore using files I had backed up using MySQL Enterprise backup and to my surprise innobackupex restored the data and it worked.  The only thing I had to do was change permissions before I started MySQL.

Here is what I did:

Create a backup user for MySQL backups.

mysql>; CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’password’;
mysql>; REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’@'localhost';
mysql>; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql>; FLUSH PRIVILEGES;

To do a full backup of MySQL run this command:
innobackupex --user=bkpuser --password=password --parallel=4 --use-memory=4G /path/to/BACKUP-DIR

My backup directory on Redhat62 is /storage/backups

innobackupex reads the my.cnf file so it knows where the MySQL data is located.  

By Default innobackupex uses only 100MB for running the backup.  To increase the speed of the backup  you need to allocate more memory.  Set the "--use-memory=" to most that  you have free.

You can increase the number of threads by using the parallel parameter.

YOU NEED TO PREPARE THE DATA FOR RESTORE

After the backup is completed you need to run the "--apply-log" before you can restore the data.  The "--apply-log" replays committed transaction in the log files and rolls back uncommitted transactions.

innobackupex  --apply-log /path/to/BACKUP-DIR/time_stamp

To restore you need to run this command:  MYSQL NEEDS TO BE SHUTDOWN DURING THE RESTORE

innobackupex  --copy-back /path/to/BACKUP-DIR

I had previously had databases in the /var/lib/mysql directory with the same name.  innobackupex failed with a message.  I deleted all of the files under /var/lib/mysql and ran it again.  It completed successfully.   I guess since it is doing a full restore it doesn't write over the files that are there.  You will need to clear our all of the files in the directory to which it is restoring.

After the restore is done run this command:

chown -R mysql:mysql /var/lib/mysql

You can do incremental backups with innobackupex.  First you need to do a Full backup and then you can run incrementals.   If you are going to use incremental backups then your Full backups you will need to have the --redo-only option added to it.  This is explained in Pecona's manual:

Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full
ones. This is, perhaps, the stage where more attention is needed:
• First, only the committed transactions must be replayed on each backup. This will put the base full backup
and the incremental ones altogether.
• Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
If you replay the commit ed transactions and rollback the uncommitted ones on the base backup, you will not be able
to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment
and the remaining increments.
Having this in mind, the procedure is very straight-forward using the --redo-only option, starting with the base
backup:

innobackupex --user=bkpuser --password=password --redo-only /storage/backups

To run an incremental you need to issue this command:

innobackupex  --user=bkpuser --password=password --incremental /storage/backups --incremental-basedir

What if you only want to restore 1 database or 1 table instead of the whole backup?  Here is what you need to do. 

Start up a second session of MySQL on the same server, but point it to your backup directory that is holding the data you want to restore.

To start a second session:

mysqld --basedir=/usr --user=mysql --log-error=/path/to/BACKUP-DIR --open-files-limit=4096 --pid-file=/path/to/BACKUPDIR/Time_Stamp/mysql.pid --port 3307 --datadir=/path/to/BACKUPDIR/Time_Stamp

Here is an example:

mysqld --basedir=/usr  --user=mysql --log-error=/storage/backups/mysql/mysql.log --open-files-limit=4096 --pid-file=/storage/backups/mysql/2012-09-14_10-01-55/mysql.pid --port 3307 --datadir=/storage/backups/mysql/2012-09-14_10-01-55

After you start your second session of MySQL you can now do a MySQLdump the database or table that you want to restore.

To restore a database, type this:

mysqldump -P 3307 -u bkpuser -p password databasename > databasename.sql

To restore a table, type this:

mysqldump -P 3307 -u bkpuser -p password databasename tablename > tablename.sql

I put this information from the following sources: Percona's xtrabackup pdf file found on their website,http://www.tekovic.com/mysql-hot-backup-with-xtrabackup-on-centos and  http://steveswanson.wordpress.com/2009/04/21/exporting-and-importing-an-individual-mysql-table/

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

Monitor MySQL queries in Real Time

Just a quick tip on monitoring the queries that mysql is handling on a production site. You can use the mysqladmin tool to return a list of the processes currently being handled. Combining this with the UNIX watch command allows a real-time monitoring of what's going on.

watch -n 1 mysqladmin processlist

The "-n 1" specifies that mysqladmin executes every second. Depending on your set-up, you may need to specify a mysql user and password:

watch -n 1 mysqladmin --user= --password= processlist

LVM Snapshot script

LVM Snapshot script

Used for copying a MySQL schema to a test environment

There are so many ways to do this, but our need was to not have any production down time* and have the test database available ASAP.  

I wrote up a little how to and I thought I might share it so others could use it.  Like I said there are many ways to do this so just take a look and use the logic that works for you. 

A Method to copy Data to a test environment

This method will copy all data in a volume and MySQL a test environment with no downtime.  The method used could also be used for backing up data or replication.

Here are the steps needed to accomplish the copy to test.

1.       Add an additional hard drive for Storage. 

2.       Configure the disk using fdisk and then create a Physical Volume with the pvcreate command.*

3.       Add the Physical Volume to the VolGroup with the vgextend command.*

4.       Setup MySQL to run a second instance.*

5.       Run the this script.

#! /bin/sh

user=username

pwd=password

olddb=originalname

newdb=newname

socket=/tmp/mysql.sock2

#This will remove LVM and stop the second instance of MySQL .

mysqladmin -u$user -p$pwd --socket=$socket shutdown 

sleep 4

umount /dev/VolGroup01/databackup

lvremove -f /dev/VolGroup01/databackup

#This will flush mysql and Lock Tables and LVM snapshot (see below)**

mysql -u$user -p$pwd < lvm_snapshot

#At this point the volume and MySQL files have been copied

#This will will mount the LVM snapshot to /mnt/databackup

 mount -t ext3 /dev/VolGroup01/databackup  /mnt/databackup

#This will start the second instance of MySQL on port 3305

mysqld_multi start 2

#This is needed to allow enough time for MySQL to start and create the mysql.sock2 file

sleep 4

#This will login to the second instance of MySQL and rename the schema to dl4test. 

mysqlconn="mysql -u$user -p$pwd -S $socket -h localhost"

$mysqlconn -e "CREATE DATABASE $newdb"

params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'")

for name in $params; do

      $mysqlconn -e "RENAME TABLE $olddb.$name to $newdb.$name"; done;

$mysqlconn -e "DROP DATABASE $olddb"

#This will grant user saitest access to the newdatabase schema

$mysqlconn -e "GRANT ALL PRIVILEGES ON newdatabase.* TO 'username'@'%' IDENTIFIED BY 'saitest'";

This script takes about 10 seconds to run and requires no down time in production.  

Note: the schema name change does not change any stored procedures so a script or logic would need to be added in order to create those after renaming the schema.

*More Information on Setup:

1.  Use fdisk to setup the disk. 

Type fdisk /dev/sdb1 (or whatever device your new hard drive is) and press Enter.  Then press, m,n,e(choose the defaults), p, and w to save the changes.  

2.  Create a Physical Volume.

Type “pvcreate /dev/sdb1”

3. Extend the Volume Group.

Type “vgextend VolGroup01 /dev/sdb1”

4. Add a second instance of MySQL.

Modify the /etc/my.cnf with the following changes.

a.       Add a [mysqld_multi] section that looks like this:

[mysqld_multi]

mysqld          = /usr/bin/mysqld_safe

mysqladmin      = /usr/bin/mysqladmin

b.      Rename [mysqld] to [mysqld1]

c.       Add a [mysqld2] section with the following parameters:

[mysqld2]

port            = 3305

datadir         = /mnt/databackup/mysql

socket          = /tmp/mysql.sock2

pid-file        = /mnt/databackup/mysql/mars-snapshot-test.pid2

log_error       = /mnt/databackup/mysql/error2.log

To start the second instance of mysql use the following command:

“mysqld_multi start 2”

To stop the second instance of mysql use the following command:

Mysqladmin –uusername –ppassword –socket=/tmp/mysql.sock2 shutdown.

To login to the second instance of mysql you will need to login using the socket:

Mysql –uusername –ppassword –S /tmp/mysql.sock2 –hlocation

DownTime - we run this at night when there are a minimal of users. 

Long queries at the time of the LVM snapshot will call some downtime.

** Here is the content of the lvm_snapshot file

FLUSH TABLES WITH READ LOCK;

system lvcreate -L 220G -s -n databackup /dev/VolGroup01/LogVol01;

UNLOCK TABLES;

QUIT

Log interactive session in MySQL

There are two ways to start recording your interactive session in MySql.

1. From the Command line:
(I'm only typing the command "mysql" and will assume that your username and password is saved in your home directory in ".my.cnf" file.

mysql --tee=filename database
ex: myssql --tee=tmp.txt dl4demo09

2. From within Mysql:
mysql> \T output.file
Logging to file 'output.file'