MySQL backup and recovery

preface

In enterprises, the value of data is very important, and data ensures the normal operation of enterprise business. Therefore, data security and data
Reliability is the top priority of operation and maintenance. Any loss of data may have serious consequences for the enterprise.

1: Classification of database backup

1.1: what are the reasons for data loss?

  1. Program error
  2. Human causes (mostly)
  3. Arithmetic error
  4. Disk failure
  5. Disasters (e.g. fire, earthquake) and theft

1.2: from the physical and logical point of view, backup can be divided into

  1. Physical backup: backup of physical files (such as data files, log files, etc.) of database operating system
  • Methods of physical backup

    • Cold backup (offline backup): it is performed when the database is closed (it is not commonly used now)
    • Hot backup (online backup): the database is running and depends on the log file of the database
    • Warm backup: the backup operation in the state of database locking table (not writable but readable)
  1. Logical backup: backup of database logical components (such as tables and other data path objects)

1.3: from the perspective of database backup strategy, it can be divided into

  1. Full backup: make a full backup of the database every time
  2. Differential backup: backs up files that have been modified since the last full backup
  3. Incremental backup: only files modified after the last full backup or incremental backup will be backed up

2: Full backup of MySQL

2.1: what is a full backup?

  1. Full backup refers to the backup of the whole database, database structure and file structure
  2. A full backup saves the database at the time the backup is completed
  3. Full backup is the foundation of incremental backup

2.2: what are the advantages and disadvantages of full backup?

  1. Advantages: high security, simple and convenient backup and recovery operation
  2. Disadvantages: data has a lot of duplication, takes up a lot of backup space, low space utilization, and long backup and recovery time

2.3: mysqldump backup Library

2.3.1: Backup MySQL database

  1. Directly package the database folder, such as / usr/local/mysql/data
    For example: tar jcvf / opt / MySQL - $(data +% f) tar. gz /usr/local/mysql/data
  2. Use the special backup tool mysqldump

2.3.2: what is mysqldump?

  1. The mysqldump command is a built-in backup tool for MySQL, which is quite convenient for MySQL backup
  2. Through this command tool, you can export the specified library, table or all libraries as SQL script, and recover the data when you need to recover

2.3.3: how to use mysqldump command to backup Library

  1. Backing up a single library
Syntax:
mysqldump -u user name -p [password] [option] [Library name] > /Backup path/Backup file name
 For example:
mysqldump -u root -p sjk > /backup/sjk.sql
  1. Backing up multiple libraries
Syntax:
mysqldump -u user name -p [password] [option] --databases Library name 1 [Library name 2] ...  > /Backup path/Backup file name (multiple libraries are separated by spaces)
For example:
mysqldump -u root -p --databases mysql test > /backup/my_tt.sql
  1. Full backup of all libraries
Syntax:
mysqldump -u user name -p [password] [option] --all-databases > /Backup path/Backup file name
 For example:
mysqldump -u root -p --opt --all-databases > /backup/all_data.sql

2.3.4: how to use mysqldump command to backup tables

  1. Operation of backing up tables using mysqldump
grammar
mysqldump -u user name -p [password] [option] Database name table name > /Backup path/Backup file name
 for example
mysqldump -u root -p sjk info > /backup/sjk_info.sql
  1. Backup table structure using mysqldump
mysqldump -u user name -p [password] [option] -d Database name table name > /Backup path/Backup file name
 for example
mysqldump -u root -p -d sjk info > /backup/sjk_info.sql

3: How to restore databases and tables

3.1: two methods of restoring database

  1. source command
mysql > source /backup/all-data.sql
'//The 'source' command is used in the mysql Library
  1. mysql command
Syntax:
mysql -u user name -p [password] < Path to the library backup script
'//The import < symbol is used here instead of the export > symbol '
for example
mysql -u root -p < /backup/all-data.sql

3.2: two methods of restoring tables

  1. You can also use the source or mysql commands when restoring tables
  2. The operation of restoring the source table is the same as that of restoring the library
  3. When the backup file contains only the backup of the table, not the statement to create the library, the library name must be specified and the target library must exist
grammar
mysql -u user name -p [password] < Path to the table backup script
 for example
mysql -u root -p mysql < /backup/mysql_info.sql

4: Incremental backup of MySQL

4.1: what is MySQL incremental backup

  • Only those files that have been modified since the last full or incremental backup will be backed up
  • Incremental backup is to back up files or contents that have been added or changed since the last backup

4.2: advantages and disadvantages of incremental backup

  1. Advantages: no data duplication, high efficiency and maximum space utilization; Small amount and time period of backup
  2. Disadvantages: it is troublesome to restore (all incremental backups can be restored only after the last full backup and full backup, and all incremental backups should be restored repeatedly one by one); Low safety

4.3: how to realize MySQL incremental backup?

  • MySQL does not provide a direct incremental backup method
  • Incremental backup can be realized through the introduction of binary logs provided by MySQL
MySQL Of the configuration file[mysqld]Add to item log-bin=filepath Item( filepath Is the path to the binary file, such as log-bin=mysql-bin´╝îThen restart mysqld Service.

The default path for binary log files is/usr/local/mysql/data

[root@localhost ~]# vim /etc/my.cnf
'//Add the configuration log bin = MySQL bin 'in the [mysqld] item
...Omit content
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=mysql-bin	'//'add this sentence'
...Omit content
[root@localhost ~]# systemctl restart mysqld 	'// 'restart service '
[root@localhost ~]# ls /usr/local/mysql/data/
...Omit content
mysql-bin.000001	'//Found that binary file has been generated, setting succeeded '
...Omit content
  • Significance of MySQL binary log to backup

    • The binary log holds all operations that update or possibly update the database
    • The binary log starts recording after starting the MySQL server, and the file reaches max_binlog_size or re create a new log file after receiving the flush logs command
grammar
mysqladmin -u user name -p [password] flush-logs
  • Just periodically execute the flush logs method to recreate new logs, generate binary file sequences, and save these old logs to a safe place in time to complete an incremental backup for a period of time

5: How to incrementally restore MySQL database

5.1: view the operation statements in the log file

mysqlbinlog --no-defaults --base64-output=decode-rows -v Log file name /opt/1.txt	'//Use the 64 bit decoder to output the log file by line and put it in / opt / 1 'in txt'
cat /opt/1.txt  '//'view log file details'

5.2: general recovery

grammar
mysqlbinlog [--no-defaults] Incremental backup file | mysql -u user name -p

5.3: breakpoint recovery

  1. Location based recovery
  • It is to import the binary log of a certain starting time into the database, so as to skip a certain point in time when an error occurs and realize data recovery
Restore data to the specified location
mysqlbinlog --stop-position='operation id' Binary log |mysql -u user name -p password
Recover data from the specified location
mysqlbinlog --start-position='operation id' Binary log |mysql -u user name -p password
  1. Point in time based recovery
  • Using point in time based recovery, there may be both correct and wrong operations at one point in time, so we need a more accurate recovery method
Recovery from the beginning of the log to a point in time
mysqlbinlog [--no-defaults] --stop-datetime='year-month-Day hour:minute:second' Binary log |mysql -u user name -p password
Recovery from a point in time to the end of the log
mysqlbinlog [--no-defaults] --start-datetime='year-month-Day hour:minute:second' Binary log |mysql -u user name -p password
Recovery from a point in time to a point in time
mysqlbinlog [--no-defaults] --start-datetime='year-month-Day hour:minute:second' --stop-datetime='year-month-Day hour:minute:second' Binary log |mysql -u user name -p password

Tags: Database MySQL

Posted by corsc on Thu, 12 May 2022 07:41:45 +0300