MySQL fifth day (incremental backup and incremental recovery)

Source: Internet
Author: User
Tags flush sha1 percona

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.