introduction
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
Idea:
- 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 data #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 case #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
case
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 class.gl;' [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; exit mysql -uroot -p123456 < /opt/class_01.sql
② -- databases is not added during backup, which means that all tables under the class library are targeted
#backups mysqldump -uroot -p123456 class > /opt/class_02.sql #recovery mysql -uroot -p123456 drop database class; create database class; exit 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
summary
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