Taking hot (online) backup using xtrabackup using perconal tools

Introduction

Percona XtraBackup is a tool for quickly taking backups of database machines and loading them onto new machines. It is much faster than taking a MySQL dump and importing the backup, but slower than using Rsync. However, unlike the Rsync method, XtraBackup can be used with a live production machine as the data source.

Instructions

1.       Make sure that XtraBackup is installed on both machines:

 which xtrabackup

/usr/bin/xtrabackup

          * If it's not installed, run the following to install it:

wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.0/RPM/rhel5/xtrabackup-1.0-56.rhel5.x86_64.rpm

rpm -ihv  xtrabackup-1.0-56.rhel5.x86_64.rpm

    2. Create a folder to hold the backup:

        mkdir /root/BACKUP

      3.Take a backup from the current database:

     /usr/bin/innobackupex-1.5.1 --slave-info --password=password  /root/BACKUP

     4. Wait for the backup to finish. When it finishes, a status message displays:

      Backup completed OK\!

     5. Identify the MySQL data directory. This is the datadir parameter in the MySQL configuration file:

      grep datadir /etc/my.cnf

      datadir=/var/lib/mysql/data

   6. If there is space, move the MySQL data directory to a backup location. (Otherwise, delete it.)

      mv data_dirdat_adir_OLD

          * data_dir is the value of the datadir parameter you found earlier.

   7. Create a new MySQL folder and copy the contents of the backup into it:

      mkdir data_dir

     mv /backup/backup_dir/* data_dir

          * The backup_dir is the folder containing the backup, for example 2010-03-20_19-00-43.

  8. Restore the backup using XtraBackup:

xtrabackup --defaults-file=/etc/my.cnf --use-memory=mem --prepare --password=password --target-dir=data_dir

          * mem is the amount of memory to use. For machines with 96 GB of RAM, use 64G. For machines with 48 GB of RAM, use 32G.

          * password is the MySQL root password

  9. Run the same command again to create the ibdata and iblogfiles

 xtrabackup --defaults-file=/etc/my.cnf --user-memory=mem --prepare --password=password --target-dir=data_dir

  10. Fix the file attributes so that they belong to the mysql user

     chown -R mysql:mysql data_dir

  11. Start MySQL.

  12. Set up replication again. You need the credentials and the binlog position on the master:

         1. If the machine was previous replicating from the same master you can get the credentials from the old master.info file:

            cat data_dir_old/master.info

            Otherwise, you should already know the replication credentials.

         2. The binlog position is in the xtrabackup_binlog_info file:

            cat data_dir/xtrabackup_binlog_info

         3. After you have the necessary information, start a MySQL session and set the master information:

            change master to master_host='IP', master_user='user', master_password='password', master_log_file='log_file', master_log_pos=log_pos;

                * IP is the internal IP address of the master

                * user is the replication user

                * password is the password for the replication user

                * log_file is the binlog file

                * log_pos is the binlog position on the master, for example 308760965.

         4. Start the slave thread

             start slave;

         5. Wait for the machine to catch up on replication.

To know more about percona tools:

Given below are the urls:

http://www.percona.com/downloads/