Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of MySQL full backup and recovery. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Free learning recommendation: mysql video tutorial

Article catalogue

Preface

1. MySQL full backup

2.1 backup using tar packaged folders

2.2 backup using the mysqldump tool

1.1 importance of database backup

1.2 Classification of database backups

1.3 interpretation of the concept of MySQL full 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. A detailed discussion on the mode of database backup

2.mysqldump backup walkthrough

II. Complete recovery of MySQL

2.1 restore the table using the source command

2.2 restore the table using the mysql command

1.1 source command to restore the whole library

1.2 MySQL command to restore the whole library

1. Resume the operation of the whole library

two。 Restore table operation

The idea of 3.MySQL backup

Preface

With the rapid development of office automation and e-commerce, enterprises are more and more dependent on the information system. As the core of the information system, database plays an important role.

Database backup is an important means to recover important data in time and prevent data loss in the case of data loss.

A reasonable database backup scheme can effectively recover data when data is lost, and it also needs to consider the difficulty of technical implementation and the effective use of resources.

1. MySQL full backup

1. A detailed discussion on the mode of database backup

1.1 importance of database backup

In the production environment, the security of data is crucial, and any loss of data 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, it is impossible to find data without database backup.

Using database backup to restore database is the best way to provide the minimum cost of data recovery when the database crashes. If users are allowed to add data again, the cost will be too high.

If there is no data, there is nothing. Database backup is a powerful means to prevent disasters.

In the process of using a database, there are a number of reasons for data loss:

Program error: it means that the program that operates on the database inevitably has some errors, resulting in data loss.

Human error: refers to the destruction of data caused by the misoperation of the user, and the data loss caused by the hacker's attack on the system, etc.

Computer failure: damage to the operating system or software of the server running the database, which may cause damage to the data

Disk failure: refers to hardware devices such as hard drives that store data, which may be damaged after running for a long time, resulting in a number of

According to missing

Disasters (such as fires, earthquakes) and theft: the occurrence of natural disasters, etc., which may result in data loss

Data loss will cause serious economic losses. For example, the database of Ctrip crashed on May 28, 2015, resulting in a loss of about $1.06 million per hour.

Therefore, in the construction of enterprise information system, database backup management is a very important content.

1.2 Classification of database backups

There are many backup methods. From a physical and logical point of view, backup can be divided into the following categories:

1.2.1 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 carried out when the database is closed, which can better ensure the integrity of the database.

Hot backup (online backup): operates in the running state of the database, which depends on the log files of the database

1.2.2 logical backup

Backup of database logical components (such as tables and other database objects)

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

1.2.3 full backup

Make a full backup of the data each time

You 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 it takes longer to make a full backup.

1.2.4 differential backup

Back up files that have been modified since the last full backup, that is, only the contents of the database

It is smaller than the original full backup because it contains only the databases that have changed since the last full backup

Its advantage is fast storage and recovery.

1.2.5 incremental backup

Only those files that were modified after the last full or incremental backup will be backed up

1.2.6 backup methods comparison full backup differential backup status Table 1, Table 2 create Table 3 backup contents Table 1, Table 2, Table 3 Table 3 second addition Table 4 create Table 4 Table 4 backup contents Table 1, Table 2, Table 3, Table 3 Table 3, Table 4 Table 4

Full backup is to back up the contents of the entire database every time.

Differential backup is backed up after the first addition, that is, the newly added content is backed up; after the second addition, the first added content is also backed up, which is determined according to the status of the full backup

A differential backup backs up all changes made after a full backup every time, regardless of whether a differential backup has been done before.

Incremental backup only backs up the contents of each new modification. The first time table 3 is created, table 3 is backed up. After table 4 is added for the second time, because table 3 has been backed up before, only the contents of table 4 are backed up.

1.3 interpretation of the concept of MySQL full backup

The main backup methods of MySQL are full backup and incremental backup.

Full backup is the backup of the whole database, database structure and file structure, which 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 the operation of backup and recovery 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 long backup time.

In the production environment, these two backup methods will be used, so 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.

2.mysqldump backup walkthrough

There are two ways to back up MySQL database.

Because a database is actually a file, you can either package the database folder directly or use the special backup tool mysqldump for backup.

2.1 backup using tar packaged folders

The database files of MySQL are saved under the data folder of the secure 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.

The yum-y install xz# database file is very large and can be compressed in xz format with high compression ratio. The first choice is to install the xz compression format tool tar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data/# packages the database folder ls / opt# to see if the package command runs successfully The backup file du-sh / opt/mysql-2021-02-05.tar.xz # has been generated. Comparing the file size before and after packaging, you can see that the compressed backup file takes up very little space. Tar-Jxvf / opt/mysql-2021-02-05.tar.xz-C / usr/local/mysql/data/systemctl restart mysql# if the database file is corrupted and data is lost, the file can be unzipped, which is equivalent to data recovery.

2.2 backup using the mysqldump tool

The way shown above is to compress the directory of the entire database of MySQL, which is to back up all the contents of the database.

Mysqldump is a mysql utility (included) for transferring the database. It mainly generates a SQL script that contains the commands necessary to recreate the database from scratch (such as CREATE, TABLE, INSERT, etc.)

You can use mysqldump to control the contents of the backup more flexibly, for example, several tables or libraries can be backed up separately.

# before starting, create a library and table as an example mysql-uroot-p12312 enter the password to enter the database. Above, create a library for my password 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)) # create table structure # field 1: id, maximum display length 2, cannot be empty, automatically increments from 1 # field 2: name, variable length field, 10, cannot be empty # field 3: sex, fixed length 5, cannot be empty # field 4: hobby, variable length, up to 10 characters # field 4: id (id) insert into CLASS01 values Insert into CLASS01 values; # insert 2 pieces of data select * from CLASS01;# check quit# exit from the database

# use mysqldump to make a full backup of some tables. The command format is as follows: mysqldump-u [user name]-p [password] [option] [database name] [data table name] > / backup path / backup file name. SQL # exported as a database script file example: backup the table class in the library SCHOOL to / opt/ directory mysqldump-uroot-p [password] SCHOOL CLASS01 > / opt/test01.sql

# full backup of some libraries mysqldump-uroot-p [password] [database name] > / directory / backup file name. SQL example: mysqldump-uroot-p123123 SCHOOL > / opt/test02# full backup of multiple libraries mysqldump-uroot-p123123-- databases SCHOOL sys > / opt/test03.sql# full backup of all libraries mysqldump-uroot-p123123-- all-databases > / opt/test04.sql# direct backup table structure mysqldump-backup- P123123-d SCHOOL CLASS01 > / opt/test05.sqlls / opt/# check whether the packaging command runs successfully Backup files have been generated

Cat / opt/test05.sql# analyzes the contents of backup files generated by the mysqldump tool

The-d option is used above, which indicates 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 before it is created.

Delete and create are the most common MySQL commands. Any statement can be executed 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 backed up. Let's see how the data is saved.

Cat / opt/test01

You can see that the difference between using the-d parameter and using the-d parameter is that there is an insert into statement that inserts two pieces of data into the database, that is, the backup of the data is a saved insert statement operation.

Through the above analysis, it is easy to understand that the essence of backup is to save the creation statement of the current table structure of the database and the insert statement of the existing data, with which you can directly restore the state of the database.

II. Complete recovery of MySQL

The full backup of the database was introduced earlier, and the creation statements of the data can be saved in the backup script file by using the mysqldump tool.

When errors occur, you can restore them in the following ways

1. Resume the operation of the whole library

1.1 source command to restore the whole library

Mysql-uroot-p123123show databases;drop database SCHOOL;show databases;source / opt/test03.sql# has been backed up before, this backup is a full backup of SCHOOL and sys libraries # Note! The option must add "--databases" to make the full backup, otherwise it will not be restored.

1.2 MySQL command to restore the whole library

Quitmysql-uroot-p123123-e'drop database SCHOOL;'mysql-uroot-p123123-e'SHOW DATABASES;'mysql-uroot-p123123 < / opt/test03.sql mysql-uroot-p123123-e'SHOW DATABASES;'

two。 Restore table operation

You can also use the source command and the mysql command to restore the data table

2.1 restore the table using the source command

Mysql-uroot-p123123use SCHOOL;show tables;drop table CLASS01; source / opt/test01.sql# recovery table show tables;select * from CLASS01;# view

2.2 restore the table using the mysql command

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 < / opt/test01.sqlmysql-uroot-p123123-e'SHOW TABLES FROM SCHOOL;'

The idea of 3.MySQL backup

MySQL needs to implement backups on a regular basis, specify appropriate backup plans or policies, and strictly comply with them.

In addition to full backup, it is also important to turn on the log function of the MySQL server. Full backup plus logs can maximize the restore of MySQL.

The name of the backup file also needs to use a unified and easy-to-understand name. It is recommended to use the library name or table name plus the naming rule of time, so that you can easily locate the corresponding backup file when you need to restore the database.

This is the end of the article on "sample Analysis of MySQL full backup and recovery". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.