introduction
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 [mysqld] #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 general_log_file=/usr/local/mysql/data/mysql_general.log #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 log-bin=mysql-bin or log_bin=mysql-bin #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 slow_query_log=ON slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log 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 with | meaning |
|---|---|
| xx% | Fields starting with xx |
| %xx | Fields ending in xx |
| %xx% | As long as the xx field appears, it will be displayed |
| xx | Accurate query |
| variables | Representation variable |
| like | Represents 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 mode | Full backup | Differential backup | Incremental backup |
|---|---|---|---|
| Status at full backup | Table 1 and table 2 | Table 1 and table 2 | Table 1 and table 2 |
| Content added for the first time | Create table 3 | Create table 3 | Create table 3 |
| Backup content | Table 1, table 2 and table 3 | Table 3 | Table 3 |
| Add content for the second time | Create table 4 | Create table 4 | Create table 4 |
| Backup content | Table 1, table 2, table 3 and table 4 | Table 3 and table 4 | Table 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 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 (including all tables therein)
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