MySQL introduces full backup and recovery

Free learning recommendation: mysql video tutorial

Article Directory

• Preface
• 1. MySQL full backup
• • 1. Database backup method explained
  • ##1.1 Database backup Importance
    • 1.2 Classification of database backup
    • 1.2.1 Physical backup
      • 1.2.2 Logical backup
      • 1.2. 3 Full backup
      • 1.2.4 Differential backup
      • 1.2.5 Incremental backup
      • 1.2.6 Comparison of backup methods
    1.3 MySQL full backup concept interpretation
  2.mysqldump backup walkthrough
  • 2.1 Use tar to package folder backup
    • 2.2 Use mysqldump tool backup
  ##2. MySQL complete recovery
• 1.Restore the entire database operation
  • 1.1 source command to restore the entire database
    • 1.2 MySQL command to restore the entire database
    2.Restore table operation
  • 2.1 Use the source command to restore the table
    • 2.2 Use the mysql command to restore the table
    3.MySQL backup ideas

---

Preface

With the rapid development of office automation and e-commerce, enterprises are becoming more and more dependent on information systems. Databases are the core of information systems. Core, playing an important role

• Database backup is an important means to restore important data in time and prevent data loss in the event of data loss
• A reasonable database backup plan , can effectively restore data when data is lost, and also need to consider the difficulty of technical implementation and effective use of resources

---

1. MySQL full backup

1. A detailed introduction to database backup methods

1.1 The importance of database backup

In a production environment, data security is Crucially, any data loss may have serious consequences

The importance of database backup is mainly reflected in:

Improving the high availability and disaster recovery of the system Restorability, when the database system crashes, the data cannot be found without database backup

1. Using database backup to restore the database is the best solution to provide the lowest cost for data recovery when the database crashes. If the user is allowed to re-add data, The price is too high
2. Without data, there is nothing. Database backup is a powerful means to prevent disasters

In the process of using the database, there are many Causes of data loss:

Program error: refers to the inevitable errors in the database operation program, resulting in data loss

1. Human error: refers to the data caused by the user's misoperation Destroyed, there may also be data loss caused by hackers attacking the system.
2. Computer failure: refers to the damage to the server operating system or software running the database, which may cause data damage
3. Disk failure : Refers to hardware devices such as hard drives that store data, which may be damaged after long-term operation, resulting in data loss
4. Disasters (such as fires, earthquakes) and theft: Refers to the occurrence of natural disasters, etc., which may cause Data loss
   

#Data loss will cause serious economic losses. For example, Ctrip’s database crashed on May 28, 2015, and the loss was approximately US$1.06 million per hour

So in the construction of enterprise information systems, database backup management is very important
• 1.2 Classification of database backup

There are many types of backup methods, ranging from physical and From a logical perspective, backup can be divided into the following categories:

1.2.1 Physical backup

refers to the physical files of the database operating system (such as data files, log files etc.) backup

Physical backup can be divided into offline backup (cold backup) and online backup (hot backup)
• Cold backup (offline backup): performed when the database is closed Backup operation can better ensure the integrity of the database
• Hot backup (online backup): Operate in the running state of the database. This backup method relies on the log file of the database
• 1.2.2 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.2.3 Full backup

• Complete backup of data every time
• Can back up the entire database, including user tables, system tables, indexes, views and All database objects such as stored procedures
• But it takes more time and space, so the cycle of doing a full backup is longer

1.2.4 Differential backup

• Back up those files that have been modified since the last full backup, that is, only back up the contents of the database part
• It is smaller than the original full backup because it only contains the files since the last full backup The database has been changed since
• Its advantage is fast storage and recovery speed

1.2.5 Incremental backup

• Only those that were fully restored last time Only modified files will be backed up after backup or incremental backup

1.2.6 Comparison of backup methods

##First time adding content Create table 3Create table 3Create table 3Backup contentTable 1, Table 2, Table 3Table 3Table 3Add content for the second timeCreate table 4Create table 4 Create Table 4Backup contentTable 1, Table 2, Table 3, Table 4Table 3, Table 4Table 4

• Full backup backs up the contents of the entire database every time
• Differential backup is backed up after the first addition of content, that is, the newly added content is backed up; after the second addition of content Backup, the content added for the first time is also backed up. It is determined based on the status of the full backup.
• Differential backup backs up all modifications after the full backup every time, regardless of whether it was before or not. I have done differential backup
• Incremental backup only backs up each newly modified content. The first time I create table 3, I back up table 3. After I add table 4 for the second time, because table 3 has been backed up before. , so only the contents of Table 4 are backed up

1.3 MySQL full backup concept interpretation

• MySQL backup methods mainly include full backup and incremental backup
• Full backup is a backup of the entire database, database structure and file structure. It saves the database at the time when the backup is completed and is the basis of incremental backup.
• The advantage of full backup is that backup The recovery operation is simple and convenient, but the disadvantage is that there is a lot of duplication of data, taking up a lot of backup space, and the backup time is long
• In a production environment, both backup methods are used, and a reasonable and efficient plan needs to be developed to achieve backup The purpose of data is to avoid serious consequences caused by data loss

2.mysqldump backup drill

• The backup of MySQL database can be done in two ways
• Because the database is actually a file, you can directly package the database folder, or use the special backup tool mysqldump to perform backup work

2.1 Use tar to package the folder backup

• MySQL database files are saved in the data folder of the safe directory by default. You can save the data folder directly. Because it takes up a lot of space, you can use data packaging and compression to save it
  

yum -y install xz#数据库文件很大，可以使用压缩率较大的xz格式压缩，首选需要安装xz压缩格式工具tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/#对数据库文件夹进行打包操作ls /opt#查看打包命令是否运行成功，已经生成了备份文件du -sh /opt/mysql-2021-02-05.tar.xz #对比打包前后的文件大小，可以看到压缩的备份文件所占用空间很小tar -Jxvf /opt/mysql-2021-02-05.tar.xz -C /usr/local/mysql/data/systemctl restart mysql#如果数据库文件损坏数据丢失，可以解压缩文件，相当于数据恢复

2.2 Use the mysqldump tool to back up

• The above demonstration is for MySQL The entire database directory is compressed by backing up all the contents of the database.
• mysqldump is a utility program (included) used by mysql to transfer and store the database. It mainly generates a SQL script, which includes re-creating it from scratch. Commands necessary for the database (such as CREATE, TABLE, INSERT, etc.)
• Using mysqldump can more flexibly control the content of the backup. For example, certain tables or libraries can be backed up individually

#开始之前，创建库和表，用作例子mysql -uroot -p123123#输入密码进入数据库，以上为我的密码create database SCHOOL;#创建库use SCHOOL;create table CLASS01 ( id int(2) not null auto_increment, name varchar(10) not null, sex char(5) not null, hobby varchar(10), primary key (id));#创建表结构#字段一：id，最大显示长度2，不能为空，自动递增从1开始#字段二：name，可变长度字段，10，不能为空#字段三：sex，固定长度5，不能为空#字段四：hobby，可变长度，最多10字符#字段四：主键（id）insert into CLASS01 values(1,'wangyi','man','reading');insert into CLASS01 values(2,'wanger','woman','singing');#插入2条数据select * from CLASS01;#检查一下quit#退出数据库

#使用 mysqldump 对某些表进行完全备份，命令格式如下：mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql#导出的为数据库脚本文件例：对库SCHOOL中的表class进行备份至/opt/目录下mysqldump -uroot -p[密码] SCHOOL CLASS01 > /opt/test01.sql

#对某些库进行完全备份mysqldump -uroot -p[密码] [数据库名] > /目录/备份文件名.sql例：mysqldump -uroot -p123123 SCHOOL > /opt/test02#对多个库进行完全备份mysqldump -uroot -p123123 --databases SCHOOL sys > /opt/test03.sql#对所有库进行完全备份mysqldump -uroot -p123123 --all-databases > /opt/test04.sql#直接备份表结构mysqldump -uroot -p123123 -d SCHOOL CLASS01 > /opt/test05.sqlls /opt/#查看打包命令是否运行成功，已经生成了备份文件

##

cat /opt/test05.sql#分析 mysqldump 工具生成的备份文件的内容

The -d option is used above, which means that only the table structure of the database is saved, and there is only one library in the table. You can see that the table (with the same name) is deleted first, and then it is created.
• Deletion and creation are the most common Any MySQL command is an executable statement in MySQL. With these statements, you can create a table with the same structure as the current table
• If you do not use the -d option, the data will also be Backup, let’s take a look at how to save the data

cat /opt/test01

You can see that the difference from using the -d parameter is that the insert into statement appears at the end. Two pieces of data were inserted into the database. In other words, the backup of the data is the saved insert statement operation
• Through the above analysis, it is easy to understand that the essence of the backup is to save the creation statement and current table structure of the database. There are insert statements for data. With them, you can directly restore the state of the database

---

2. MySQL complete recovery

Previous introduction For a complete backup of the database, use the mysqldump tool to save the data creation statements in the backup script file
• When errors occur, you can use the following methods to restore them

1. Restore the entire database operation

1.1 source command to restore the entire database

mysql -uroot -p123123show databases;drop database SCHOOL;show databases;source /opt/test03.sql#之前备份过的，该备份为SCHOOL和sys两个库的完整备份#注意！选项得加“--databases”才是完整的备份，不然会恢复不了

1.2 MySQL command to restore the entire database

quitmysql -uroot -p123123 -e 'drop database SCHOOL;'mysql -uroot -p123123 -e 'SHOW DATABASES;'mysql -uroot -p123123 <p><img src="https://img.php.cn/upload/article/000/000/052/46d75be18c0819b67f9c8259bacd7776-9.png" alt="MySQL introduces full backup and recovery"></p><p>2. Restore table operation<strong></strong></p>

The same can be used to restore the data table Source command and mysql command operations

2.1 Use the source command to restore the table

mysql -uroot -p123123use SCHOOL;show tables;drop table CLASS01;
 source /opt/test01.sql#恢复表show tables;select * from CLASS01;#查看

2.2 使用 mysql 命令恢复表

quitmysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 -e 'DROP TABLES SCHOOL.CLASS01;'mysql -uroot -p123123 -e 'SHOW TABLES FROM SCHOOL;'mysql -uroot -p123123 SCHOOL <p><img src="https://img.php.cn/upload/article/000/000/052/29945f61915e1a240cc5e16f53f849e5-12.png" alt="MySQL introduces full backup and recovery"></p><p><strong>3.MySQL 备份思路</strong></p>

• MySQL 需要定期实施备份，指定合适的备份计划或策略，并严格遵守
• 除了进行完全备份，开启 MySQL 服务器的日志功能也很重要，完全备份加上日志，可以对 MySQL 进行最大化还原
• 备份文件的名字还需钥使用统一的易于理解的名称，推荐使用库名或表名加上时间的命名规则，在需要恢复数据库时能很容易的定位到相应的所需备份文件

相关免费学习推荐：mysql数据库(视频)

Backup methods	Full backup	Differential backup	Incremental backup
State during full backup	Table 1, Table 2	Table 1, Table 2	Table 1, Table 2

The above is the detailed content of MySQL introduces full backup and recovery. For more information, please follow other related articles on the PHP Chinese website!