Example of innobackupex used to back up and restore a MySQL database

Background:

In some technical groups, we can see that some O & M personnel still use commands such as mysqldump to back up MySQL, so we feel it is necessary to introduce innobackupex.
Currently, most MySQL-based scenarios use the Master-Slave architecture. Compared with mysqldump, innobackupex backup has the following benefits:
1. Data files are used as backup objects and file-level backup is fast. This is especially suitable for scenarios where all data needs to be backed up;
2. Hot backup will not affect access to existing databases;
3. Record binlog and replication-related information, which is useful when creating and recovering Slave;

The following are some supplements from Feng Ge:
1. innobackupex can be used for online backup of non-stop services, provided that tables are locked for the innodb engine and myisam;
2. in the backup process, IO is high. We recommend that you back up data on a server load balancer instance (I usually use a server load balancer instance for backup only). We do not recommend that you back up data on the Master instance. 3. innobackupex can be used in combination with incremental and full backup;

Use cases:

Environment introduction

Architecture: Master-Slave
MySQL Port 3308
Configuration File:/etc/my_3308.cnf
Backup Directory:/mysql-backup/3308
MySQL data directory:/opt/mysql_3308/data
Service script:/etc/init. d/mysql_3308
Back up data on the Master

# Innobackupex -- defaults-file =/etc/my_3308.cnf/mysql-backup/3308 -- user = backup-user -- password = backup-user -- no-lock
Back up on Slave

# Innobackupex -- defaults-file =/etc/my_3308.cnf/mysql-backup/3308 -- user = backup-user -- password = backup-user -- no-lock -- slave-info -- safe- slave-backup
Restore on Master

#/Etc/init. d/mysql_3308 stop

# Mv/opt/mysql_3308/data/opt/mysql_3308/data_broken
# Mkdir/opt/mysql_3308/data

# Innobackupex -- apply-log/mysql-backup/2015-01-12_08-00-10
# Innobackupex -- copy-back/mysql-backup/2015-01-12_08-00-10 -- defaults-file =/etc/my_3308.cnf

# Chown-R mysql: mysql/opt/mysql_3308/data

#/Etc/init. d/mysql_3308 start
Restore on Slave

#/Etc/init. d/mysql_3308 stop

# Mv/opt/mysql_3308/data/opt/mysql_3308/data_broken
# Mkdir/opt/mysql_3308/data

# Innobackupex -- apply-log/mysql-backup/2015-01-12_08-00-10
# Innobackupex -- copy-back/mysql-backup/2015-01-12_08-00-10 -- defaults-file =/etc/my_3308.cnf

# Chown-R mysql: mysql/opt/mysql_3308/data

#/Etc/init. d/mysql_3308 start

# Cd/opt/mysql_3308/data
# Cat xtrabackup_binlog_pos_innodb
./Bin-log-mysqld.000222 222333

# Mysql_3308-uroot-p

Mysql> change master
Master_host = 'master _ host ',
Master_port = 3308,
Master_user = 'master _ user ',
Master_password = 'master _ password ',
Master_log_file = 'bin-log-mysqld.000222 ',
Master_log_pos = 222333;

Mysql> start slave;