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

Complete Raw data: test Library

Start complete: backup directory / backup/full/20191126

Innobackupex-socket=/tmp/mysql.sock-user=root-password=123456 / backup/full/20191126-no-timestamp-no-lock-throttle=100

-- no-timestamp does not use timestamps to generate directories. The default backup directory is a timestamp directory.

-- throttle IO per second

-- no-lock closes table locks. For applicable scenarios, please see innobackupex help.

Xtrabackup_binlo_info records the location of the binary log, and we need to use the

Start the data operation, create a table t, and add 2 pieces of data

Check out the binary log: [root@localhost logs] # mysqlbinlog-vvv mysql-bin.000016 / *! 50530 SET @ @ SESSION.PSEUDORESLAVEMODEFOR "50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4: 191126 10:25:02 server id 1 end_log_pos 123 CRC32 0xfaeb9c84 Start: binlog v 4, server v 5.7.26-log created 191126 10:25:02 at startup# Warning: this binlog is either in use or was not closed proper.ROLLBACKAAXQ8BAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADjNxdEgNAAgAEgAEBAQEEgAXwAEGAggAICCAAgAACKgoKioAjAAYAAYAZWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 123 / 191126 10:25:02 server id 1 end_log_pos 194 CRC32 0xdfc752d1 Previous-GTIDs# 044c1532-f7c9-11e9-8ac4-00505637b733:1-311 operations at 194 # location where the new operation begins # 191126 14:17:58 server id 1 end_log_pos 259 CRC32 0x894b1a31 GTID last_committed=0 sequence_number=1 rbr_only=noSET @ @ SESSION.GTID_NEXT= '044c1532-f7c9-11e9-8ac4-00505637b733JV 3113 seam operations / # at 259 sessions 191126 14:17:58 server id 1 end_log_pos 376 CRC32 0x4eb46aa2 Query thread_id=22 exec_time=0 error_code=0use `test` / * * /; SET TIMESTAMPphones 1574749078 tobacco sets @ @ session.pseudothread readreadable cards 22pxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; SET TIMESTAMPphones 1574749078; set @ @ session.sqlcards modewords 1436549120sweep / session.session. SET @ @ session.auto_increment_increment=1, @ @ session.autoincrement increment offsetbacks 1 racer,\ C utf8 * / / *! * /; SET @ @ session.session setsetclientholders 331 copies of sessions. Collationalization contacts connections3 copies session. Collationalization timetables namespace 33 plains session. Collection timetables namespace names 0 minutes session. LCCs timetables namespaces @ session. Session. Collection databases database sets @ session.collationalization databases DEULTGULTGULTGULTGULTULTGULTING accounts, create table t (id int) default charsets # at 376 "191126 14:24:57 server id 1 end_log_pos 441 CRC32 0x0f84381a GTID last_committed=1 sequence_number=2 rbriches onlyzag" set @ @ SESSION.GTID_NEXT= '044c1532-f7c9-11e9-00505637b733 server id 14:24:57 server id 1 end_log_pos 513 CRC32 0x6de752e9 Query thread_id=23 exec_time=0 error_code=0SET timespacks 15747497Universe # at 513 October 191126 14:24:57 server id 1 end_log_pos 557 CRC32 0x95e465d0 Table_map: `test`.`t` mapped to number 27 examples at 557 191126 14:24:57 server id 1 end_log_pos 602 CRC32 0x5e3fb325 Write_rows: table id 275 flags: STMT_END_FBINLOG 'OcXcXRMBAAAAALAAAC0CAAABMBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # INSERT INTO `test`.`t` # # SET### @ 1cm 1 / * INT meta=0 nullable=1 is_null=0 * / # INSERT INTO `test`.`t` # SET### @ 1mm 2 / * INT meta=0 nullable=1 is_null=0 * / # at 602191126 14:24:57 server id 1 end_log_pos 633 CRC32 0x11f291f2 Xid = 139COMMIT Universe @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER # End of log file deletion operation: delete all data under the data directory of the mysql database directly, because you need this directory to be empty during restore, so delete it right here.

Restore operation: prepare step:

Innobackupex-default-file=/backup/full/20191126/backup-my.cnf-user=root-password=123456-apply-log / backup/full/20191126/

Copy_back steps:

Innobackupex-default-file=/backup/full/20191126/backup-my.cnf-user=root-copy-back / backup/full/20191126/

After recovery, you need to change the master group under the mysql data directory to mysql (I am the mysql user and the default root user), and restart mysql

Login verification:

Restored to the original table location.

What if you want to resume what you do later? Restore using binary logs. The binlog2sql tool is used here (installation is brief).

Find the starting and ending point of the binary log that we need to recover through the backed-up xtrabackup_binlog_info.

Python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uadmin-padmin-- start-file='mysql-bin.000016'-- start-position= "259"-- stop-position= "602" > / tmp/tmp.sql

It should be noted here that the USE statement in tmp.log is a bit wrong and needs to be modified. In addition, you can change tmp.log to tmp.sql.

Restore:

Mysql-p

< /tmp/tmp.sql 注意事项： mysql在做恢复的时候需要关闭二进制日志 set sql_log_bin=0，数据恢复后在开启; 在恢复之前需要重启mysql，而且还会报表t已经存在。 增量备份： 为了防止干扰，把/backup/full/20191126清空，并把最新的表数据作为原始数据。 当前原始数据5张表，t表2条数据（主要以t表做测试）

Complete:

Innobackupex-socket=/tmp/mysql.sock-user=root-password=123456 / backup/full/20191126-no-timestamp-no-lock-throttle=100

Do data operations: delete 2, add 3pens 4pens 5

First incremental backup

Innobackupex-defaults-file=/etc/my.cnf-socket=/tmp/mysql.sock-user=root-password=123456-no-timestamp-incremental-basedir=/backup/full/20191126-incremental / backup/incre/20191126

Second incremental backup

Do some more operations on the database

New data 6pc7

Innobackupex-defaults-file=/etc/my.cnf-socket=/tmp/mysql.sock-user=root-password=123456-no-timestamp-incremental-basedir=/backup/incre/20191126-incremental / backup/incre/20191127

Differential additions, based on the previous additions catalog

Backup recovery:

Prepare process

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456-apply-log-redo-only / backup/full/20191126

Merge incremental backups into the initial backup:

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456-apply-log-redo-only / backup/full/20191126-incremental-dir=/backup/incre/20191126

Copy-back process:

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456-copy-back / backup/full/20191126/

Modify the group owner of the data under the mysqldata directory and restart mysql

At present, the merge incremental backup only merges the first incremental backup, so only the first incremental backup data is restored.

Merge the second incremental backup

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456-apply-log-redo-only / backup/full/20191126-incremental-dir=/backup/incre/20191127

Copy-back:

Innobackupex-defaults-file=/etc/my.cnf-user=root-password=123456-copy-back / backup/full/20191126/

Still modify the master group and restart mysql. This time the data will be restored to the second backup.

Here, in actual production, you can merge incremental backups before copy-back operation.

Binary log recovery supplement:

For sql statements that are mistakenly deleted, you can use-- flashback to flashback sql statements.

Ex.: I deleted 7 of this data, and now I need to restore it. I still need to find the starting and ending position of the most recent backup.

Without adding-- flashback:

Python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uroot-p123456-start-file='mysql-bin.000019'-- start-position= "4"-- stop-position= "375" > / tmp/tmp.sql

You can see the SQL statement of your delete operation.

Add-- flashback:

Python binlog2sql/binlog2sql.py-h227.0.0.1-P3306-uroot-p123456-- flashback-- start-file='mysql-bin.000019'-- start-position= "4"-- stop-position= "375" > / tmp/tmp.sql

Data recovery can be done through flashback statements.

Of course, the actual situation may be more complicated than this. For example, we have erroneous deletions, but there are also new and correct data inserted, so we cannot all use flashbacks, or none of them use flashbacks. Then it is necessary to accurately find the point of misdeletion.

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.