Shulou(Shulou.com)06/01 Report--

The backup strategy in the company is not the same, but the appropriate backup strategy is made according to the actual production environment and business needs of each enterprise. Whether you choose full backup or incremental backup, you need to consider their advantages and disadvantages and whether they are suitable for the current production environment. At the same time, in order to ensure the integrity of the recovery, it is recommended to enable the binary log function. Binary log files bring great flexibility to the recovery work, which can be recovered based on point-in-time or location, taking into account the performance of the database. Binary log files can be saved to other secure hard drives.

During the hot backup, the backup operation and the application service run at the same time, which consumes the system resources and leads to the decline of the database service performance. this requires us to choose an appropriate time (usually when the application burden is very small) to carry out the backup operation.

It is important to note that everything will be fine if it is not a backup, and it is best to make sure that the backup is available, so the recovery test after the backup is very necessary. At the same time, the backup time should be adjusted flexibly, such as:

If the data is updated frequently, it should be backed up frequently. The importance of data, back up when there are appropriate updates. Make backups during periods of low pressure on the database, such as a full backup once a week and an incremental backup every day. For small and medium-sized companies, full backup is usually done once a day. Large companies can make full backups once a week and incremental backups once a day. Try to implement the master-slave replication architecture for the enterprise to increase the availability of the data.

The type of database backup can be viewed from two perspectives:

1. From the physical and logical point of view:

A physical backup is a backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be quickly recovered when problems occur.

Physical backups can be divided into the following types:

①, cold backup: backup operation when the database is closed

②, hot backup: backup operation while the database is running. This backup method relies on the log files of the database.

③, warm backup: backup in the state of database locked table (not writable but readable)

A logical backup is a backup of database logical components (such as tables and other database objects), expressed as logical database structure (create database, create table statements) and content (insert statements or delimited text files). This type of backup is used to edit data values or small amounts of data with a table structure, or to recreate data on different machine systems.

2. From the point of view of database backup strategy:

From the point of view of database backup strategy, database backup can be divided into full backup, differential backup and incremental backup. Among them, full backup is the basis of differential and incremental backup.

Full backup: a complete backup of the data each time, that is, a backup of the entire database. The operation of backup and recovery is very simple, but there is a lot of data duplication, which will take up a lot of disk space, and the backup time is also very long. Differential backup: back up all files that have been modified since the last full backup, the backup time is from the last full backup, the backup data will become larger and larger, when you restore the data, just restore the last full backup and the most recent differential backup. Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up, taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the amount of data backed up is small, the space occupied is small, and the backup speed is fast, but when restoring, all the increments from the last full backup to the last incremental backup need to be restored in turn. Once the intermediate data is damaged, it will lead to the loss of data.

Here are a few backup examples:

1. Physical cold backup and recovery:

[root@localhost ~] # tar zcf / backup/mysql_$ (date +% F). Tar.gz / usr/local/mysql/data # directly tar packaged database file [root@localhost ~] # cd / backup/ [root@localhost backup] # lsmysql_2019-05-16.tar.gz # backup file [root@localhost backup] #

2. Mysqldump backup and recovery:

1) back up a table:

[root@localhost backup] # mysqldump-u root-p mysql user > mysql-user.sql # backup the user table in the mysql library Enter password: # enter the password [root@localhost backup] # ls # to view the backup file mysql-user.sql

2) restore the user table in the mysql database (note the path problem):

[root@localhost backup] # mysql-u root-p mysql

< mysql-user.sql Enter password: 3）备份mysql库： [root@localhost backup]# mysqldump -u root -p --databases mysql >

Mysql.sql # backup mysql library Enter password: # enter password [root@localhost backup] # ls # View backup file mysql.sql

4) restore the mysql library:

[root@localhost backup] # mysql-u root-p

< mysql.sql Enter password: 5）备份所有的库（当导出的数据量较大时，可以添加"--opt"选项以优化执行速度）： [root@localhost backup]# mysqldump -u root -p --opt --all-databases >

All-data.sql # backup all libraries Enter password: # enter password [root@localhost backup] # ls # View backup file all-data.sql

3. MySQL incremental backup and recovery:

Unlike a full backup, an incremental backup has no duplicate data, a small amount of backup, and a time period, but it is troublesome to restore. It needs to be restored after the last full backup and all incremental backups after the full backup. And restore all incremental backups one by one. MySQL does not provide a direct incremental backup method, so incremental backup is usually achieved indirectly through the binary logs provided by MySQL.

To perform an incremental backup of MySQL, you first need to enable the binary log feature:

[root@localhost ~] # mkdir / usr/local/mysql/logs # create a directory dedicated to storing binary log files [root@localhost ~] # chown mysql:mysql / usr/local/mysql/logs # change directory attribution Give it write permission [root@localhost ~] # vim / etc/my.cnf # Edit MySQL's master configuration file [mysqld] log-bin=/usr/local/mysql/logs/mysql-bin # to write to this line Specify the location of the binary log [root@localhost ~] # systemctl restart mysqld # restart the service [root@localhost ~] # ls-l / usr/local/mysql/logs # and then you can see the automatically generated log file in the directory-rw-rw----. 1 mysql mysql 120 May 16 14:16 mysql-bin.000001-rw-rw----. 1 mysql mysql 39 May 16 14:16 mysql-bin.index

Now all changes to the database will be recorded in the mysql-bin.000001 file. When "mysqladmin-u root-p flush-logs" is executed to refresh the binary log, a file named mysql-bin.000002 will continue to be generated, and all subsequent changes will be stored in the mysql-bin.000002 file, and so on.

Next, back up the user_info table in the following test library:

Mysql > select * from user_info +-+ | gonghao | xingming | xingbie | +-+ | 1 | zhangsan | nan | | 2 | lisi | nan | | 3 | wangwu | nan | +-- -+

1) make a full backup first:

[root@localhost ~] # mysqldump-u root-p test user_info > / backup/user_info_$ (date +% F) .sql; # full backup Enter password: # enter the password [root@localhost ~] # ls / backup/ # to view the backup generated file user_info_2019-05-16.sql

2) refresh the binaries:

[root@localhost ~] # mysqladmin-u root-p flush-logs # refresh log file Enter password: [root@localhost ~] # ls-l / usr/local/mysql/logs/ view, after each refresh, there will be an extra log file, and all subsequent changes will be saved in this log file-rw-rw----. 1 mysql mysql 1007 May 16 14:36 mysql-bin.000001-rw-rw----. 1 mysql mysql 120 May 16 14:36 mysql-bin.000002-rw-rw----. 1 mysql mysql 78 May 16 14:36 mysql-bin.index

3) now there are two more pieces of data in the database:

Mysql > select * from user_info +-+ | gonghao | xingming | xingbie | +-+ | 1 | zhangsan | nan | | 2 | lisi | nan | 3 | wangwu | nan | | 4 | zhaoliu | nan | | 5 | | sunqi | nan | +-+ |

4) if the table is deleted by mistake, it can be restored as follows:

[root@localhost ~] # mysqladmin-u root-p flush-logs # refresh log Enter password: [root@localhost ~] # mysql-u root-p test < / backup/user_info_2019-05-16.sql # restore full backup Enter password: [root@localhost ~] # mysql-u root-p-e 'select * from test.user_info '# the content of the full backup was restored successfully Enter password: +-+ | gonghao | xingming | xingbie | +-+ | 1 | zhangsan | nan | | 2 | lisi | nan | | 3 | | wangwu | nan | +-+ when restoring incremental backups | -- no-defaults option is required, otherwise an error will be reported: [root@localhost ~] # mysqlbinlog-- no-defaults / usr/local/mysql/logs/mysql-bin.000002 | mysql- u root-p # restore incremental backup Enter password: [root@localhost ~] # mysql- u root-p-e 'select * from test.user_info '# check whether the recovery is successful Enter password: +-+ | gonghao | xingming | xingbie | +-+ | 1 | zhangsan | nan | | 2 | lisi | nan | | 3 | wangwu | nan | | 4 | zhaoliu | nan | | 5 | sunqi | nan | +-+ |

If the recovery is successful, then there is also a location-based and time-based recovery. The command format is as follows:

[root@localhost ~] # mysqlbinlog-- no-defaults / usr/local/mysql/logs/mysql-bin.000002 # use the tool to view the log file. # at 199199 is the location of an action, and then is the time of the action # 190516 14:39:47 server id 1 end_log_pos 322 CRC32 0x5339b5bd Query thread_id=5 exec_time=0 error_code=0use `test` / *! * / SET timestamp 1557988787) / * /; # at 322mm 190516 14:39:47 server id 1 end_log_pos 353,353 CRC32 0x873df67e Xid = 54COMMIT Universe into user_info values; # this word means to submit # at 353 "190516 14:39:57 server id 1 end_log_pos 432 CRC32 0xcd2543f7 Query thread_id=5 exec_time=0 error_code=0SET TimESTAMPay 1557988797lead" # at 432 CRC32 0xebb496fb Xid 190516 14:39:57 server id 1 end_log_pos 553 CRC32 0xc1300526 Query thread_id=5 exec_time=0 error_code=0SET timestamp 1557988797According to the exact insert into user_info values / *! /; # at 553mm 190516 14:39:57 server id 1 end_log_pos 584 CRC32 0xebb496fb Xid = 55COMMITmax . [root@localhost ~] # mysqlbinlog-- no-defaults-- start-position='353' / usr/local/mysql/logs/mysql-bin.000002 | mysql- u root-p Enter password:

-- start-position='353': indicates that the recovery starts at location 353, and the data of the log file before 353 will not be restored.

The above options can be changed to the following types:

-- stop-position='353': indicates that the recovery will stop at location 353, and the data after the log file 353 will not be restored.

Point-in-time recovery:

-- start-datetime='2019-05-16 14-14-39-19-47: recover the data after that time

-- stop-datetime='2019-05-16 14-14-39-19-47: indicates that only data prior to this time is recovered

Keep in mind that all types of incremental recoveries must be preceded by the most recent full recovery.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.