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

Source: Internet
Author: User
Tags mixed install perl percona

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

Related Article

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.