How To Create Hot Backups of MySQL Databases with Percona XtraBackup

Steps to follow  :

• Introduction
• Prerequisites
• Step 1: Create a non-root system user
• Step 2: Check the storage engine
• Step 3: Create a database user for backup
• Step 4: Install Percona XtraBackup
• Step 5: Create backup storage directories
• Step 6: Create the first full backup
• Step 7: Create the following incremental backups
• Step 8: Prepare backup files for restoring database
• Step 9: Restore database
• Further steps

Introduction

Percona XtraBackup is a free MySQL-based program used for making hot backups. It is also open-sourced. With Percona XtraBackup, you can make hot backups of running MySQL, MariaDB, or Percona Server databases without stopping your database service or making it read-only. This is a business critical feature for lots of online businesses.

For databases using InnoDB, XtraDB, and HailDB storage engines, Percona XtraBackup can perform non-blocking backups. For databases using MyISAM, Merge, and Archive storage engines, Percona XtraBackup can also perform backups by briefly pausing writes at the end of the backup procedure.

In this article, I will show you how to install and use Percona XtraBackup to perform full and incremental hot backups on a Vultr server based on the One-Click WordPress application. We will perform a full backup and two incremental backups, and then restore the database to the state of each of the three backups accordingly.

Prerequisites

I assume that you have deployed a One-Click WordPress Vultr server instance from scratch and have logged in as root, using SSH.

Step 1: Create a non-root system user

For security purposes, a recommended practice is to create another user account with root permissions, then use it to log in and perform your daily operations on the system. You can still execute almost all of the superuser commands with the sudo command.

1) Create a new user. Replace sysuser with your own username.
useradd sysuser
2) Set the password for your new user. Replace sysuser with your own username.
passwd sysuser

3) Grant root permissions to your new user.
visudo
Find the paragraph below.
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
Add a row directly beneath this paragraph, replace sysuser with your own username.
sysuser ALL=(ALL) ALL
Save and quit.
:wq
4) Switch to your new user account.
logout
Then, use the new user’s credentials to log in from your terminal window.

Step 2: Check the storage engine

By default, the MySQL root login is saved on the VPS in /root/.my.cnf. Display the password in your terminal with the following command.

sudo cat /root/.my.cnf

Use the credential displayed on the screen to log into the MySQL console.

mysql -u root -p

In the MySQL shell, run the following.

SHOW DATABASES;

All the MySQL databases will have been displayed on the screen. The database named like wp5273512 is the WordPress database that we want to backup. In the following command, replace wp5273512 with your own one:

USE wp5273512;

Check the storage engine for each table:

SHOW TABLE STATUS\G

You will find that all of the tables in your WordPress MySQL database are using the InnoDB storage engine which is perfect for performing hot backups with Percona XtraBackup.

For any other MySQL databases using MyISAM storage engine, we can still backup them with Percona XtraBackup by briefly pausing writes.

Step 3: Create a database user for backup

Still in the MySQL shell, use the following commands to create a dedicated database user for backup. Remember to replace the database username xbuser and the password xbpasswd with your own ones:

CREATE USER 'xbuser'@'localhost' IDENTIFIED BY 'xbpasswd';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO 'xbuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

The privileges granted above are necessary for full Percona XtraBackup functionality. You can remove some of them for less functionality and better security. For more details, see the Percona XtraBackup offical website.

Step 4: Install Percona XtraBackup

You can install Percona XtraBackup from Percona’s RPM repository quite easily:

sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
sudo yum install -y percona-xtrabackup

Step 5: Create backup storage directories

First, you need to add user sysuser to mysql group. Replace sysuser with your own username.

sudo gpasswd -a sysuser mysql

Create a directory to store full backups.

sudo mkdir -p /dbbackup/full/

Create another directory to store incremental backups.

sudo mkdir -p /dbbackup/inc/

Change the owner of these directories to user sysuser and group sysuser.

sudo chown -R sysuser:sysuser /dbbackup

Log out to put these changes into effect.

logout

Then, log in with sysuser again.

Step 6: Create the first full backup

XtraBackup consists primarily of the XtraBackup program and the innobackupex perl script. Usually, you can use the innobackupexperl script to perform various operating procedures for convenience.

Input the following command to create the first full backup. Remember to replace the database username xbuser, the database user password xbpasswd, and the full backup directory /dbbackup/full/ with your own ones.

sudo innobackupex --user=xbuser  --password=xbpasswd /dbbackup/full/

With this command executed correctly, you will see the confirmation message “innobackupex: completed OK!” at the last line of the output.

All of the newly-created files of this full backup will be stored in a time-stamped directory under /dbbackup/full/. For example, /dbbackup/full/2015-05-22_05-45-54.

Step 7: Create the following incremental backups

Input the following command to create the first incremental backup. Substitute variables in the command accordingly.

sudo innobackupex --user=xbuser  --password=xbpasswd --incremental --incremental-basedir=/dbbackup/full/2015-05-22_05-45-54 /dbbackup/inc/

Again, you will see “innobackupex: completed OK!” at the end of the output when the command executes successfully. The backup files will be stored in a time-stamped directory under /dbbackup/inc/.

Input the following command to create the second incremental backup. Replace variables in the command accordingly.

sudo innobackupex --user=xbuser  --password=xbpasswd --incremental --incremental-basedir=/dbbackup/inc/2015-05-22_05-48-12 /dbbackup/inc/

Upon success, you will see the “innobackupex: completed OK!” message again. Check the /dbbackup/inc/ folder again to see the backup files.

Step 8: Prepare backup files for restoring database

All of the database backup files need to be prepared before they can be used to restore the database.

Note: Before you perform the prepare and restore procedures, you’d better keep a copy of the whole backup directory (such as /dbbackup/) at another place in case any damage to backup the files is caused by mistake.

In each backup directory, there is a file named xtrabackup_checkpoints which contains the backup type and the beginning and end log sequence numbers (from_lsn and to_lsn). You can use those numbers to clarify your database restore strategy. Look at the examples below.

In the xtrabackup_checkpoints file of the first full backup, I have:

backup_type = full-backuped
from_lsn = 0
to_lsn = 2932478

In the xtrabackup_checkpoints file of the first incremental backup, I have:

backup_type =incremental
from_lsn = 2932478
to_lsn = 2970177

In the xtrabackup_checkpoints file of the second incremental backup, I have:

backup_type = incremental
from_lsn = 2970177
to_lsn = 3004672

In brief, you should tackle each backup with the increasing order of lsn. If the lsn sequence is incomplete or disordered, you may lose data.

Note: The following commands involve three directories, replace them with your own ones.

To restore the database to the state of the first full backup, you need to prepare the backup files with the following command:

sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

To restore the database to the state of the first incremental backup, you need to prepare the backup files with the following commands:

sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-48-12
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

To restore the database to the state of the second incremental backup, you need to prepare the backup files with the following commands:

sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54
sudo innobackupex --apply-log --redo-only /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-48-12
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54 --incremental-dir=/dbbackup/inc/2015-05-22_05-51-32
sudo innobackupex --apply-log /dbbackup/full/2015-05-22_05-45-54

Notes:

For incremental backups, You should use the --redo-only option on all but the last incremental backup. Nevertheless, using this option on the last incremental backup is still harmless to the consistency of your data – it will only cause some delay due to the database rollback.

The last command of each incremental scenario is optional but recommended, because it will accelerate the restore.

After preparation, the changes recorded in the incremental backup files will be appended to the prepared base full backup files, so you should always use the prepared full backup files to restore your database, no matter if you choose a full backup or an incremental backup.

Step 9: Restore database

Before you can restore your database, you need to stop the database service.

sudo service mysqld stop

You also need to empty the database directory. You can move the current database files to another place for precaution.

sudo mkdir /currentdb
sudo mv /var/lib/mysql/* /currentdb

Restore your database with the prepared “full backup” files.

sudo innobackupex --copy-back /dbbackup/full/2015-05-22_05-45-54

Because the restore procedure will modify the owner of the database directory, you need to change it back to mysql:mysql to make it operational.

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

Restart the database service.

sudo service mysqld start

That’s it. At this point, you can visit your WordPress site to verify that the restore process was successful.

Types of Backups - MySQL

1.Physical Backups :

backup consists of exact copies of database directories and files.

Physical backup methods are faster than logical because they onvolve only file copying.

In addition to databases,the backup can include any related files such as log or configuration files.

Backups are portable only to other machines that have identical or similar hardware characteristics.

Backups can be performed while the MySQL server is not running. if the server is running, it is necessary to perform appropriate locking.

Physical backup tolls include file system-level commands (such as cp,scp,tar,rsync),mysqlhotcopy for MyISAM tables,ibbackup for Innodb tables or START BACKUP for NDB tables.

Output is larger than for physical backup, particularly when saved in text format.

2.Logical backups :

The backup is done by querying the MySQL Server to obtain database structure and content information.

Backup is slower than physical methods because the server must access database information and convert it to logical format.

The backup does not include log or configuration files or other database-related files that are not part of database-related files that are not part of databases.

Backup stored in logical formats are machine independent and highly portable.

Logical backups are performed with the MySQL server running.

Logical backup tools include the mysqldump program and the SELECT … INTO OUTFILE statement.

To restore logical backups we use the MySQL import client. To load delimited-text files use the LOAD DATA INFILE statement.

Types of Storage Engines in Mysql

Storage for MYISAM Tables :

1.When ever we create a table in database it creates a directrory with that name and stores in DATADIR location.
2.For MyISAM tables it stores tables it stores table information in 3 files (.frm,.MYD,.MYI).
3..frm files –> stores MYISAM skeletal structure of tables.
4..MYI files –> stores MYISAM index information.
5..MyD files –> stores MYISAM tables data.

Storage for INNODB Tables :

1. Data is stored in tables spaces.
2. Tables space contains pages with default size 16k.
3. These Pages are grouped into extents of size 1mb each(64 pages)
4. These Extents are grouply known as SEGMENTS.
5. When a segment starts growing it individually allocates first 32 pages,after that it will allocate whole extents to segments.
6.Default location for innodb table space is DATADIR.
7.it is created with a default size 10m later can be resized.
8.it is also creates two transaction log files which supports for innodb tables with default size of 5m;
9. innodb-data-file-path = /ibdata/ibdata1:15m:autoextend
10. the format of the tables is stored in <table name>.frm files in DATADIR location and the data is stored in TABLE SPACE.

Storage for BERKELEY DB AND MEMORY STORAGE Tables :

1.Each BDB table is stored on disk in two files (.frm and .db)
2..frm files –> stores table format information.
3..db files –> stores indexes and data information.
4. By default it runs with auto commit mode to disable auto commit give ‘set auto commit =0’
5.MySQL requires a primary key in each BDB table so that each row cab be uniquely identified. if you don’t Create      one explicitly by declaring a PRIMARY KEY,MySQL creates and maintains a hidden primary key for you.
6.To support transaction rollback , the BDB storage engine maintain log files.(dbd.logdir).

Connecting to a DB Instance Running the MySQL Database

The following steps will allow you to create and manage a MySQL instance within your new web space.

Before starting these steps, you will need the following information:
Server account credentials (server name, username, password)
The port number that ITServices has reserved for your MySQL instance
Knowledge of server connectivity via a Secure Shell (SSH) and prior MySQL experience is required to fully understand, implement, and test a new MySQL instance.

Create the MySQL Instance :

1) Login to the new server using SSH. Which of these servers you login to will depend on the type of account you have. If you are unsure which server to create your MySQL instance on please contact ITServices.
2) The directory that will hold your database(s) should be called www-data and should be located in your server account document root. Create the www-data directory and a temporary directory (i.e. “tmp”) under it that will hold your database(s). To do this, issue the following commands:

a) umask 077
b) cd ;
c) mkdir -p www-data/tmp
d) chmod 711 www-data www-data/tmp

3) Install the MySQL instance by issuing the following command:

Note: When issuing this command and other commands in these steps, replace PORT_NUMBER_HERE with your reserved port number

mysql_install_db --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" --ldata="$HOME/www-data"

4) Start the database instance by issuing the following command:

mysqld_safe --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" --datadir="$HOME/www-data" &

5) When creating your new MySQL instance, it will default to not having a password for the root user. Not having a password is not secure, therefore, the first task after starting the instance is to create the root user password:

Note: When issuing this command, replace YOUR_NEW_PASSWORD with a desired password.

mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" -u root password 'YOUR_NEW_PASSWORD'

If you wish to change this password in the future, issue the following command (it will prompt you for the current password before changing it to the new password):

 mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" -u root -p password 'YOUR_NEW_PASSWORD'

Recommendation: The database root account should only be used for administering the database, and should never be used to access the database from your application web pages. We recommend creating additional database users that only have necessary privileges to be used in web applications. This will increase the security of your database.
After going through the steps above, you will have an installed and running instance of MySQL.

Additional Commands :

Below are some additional commands which are common:

To login to the MySQL instance:

 mysql --socket="$HOME/www-data/tmp/mysql.sock" -u root -p

To stop the MySQL service:

 kill `cat "$HOME/www-data/uni.pid"`

To show the threads running against the MySQL instance:

 SHOW PROCESSLIST;

To find out the status of the MySQL instance:

 mysqladmin --port=PORT_NUMBER_HERE --socket="$HOME/www-data/tmp/mysql.sock" ping