05: Real-time incremental backup, xtrabackup Backup, summary and troubleshooting, MySQL master-Slave synchronization

DAY05 Incremental Backup
First, enable Binlog log for real-time incremental backup
Second, the use of 3rd-party software provided by the command to do incremental backup

+++++++++++++++++++++++++++++++++
First, enable Binlog log for real-time incremental backup
1.1 Binlog Log Description: Binary log, which is one of the MySQL database service log files, records the execution of SQL commands other than the query. Not enabled by default.

SQL command for query: Show desc SELECT

1.2 Enable Binlog log?
Vim/etc/my.cnf
[Mysqld]
Server_id=50 #1-255
Log-bin
binlog-format= "Mixed" #ROW
....
: Wq
#systemctl Restart Mysqld

Log file default storage location: Database directory/var/lib/mysql/
Default name for log file: hostname-bin. Ref. 500M
Log index file Host50-bin.index

1.3 Viewing the contents of a log file?
#mysqlbinlog Directory Name/log file name
#mysqlbinlog/var/lib/mysql/host50-bin.000001

1.4 Customize the name and storage directory of the log file?
#mkdir/logdir
#chow Mysql/logdir
#setenforce 0
#vim/etc/my.cnf
[Mysqld]
Server_id=50 #1-255
#log-bin
Log-bin=/logdir/plj
binlog-format= "Mixed"
....
: Wq
#systemctl Restart Mysqld
#ls/logdir/

1.5 How does the log file record SQL commands?
Point in time
Offset (POS)

1.6 Execute SQL command in log file to recover data?
#mysqlbinlog [Options] Directory name/log file name | mysql-uroot-p123456

Point in time
--start-datetime= "Yyyy-mm-dd Hh:mm:ss"
--stop-datetime= "Yyyy-mm-dd Hh:mm:ss"

Offset (POS)
--start-position= Digital--stop-position= Digital

#mysqlbinlog--start-position=293--stop-position=1490/logdir/plj.000001 | mysql-uroot-p123456

1.7 Create a new binlog log file manually?
mysql> flush logs;
#mysql-uroot-p123456-e "Flush Logs"
#mysqldump-uroot-p123456--flush-logs DB4 >/root/db4.sql

Systemctl Restart Mysqld

1.7 Delete an existing binlog log file
mysql> Reset Master;
Mysql> purge master logs to "log file name";
Mysql> purge master logs to "plj.000006";
++++++++++++++++++++++++++++++++++++++++++
Second, use the command provided by 3rd party software to do incremental backup---
2.1 Percona Introduction?

2.2 Installing Percona
#yum-y Install perl-digest-md5 perl-dbd-mysql
#rpm-IVH libev-4.15-1.el6.rf.x86_64.rpm
#rpm-IVH percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2.3 The syntax format of the Innobackupex command?
#innobackupex < options >

Options?
--user User Name
--password Password
--databases Library Name
--no-timestamp a subdirectory name that does not use the system date for the backup directory
--apply-log Recovery Log
--copy-back Recovering data

A, use innobackupex name only to do full and full recovery?
A.1 Full backup
#innobackupex--user root--password 123456--databases= "MySQL performance_schema sys db66"/allbak--no- Timestamp
#ls/allbak
A.2 full restore
#innobackupex--user root--password 123456--databases= "MySQL Performance_ Schema sys db66 "--apply-log/allbak
#rm-rf/var/lib/mysql
#mkdir/var/lib/mysql
#innobackupex--user Root-- Password 123456--databases= "MySQL performance_schema sys db66"--copy-back/allbak
#ls/var/lib/mysql
#chown-R Mysql:mysql/var/lib/mysql
#systemctl Stop mysqld
#systemctl start mysqld
#mysql-uroot-p123456
MySQL > show databases;

/var/lib/mysql
++++++++++++++++++++++++++++++++++++++
Incremental backup
--incremental Directory name #增量备份数据存储目录
--incremental-basedir= Directory name #上次备份数据存储目录
1 Get a full backup first/onedir
#innobackupex--user root--password 123456--databases= "MySQL performance_schema sys db66"/onedir--no-timestamp
2 Incremental backup
First incremental backup storage directory/new1dir
#innobackupex--user Root--password 123456
--databases= "MySQL performance_schema sys db66"
--incremental/new1dir--incremental-basedir=/onedir--no-timestamp
The second incremental backup data store directory/new2dir
#innobackupex--user Root--password 123456
--databases= "MySQL performance_schema sys db66"
--incremental/new2dir--incremental-basedir=/new1dir--no-timestamp

How incremental Backups work:
Innobackupex command-----> InnoDB Storage Engine table

Under Database directory/var/lib/mysql
Transaction log files record SQL operations and data information that is performed
Ib_logfile1 Ib_logfile0 ibdata1

LSN Log Sequence Number

File description for backup directory:/onedir/new1dir/new2dir
Cat Xtrabackup_checkpoints
Xtrabackup_logfile

Incremental recovery (requires that the database directory must be empty)
--redo-only Merge Log
The--incremental-dir= directory name #增量恢复时 that specifies which backup directory to use for recovery data.

1 #rm-rf/var/lib/mysql
2 Recovery Log
#innobackupex--user Root--password 123456
--databases= "MySQL performance_schema sys db66"
--apply-log--redo-only/onedir

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

#innobackupex--user Root--password 123456
--databases= "MySQL performance_schema sys db66"
--apply-log--redo-only/onedir--incremental-dir=/new2dir
3 Recovering data
#innobackupex--user Root--password 123456
--databases= "MySQL performance_schema sys db66"
--copy-back/onedir

4 Modifying the file owner

Chown-r Mysql:mysql/var/lib/mysql

5 Restarting the database service
#systemctl Restart Mysqld

6 Viewing data
#mysql-uroot-p123456
Mysql>
++++++++++++++++++++++++++++++++++++++++
Recovering a table from a fully backed up data

Innobackupex Command information:
--export Exporting table information
Import Imports Table Space

Delete Table space
mysql> ALTER TABLE library. Tables Discard tablespace;

Db66.a
db66.b
Full backup:
#innobackupex--user Root--password 123456
--databases= "Db66"/db66bak--no-timestamp
#ls/db66bak

mysql> use db66;
mysql> drop table A;
Mysql> CREATE TABLE A (id int);

#ls/var/lib/mysql/db66/
A.frm A.IBD

Export table Information
#innobackupex--user Root--password 123456
--databases= "Db66"--apply-log--export/db66bak
#ls/db66bak/db66/
Delete Table space
Mysql> ALTER TABLE db66.a discard tablespace;
#ls/var/lib/mysql/db66/

Copy the exported table information file from the backup directory to the database directory

CP/DB66BAK/DB66/A.{CFG,EXP,IBD}/var/lib/mysql/db66/chown mysql/var/lib/mysql/db66/a.*

Import Table Space
mysql> ALTER TABLE db66.a import tablespace;
Mysql> Select from A;
Mysql> Select from B;

++++++++++++++++++++++++++++++++++++++++
Innobackupex Incremental Backup
Options:
--user--password--databases
--incremental
--incremental-basedir
--apply-log--redo-only--incremental-dir
--export Import

mysql> ALTER TABLE library. Tables Discard tablespace;

Full backup Full recovery
Incremental backup incremental Recovery (incremental backup principle)
Recovering data from a table using a full backup file

+++++++++++++++++++++++++++++++++++++++++
53:scp-r/var/lib/mysql 192.168.4.51:/var/lib/
51:chown-r Mysql:mysql/var/lib/mysql
Systemctl Restart Mysqld

05: Real-time incremental backup, xtrabackup Backup, summary and troubleshooting, MySQL master-Slave synchronization