1 about backup
1.1 why backup
- disaster recovery In the process of running, the database will eventually encounter a variety of problems: hardware failure, data corruption caused by bugs, database unavailability caused by server downtime or other reasons. In addition, there are human operations:
DELETEForget to add conditions
ALTER TABLEExecution error table
DROP TABLEEven if you haven’t met these problems, according to Murphy’s law, there will always be times.
- RollBACK Because a certain bug or system is hacked, causing a lot of losses, this time you need to roll back to a certain state. Common blockchain transactions are hacked and rolled back, and game vulnerabilities are exploited and rolled back as a whole.
- auditSometimes there is a need to know the data at a certain time point, which may be the year-end audit or the lawsuit.
- testA basic test requirement is to pull online data to the test environment regularly. If there is a backup, it is very convenient to pull data.
1.2 what are the backup methods
1.2.1 logical backup
Logical backup is the most common way, which is often used when the amount of data is relatively small.
Advantages of logical backup:
- Backup recovery is relatively simple, such as
mysqldumpIt’s the backup work of MySQL, and no additional installation is required. It can be used directly during recovery
mysqlCommand to recover.
- Backup and recovery can be done remotely, that is, backup commands can be executed on other machines.
- The backup data is very intuitive and can be used after backup
grepAnd other tools for data extraction or modification.
- It has nothing to do with the storage engine, because the backup file is directly extracted from mysql, so intuitively, the backup data does not distinguish between engines, and can be easily extracted from mysql
MyISAMEngine changed to
- Avoid the impact of file damage, if you copy the original file directly, you may be affected by a file damage and get a damaged backup. Using logical backup, as long as MySQL can execute the select statement, you can get a reliable logical backup, which is very useful when the file is damaged.
Disadvantages of logical backup
- Because MySQL service must be used for data operation, more CPU will be used for backup, and the backup time may be longer.
- Logical backup is larger than database file in some scenarios, and the data stored in text is not always more efficient than storage engine. Of course, using compression will get a smaller backup, but it will take up CPU resources（ If there are more indexes, the logical backup will be smaller than the physical backup.)
- The recovery time is longer, and the data recovery using logical backup needs to occupy more resources for lock allocation, index construction, conflict checking and log refresh.
Common methods of logical backup:
MySQLThe built-in backup tool is versatile and very common. Some parameters are usually added to the usage, which will be described later.
select into outfileTo create a logical backup by dividing the data with symbols
CSVAnd other forms will be more practical.
mydumperMulti thread backup is allowed. The backup file will separate the table structure and data, which will be very effective when recovering some tables or data.
1.2.2 physical backup
Physical backup is very common when there is a large amount of data.
Advantages of physical backup:
- The backup speed is fast, because the physical backup is based on the replication, which means that the backup can be as fast as the replication.
- The recovery speed is fast. You only need to copy the file to the database directory to complete the recovery without checking the lock and building the index.
- The recovery is simple. For the table of mysiam engine, you don’t need to stop the database, you just need to copy it into the data directory. For InnoDB, if there is a table space for each table, it can also operate without stopping the library. It can be imported by unloading and loading the table space (not very safe).
Disadvantages of physical backup:
- There is no official physical hot backup tool. Without the support of official tools, it means that there is a high probability of problems, so we should be careful when using them
- InnoDB’s original files are usually larger than logical backups. InnoDB table space often contains a lot of unused space. InnoDB table will not release space after deleting data, so even if the amount of data is small, the file may be large. In addition, in addition to data, the file also contains index, log and other information.
- Physical backup can not always be cross platform and cross version. MySQL files are closely related to the operating system and MySQL version. If the environment is inconsistent with the original, problems may occur.
Common methods of physical backup:
xtrabackupIs the most commonly used physical backup tool
perconaIt is open source and can realize non blocking backup of InnoDB storage engine and xtradb storage engine (for MyISAM, it still needs to be locked) to get a consistent backup.
Direct copy file / file system snapshotIn this way, for
MySIAMThe engine is very efficient and only needs to execute
FLUSH TABLE WITH READ LOCKYou can copy a backup file. But for
InnoDBThe engine is more difficult because
InnoDBThe engine uses a lot of asynchronous technology, even if it is executed
FLUSH TABLE WITH READ LOCKIt will continue to merge logs and cache data. So we need to back it up in this way
InnoDB, need to ensure
checkpointIt’s up to date.
1.2 why backup binlog
If a DBA tells you that the database can be restored to any state within two months, it means that the binlog log of the database has been kept for at least two months. Benefits of backup binlog:
- Recovery based on any point in time can be realized
- Can be used for misoperation data flashback
- It can be used for auditing
When you want to do data recovery, you will be very glad to do it
binlogBackup. Of course, using
binlogThe premise of data recovery is
rowDon’t worry about space. The most important resource is hard disk space. about
binlogOur recommended configuration is
#Record the change of each line of data binlog_format = row #When redoing data, the standby database records a binlog log_slave_updates = 1
1.3 replication and backup
Master slave replication is equal to one more data copy, but replication is not equal to backup, nor can it replace backup. Suppose that the
drop tableOperation, will immediately synchronize to the standby database, and perform the same operation, there is no way to use the standby database for data recovery when there is an accident.
Delayed replication can not replace backup, but it can speed up the recovery, which is a very useful strategy.
In actual use, in order not to affect the use of the main library, we often backup in the standby library, and record the synchronization point, so as to facilitate the construction of a new standby library. In standby database backup, it should be noted that master-slave replication can not guarantee the data consistency between master and standby. In fact, replication based
MySQLCluster does not guarantee the internal consistency of the cluster, and there is no very good way at present. The common way is to use cluster
pt-table-checksumCheck the consistency.
2. Full backup
Full backup introduces the most common logical backup tools
mysqldumpAnd physical backup tools
xtrabackup。 If right
mysqldumpNot very satisfied, it can be used
mysqldumpIs the most used tool, but to use it well, you need to add some additional parameters.
mysqldumpThere are many available parameters, which are not expanded here. It is recommended to visit the official website directlymysqldump。 use
mysqldumpSome parameters require
2.1.1 common examples
184.108.40.206 full database backup of InnoDB
mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql
--optIf this parameter indicates that the quick, add drop table, add locks, extended insert and lock tables parameters of the mysqldump command are activated at the same time, it can give a quick dump operation and generate a dump file that can be loaded into the MySQL server quickly. When backing up a large table, this parameter can prevent it from taking up too much memory
--single-transactionSet the isolation level of transaction to repeatable read, and then start the transaction during backup, so that all the same queries in a transaction can read the same data. Note that this parameter is only valid for engines that support transactions, if any
MyISAMThe consistency of data cannot be guaranteed
-AExport all databases
–-default-character-set=charsetSpecifies which character set to use when exporting data
--master-data=2Represents the number of records of the primary library during the backup
posClick, and comment out this line in the dump file, which will be used when using the backup file as a new backup library
220.127.116.11 full database backup of MyISAM
mysqldump --opt --lock-all-tables --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql
--lock-all-tablesLock table backup. because
MyISAMIf you want to get a consistent backup, you can only lock the whole table.
18.104.22.168 compression on backup tape
mysqldump -h<host> -u<user> -p<password> -A | gzip >> backup.sql.gz
22.214.171.124 backup multiple libraries
mysqldump -h<host> -u<user> -p<password> --databases <dbname1> <dbname2> > backup.sql
The recovery method is relatively simple, just execute the SQL statement directly
mysql -h<host> -u<user> -p<password> < backup.sql
2.1.3 execution process of mysqldump
mysqldumpThe implementation process of the
--single-transaction --opt -AParameter as an example
FLUSH /*!40101 LOCAL */ TABLES FLUSH TABLES WITH READ LOCK SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ START TRANSACTION SHOW VARIABLES LIKE 'gtid\_mode' SHOW MASTER STATUS UNLOCK TABLES ... SHOW CREATE DATABASE IF NOT EXISTS `employees` SAVEPOINT sp ... SELECT /*!40001 SQL_NO_CACHE */ * FROM `departments` ....
2.2.1 installation method
Refer to the official website for more installation methodsxtrabackup
Here we use
rpmHow to install
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum update percona-release #Qpress is used for compression and decompression yum install percona-xtrabackup-24 qpress
126.96.36.199 add backup account and authorize
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup'; GRANT PROCESS,RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;
188.8.131.52 full standby
innobackupex --defaults-file=/etc/my.cnf --user=<user> -- password=<pwd> < Which directory do you want to back up to -- no-timestamp --compress --compress-threads=4 --parallel=4
--no-timestampCreate a folder without using the current time. By default, the folder is created in the backup directory at the current time
#Step 1: unzip Innobackupex -- decompress < directory of backup file > -- parallel = 4 #Step 2: application log Innobackupex -- apply log < directory of backup file > -- parallel = 4 #Step 3: copy the backup file to the data directory Innobackupex -- dataDir = < MySQL data Directory > -- copy back < backup file directory > -- parallel = 4
3. Incremental backup
When the data becomes huge, a common strategy is to do regular incremental backup. For example: do a full backup on Monday, and then do an incremental backup from Tuesday to Sunday.
Incremental backup only contains changed data sets, which is generally not larger than the original data, so it can reduce the server overhead, backup time and backup space.
Of course, there are risks in using incremental backup. Every iteration of incremental backup is based on the last backup, which means that as long as one of the backups has a problem, it may cause all the backups to be unavailable.
Here are some incremental backup methods:
3.1 using xtrabackup for incremental backup
Xtrabackup allows incremental backup. The command is as follows:
innobackupex --defaults-file=/etc/my.cnf --user=<user> -- password=<pwd> -- No timestamp -- compress -- incremental -- incremental basedir = < directory of full backup > < What directory do you want to incremental backup to
#Step 1: decompress the full backup Innobackupex -- decompress #Step 2: apply log to full backup Innobackupex -- apply log -- redo only < directory of full backup file > #Step 3: decompress the incremental backup Innobackupex -- decompress #Step 4: merge incremental data Innobackupex -- apply log -- redo only -- incremental < directory of full backup file > -- incremental dir = < directory of incremental File > #Step 5: apply the log to the merged data Innobackupex -- apply log #Step 6: copy the backup file to the data directory Innobackupex -- dataDir = < MySQL data Directory > -- copy back < directory of full backup file >
3.2 using binlog for incremental backup
binlogIt’s easy to do incremental backup. It’s easy to do incremental backup
FLUSH LOGSRotate the log, then put the old one
binlogJust copy it to the backup directory.
Use when recovering
Mysqlbinlog -- start position = < POS point of backup set > binlog log | MySQL - U < user > - PThat’s it
4. Delay synchronization
Delayed synchronization is a common use mode of master-slave replication. When encountering misoperation, it is very useful whether it is used to recover data or to skip the error by skipping.
For example, in the main library
dropFind the binlog log and POS location of the command in the main library, then stop the synchronization in the delay library, and use the
start slave until master_ log_ File = '< corresponding file >', master_ log_ POS = < POS of the SQL before the misoperation command >;Wait for synchronization to this location, execute the command to skip a SQL, and then start synchronization.
Common delayed synchronous replication modes are as follows:
One master with three followers
Sometimes, in order to reduce the pressure on the primary database, the delayed database is placed behind the standby node
The delayed synchronization mode is as follows:
stop slave; CHANGE MASTER TO MASTER_ Delay = N seconds; start slave;
5. Data verification
Besides backup, it is very important to verify the availability of backup data. Imagine, when you need to carry out data recovery, suddenly found that the past backup data are invalid, how hard it must be. After a lot of friends have written a backup script and added it to a scheduled task, they will no longer pay attention to it as long as they check that the scheduled task has been executed and that there are files in the backup directory. They often find that there is something wrong with the backup data when they need to use the backup files.
At present, there is no very convenient way to check the data of backup files. Most of them are pulling the backup files out regularly to do backup recovery drills. For example, doing a backup recovery drill once a month can effectively improve the availability of backup files, and they are also down-to-earth.
In the data verification part, if it is a logical backup, it often checks the data of a table to see if it meets the expectation. If it is a physical backup, first use the
mysqlcheckWait for the command to check if there is any table damage, and then check the table data if there is no damage.
- Logical backup and physical backup can be used together. Different backup cycles use different tools. The full backup cycle should not be too long, at least once a week
- If the amount of data is large, incremental backup can be used to reduce the amount of data. It should be noted that the risk of incremental backup is greater
- To enable the binlog function, set it to
log_slave_updates = 1And it is better to back up binlog regularly
- If conditions permit, you can add a delay library, which will work wonders in emergency recovery
- Data verification should be done regularly, otherwise when backup and recovery is needed, the backup file will be invalid, and it will be too late to regret