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

Editor to share with you the principle of xtrabackup in mysql, backup log analysis, backup information access example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

First, the working principle of xtrabackup backup and recovery:

The InnoDB engine is similar to Oracle to a large extent, using the redo,undo mechanism, XtraBackup opens the innodb data file in read-write mode when backing up, and then copies it. At the same time, XtraBackup has another thread monitoring the transactions log, replicating the changed log pages as soon as the log changes, and stopping monitoring log buffer and log replication until all innoDB data files have been copied. If log buffer does not write out in time, it will be overwritten by the circular write feature of the log. Xtrabackup remembers log sequence number (LSN) when it starts, and then copies InnoDB's data page by page.

Xtrabackup rolls back committed transactions, uncommitted or failed transactions during recovery to ensure data consistency. Therefore, for InnoDB tables, the table is not locked during the backup. Because XtraBackup's built-in InnoDB library opens the file with rw, the user running XtraBackup must have read and write access to InnoDB's data file.

Add:

1.

In the process of backing up innodb page, XtraBackup reads and writes 1MB data, 1MB/16KB=64 page at a time. This is not configurable. After reading the 1MB data, XtraBackup traverses the 1MB data page by page, and uses the buf_page_is_corrupted () function of innodb to check whether the data on this page is normal. If the data is abnormal, reread the page, up to 10 times. If it still fails, the backup fails and exits. When copying transactions log, read and write the data of 512KB each time. It is also not configurable.

two。 "if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:"

Mysql > CREATE USER 'bkuser'@'localhost' IDENTIFIED BY' secret'

Mysql > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkuser'

Mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO 'bkuser'@'localhost'

Mysql > FLUSH PRIVILEGES

Second, full backup demonstration and log analysis:

[root@mysql01 full] # innobackupex-- user=root-- password=oracle / xtrabackup/full/

170602 20:24:02 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!".

170602 20:24:02 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: not set

Using server version 5.6.25-log

Innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 6f7a799)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / var/lib/mysql/

Xtrabackup: open files limit requested 0, set to 1024

# # obtain configuration information about innodb from configuration file

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =.

Xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

# # scan innodb log lsn and copy inndodb system tablespace

170602 20:24:02 > > log scanned up to (1626057)

Xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 1 for mysql/innodb_table_stats, old maximum was 0

170602 20:24:02 [01] Copying. / ibdata1 to / xtrabackup/full/2017-06-02 20-24-02/ibdata1

170602 20:24:02 [01]... done

170602 20:24:03 [01] Copying. / mysql/innodb_table_stats.ibd to / xtrabackup/full/2017-06-02 20-24-02/mysql/innodb_table_stats.ibd

.

170602 20:24:03 [01]... done

170602 20:24:03 [01] Copying. / mysql/slave_master_info.ibd to / xtrabackup/full/2017-06-02 20-24-02/mysql/slave_master_info.ibd

170602 20:24:03 [01]... done

170602 20:24:03 > > log scanned up to (1626057)

# # buffer write to data file and lock table

170602 20:24:03 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

170602 20:24:03 Executing FLUSH TABLES WITH READ LOCK...

# # start copying non-innoDB tables and related files

170602 20:24:03 Starting to backup non-InnoDB tables and files

170602 20:24:03 [01] Copying. / mysql/help_category.MYI to / xtrabackup/full/2017-06-02 20-24-02/mysql/help_category.MYI

170602 20:24:03 [01]... done

170602 20:24:03 [01] Copying. / mysql/plugin.frm to / xtrabackup/full/2017-06-02 20-24-02/mysql/plugin.frm

170602 20:24:03 [01]... done

170602 20:24:03 [01] Copying. / mysql/plugin.MYD to / xtrabackup/full/2017-06-02 20-24-02/mysql/plugin.MYD

.

170602 20:24:05 [01] Copying. / performance_schema/events_stages_history_long.frm to / xtrabackup/full/2017-06-02 20-24-02/performance_schema/events_stages_history_long.frm

170602 20:24:05 [01]... done

# # end copying non-innoDB tables and related files

170602 20:24:05 Finished backing up non-InnoDB tables and files

170602 20:24:05 [00] Writing xtrabackup_binlog_info

170602 20:24:05 [00]... done

# # force the commit log to be flushed to redo to ensure that the transaction is complete

170602 20:24:05 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

# # obtain the latest lsn

Xtrabackup: The latest check point (for incremental): '1626057'

Xtrabackup: Stopping log copying thread.

.170602 20:24:05 > > log scanned up to (1626057)

# # releasing Lock

170602 20:24:05 Executing UNLOCK TABLES

170602 20:24:05 All tables unlocked

# # list backup location

170602 20:24:05 Backup created in directory'/ xtrabackup/full/2017-06-02 Backup created in directory'20-24-02 Universe

# # list the location of binlog

MySQL binlog position: filename 'binlog.000001', position' 120'

170602 20:24:05 [00] Writing backup-my.cnf

170602 20:24:05 [00]... done

170602 20:24:05 [00] Writing xtrabackup_info

170602 20:24:05 [00]... done

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

170602 20:24:05 completed OK!

Third, obtain the relevant information of the backup

1. View files generated during backup

[root@mysql01 full] # ls / xtrabackup/full/2017-06-02 / 20-24-02 /

Backup-my.cnf performance_schema xtrabackup_checkpoints

Ibdata1 test xtrabackup_info

Mysql xtrabackup_binlog_info xtrabackup_logfile

Description:

two。 View overall information about backups

[root@mysql01 full] # more / xtrabackup/full/2017-06-02 20-24-02/xtrabackup_info

Uuid = 5e8841af-478e-11e7-a0d7-000c2944297a

Name =

Tool_name = innobackupex

Tool_command =-user=root-password=... / xtrabackup/full/

Tool_version = 2.4.7

Ibbackup_version = 2.4.7

Server_version = 5.6.25-log

Start_time = 2017-06-02 20:24:02

End_time = 2017-06-02 20:24:05

Lock_time = 0

Binlog_pos = filename 'binlog.000001', position' 120'

Innodb_from_lsn = 0

Innodb_to_lsn = 1626057

Partial = N

Description > xtrabackup_info record: summary of the entire backup information

3. View information about backup checkpoint

[root@mysql01 full] # more / xtrabackup/full/2017-06-02 20-24-02/xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 1626057

Last_lsn = 1626057

Compact = 0

Recover_binlog_info = 0

Description > xtrabackup_checkpoints record: backup type (such as full or incremental), backup status (such as whether it is already prepared status) and LSN (log serial number) scope information

4. View information about backup binlog

[root@mysql01 full] # more / xtrabackup/full/2017-06-02 20-24-02/xtrabackup_binlog_info

Binlog.000001 120

Description > xtrabackup_binlog_info record: the binary log file currently in use by the mysql server and the location of the binary log event up to the moment of backup.

5. View configuration options information for backup commands

[root@mysql01 full] # more / xtrabackup/full/2017-06-02 20-24-02/backup-my.cnf

# This MySQL options file was generated by innobackupex.

# The MySQL server

[mysqld]

Innodb_checksum_algorithm=innodb

Innodb_log_checksum_algorithm=innodb

Innodb_data_file_path=ibdata1:12M:autoextend

Innodb_log_files_in_group=2

Innodb_log_file_size=50331648

Innodb_fast_checksum=false

Innodb_page_size=16384

Innodb_log_block_size=512

Innodb_undo_directory=.

Innodb_undo_tablespaces=0

These are all the contents of this article entitled "the principle of xtrabackup in mysql, backup log analysis, sample analysis of backup information acquisition". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.