Shulou(Shulou.com)06/01 Report--

The previous article briefly introduced mysqldump for database backup and recovery, this one talked about another backup tool xtrabackup, in the era of InnoDB transaction engine flooding, xtrabackup can well support the hot backup of the database, which is very satisfying

Xtrabackup is available in the epel source, so you can install it directly with yum.

A full backup is a direct connection to the MySQL service, given a target directory.

[root@www] # innobackupex-user=root-host=localhost-port=3306-password='123456' / data/mydata181105 20:31:30 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!"... 181105 20:31:39 Executing UNLOCK TABLES181105 20:31:39 All tables unlocked181105 20:31:39 Backup created in directory'/ data/mydata/2018-11-05'20-31-30 '181105 20:31:39 [00] Writing backup-my.cnf181105 20:31:39 [00]... done181105 20:31:39 [00] Writing xtrabackup_info181105 20:31:39 [00]... donextrabackup: Transaction log of lsn (9424992) to (9424992) was copied.181105 203Backup created in directory 39 completed OK!

Then we can see several files in the backup directory

[root@www] # ls / data/mydata/2018-11-05-20-31-30/backup-my.cnf ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfilehellodb mysql wpsdb xtrabackup_info zabbix

In addition to the database, there are several special files:

Xtrabackup_binlog_info: records some attribute information during the backup process

[root@www] # cd / data/mydata/2018-11-05 / 20-31-30 / [root@www 2018-11-05 / 20-31-30] # cat xtrabackup_infouuid = 725248b0-da18-11e8-9fcc-000c29ceaa48name = tool_name = innobackupextool_command =-user=root-- host=localhost-- port=3306-- password=... / data/mydatatool_version = 2.3.6ibbackup_version = 2.3.6server_version = 5.5.60-MariaDBstart_time = 2018-11-05 20: 31:31end_time = 2018-11-05 20:31:39lock_time = 0binlog_pos = innodb_from_lsn = 0innodb_to_lsn = 9424992partial = Nincremental = Nformat = filecompact = Ncompressed = Nencrypted = N

Backup-my.cnf: record important configuration parameters related to the InnoDB storage engine

[root@www 2018-11-05 20-31-30] # cat backup-my.cnf# This MySQL options file was generated by innobackupex. # The MySQL server[mysqld] innodb_checksum_algorithm=innodbinnodb_log_checksum_algorithm=innodbinnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=5242880innodb_fast_checksum=falseinnodb_page_size=16384innodb_log_block_size=512innodb_undo_directory=.innodb_undo_tablespaces=0

Xtrabackup_checkpoints: records the type and the starting and ending LSN of this time

[root@www 2018-11-05log 20-31-30] # cat xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 9424992last_lsn = 9424992compact = 0recover_binlog_info = 0 xtrabackup_binlog_info: record the consistency coordinates of the binary log currently used; [root@www 2018-11-05log 20-55-04] # cat xtrabackup_binlog_infobinlog.000001 245

Restore of a full backup (mysql's data directory is empty):

The process of restoring data only needs to add a-- copy-back.

[root@www 2018-11-05 20-55-04] # innobackupex-- copy-back. / 181105 20:59:30 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!".

Incremental backup:

Compared to the previous full backup, you need two options-- incremental and-- incremental-basedir.

[root@www] # innobackupex-- incremental / data/mydata-- incremental-basedir=/data/mydata/2018-11-05 October 20-31-30181105 20:54:41 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!"... 181105 20:55:14 Executing UNLOCK TABLES181105 20:55:14 All tables unlocked181105 20:55:14 Backup created in directory'/ data/mydata/2018-11-05 04'MySQL binlog position 20-55-04'MySQL binlog position: filename 'binlog.000001' Position '245181105 20:55:14 [00] Writing backup-my.cnf181105 20:55:14 [00]... done181105 20:55:14 [00] Writing xtrabackup_info181105 20:55:14 [00]... donextrabackup: Transaction log of lsn (9424992) to (9424992) was copied.181105 20:55:14 completed OK!

You can view the contents of the xtrabackup-checkpoints file

[root@www 2018-11-05 20-55-04] # cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 9424992to_lsn = 9424992last_lsn = 9424992compact = 0recover_binlog_info = 0

The recovery process of incremental backup is the same as above, and it is also a copy-back.

Xtrabackup is a very good backup tool. In today's era of transaction engines, good hot backup mechanism and simplified operation are very friendly to us. Therefore, I highly recommend this tool, while mysqldump is mostly used for testing. Because mysqldump needs to lock tables, the warm backup mechanism is not as convenient as xtrabackup hot backup mechanism. However, in the case of testing in our experimental environment, this is packaged directly into a database and copy can be released to other hosts, which is still very good.

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.