Mysql1.5 (Binlog incremental backup and recovery, Innobackupex)

Directory: one, incremental backup and recovery

Second, MySQL master-slave synchronization

One incremental backup with incremental recovery:

1 Enable Binlog log files for MySQL services for real-time incremental backups of data

(1) Binlog Log Introduction

(2) Enable Binlog log

(3) manually generate a new Binlog log file

(4) Delete existing binlog log files

(5) Recovering data using Binlog logs

(1) Binlog Log Introduction:

One of the MySQL database service log files that records user connections

After the database server, execute an SQL command other than the query.

(2) Enable Binlog log:

1,VIM/ETC/MY.CNF (Configuration Master profile)

[Mysqld]

server_id=51 //define host number
Log_bin //start Binlog log
binlog_format="mixed"//define Logging format
Record format: statement: Record SQL command for each modified data

Row: Does not log the SQL statement security context and only saves which record is modified
Mixed: mix of both, record SQL commands and record security context, and modified records
2, Systemctl restart mysqld (restart service)

Default storage location for log files:

cd/var/lib/mysql/

Host name-bin.index log index file (records the current binlog log file name)

You can view it with cat

Hostname-bin. log file for the SQL command (the binary execution code is stored inside)

How to view:

mysqlbinlog [options] mysql51-bin.000001

Mysqlbinlog mysql51-bin.000001 | Grep-i Insert

Options are:

--start-datetime= "Yyyy-mm-dd hh:mm:ss"//Start date Time

--stop-datetime= "Yyyy-mm-dd hh:mm:ss"//End Date Time

--start-position= Digital--stop-position= Digital

Define the log file name storage location:

1,mkdir/logdir; Chown MYSQL/LOGIDR

2,vim/etc/my.cnf

[Mysqld]
server_id=51
log_bin="/logdir/hcy"
binlog_format= "Mixed"

3,systemctl Restart Mysqld

Note: Once configured, the original log file is invalid, and the newly generated command is present under/logdir/.

(3) manually generate a new Binlog log file

Why should I manually generate a new binlog log file?

The system default log file is only larger than 500M to generate a new log file and cannot be

Separate tables for individual libraries to record log files.

1, in the state of landing:

Execute Mysql->flush logs;

2 under the operating interface:

Execute mysql-uroot-p password-e ' flush logs '

Or: mysqldump-uroot-p password--fulsh-logs library name > Xxx.sql

3, restarting the MYSQLD service will also generate a new log file (this is not used in a production environment)

(4) Delete existing binlog log files

Use system commands to delete RM (index file does not synchronize updates)

To delete using the SQL command:

Mysql->reset master; Delete all log files reinitialize log files

Mysql->purge Master logs to "log name"; Delete log file before specified number

(5) command format for recovering data using Binlog logs

Mysqlbinlog "Options" Log file name | Mysql-uroot-p Password

Options for specifying offsets:

--start-position= Digital--stop-position= Digital

Options for specifying the time:

--start-datetime= "Yyyy-mm-dd hh:mm:ss"//Start date Time

--stop-datetime= "Yyyy-mm-dd hh:mm:ss"//End Date Time

2, using 3rd party software to provide commands Innobackupex

1, find the resources to install the package:

percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

RPM-UVH view the dependent packages and install the dependent packages to install the main package.

Perl-dbd-mysql PERL-DIGEST-MD5

libev-4.15-1.el6.rf.x86_64.rpm

Rpm-qa | grep Percona//List package name

RPM-QL percona-xtrabackup-24//list the files provided by the package

/usr/bin/innobackupex (Integrated xtrabackup supports InnoDB and xtradb and MyISAM)

/usr/bin/xtrabackup ( can only back up InnoDB, and xtradb)

2. Use the Innobackupex command to make a backup request?

(1) Ensure that all libraries in the database, the storage engine is InnoDB.

(2) To ensure that only the initial configuration of the main directory 3 libraries

(3) for the first time full backup, back up 3 main libraries

The above operation is a must, mainly for the software to build the environment.

Syntax format of the 3,innobackupex command

Innobackupex < options >

--user user--password password--databases database Name list

--incremental Directory name--incremental-basedir directory name

Innobackupex--user root--password 123456--databases= "

MySQL performance_schema sys gamedb "backup directory name--no-timestamp (full backup)

4, incremental backup

Innobackupex--user root--password 123456--databases= "

MySQL performance_schema sys gamedb "backup directory name--incremental directory name

--incremental-basedir= directory Name of the last backup data store (incremental backup)

Note: The directory specified for the backup must be empty.

5, Full recovery

Innobackupex--user root--password 123456--databases= "

MySQL performance_schema sys gamedb "--apply-log--redo-only full backup directory

Incremental recovery:

Innobackupex--user root--password 123456--databases= "

MySQL performance_schema sys gamedb "--apply-log--redo-only the directory where full backup data is stored

--incremental-basedir= Directory Name

Copy files:

Innobackupex--user root--password 123456--databases= "

MySQL performance_schema sys gamedb "--copy-back stores the directory for full backup data

principle Problem: if one day the main directory of the database is taken out, you can use the recovery log, first restore the full backup directory

In the iterative recovery of incremental catalog data, can not leapfrog recovery.              Executes the command--copy-back command after recovering a full backup. Last in Chown-r mysql:mysql mysql (directory)

Backup configuration file Description:

Cat xtrabackup_checkpoints ( log SQL command operation sequence number )

Backup_type = full-backuped//backup type (full backup)

FROM_LSN = 0//log serial number

TO_LSN = 4624642//log serial number

LAST_LSN = 4624651//log serial number
Compact = 0
Recover_binlog_info = 0

Xtrabackup_logfile ( stored record SQL command corresponds to serial number of xtrabackup_checkpoints )

Library Directory: ( data generated after SQL command execution )

Mysql1.5 (Binlog incremental backup and recovery, Innobackupex)