Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the process of full backup and recovery of XtraBackup". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Full backup

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=mysql / backup/xtrabackup

When the backup is successful, it prompts:

170920 17:55:25 Executing UNLOCK TABLES

170920 17:55:25 All tables unlocked

170920 17:55:25 [00] Copying ib_buffer_pool to / backup/xtrabackup/2017-09-20 October 17-53-54/ib_buffer_pool

170920 17:55:25 [00]... done

170920 17:55:25 Backup created in directory'/ backup/xtrabackup/2017-09-20 September 17-53-54 ax'

MySQL binlog position: filename 'mysql-bin-3306.000026', position' 154'

170920 17:55:25 [00] Writing / backup/xtrabackup/2017-09-20 October 17-53-54/backup-my.cnf

170920 17:55:25 [00]... done

170920 17:55:25 [00] Writing / backup/xtrabackup/2017-09-20 October 17-53-54/xtrabackup_info

170920 17:55:25 [00]... done

Xtrabackup: Transaction log of lsn (1833677659) to (1833677668) was copied.

170920 17:55:25 completed OK!

And the file name and position point of the current binlog are recorded to facilitate synchronous replication.

2. Full recovery

Stop the MySQL service

Service mysql stop

Delete all files under the old database data directory

Cd / mysql/data

Apply the log from the backup file to the data file in the backup file

Innobackupex-- detaults-file=/etc/my.cnf-- apply-log / backup/xtrabackup/2017-09-20 17-53-54

When the transaction log is successfully restored, it prompts:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1833677864

170920 18:10:21 completed OK!

Restore the data from the backup file to the database:

Innobackupex-- defaults-file=/etc/my.cnf-- copy-back / backup/xtrabackup/2017-09-20 17-53-54

Upon successful recovery of physical files and transaction logs, you will be prompted:

170920 18:15:33 [01] Copying. / weixin/admin_depart.frm to / mysql/data/weixin/admin_depart.frm

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/admin_member.frm to / mysql/data/weixin/admin_member.frm

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/admin_member.ibd to / mysql/data/weixin/admin_member.ibd

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/admin_action.ibd to / mysql/data/weixin/admin_action.ibd

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/admin_pri.frm to / mysql/data/weixin/admin_pri.frm

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/admin_depart.ibd to / mysql/data/weixin/admin_depart.ibd

170920 18:15:33 [01]... done

170920 18:15:33 [01] Copying. / weixin/wechat_conf.frm to / mysql/data/weixin/wechat_conf.frm

170920 18:15:33 [01]... done

170920 18:15:33 completed OK!

After the data recovery is complete, you need to modify the permissions of the relevant files:

Chown-R mysql:mysql / mysql/data

Restart and start MySQL

Service mysql start

This is the end of the process of full backup and recovery of XtraBackup. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.