preface         

        The main purpose of backup is disaster recovery. Backup can also test applications, rollback data modification, query historical data, audit, etc.

Log plays an important role in backup and recovery.

1, MySQL log

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

    1. Configuration file

vim /etc/my.cnf
[mysqld]
##The error log is used to record error messages that occur when MySQL is started, stopped, or running. It is enabled by default
log-error=/usr/local/mysql/data/mysql_error.log     #Specify the save location and file name of the log

##The general query log is used to record all MySQL connections and statements. It is closed by default
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##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
log-bin=mysql-bin      
or
log_bin=mysql-bin


##relay logs 
In general, it is Mysql The master-slave synchronization (replication) and read-write separation cluster's slave nodes are enabled. The primary node generally does not need this log


##Slow query log, used to record all execution times exceeding long_ query_ For the statement of time seconds, you can find which query statements take a long time to remind optimization. It is off by default
s1ow_query_log=ON
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

##Copy segment
log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

      2. View binary log

systemctl restart mysqld
mysql -u root -P
show variables like 'general%';       #Check whether the general query log is enabled
show variables like 'log_bin%';       #Check whether the binary log is enabled
show variables like '%slow%';         #Check whether the slow query day function is enabled
show variables like 'long_query_time';    #View slow query time settings
set global slow_query_log=ON;         #Set the method of starting slow query in the database

PS:variables Representation variable like Represents a fuzzy query

#xxx (field)
xxx% with xxx Fields starting with
%xxx with xxx Fields ending with
%xxx% As long as it appears xxx All fields will be displayed
xxx   Accurate query

#After the binary log is enabled, restart mysql to view the binary log in the directory
cd /usr/local/mysql/data
ls
mysql-bin.000001		#When binary log is opened, an index file and an index list will be generated

Index files: record update statements
 Index file refresh method:
1,restart mysql The index file is updated to record new update statements
2,Refresh binary log

mysql-bin.index: 
Index of binary log file

2, Importance of data backup

    1. The primary purpose of backup is disaster recovery

    2. In a production environment, data security is critical

    3. Any loss of data can have serious consequences

    4. Causes of data loss

          Program error
          Human operation error
          Arithmetic error
          Disk failure
          Disasters (e.g. fire, earthquake) and theft

3, Backup type

    1. Physical backup

        Database backup can be divided into physical backup and logical backup. Physical backup refers to the backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be quickly restored in case of problems. Physical backup can be cold backup (offline backup), hot backup (connected backup) and warm backup

        1-1. Cold backup (offline backup): it is performed when the database is closed (tar)

        1-2. Hot backup (online backup): the database is running and depends on the database log file (mysqlhotcopy mysqlbackup)

          1-3. Warm backup: perform backup operation (mysqldump) in the state of database locking table (not writable but readable)

      2. Logical backup

           Logical backup refers to the backup of the logical components of the database, which is expressed as the logical database structure. This type of backup is suitable for data values or table structures that can be edited    

          From the perspective of database backup strategy, backup can be divided into full backup, differential backup and incremental backup

          2-1. Full backup
                Every time the data is backed up completely, that is, the whole database, database structure and file structure are backed up. What is saved is the database at the time when the backup is completed. It is the basis of differential backup and incremental backup. The backup and recovery operations of full backup are very simple and convenient, but the data has a lot of duplication and will occupy a lot of disk space, and the backup time is also very long. Every time a full backup is performed, the backup file will occupy a huge space and have a large amount of duplicate data. When restoring, you can directly use the full backup file

          2-2. Differential backup
                Backup all files that have been modified since the last full backup. The backup time node is from the last full backup, and the amount of backup data will be larger and larger. When restoring data, you only need to restore the last full backup and the best differential backup. Every differential backup will back up the data after the last full backup, and duplicate data may occur. When restoring, first restore the fully backed up data, and then restore the differential backed up data

          2-3. Incremental backup
                Only those files modified after the last full backup or incremental backup will be backed up. The time of the last full backup or incremental backup is the time point. Only the data changes during the backup period, so the amount of data backed up is small, the occupied space is small, and the backup speed is fast. However, during recovery, all increments from the last full backup to the last incremental backup need to be recovered in turn. If the backup data in the middle is damaged, it will lead to data loss. Each incremental backup is to back up the data after the last full backup or incremental backup. There will be no duplication of data, and it will not occupy additional disk space. To restore the data, you need to restore the data of full backup and incremental backup in order.

      3. Comparison of backup methods

Backup mode	Full backup	Differential backup	Incremental backup
Status at full backup	Table 1 and table 2	Table 1 and table 2	Table 1 and table 2
Content added for the first time	Create table 3	Create table 3	Create table 3
Backup content	Table 1, table 2 and table 3	Table 3	Table 3
Add content for the second time	Create table 4	Create table 4	Create table 4
Backup content	Table 1, table 2, table 3 and table 4	Table 3 and table 4	Table 4

      4. Strategies for logical backup (incremental, full, and differential)

          Reasonably select logical backup strategy (frequency):

                Once a week, the full backup shall be conducted between PM 10:00 and AM 5:00 in the time interval when the service is not provided
              Incremental: one incremental backup every 3 days / 2 days / 1 day

              Difference: select a specific scenario for backup
              A processing server (NFS) provides additional space for the mysql server

4, Common backup methods

      1. Physical cold standby

         When backing up, the database is closed, and the database file (tar) is packaged directly. The backup speed is fast and the recovery is the simplest

      2. Special backup tools mysqldump 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

          MySQL supports incremental backup. Binary log must be enabled during incremental backup. Binary log files provide users with replication and restore the information required for database changes after the backup point. In case of incremental backup (including data modification since the last full backup or incremental backup), the binary log needs to be refreshed.

      4. Third party tool backup

          Free MySQL hot backup software Percona XtraBackup mysqlbackup

5, MySQL full backup

      It is the backup of the whole database, database structure and file structure. It saves the database at the time of backup completion. It is the basis of differential backup and incremental backup.

      Advantages: simple and convenient backup and recovery operation

      Disadvantages: there are a lot of data duplication, which takes up a lot of backup space and takes a long time for backup and recovery

      1. Database full backup classification

        1-1. Physical cold backup and recovery

                Close the MySQL database, use the tar command to directly package the database folder and directly replace the existing MySQL directory

          1-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, and use the command mysq | to import the backed up data

  6, MySQL full backup and recovery deployment process

       The database of InnoDB storage engine is stored in three files on disk: db.opt (table attribute file), table name. Frm (table structure file), and table name. IBD (table data file).

      1. Environmental preparation

use mysql;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id));

insert into info1 values(1,'user1',20,'running');
insert into info1 values(2,'user2',30,'singing');

        2. Physical cold backup and recovery

systemctl stop mysqld
yum -y install xz
#Compressed backup
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
mv /usr/local/mysql/data/ /opt/
#Decompression recovery
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data/
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./

        3. mysqldump backup and recovery (warm backup)

create table info2 (id int,name char(10),age int,sex char(4));
insert into info2 values(1,'user',11,'Gender');
insert into info2 values(2,'user',11,'Gender');

(1),Full backup of one or more complete libraries (Include all tables)
mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql    #The exported is the database script file
 example:
mysqldump -u root -p --databases kgc > /opt/kgc.sql       #Back up a kgc Library
mysqldump -u root -p --databases mysql kgc > /opt/mysql-kgc.sql    #Backup mysql and kgc Libraries 

(2),Full backup MySQL All libraries in the server
mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql
 example:
mysqldump -u root -p --all-databases > /opt/all.sql

(3),Fully backs up some tables in the specified library
mysqldump -u root -p[password] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.sql
 example:
mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_info1.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/kgc_info1.sql | grep -v "^/" | grep -v "^$"

        4. Mysql full recovery

#Restore database
1.use mysqldump For exported files, you can use the import method
source command
mysql command

2.use source To restore a database

Log in to MySQL database
 implement source backups sql Path to the script

3.source Examples of recovery

MySQL [(none)]> source /backup/all-data.sql

use source Command recovery data
1.There is a problem with the simulation database

[root@server1 backup]# mysql -uroot -pabc123 login database
mysql> show databases;  View database information
mysql> drop database school;  Delete database school
mysql> show databases; 



2 Application example:

create backup copy(Backup tables)
[root@server1 ~]# mysqldump -uroot -pabc123 school info > /opt/info.sql  
[root@server1 ~]# mysql -uroot -pabc123 log in to the database to view
 
Restore data table

mysql> source /opt/info.sql   
mysql> select * from info;  Query all fields
mysql> show tables; View table information

Mode 2:
[root@mysql abc]# mysqldump -uroot -p123123 school info >/abc/school.info.sql
[root@mysql abc]# mysql -uroot -p123123 -e 'drop table school.info;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql abc]# mysql -uroot -p123123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| kk02             |
| kk09             |
| kk11             |
| kk12             |
| kk12_new         |
| mc               |
| test04           |
| test05           |
+------------------+
[root@mysql abc]# mysql -uroot -p123123 school < /abc/school.info.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql abc]# mysql -uroot -p123123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| info             |
| kk02             |
| kk09             |
| kk11             |
| kk12             |
| kk12_new         |
| mc               |
| test04           |
| test05           |
+------------------+

PS:mysqldump Strictly speaking, it belongs to warm backup, which requires write locking of the table
 In the full backup and recovery experiment, it is assumed that the existing kk11 Library, kk11 There is one in the library test The following points should be noted:
① Add when backing up --databases ,Indicates the needle pair ky11 library
#Backup command
mysqldump -uroot -p123123 --databases school > /opt/school_01.sql After backing up the library
#The recovery command process is:
mysql -uroot -p123123 
drop database kk11;
exit
mysql -uroot -p123123 < /opt/kk11_01.sql
② Do not add when backing up --databases，Indicates for kk11 All tables under the library
#Backup command
mysqldump -uroot -p123123 kk11 > /opt/kk11_all.sql
#Recovery process:
mysql -uroot -p123123
drop database kk11;
create database kk11;
exit
mysql -uroot -p123123 kk11 < /opt/kk11_02.sql 

#View kk11_01.sql and kk11_02.sql 
The main reason is that there are two methods of backup (the former will be from"create databases"start,The latter is all about tables) 

4.In a production environment, you can use Shell Script automatically realizes scheduled backup (time and frequency need to be confirmed)

summary

        Cold backup (offline backup): it is performed when the database is shut down (tar)

        Warm backup: perform backup operation (mysqldump) in the state of database locking table (not writable but readable)