Mysql (5)-data recovery
#
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 、
