MySQL backup and recovery

Keywords: Database MySQL

1, Overview of backup and recovery

1.1 backup function

Disaster recovery: hardware failure, software failure, natural disaster, hacker attack, misoperation test and other data loss scenarios

1.2 types of backup

  • Full backup: backs up the entire database
  • Partial backup: backs up a subset of the database, such as a partial library or table
  • Incremental backup: only the data changed since the last full backup or incremental backup (if there is an increment) is backed up. The backup is fast and the restore is complex
  • Differential backup: only the data changed since the last full backup is backed up. The backup is slow and the restore is simple

Incremental backup structure diagram:

Restore method: restore the first day, then restore the second day, and so on.

Differential backup structure diagram:

Restore method: only restore the first day and the last day.

  • Cold, warm and hot backup

    • Cold standby: read and write operations are not allowed, and the database service is stopped
    • Warm standby: the read operation is executable; But the write operation is not executable
    • Hot standby: both read and write operations can be performed
      MyISAM: cold standby and warm standby. Hot standby is not supported
      InnoDB: all support

  • Physical and logical 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 and saving it as a file. It has nothing to do with the storage engine, occupies less space, is slow, and may lose accuracy

1.3 backup objects

  • data
  • Binary log, transaction log of InnoDB
  • User account, permission setting, program code (stored procedure, function, trigger, event scheduler)
  • Server profile

1.4 key points for backup

  • How much data can you tolerate
  • Load from backup
  • Length of backup process
  • How long does the warm lock last
  • How long does it take to recover data
  • What data needs to be backed up and restored

1.5 restore key points

  • Perform restore test to test the availability of backup
  • Restore the drill and write it into a standard technical document

1.6 backup tools

  • cp, tar and other replication and archiving tools: physical backup tools, applicable to all storage engines; Only cold standby is supported; Full and partial backups
  • Snapshot of LVM: add read lock first, unlock after snapshot, almost hot standby; Backup with file system tools
  • mysqldump: a logical backup tool, which is applicable to all storage engines and provides warm backup for MyISAM storage engine; Support full or partial backup; Support hot standby for InnoDB storage engine, combined with incremental backup of binlog
  • xtrabackup: Percona provides a tool to support hot backup (physical backup) of InnoDB, supporting full backup and incremental backup
  • MariaDB Backup: the integration starts from MariaDB 10.1.26 and is implemented based on Percona XtraBackup 2.3.8
  • mysqlbackup: hot backup, MySQL Enterprise Edition component
  • mysqlhotcopy: it is implemented in PERL language and is almost cold standby. It is only applicable to MyISAM storage engine. LOCK TABLES, FLUSH TABLES and cp or scp are used to quickly back up the database

2, Backup practice

2.1 database cold backup and restore

1.stop it mysql service
[root@rocky8 my.cnf.d]# systemclt stop mysqld
2.Backup data
[root@rocky8 mysql]# cd / && tar -czf  /opt/mysql_`date +%F`.tar.gz  /var/lib/mysql/*

[root@rocky8 /]# ll /opt
total 9700
-rw-r--r--. 1 root root 9932094 Nov 15 21:00 mysql_2021-11-15.tar.gz

#If there are special settings related to configuration and binary files, they also need to be backed up
# To restore, just find another machine and unzip the tar package to / var/lib/mysql /

2.2 mysqldump backup tool

mysqldump is a MySQL client command that connects to the MySQL server through the MySQL protocol for backup
Command format:

1.mysqldump [OPTIONS] database [tables]   #Supports backup of specified databases and specified multiple tables,
However, the definition of the database itself is not backed up and is not recommended

2.mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  #Support specified database backup, including
 The definition of the database itself will also be backed up --databases Equivalent to-B

3.mysqldump [OPTIONS] --all-databases [OPTIONS]  #Backup system database mysql and user built
 All databases, including the definition of the database itself, will also be backed up --all-databases Equivalent to-A

mysqldump official reference document

-u --user=name user for login if not current user # Specify user name
-p, --password[=name] Password to use when connecting to server. # Specify password
-A, --all-databases #Back up all databases, including create database
-B, --databases db_name...  #Specify the database to back up, including the create database statement
-E, --events: #Back up all relevant event scheduler s
-R, --routines: #Back up all stored procedures and custom functions
--triggers: #Backup table related triggers are enabled by default. Use -- skip triggers. Do not backup triggers
--default-character-set=utf8 #Specify character set
--master-data[=#]:  #Binary logging must be enabled for this option
#1: Add a record before the backed up data as CHANGE MASTER TO Statement, non comment, unspecified#, the default is 1, which is suitable for master-slave complex
 Multi machine use
#2: Record as annotated#The CHANGE MASTER TO statement is suitable for stand-alone use and backup and restore
#This option will automatically turn off the -- lock tables function and automatically turn on the - x | -- lock all tables function (unless it is turned on)--
single-transaction)

-F, --flush-logs #Roll the log before backup. After locking the table, execute the flush logs command to generate a new binary log file,
coordination-A or -B Option will cause the database to be refreshed multiple times. It is recommended to perform dump and log refresh at the same time--singletransaction or-x，--master-data Using the implementation together, the binary log is refreshed only once
--compact        #The notes are removed, which is suitable for debugging, saves the space occupied by backup, and is not used in production
-d, --no-data    #Only the table structure is backed up, not the data, that is, only the create table is backed up 
-t, --no-create-info #Only the data is backed up, and the table structure is not backed up, that is, the create table is not backed up 
-n,--no-create-db #create database is not backed up and can be overwritten by - A or - B
--flush-privileges #Required when backing up mysql or related
-f, --force       #Ignore SQL errors and continue
--hex-blob        #Use hexadecimal symbols to dump BINARY columns. When BINARY and VARBINARY are included,
BLOB，BIT The data type of the column is used to avoid garbled code
-q, --quick     #Do not cache queries, output directly, and speed up backup

– master data usage

First, you need to start the binary log. mysql8 is enabled by default
1. View the current binary position

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11042 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

2. Backup

# It is equivalent to a full backup
[root@rocky8 ~]# mysqldump -uroot -p123 -A --master-data=1 > /tmp/full-master-data.sql

3. Check the backed up sql file and find that the starting position of binary log is added in the first line

[root@rocky8 ~]# vim /tmp/full-master-data.sql 
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=11042;

If -- master data = 2, the difference between backup and backup is that the above line is also generated, but it is commented out
4. Simulate the real environment and add records to the database

mysql> insert into teachers (name,age,gender) values ('Jack Ma',18,'M'),('Musk',20,'M');

View database status
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Jack Ma          |  18 | M      |
|   6 | Musk        |  20 | M      |
+-----+---------------+-----+--------+

5. Review the binary position

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11361 | No        |
+---------------+-----------+-----------+

6. Backup binlog file to generate incremental backup

[root@rocky8 mysql]# mysqlbinlog /var/lib/mysql/binlog.000001 --start-position=11042 > /tmp/inc.sql

6. Simulate the fault and manually delete the / var/lib/hellodb folder, that is, delete the library

7. Exit and login mysql again to execute the command

mysql> source /tmp/full-master-data.sql
mysql> source /tmp/inc.sql


# Found that the data unexpectedly tm came back
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Jack Ma          |  18 | M      |
|   6 | Musk        |  20 | M      |
+-----+---------------+-----+--------+

Backup options related to mysqldump's MyISAM storage engine:
MyISAM does not support transactions and can only support warm standby; it does not support hot standby, so you must first lock the library to be backed up and then start the backup operation

-x,--lock-all-tables #Add a global read lock, lock all tables of all libraries, and add -- single transaction or--
lock-tables Option will turn off this option function. Note: when the amount of data is large, the database may not be accessed concurrently for a long time
-l,--lock-tables #For each database to be backed up, lock all its tables before starting the backup. The default value is on--
skip-lock-tables Option can be disabled,For backup MyISAM Multiple libraries for,Data inconsistency may occur
#Note: the above options are effective for InnoDB table and realize warm standby, but they are not recommended

Backup options related to InnoDB storage engine of mysqldump:
The InnoDB storage engine supports transactions. It can use the corresponding isolation level of transactions to realize hot standby or warm standby, but it is not recommended

--single-transaction
#This option is recommended in Innodb and does not apply to MyISAM. This option will be enabled by executing the START TRANSACTION command before starting the backup
 affair
#This option creates a consistent snapshot by dumping all tables in a single transaction. Applies only to tables stored in a storage engine that supports multi version control
(Currently only InnoDB (yes); Dumps are not guaranteed to be consistent with other storage engines. When performing a single transaction dump, ensure a valid dump
 File (correct table content and binary log location), no other connection, the following statement should be used: ALTER TABLE，DROP TABLE，
RENAME TABLE，TRUNCATE TABLE,This option and--lock-tables(This option implies that the commit pending transactions) option is mutual
 exclude,When backing up large tables, it is recommended that--single-transaction Options and--quick Use together

2.3 production environment backup strategy

InnoDB recommended backup strategy

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM recommended backup strategy

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges  --
triggers  --default-character-set=utf8  --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

5.3 actual combat

5.3.1 backup script of specific database

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASSWD=123
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p$PASSWD -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

5.3.2 sub database backup and compression

[root@rocky8 ~]# cat mysq_backup1.sh
TIME=`date +%F_%H-%M-%S`
DIR=/backup

PASS=123
for db in `mysql -uroot -p$PASS -e 'show databases'|egrep -v '^(Database|sys|information_schema|performance_schema)$'`;
do 
	mysqldump -uroot -p$PASS -B $db | gzip >  ${DIR}/${DB}_${TIME}.sql.gz
;
done

Posted by Jeyush on Thu, 18 Nov 2021 13:22:47 -0800

Hot Keywords

©2024 Programmer Group