MySQL full backup and recovery

Keywords: Database MySQL SQL


With the rapid development of office automation and e-commerce, enterprises rely more and more on information system. Database, as the core of information system, plays an important role.
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 log

Because the log file is an important reference for mastering the running state of the database, the maintenance of the log file is also of great significance.

Default location for MySQL logs: / usr/local/mysql/data

1. Type and function

redo Redo log :Achieve transaction consistency(Do it again every time you restart)
effect:Ensure the persistence of logs to prevent dirty pages from not being written to disk in case of failure.
Restart the database redolog Perform redo to achieve transaction consistency

undo Rollback Log 
effect:Ensure the atomicity of data, record one version before the transaction, and use it for rollback, innodb Transaction repeatable read and read committed isolation levels are passed mvcc+undo realization

error log Error log 
effect: MySQL Error messages that occur during startup, shutdown, and operation

slow query log Slow query log
 Function: records that take too long to execute sql,Time threshold(10s) It can be configured to record only successful execution
 Another function:It's like reminding you whether to optimize this item

bin log Binary log
 effect:Used for master-slave replication to realize master-slave synchronization; Master-slave replication is the basis of two other core architectures: read-write separation and MHA(mysql ha);ha:High availability
 The content of the record is:Executed in database sql sentence

relay log relay logs 
effect:Used for database master-slave replication/Synchronization, send the data from the main database bin/log Save locally and play back from the library

general log General log
 effect:Record the operation details of the database. It is closed by default. After it is opened, the database performance will be reduced

2. Log profile

vim /etc/my.cnf

#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 location and file name of the error log
	general_log=ON	##The general query log is used to record all MySQL connections and statements. It is closed by default
#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
#relay logs 
In general, it is Mysql Master slave synchronization(copy),The slave node of the read-write separation cluster is 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
	long_query_time=5								#Set that statements executed for more than 5 seconds are recorded. The default is 10 seconds
systemctl restart mysqld
mysql -u root -P

3. Query log

accord withmeaning
xx%Fields starting with xx
%xxFields ending in xx
%xx%As long as the xx field appears, it will be displayed
xxAccurate query
variablesRepresentation variable
likeRepresents a fuzzy query
  • Check whether the general query log is enabled (closed by default)
show variables like 'general%';  
#general_log corresponds to the general query log   
  • Check whether binary log is enabled (enabled by default)
#Binary log is used to record all statements that have updated data or have potentially updated data. It records the changes of data. The main purpose is to recover the database to the greatest extent when recovering data

show variables like 'log_bin%'; 
  • Check whether the slow query function is on (off by default)
#Slow query log records that all execution times exceed long_ query_ The SQL statement of time seconds is used to find which query statements take a long time to execute in order to optimize them
show variables like '%slow%';         

2, MySQL full 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

(1) 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

(2) 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

(3) Incremental backup

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

  • Summary of differences between several backups
Backup modeFull backupDifferential backupIncremental backup
Status at full backupTable 1 and table 2Table 1 and table 2Table 1 and table 2
Content added for the first timeCreate table 3Create table 3Create table 3
Backup contentTable 1, table 2 and table 3Table 3Table 3
Add content for the second timeCreate table 4Create table 4Create table 4
Backup contentTable 1, table 2, table 3 and table 4Table 3 and table 4Table 4

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 physical backup and recovery

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

  • Idea:
    ① Close MySQL database
    ② Use the tar command to package the database folder directly
    ③ Simply replace the existing MySQL directory

  • 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 (including all tables therein)
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

Posted by Hayce on Fri, 29 Oct 2021 06:46:24 -0700