MySQL database backup (full backup and recovery)


Database backup is an important means to recover important data in time and prevent data loss in case of data loss. A reasonable database backup scheme can effectively recover data in case of data loss, and it also needs to consider the difficulty of technical implementation and effective utilization of resources.

1, MySQL data backup

1. Importance of database backup

In the production environment, the security of data is very important, and any data loss may have serious consequences.

The importance of database backup is mainly reflected in:
① Improve the high availability and disaster recoverability of the system. When the database system crashes, data cannot be found without database backup
② Using database backup to restore the database is the best scheme to provide the least cost of data recovery when the database crashes. If you let users add data again, it will be very expensive.
③ Without backup, there is nothing. Database backup is a powerful means to prevent disasters

  • There are many reasons for data loss:

① Program error: it refers to the inevitable errors in the program of database operation, resulting in data loss
② Human error: refers to the data damage caused by the misoperation of the user, and the data loss caused by the hacker's attack on the system
③ Computer failure: refers to the damage of the server operating system or software running the database, which may cause data damage
④ Disk failure: refers to hard disk and other hardware devices storing data, which may be damaged after long-term operation, resulting in data loss
⑤ Disaster (such as fire, earthquake) and theft: refers to the occurrence of natural disasters, which may cause data loss

2. Classification of database backup

There are many backup methods. From the physical and logical point of view, backup can be divided into the following categories

2.1 physical backup

Physical backup refers to the backup of physical files (such as data files, log files, etc.) of the database operating system. Physical backup can be divided into offline backup (cold backup) and online backup (hot backup)

Cold backup (offline backup): the backup operation when closing the database can better ensure the integrity of the database
Hot backup (online backup): operate in the running state of the database. This backup method depends on the log file of the database

2.2 logical backup

Logical backup refers to the backup of database logical components (such as tables and other database objects).

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

  • Full backup
    Each complete backup of data can back up the entire database, including all database objects such as user tables, system tables, indexes, views and stored procedures
    But it takes more time and space, so a full backup takes longer

  • Differential backup
    Back up files that have been modified since the last full backup, only the contents of the database
    It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup
    Its advantage is fast storage and recovery

  • Incremental backup
    Only those files that have been modified since the last full or incremental backup will be backed up

2.3 summary

Full backup is the backup of the whole database, database structure and file structure. It saves the database at the time of backup completion and is the basis of incremental backup
The advantage of full backup is that the operation of backup and recovery is simple and convenient. The disadvantage is that there are a lot of data duplication, occupying a lot of backup space and long backup time
In the production environment, these two backup methods will be used. It is necessary to formulate a reasonable and efficient scheme to achieve the purpose of backing up data and avoid serious consequences caused by data loss

3. Common backup methods

① Physical cold standby

  • The database is closed during backup, and the direct packaging database file (tar) backup is fast and easy to restore
  • The reason why the physical cold standby shuts down the database: because the database is characterized by continuous data interaction and frequent data updates, if the database is not shut down, tar cannot ensure the update and consistency of data

② Special backup tools mysqldump or mysqlhotcopy

  • mysqldump is a common logical backup tool
  • mysqlhotcopy is only used to back up MyISAM and ARCHIVE tables (MyISAM and ARCHIVE refer to engine tables)

③ Enable the binary log for incremental backup. For incremental backup, you need to refresh the binary log

④ Third party tool backup

  • Free MySQL hot backup software Percona, XtraBackup, mysqlbackup

3, 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: data has a lot of duplication, occupies a lot of backup space, and takes a long time for backup and recovery

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
systemctl stop mysqld
yum -y install xz
#The database file is large and can be compressed in xz format with high compression rate. It is preferred to install xz compression format tool

tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
#Package database folders

ls /opt
#Check whether the package command runs successfully and the backup file has been generated

du -sh /opt/mysql-2021-10-24.tar.xz 
#Comparing the file sizes before and after packaging, you can see that the compressed backup file takes up very little space

tar -Jxvf /opt/mysql-2021-10-24.tar.xz -C /usr/local/mysql/data/
systemctl restart mysql
#If the database file is damaged and the data is lost, the file can be decompressed, which is equivalent to data recovery

The specific operations are as follows:

#Case environment preparation
mysql> select * from IT_salary;
| Job category        | full name      | Age   | staff ID   | education   | Years   | salary     |
| JAVA engineer      | Xiao Wang      |     25 |        1 | undergraduate   |      2 |  3500.00 |
| Operation and maintenance engineer      | Cui Peiwen    |     28 |        2 | undergraduate   |      2 | 20000.00 |
| Network Engineer      | Sun Kongwu    |     27 |       11 | undergraduate   |      3 |  4800.00 |
3 rows in set (0.00 sec)

#Specific operation process
[root@mysql ~]#systemctl stop mysqld.service 
[root@mysql ~]#cd /usr/local/mysql/

#Data backup and packaging; date +%F: current time
[root@mysql /usr/local/mysql]#tar Jcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/

#Simulated fault
[root@mysql /usr/local/mysql]#rm -rf data
[root@mysql /usr/local/mysql]#systemctl start mysqld.service 
[root@mysql /usr/local/mysql]#mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

#Recover data
[root@mysql /usr/local/mysql]#cd /opt/
[root@mysql /opt]#ls
mysql_all_2021-10-24.tar.gz  rh  usr
[root@mysql /opt]#cd usr/local/mysql/
[root@mysql /opt/usr/local/mysql]#ls
#Move the data directory to / usr/local/mysql /
[root@mysql /opt/usr/local/mysql]#mv data /usr/local/mysql/

2. mysqldump backup and recovery (warm backup)

  • MySQL has its own backup tool, which can facilitate the backup of MySQL
  • You can export the specified libraries and tables as SQL scripts
  • Use the command mysq| to import backed up data
  • Cold backup is packaged with tar. You need to shut down the database first because the database is still writing continuously. If you don't shut down, you will lose data
2.1 mysqldump backup
#-e: Operate on the contents of the table on the command line
[root@mysql ~]#mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
| Database           |
| information_schema |
| IT                 |
| mysql              |
| performance_schema |
| sys                |
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


#Back up the library named IT to the / opt directory
[root@mysql ~]#mysqldump -uroot -p123456 --databases IT >/opt/IT.sql
#Backup IT and sys Libraries
mysqldump -uroot -p123456 --databases IT sys > /opt/IT-sys.sql    

Fully back up all libraries in the MySQL server
mysqldump -u root -p [password] - All Databases > / backup path / backup file name.sql


mysqldump -uroot -p123456 --all-databases > /opt/all.sql

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
	Use“-d"Option to save only the table structure of the database
	Not used“-d"Option, indicating that table data is also backed up
	As a table structure template

mysqldump -uroot -p123456 IT class > /opt/IT_class.sql

View backup files

grep -v "^--" /opt/IT.sql | grep -v "^/" | grep -v "^$"
2.2 database recovery

Method ① use the source command to log in to the MySQL database and execute the path of the source backup sql script

mysql> show databases;
mysql> drop database IT;
mysql> show databases;
mysql> source /opt/IT.sql

Method ② use the MySQL command without logging into the MySQL database
Use - e to delete the data table and execute the path of mysql backup sql script

[root@mysql ~]#mysqldump -uroot -p123456 class gl > /opt/gl.sql
[root@mysql ~]#mysql -uroot -p123456 -e 'drop table;'
[root@mysql ~]#mysql -uroot -p123456 -e 'show table from class;'
[root@mysql ~]#mysql -uroot -p123456 class < /opt/gl.sql 
[root@mysql ~]#mysql -uroot -p123456 -e 'show tables from class;'
2.3 mysqldump summary

mysqldump strictly belongs to warm backup, which requires write locking of the table

In the case of full backup and recovery, assume that there is an existing class library with a gl table. Note:

① When backing up, add -- databases, which means for the class library

#Add -- databases during backup, which means for class library
mysqldump -uroot -p123456 --databases class > /opt/class_01.sql 

#Recover data
mysql -uroot -p123456
drop database class;
mysql -uroot -p123456 < /opt/class_01.sql

② -- databases is not added during backup, which means that all tables under the class library are targeted

mysqldump -uroot -p123456 class > /opt/class_02.sql
mysql -uroot -p123456
drop database class;
create database class;
mysql -uroot -p123456 class < /opt/class_02.sql 

There are two methods of backup. The former starts with "create databases", while the latter is all for table operations


From the perspective of database backup strategy, backup can include full backup, differential backup and incremental backup

From the physical and logical point of view, backup can be divided into physical backup (cold and hot backup) and logical backup

The backup can be performed for the whole library, some libraries or some tables

How to select a logical backup strategy

Firstly, according to the project frequency, secondly, the reasonable value interval value of relationship backup. In terms of logical backup strategy:
Full backup: full backup once a week (the full backup time needs to be carried out when the service is suspended / idle)
Incremental: 3-day / 2-day incremental backup
Difference: select a specific scenario for backup

Keywords: Database MySQL

Added by culprit on Sun, 31 Oct 2021 08:39:45 +0200