MySQL database log, backup and recovery

Log type and function:

The default location of MySQL logs is / usr/local/mysql/data

1.redo redo log

Achieve transaction consistency (redo every restart)

Function: to ensure the persistence of logs and prevent dirty pages from not being written to disk in case of failure. Restarting the database will redo the redo log to achieve transaction consistency

2.undo rollback log

Function: ensure the atomicity of data, record the version before the transaction, and use it for rollback. innodb transactions can be read repeatedly and read committed. The isolation level is realized through mvcc+undo

3.errorlog error log

Function: Mysql itself starts, stops, and error messages that occur during operation

4.slow query log

Function: record sql that takes too long to execute. The time threshold (10s) can be configured to only record successful execution. Another function: to remind optimization

5.bin log binary log

Function: used for master-slave replication to realize master-slave synchronization. The content of the record is: sql statements executed in the database

6.relay log

Function: used for database master-slave synchronization, save the bin log sent from the master database locally, and then play back from the database

7.general log

Function: records the operation details of the database. It is closed by default. Opening it will reduce the performance of the database

Log query

#Check whether show is enabled in the general query log show variables like 'general%';

#Check whether the binary log is enabled show variables like 'log_bin%';

#Check whether the slow query day function is enabled show variables like '%slow%';

#View slow query time settings show variables like 'long_query_time';

The primary purpose of backup is disaster recovery

• In a production environment, data security is critical
• Any loss of data can have serious consequences
• Causes of data loss
• Program error
• Human operation error
• Arithmetic error
• Disk failure
• Disasters (e.g. fire, earthquake) and theft

Classification of database backup

Physical backup:

Directly copy data files for backup, which is related to the storage engine, occupies more space and is fast

Logical backup:

The backup performed by "exporting" data from the database to save as is independent of the storage engine, takes up less space, is slow, and may lose accuracy

1. From a physical and logical point of view, backup can be divided into

• Cold backup (offline backup): it is performed when the database is closed
• Hot backup (online backup): the database is running and depends on the log file of the database
• Warm backup: the backup operation is performed when the database is locked in a table (not writable but readable)

2. From the perspective of database backup strategy, backup can be divided into

• Full backup: perform a full backup of the database each time
• Differential backup: backs up files that have been modified since the last full backup
• Incremental backup: only files modified after the last full backup or incremental backup will be backed up

Common backup methods

1. Physical cold standby
The database is closed during backup, and the database files are packaged directly
Backup is fast and recovery is the simplest

2. Special backup tools mydump or mysqlhotcopy
mysqldump is a common logical backup tool
mysqlhotcopy only has backup MyISAM and ARCHIVE tables

3. Enable binary logs for incremental backups
For incremental backup, you need to refresh the binary log

4. Third party tool backup
Free MySQL hot backup software Percona XtraBackup

Cold standby: Read and write operations are not allowed, and the database service is stopped Warm standby: Read operation is executable; But the write operation is not executable Hot standby: Both read and write operations can be performed

MyISAM: warm standby, hot standby is not supported

InnoDB: all support

Actual operation

Configuration environment

mysql -u root -p create database SCHOOL; use SCHOOL; create table if not exists CLASS1 ( id int(4) not null auto_increment, name varchar(10) not null, sex char(10) not null, hobby varchar(50), primary key (id)); insert into CLASS1 values(1,'user1','male','running'); insert into CLASS1 values(2,'user2','female','singing'); set password = password('123123');

Physical cold backup and recovery

The database of InnoDB storage engine is stored in three files on disk:

• Db.opt (table properties file)
• Table name.frm (table structure file)
• Table name. IBD (table data file)

systemctl stop mysqld yum -y install xz

  Compressed backup

tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

Decompression recovery

tar Jxvf /opt/mysql_all_2021-12-01.tar.xz -C /usr/local/mysql/data systemctl restart mysql

mysqldump backup and recovery (warm backup)

1. Fully back up one or more complete libraries (including all tables therein)

mysqldump -u root -p [password] - databases database name 1 [database name 2]... > / backup path / backup file name.sql  

Back up a library mysqldump -u root -pabc123 --databases school > /opt/school.sql #The exported is the database script file backups mysql And kgc Two libraries mysqldump -u root -pabc123 --databases mysql school > /opt/mysql-school.sql

2. Fully back up all databases in the MySQL server

mysqldump -u root -p [password] - All Databases > / backup path / backup file name.sql

mysqldump -u root -pabc123 --all-databases > /opt/all.sql

​

3. Fully back up some tables in the specified library

mysqldump -u root -p [password] database name [table name 1] [table name 2]... > / backup path / backup file name.sql

mysqldump -u root -pabc123 school info > /opt/info.sql

mysqldump -u root -pabc123 [-d] school info > /opt/info.sql

//Use the "- d" option to explain that only the table structure of the database is saved

//If the "- d" option is not used, the table data will also be backed up

//As a table structure template

4. View backup files

grep -v "^--" /opt/school.sql | grep -v "^/" | grep -v "^$"

Full backup recovery
(1) , restore database

mysql -uroot -p123123 -e 'drop database SCHOOL;' 1 #The "- e" option is used to specify the command to be executed after connecting to MySQL. After the command is executed, it will exit automatically mysql -uroot -p123123 -e 'SHOW DATABASES;' mysql -uroot -p123123 < /opt/SCHOOL.sql mysql -uroot -p123123 -e 'SHOW DATABASES;'

(2) . recovery data table
When the backup file contains only the backup of the table, but not the statement of the created library, the library name must be specified when performing the import operation, and the target library must exist.

mysql -uroot -p123123 -e 'drop table SCHOOL.CLASS1;' mysql -uroot -p123123 -e 'show tables from SCHOOL;' mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1.sql mysql -uroot -p123123 -e 'show tables from SCHOOL;'

MySQL incremental backup and recovery

MySQL database incremental recovery

1. General recovery

• Restore all backed up binary log contents

2. Location based recovery

• The database may have both wrong and correct operations at a certain point in time
• Wrong operations can be skipped based on precise location
• The node before the error node, the location point of the last correct operation stops

3. Point in time based recovery

• Skip a point in time when an error occurs to achieve data recovery
• Stop at the wrong time point and start at the next correct time point

1. Enable binary log function

vim /etc/my.cnf [mysqld] log-bin=mysql-bin binlog_format = MIXED #Optional, specify that the recording format of binary log is MIXED (MIXED input) server-id = 1 #This command can be added or not #There are three different recording formats for binary log: state (based on SQL STATEMENT), row (based on row) and mixed (mixed mode). The default format is state systemctl restart mysqld ls -l /usr/local/mysql/data/mysql-bin.*

  2. The database or table can be fully backed up every week

mysqldump -uroot -p123123 SCHOOL CLASS1 > /opt/SCHOOL_CLASS1_$(date +%F).sql mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql

3. Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 2)

mysqladmin -uroot -p123123 flush-logs

4. Insert new data to simulate the addition or change of data

mysql -uroot -p123123 use SCHOOL; insert into CLASS1 values(3,'user3','male','game'); insert into CLASS1 values(4,'user4','female','reading');

5. Generate a new binary log file again (for example, MySQL bin. 00000 3)

mysqladmin -uroot -p123123 flush-logs

6. View the contents of the binary log file

cp /usr/local/mysql/data/mysql-bin.000002 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 #-- Base64 output = decode rows: decode and read by line using 64 bit encoding mechanism #-v: Show details

BEGIN /!/; #at 302 #210129 0:39:12 server id 1 end_log_pos 430 CRC32 0x2c164d0a Query thread_id=10 time=0 error_code=0 use SCHOOL/!/; SET TIMESTAMP=1611851952/!/; insert into CLASS1 values(3,'user3','male','game') /!/; #at 430 #210129 0:39:12 server id 1 end_log_pos 461 CRC32 0x225bb461 Xid = 76 COMMIT/!/; #at 461 #210129 0:39:13 server id 1 end_log_pos 526 CRC32 0xe5abe22c Anonymous_GTID last_comd=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/!/; #at 526 #210129 0:39:13 server id 1 end_log_pos 609 CRC32 0x2cfb793b Query thread_id=10 time=0 error_code=0 SET TIMESTAMP=1611851953/!/; BEGIN /!/; #at 609 #210129 0:39:13 server id 1 end_log_pos 742 CRC32 0x7ea13a1a Query thread_id=10 time=0 error_code=0 SET TIMESTAMP=1611851953/!/; insert into CLASS1 values(4,'user4','female','reading') /!/; #at 742 #210129 0:39:13 server id 1 end_log_pos 773 CRC32 0x11b21cd0 Xid = 77 COMMIT/!/;

MySQL incremental backup and recovery

1. General recovery
(1) Simulate recovery steps for lost changed data

mysql -uroot -p123123 use SCHOOL; delete from CLASS1 where id=3; delete from CLASS1 where id=4; select * from CLASS1; quit mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

(2) . simulate the recovery steps of all lost data (basically the same, pay attention to the log date)

mysql -uroot -p123123 use SCHOOL; drop table CLASS1; quit mysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS1_2021-01-29.sql mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

2. Breakpoint recovery

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

Example:
at 302
201122 16:41:16
User data for 'user3' was inserted

at 623
201122 16:41:24
User data for 'user4' was inserted

(1) Location based recovery
#Only recover the data before operation ID "609", that is, do not recover the data of "user4"

mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p

Example:

mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

#Only recover the data of "user4". Skip the data recovery of "user3". After 609, there is only the fourth record
Example:

mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --start-position='609' /opt/mysql-bin.000002 | mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

(2) Point in time based recovery
#Only recover the data before 0:39:13, that is, do not recover the data of "user4"

Example: clear the table first CLASS1,Convenient experiment mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS1;" mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;" mysqlbinlog --no-defaults --stop-datetime='2021-01-29 0:39:13' /opt/mysql-bin.000002 |mysql -uroot -p123123 mysql -uroot -p123123 -e "select * from SCHOOL.CLASS1;"

#Only recover the data of "user4" and skip the data recovery of "user3" (basically consistent)

mysqlbinlog --no-defaults --start-datetime='2021-12-01 10:39:13' /opt/mysql-bin.000002 |mysql -uroot -p

Summarize breakpoint recovery
If you restore all data before an SQL statement, stop at the location node or time point of the statement
If you restore an SQ statement and all subsequent data, start from the location node or time point of the statement

Add new comment

0 comments