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

This article mainly gives you a brief account of how MySQL should be backed up and restored. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on how MySQL should back up and restore can bring you some practical help.

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.

Backup instance:

1. Physical cold backup and recovery:

[root@mysql /] # systemctl stop mysqld # first stop the service [root@mysql /] # mkdir / backup # create a backup directory [root@mysql /] # tar zcf / backup/mysql_all-$ (date +% F). Tar.gz / usr/local/mysql/data/ # package the entire database folder for backup (date +% F) current date [root@mysql /] # ls-l / backup/ # View backup files total 732 # Total usage-rw-r--r-- 1 root root 746839 Aug 2 14:48 mysql_all-2019-08-02.tar.gz # backup files

To simulate missing database files:

[root@mysql /] # mkdir / diushi [root@mysql /] # mv / usr/local/mysql/data/ / diushi/ # move the database storage directory to another directory

Restore the database:

[root@mysql /] # mkdir / restore/ [root@mysql /] # tar zxf / backup/mysql_all-2019-08-02.tar.gz-C / restore/ # release the backup files to an empty directory first Then restore what you need to the original location [root@mysql /] # mv / restore/usr/local/mysql/data/ / usr/local/mysql/ # restore the database directory to the original location [root@mysql /] # systemctl restart mysqld # restart service verification

2. Mysqldump backup and recovery:

Backup database

Back up the tables in the specified library:

Mysqldump [options] Library name Table name Table name 2... > / backup path / backup file name [root@mysql /] # mysqldump-u root-p test user > / backup/user-table.sql # back up the user table in the test library to the backup directory Enter password: # enter the password

Back up one or more complete libraries:

Mysqldump [option]-- databases Library name 1 Library name 2... > / backup path / backup file name [root@mysql /] # mysqldump-u root-p-databases test mysql > / backup/databases.sql # backup test and mysql libraries to backup Enter password: # enter password

Back up all libraries in MySQL:

Mysqldump [options]-- all-databases > / backup path / backup file name [root@mysql /] # mysqldump-u root-p-- opt-- all-databases > all-data.sql #-- opt: optimize execution speed Enter password: # enter password [root@mysql /] # ls backup/ # View backup file all-data.sql Databases.sql user-table.sql

Restore the database:

Restore tables in the library

Mysql [option] library name

< /备份路径/备份文件名[root@mysql /]# mysql -u root -p test < /backup/user-table.sql Enter password: [root@mysql /]# mysql -u root -p -e ' show tables from test;' // 验证导入结果Enter password: +----------------+| Tables_in_test |+----------------+| user |+----------------+ 恢复单个或多个库： [root@mysql /]# mysql -u root -p -e ' drop database test;' // 删除 test 数据库，模拟故障Enter password: [root@mysql /]# mysql -u root -p -e ' show databases;' // 验证 test 数据库是否存在Enter password: +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema |+--------------------+[root@mysql /]# mysql -u root -p < /backup/databases.sql // 执行导入恢复操作Enter password: [root@mysql /]# mysql -u root -p -e ' show databases;' // 确认恢复后结果Enter password: +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+ MySQL 增量备份与恢复： 与完全备份不同，增量备份没有重复数据，备份量不大，时间段，但其恢复比较麻烦，需要上次完全备份及完全备份之后的所有增量备份之后才能恢复，而且要对所有增量备份逐个反推恢复。MySQL没有提供直接的增量备份办法，所以一般是通过MySQL提供的二进制日志来间接实现增量备份。 要进行MySQL的增量备份，首先需要开启二进制日志功能： [root@mysql /]# mkdir /usr/local/mysql/logs # 创建一个存放二进制日志文件的目录[root@mysql /]# cd /usr/local/mysql/ [root@mysql mysql]# chown mysql:mysql logs/ # 设置目录归属，使其能够写入[root@mysql /]# vim /etc/my.cnf # 编写配置文件[mysqld]log-bin=/usr/local/mysql/logs/mysql-bin[root@mysql /]# systemctl restart mysqld # 重启服务，使配置生效[root@mysql /]# ll /usr/local/mysql/logs/mysql-bin.* # 目录下自动生成日志文件-rw-rw---- 1 mysql mysql 120 Aug 2 17:04 /usr/local/mysql/logs/mysql-bin.000001-rw-rw---- 1 mysql mysql 39 Aug 2 17:04 /usr/local/mysql/logs/mysql-bin.index 现在所有对数据库的修改，都将记录mysql-bin.000001文件中，当执行"mysqladmin -u root -p flush-logs"刷新二进制日志后，将会继续生成一个名为mysql-bin.000002的文件，之后所有的更改又将存在mysql-bin.000002文件中，以此类推，每刷新一次，就会生成一个新文件！ 首先我们在表中先录入一些信息，然后进行一次完整备份： mysql>

Select * from user_info +-+ | id | xingming | nianling | +-+ | 001 | zhangsan | 20 | | 002 | lisi | 25 | | 003 | wangwu | 20 | +- -+ [root@mysql /] # mkdir / mysql_bak # create a backup location [root@mysql /] # mysqldump-u root-p test user_info > / mysql_bak/test_userinfo$ (date +% F). Sql # perform a full backup Enter password: [root@mysql /] # ls / mysql_bak/ # verify the backup result test _ userinfo2019-08-02.sql [root@mysql /] # mysqladmin-u root-p flush-logs # Refresh the log file Enter password: [root@mysql /] # ll / usr/local/mysql/logs/mysql-bin.* # generate a new log file 000002LRWLY RWLLV-1 mysql mysql 1192 Aug 2 17:18 / usr/local/mysql/logs/mysql-bin.000001-rw-rw---- 1 Mysql mysql 120 Aug 2 17:18 / usr/local/mysql/logs/mysql-bin.000002-rw-rw---- 1 mysql mysql 78 Aug 2 17:18 / usr/local/mysql/logs/mysql-bin.index

Continue to enter new data and make incremental backups:

Mysql > select * from user_info +-+ | id | xingming | nianling | +-+ | 001 | zhangsan | 20 | | 002 | lisi | 25 | | 003 | wangwu | 20 | | 004 | zhaoliu | 20 | 005 | sunqi | 30 | +- -+ [root@mysql /] # mysqladmin-u root-p flush-logs # refresh log files In this way, there are only two data operations in 000002 Enter password: [root@mysql /] # cp / usr/local/mysql/logs/mysql-bin.000002 / mysql_bak/ # copy the log files to the backup directory

The simulated user_info table was deleted by mistake. Restore:

[root@mysql /] # mysql-u root-p test < / mysql_bak/test_userinfo2019-08-02.sql # restore the full backup first Enter password: [root@mysql /] # mysql-u root-p-e 'select * from test.user_info '# check to confirm Enter password: +-+ | id | xingming | nianling | +-+ | 001 | zhangsan | 20 | | 002 | lisi | 25 | | 003 | wangwu | 20 | +-+- +-+ [root@mysql /] # mysqlbinlog-- no-defaults / mysql_bak/mysql-bin.000002 | mysql- u root-p # restore incremental backup -- no-defaults option must have [root@mysql /] # mysql-u root-p-e 'select * from test.user_info '# confirm Incremental backup recovered successfully Enter password: +-+ | id | xingming | nianling | +-+ | 001 | zhangsan | 20 | | 002 | lisi | 25 | | 003 | wangwu | 20 | | 004 | zhaoliu | 20 | 005 | sunqi | | 30 | +-+

The next step is location-based recovery and point-in-time recovery, which have a lot in common. To achieve this, you must first check the binary log file to confirm the location or point in time of the recovery.

[root@mysql /] # mysqlbinlog-- no-defaults / mysql_bak/mysql-bin.000002. / / omit part of the content # at 199# this line is to operate the ID number # 190802 17:21:40 server id 1 end_log_pos 346 CRC32 0xc61c38c9 Query thread_id=4 exec_time=0 error_code=0use `test` / *! * /; SET timestamp 1564737700 pound hammer hammer insert into user_info (id,xingming,nianling) values # at 346mm 190802 17:21:40 server id 1 end_log_pos 377 CRC32 0xea2c7707 Xid = 50COMMIT Universe; # Action confirmation Mark remember that an action ends here # at 377 "190802 17:22:09 server id 1 end_log_pos 456TIMESTAMPay 1564737729 # at 456mm 190802 17:22:09 server id 1 end_log_pos 601 CRC32 0x3727aeb7 Query thread_id=4 exec_time=0 error_code=0SET timestamp 1564737729 values insert into user_info (id,xingming,nianling) values ('005') server id 601 "190802 17:22:09 server id 1 end_log_pos 632 CRC32 0x17c4779a Xid = 51 COMMITRUR # at 632 October 190802 17:24:05 server id 1 end_log_pos 679 CRC32 0x9c698f03 Rotate to mysql-bin.000003 pos: 4DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET completion SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/ [root@mysql /] # mysqlbinlog-- no-defaults-- stop-position='456' / mysql_bak/mysql-bin.000002 | mysql- u root-p # restore the operation Enter password before ID'456':

-- start-position='456': indicates that the log file before 456 will not be recovered.

The above options can be changed to the following types:

-- stop-position='456': indicates that the recovery will stop after operation 456, and the data after the log file 456 will not be recovered.

Point-in-time recovery:

-- start-datetime='2019-08-2 17-14-22-22-9: recover the data after that time

-- stop-datetime='2019-08-2 17 _ (22) _

MySQL should be how to backup and restore to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.