Shulou(Shulou.com)06/01 Report--

#

Incremental backup and incremental recovery

First, enable binlog logs to achieve incremental backup

1. Binlog log description:

Also known as binary log, is a kind of mysql database service log files.

Benefits:

Record all SQL commands except queries

Can be used for data recovery

Necessary conditions for configuring mysql master-slave synchronization

# # #

2. Enable binlog log

# vim / etc/my.cnf

[mysql]

Server_id=id number / / value range is 1-255. cannot be repeated

Log-bin [= dir/name]

/ / the default location for log storage is / var/lib/mysql/,. If you need to specify the name and location of the log file, you need to write it in parentheses.

Note: the directory of the location specified by yourself needs to modify the file owner and group. Chown-R mysql:mysql storage path.

Binlog_format= "mixed" / / log format

# systemctl restart mysqld

# ls / var/lib/mysql/

Hostname-bin.000001 / / default binlog log file name

Hostname-bin.index / / record the current binlog log file name

Log format:

Statement: every command that modifies data is recorded in the binlog log.

Row: the context information of the sql command is not recorded, only which record is modified.

Mixed: a combination of the first two.

# # #

3. View the binlog log:

# mysqlbinlog binlog log file name

# # #

4. How the binlog log file records sql commands:

Point in time / / recorded according to the modified time

Pos points / / record according to data offset

# # #

5. Restore data

Format:

Mysqlbinlog option binlog log file name | mysql-uroot-p password

Options:

-- "restore based on point in time"

Start-datatime= "yyyy-mm-dd hh:mm:ss"-- stop-datatime= "yyyy-mm-dd hh:mm:ss"

-- "restore based on offset-- start-position= digits-- stop-position= digits

# # #

6. Generate and delete logs

-- "generate log

Mysql > flush logs

# mysql-uroot-p123456-e "flush logs"

# systemctl restart mysqld

-- "Delete logs

Mysql > reset master; / / Delete all

Mysql > purge master logsto "Log File name" / / Delete all logs before the specified log

Mysql > purge master logs to "mysql11-bin.000003" / / 1 and 2 have been deleted, but reserved after 3

#

7. Give an example

Enable binlog Log

Restart the service

Write data

# mysql-uroot-p123456

Show master status; / / View the currently used binlog log

Create database db1

Create table db1.t1 (id int)

Insert into table db1 values (100)

Insert into table db1 values (200)

Select * from db1.t1; / / check whether the data is inserted successfully

Delete data

Drop databases db1

View the log

Mysqlbinlog / var/lib/mysql/mysql11-bin.000001

/ / by querying the log, the offset of create database db1; table building at 296

/ / insert into table db1 values (200); insert the offset of this record at 1097

Recover data (by offset)

Mysql > show databases; / / confirm that the library has been deleted

Mysql > quit

# mysqlbinlog-start-position=296-stop-position=1097\

/ var/lib/mysql/mysql11-bin.000001 | mysql-uroot-p123456

View the result

Mysql > show databases

Mysql > select * from db1.t1

#

Second, use the command innobackupex provided by the third-party software percona to do incremental backup

Physical backup disadvantages:

Long backup time, redundant backup, waste of village space

Poor cross-platform

Mysqldump backup disadvantages:

Low efficiency and slow backup and restore time

During backup, data insertion and update will be suspended

Advantages of percona:

The backup process does not lock the table, which is suitable for production environment.

Support for InnoDB/XtraDB

Encapsulating Xtrabackup with Perl script also supports MyISAM

# # #

1. Install the software package (download from the Internet)

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

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

# # #

2. Basic options for innobackupex

-- host / / hostname

-- user / / user name

-- password / / login password

-- databases / / database name

-- no-timestamp / / backup files are not named by date

-- apply-log / / rollback the log (ready to restore)

-- redo-only / / Log rollback merge (this option is not required for the last rollback merge)

-- incremental directory name / / incremental backup

-- incremental-basedir= directory name / / specify the file name stored in the last backup data village during the incremental backup

-- export / / Export table information

Import / / Import tablespace

# # #

3. Full backup

# innobackupex-user root-password 123456-databases= "system Library Repository" backup directory name-no-timestamp

# rm-rf / var/lib/mysql

# mkdir / var/lib/mysql

Fully recover data

# innobackup-- user root-- password 123456-- databases= "system Library Repository"-- apply-log backup directory name

/ / roll back the log

# innobackupex-- user root-- password 123456-- databases= "system Library list Storage Database"-- copy-back backup directory name

/ / restore data

# ls / var/lib/mysql/

# chown-R mysql:mysql / var/lib/mysql

# systemctl stop mysqld

# systemctl start mysqld

# mysql-uroot-pabc123

Mysql > show databases

# # #

4. Incremental backup

Usually make a full backup before an incremental backup

Incremental backup for the first time:

# innobackupex-user root-password 123456-databases= "system Library list Storage Database"-- incremental directory name 1-- incremental-basedir= full backup name-- no-timestamp

The second incremental backup:

# innobackupex-user root-password 123456-databases= "system Library list Storage Database"-- incremental directory name 2-- incremental-basedir= directory name 1-- no-timestamp

/ / it is equivalent to your next backup, referring to the last backup file.

5. Delete information

Rm-rf / var/lib/mysql

Mkdir / var/lib/mysql

6. Incremental recovery log files

# innobackupex-- user root-- password 123456-- databases= "system Library Repository"-- apply-log-- redo-only full backup directory name

# innobackupex-- user root-- password 123456-- databases= "system Library Repository"-- apply-log-- redo-only full backup directory name

-- incremental-dir= directory name 1

# innobackupex-- user root-- password 123456-- databases= "system Library Repository"-- apply-log-- redo-only full backup directory name

-- incremental-dir= directory name 2

# cat backup directory / xtraback_checkpositions / / you can view the log serial number

7. Restore data

# innobackupex-- user root-- password abc123-- databases= "system Library list Storage Database"-- copy-back full backup directory name

# chown-R mysql:mysql / var/lib/mysql

8. Restart the service to view

# systemctl stop mysqld

# systemctl start mysqld

#

9. Incremental examples

/ allbak / / Custom the directory name of the full backup

/ new1 / / customize the directory name of the first incremental backup

/ new2 / / customize the directory name of the second incremental backup

/ / you do not need to create your own

Full backup

# innobackupex-- user root-- password 123456-- databases= "system Library list gamedb" / allbak

-- no-timestamp

Incremental for the first time:

Mysql > create table gamedb.t2 (id int)

Mysql > insert into gamedb.t2 values

# innobackupex-user root-password 123456-databases= "system Library list gamedb"-incremental / new1-incremental-basedir=/allbak-no-timestamp

Increment the second time:

Mysql > create table gamedb.t3 (id int)

Mysql > insert into gamedb.t3 values

# innobackupex-user root-password 123456-databases= "system Library gamedb"-incremental / new2

-incremental-basedir=/new1-no-timestamp

Delete data

Rm-rf / var/lib/mysql

Mkdir / var/lib/mysql

Restore log files

# innobackupex-user root-password abc123-databases= "system Library list gamedb"-apply-log-redo-only

/ allbak

/ / roll back the log

# cat / allbak/xtraback_checkpositions / / File can view log serial number

# innobackupex-user root-password abc123-databases= "system Library list gamedb"-apply-log-redo-only

/ allbak-- incremental-dir=/new1

/ / incremental rollback / new1

# cat / new1/xtraback_checkpositions / / File can view log serial number

# cat / allbak/xtraback_checkpositions / / File can view log serial number

/ / by comparison, it can be found that the rollback is successful and their serial numbers are connected end to end.

# innobackupex-user root-password abc123-databases= "system Library list gamedb"-apply-log-redo-only

/ allbak-- increment-dir=/new2

/ / incremental rollback / new2

# cat / new2/xtraback_checkpositions / / File can view log serial number

# cat / allbak/xtraback_checkpositions / / File can view log serial number

/ / by comparison, it can be found that the rollback is successful and their serial numbers are connected end to end.

Recover data

# innobackupex-user root-password abc123-databases= "system Library list gamedb"-copy-back / allbak

View

# systemctl stop mysqld

# systemctl start mysqld

#

10. Restore a single table:

10.1 Reserve Library

# innobackup-user root-password 123456-databases= "gamedb" / gamedbbak

10.2 Delete a single table

Mysql > drop table gamedb.t1

10.3 create a table according to the original table structure

Mysql > create table gamedb.t1 (id int)

10.4 Export table information

# innobackup-user root-password abc123-databases= "gamedb.t1"-apply-log-export / gamedbbak

10.5 Delete tablespace

Mysql > alter table gamedb.t1 discart tablespace

10.6 copy the exported table information to the corresponding database directory

# cd / gamedbbak/gamedb

# cp t1. {exp,ibd,cfg} / var/lib/mysql/gamedb/

# ls / var/lib/mysql/gamedb/t1.

# chown mysql:myql / var/lib/mysql/gamedb/t1.

10.6 Import tablespace

Alter table t1 import tablespace

10.7 Verification

Select * from gamedb.t1

#

4 、

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.