MySQL fifth day (incremental backup and incremental recovery)

Day05
I. Data backup and recovery-incremental and incremental recovery
A.binlog Log description (binary file): one of the types of log files used by the MySQL database service. Logs a SQL command other than a query. Not enabled by default

Queries for SQL include: Select desc Show

Command not queried: Insert update delete create drop grant revoke

B. Enable Binlog logging
How to Enable
Log Default storage location
/var/lib/mysql
Log default name
Host name-bin.000001
51-bin.000001 (more than 500M auto-generated 000002)
51-bin.index #索引文件

Modify the log storage location and name?
Mkdir/logdir
Chown Mysql/logdir
Setenforce 0
Vim/etc/my.cnf
server_id=51
#log-bin
LOG-BIN=/LOGDIR/PLJ # #指定日志文件位置
binlog-format= "Mixed" # # #指定日志保存类型, displays the full format of the SQL command, if not added only the command is logged
#systemctl Restart Mysqld

View the contents of the log file (viewed with a command, because it is a binary file)
] #mysqlbinlog Binlog Log | Grep-i Insert
] #mysqlbinlog Binlog Log

E. How log files record SQL commands
Time Point--start-datetime= "Yyyy-mm-dd hh:mm:ss"
--stop-datetime= "Yyyy-mm-dd Hh:mm:ss"

Offset--start-position= Number
--stop-position= Digital

] #mysqlbinlog [options] Binlog Log | mysql-uroot-p123456
Log Default storage location/var/lib/mysql
Log default name host-bin.000001
51-bin.000001 binlog log file (more than 500M auto-generated 000002)
51-bin.index Index File

F. SQL commands in the execution log file to recover data
#mysqlbinlog [options] Log name | Mysql-uroot-p
Mysql> Delete from Db3.user where id=54;
[Email protected] logdir]# mysqlbinlog--start-position=328--stop-position=1102 plj.000001 | mysql-uroot-p654321

G. Manually creating a new file
Mysql>flush logs; #刷新一次生成一个 which number large log to which to save
mysql> system ls/logdir;
plj.000001 plj.000002 Plj.index

[Email protected] logdir]# mysql-uroot-p123456-e "flush Logs" #命令行直接执行sql命令
MySQL: [Warning] Using a password on the command line interface can is insecure.
[[email protected] logdir]# ls
plj.000001 plj.000002 plj.000003 Plj.index

[[email protected] logdir]# ls
plj.000001 plj.000002 plj.000003 Plj.index
[Email protected] logdir]# systemctl restart mysqld
[[email protected] logdir]# ls
plj.000001 plj.000002 plj.000003 plj.000004 plj.index

] #systemctl Restart Mysqld

#]mysqldump-uroot-p654321--flush-logs db3 > Db3.sql #备份时生成新的日志文件
F. Deleting a log file
F.1 using SQL command to delete
mysql> Reset Master;
Query OK, 0 rows affected (0.21 sec)
mysql> system ls/logdir;
plj.000001 Plj.index

Mysql> purge master logs to "plj.000005"; #删除指定000005编号之前的日志所有index (not including 000005 log files)
Query OK, 0 rows affected (0.14 sec)
mysql> system ls/logdir;
plj.000005 Plj.index

F.2 using System removal
RM-RF log files

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==
6.2 Installs the commands provided by 3rd party software to make incremental backups of the data
[[email  Protected] 09.mysql]# RPM-IVH libev-4.15-1.el6.rf.x86_64.rpm
Warning: libev-4.15-1.el6.rf.x86_64.rpm: Head V3 DSA/SHA1 Signature, key ID 6b8d79e6:nokey
Preparing ... ################################# [100%]
Package libev-4.15-1.el6.rf.x86_64
[[email protected] 09.mysql]# RPM-IVH percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
is already installed Warning: percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm: header V4 dsa/sha1 Signature, key ID Cd2efd2a:nokey
in preparation ... ############# #################### [100%]
Package percona-xtrabackup-24-2.4.7-1.el7.x86_64 already installed

[[email protected] 09.mysql]# rpm-qa |grep percona
percona-xtrabackup-24-2.4.7-1.el7.x86_64
[[Email  protected] 09.mysql]# rpm-ql percona-xtrabackup-24-2.4.7-1.el7.x86_64
/usr/bin/innobackupex
/usr/bin/ Xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/ share/doc/percona-xtrabackup-24-2.4.7
/usr/share/doc/percona-xtrabackup-24-2.4.7/copying
/usr/share/man/ man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/ man/man1/xtrabackup.1.gz

Full backup
[Email protected] 09.mysql]# Innobackupex--user root--password 123456--databases= "MySQL sys performance_schema DB5"/A Lldir--no-timestamp ##/alldir is a backup destination directory and must be empty, if a date directory is required,--no-timestamp is not required
[Email protected] 09.mysql]# Ls/alldir
BACKUP-MY.CNF DB5 ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_binlog_info xtrabackup_checkpoints Xtrabackup_info Xtrabackup_logfile

Full recovery (/var/lib/mysql must be empty)
[Email protected] 09.mysql]# Rm-rf/var/lib/mysql

[Email protected] 09.mysql]# Mkdir/var/lib/mysql

[Email protected] 09.mysql]# Innobackupex--user root--password 123456--databases= "MySQL sys performance_schema DB5"-- apply-log/alldir/# #恢复日志

[Email protected] 09.mysql]# Innobackupex--user root--password 123456--databases= "MySQL sys performance_schema DB5"-- copy-back/alldir/# #恢复数据

[Email protected] 09.mysql]# Ls/var/lib/mysql
DB5 ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema sys xtrabackup_binlog_pos_innodb Xtrabackup_info

[Email protected] 09.mysql]# chown-r mysql:mysql/var/lib/mysql

[Email protected] 09.mysql]# Ls-ld/var/lib/mysql
Drwxr-xr-x. 6 mysql mysql 4096 February 04:24/var/lib/mysql

[Email protected] 09.mysql]# systemctl restart mysqld

[Email protected] 09.mysql]# mysql-uroot-p123456
Mysql>

Incremental backup:
1. One full backup
] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"/fullbak--no-timestamp
INSERT into DB5.T1 values (400);
SELECT * from Db5.t1;

2. Incremental backup: Incremental backup after changing data (backup of newly generated data)
] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--incremental/onedir
--incremental-basedir=/fullbak--no-timestamp

INSERT into DB5.T1 values (500);
SELECT * from Db5.t1;

3. Incremental backup After the second incremental backup changes the data
[Email protected] 09.mysql]# Innobackupex--user root--password 123456--databases= "MySQL sys performance_schema DB5"-- Incremental/twodir--incremental-basedir=/onedir--no-timestamp

Incremental recovery data? (First, second, third backup serial number and data, restore (merge) to first backup directory # #序列号和数据合并)
--applay-log Recovery Log
--redo-only Merge Log
--incremental-dir= directory name when recovering data, the backup directory used during the first incremental backup

1. Clear the database directory
Rm-rf/var/lib/mysql
Mkdir/var/lib/mysql
2. Recovery log (Restore in order: full, second incremental, third incremental, fourth time pushed backwards)
] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--apply-log--redo-only/fullbak

] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--apply-log--redo-only/fullbak--incremental-dir= "/onedir"

Innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--apply-log--redo-only/fullbak--incremental-dir= "/twodir"

3. Copy the backup data to the database directory
] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--copy-back/fullbak

4. Modify the owner of the database directory all groups
] #chown-R mysql:mysql/var/lib/mysql
5. Restart the database service
Systemctl Stop Mysqld

6 Viewing data
mysql-uroot-p123456 DB5
Mysql>show tables;

] #innobackupex--user Root--password 123456
--databases= "MySQL sys performance_schema DB5"
--incremental/firstdir
--incremental-basedir=/fullbak--no-timestamp

How does incremental backup work?
Innobackupex enables incremental backups of the InnoDB storage engine.
The InnoDB storage engine has transaction log files that record SQL operations and data information

/var/lib/mysql/
IB_LOGFILE0 # #存放sql命令操作
Ib_logfile1 # #存放sql命令操作
IBDATA1 # #存放数据信息

LSN (serial number) # #ib_logfile0存放的sql命令操作从哪到哪 becomes the serial number from where to where (range)

Under Backup directory:
Xtrabackup_checkpoints (record LSN) ib_logfile0 the SQL command operation from where to where to change the serial number from to where (range), to compare with the LSN file of the last backup
Xtrabackup_logfile (Logging sql)
IBDATA1 (Data information)
The data is in the folder of the corresponding library

Recover a table's records from a full backup file
1.1 Do a full backup
[Email protected] ~]# Innobackupex--user root--password 123456--databases= "DB6"/db6allbak--no-timestamp
[Email protected] ~]# ls/db6allbak/db6/
A.frm a.ibd b.frm b.ibd db.opt
[Email protected] ~]# LS/VAR/LIB/MYSQL/DB6
A.frm a.ibd b.frm b.ibd db.opt

Simulate Delete:
Mysql>drop table db6.a;

2.2 Recovery Steps
--export
Import
Mysql>alter table library name. Name Discard

1 Exporting Table information
Innobackupex--user root--password 123456--databases= "DB6"--apply-log--export/db6allbak # #db6allbak为导出表目标目录
] #ls/db6allbak/db6/
A.cfg a.exp a.frm a.ibd b.cfg b.exp b.frm b.ibd db.opt

2. Create a table, delete the tablespace (the table structure must be the same as the structure before deleting)
Mysql> CREATE TABLE db6.a (name char (10));
Query OK, 0 rows affected (0.29 sec)
Mysql>alter table db6.a Discard tablespace; # #删除空表间文件ibd
[Email protected] sys]# ls/var/lib/mysql/db6/
A.frm b.frm b.ibd c.frm c.ibd db.opt

4. Copy the backup table information file to the database directory and modify the owner and group for MySQL
] #cp/DB6ALLBAK/DB6/A.{CFG,EXP,IBD}/var/lib/mysql/db6/
] #chown mysql:mysql/var/lib/mysql/db6/a.*
[Email protected] sys]# ls/var/lib/mysql/db6/
A.cfg a.exp a.frm a.ibd b.frm b.ibd c.frm c.ibd db.opt

5. Import Table Space
Mysql>alter table db6.a Import tablespace;

6. View table Records
Mysql>select * from db6.a
+++++++++++++++++++++++++++++++=

MySQL fifth day (incremental backup and incremental recovery)