1, Importance of data backup

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
Operation error disk failure
Disasters (e.g. fire, earthquake) and theft

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

Physical backup: backup of physical files (such as data files, log files, etc.) of database operating system
Physical backup method
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)
Logical backup: backup of database logical components (such as tables and other database objects)

3, From the perspective of database backup strategy

1. Three backup methods

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

2. Difference between differential backup and incremental backup

According to the above conceptual analysis, the difference between differential backup and incremental backup lies in their different reference points: the reference point of the former is the last full backup, differential backup or incremental backup, and the reference point of the latter is the last full backup.
The following figure shows the difference between differential backup and incremental backup, in which Cumulative is differential backup and differential incremental backup:

3. Examples of combined applications of different backup types

① Full backup and differential backup

Take the weekly data backup plan as an example. We can perform a full backup on Monday and a differential backup from Tuesday to Friday. If the data is damaged on Friday, we only need to restore the full backup on Monday and the differential backup on Thursday. This strategy takes more time to backup data, but less time to restore data.

② Full backup and incremental backup

Take the weekly data backup as an example. Perform a full backup on Monday and an incremental backup from Tuesday to Friday. If the data is damaged on Friday, you need to restore the normal backup on Monday and all incremental backups from Tuesday to Friday. This strategy takes less time to back up the data, but it takes more time to restore the data.

4, Common backup methods

1. Physical cold standby

·T he database is closed during backup. Directly packaging database files is fast for backup and the simplest for recovery

2. Special backup tools

mydump or mysqlhotcopysysqldump is a common logical backup tool
mysqlhotcopy only has backup MyISAM and ARCHIVE tables

3. Enable binary logs for incremental backups

·For incremental backup, the binary log needs to be refreshed and backed up by a third-party tool

4. Free MySQL hot backup software

Percona XtraBackup

5, Database full backup classification

1. Physical cold backup and recovery

Close MySQL database
Use the tar command to package the database folder directly
Simply replace the existing MySQL directory

2. mysqldump backup and recovery

MySQL's built-in backup tool can facilitate the backup of MySQL
You can export the specified libraries and tables as SQL scripts
Use the mysql command to import the backed up data

6, 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 the log and prevent dirty pages from being written to the 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: Records sql that takes too long to execute. The time threshold (10s) can be configured. Only successful execution is recorded
Another function is to remind optimization

5. bin log binary log

Function: used for master-slave replication to realize master-slave synchronization
The contents of the record are: 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

7, View log

1. View general query log

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

2. View binary log

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

3. View slow query function

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

4. View slow query time

(root@localhost) [class]> show variables like 'long_query_time';    #View slow query time settings

5. Configuration file

[root@localhost data]# vim /etc/my.cnf

log-error=/usr/local/mysql/data/mysql_error.log            #Specify the save location and file name of the log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log    #The general query log is used to record all MySQL connections and statements. It is closed by default
log-bin=mysql-bin        #Binary log is used to record all statements that have updated data or have potentially updated data, record data changes, and can be used for data recovery. It is enabled by default
slow_query_log=ON                        #The slow query log is used to record all statements whose execution time exceeds long_query_time seconds. You can find which query statements take a long time to remind optimization. It is turned off by default 
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5                  #Set that statements executed for more than 5 seconds are recorded. The default is 10 seconds
binlog_format = MIXED

[root@localhost data]# systemctl restart mysqld

When binary log is opened, an index file and an index list will be generated

8, Backup operation

Existing table

1. Cold backup:

[root@localhost ~]# systemctl stop mysqld                   #Deactivate the database first

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /back
[root@localhost ~]# tar zcf /back/mysql_all_$(date +F%).tar.gz /usr/local/mysql/data

Remove the 'cy' table from the database

[root@localhost ~]# mv /usr/local/mysql/data /usr/local/mysql/data.bak back up the original data
[root@localhost ~]# cd /back
[root@localhost back]# tar zxf mysql_all_2021-11-29.tar.gz extract the file

Pay attention to the permission problem here

So you have to copy the permissions

[root@localhost mysql]# CP - RA data / usr / local / MySQL / data use - p or - a here
 perhaps
[root@localhost mysql]# mv  data /usr/local/mysql/data
[root@localhost mysql]# systemctl restart mysqld.service

So the form comes back

2. mysqldump backup and recovery (warm backup)

2.1. Fully backup one or more complete libraries (including all tables therein)

mysqldump -uroot -p [password] - databases database name 1 [database name 2]... > / backup path / backup file name. sql # exports database script files

[root@localhost mysql]# mysqldump -uroot -pzy44123 -- databases class > /back/class.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cd /back/
[root@localhost back]# ls

2.2. Backup two libraries

[root@localhost ~]# mysqldump -uroot -pzy44123 --databases class mysql > /back/class_mysql.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

2.3. Fully back up all databases in the MySQL server

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

[root@localhost ~]# mysqdump -uroot -pzy44123 --all-databases  > /back/mysql_all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

2.4. Fully backup 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

[root@localhost back]# mysqldump -uroot -pzy44123 class cy > /back/class_cy.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

2.5. Only backup table structure

#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

2.6. View backup files

[root@localhost back]# cat /back/class_cy.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

9, Restore database

Simulate deleting class library

1. Restore database

[root@localhost ~]# mysql -uroot -pzy44123 < /back/class_mysql.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

When the backup file has only tables, you need to specify the database. There is no need to create a new database, otherwise an error will be reported

[root@localhost ~]# mysql -uroot -pzy44123 class</back/class_cy.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'class'

2. Restore table structure

[root@localhost back]# mysql -uroot -pzy44123 class < /back/class_cy-d.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

There is only structure and no data

10, 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

11, Additional preparation experiment

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 logs:
#STATEMENT (based on SQL STATEMENT)
#Row (row based)
#Mixed mode,
#The default format is state

[root@localhost data]# systemctl restart mysqld restart service

There will be one more binary log

2. You can perform a full backup of the database or table every week

mysqldump -uroot -p123123 class cy >/backup/class_cy.sql
mysqldump -uroot -p123123 --all-databases  >/backup/class_cy.sql

3. Incremental backup operations can be performed every day to generate new binary files (MySQL bin. 00000 n)

mysqladmin -uroot -pzy44123 -p flush-logs

4. Insert new data into table

5. Generate a new binary log file again

mysqladmin -uroot -pzy4123  flush-logs

#Previous operations are saved in the previous binary file, and subsequent operations are saved in the new binary file

6. View binary log files

cp /usr/loacal/mysql/data/mysql-bin.000006 /back
mysqlbinlog --no-defaults --base64-output=decode-rows -v /back/mysql-bin.000006

7. Incremental recovery:

delete from cy where id=1;                          First simulate lost data in the database

mysqlbinlog --no-defaults /back/mysql-bin.000006 |mysql -u -root -pzy44123   Retrieve data