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

The importance of database backup:

In the production environment, the security of data is very important, and any loss of data may have serious consequences. The importance of database backup is mainly reflected in:

1. Improve the high availability and disaster recoverability of the system, when the database system crashes, it is impossible to find data without database backup.

two。 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 is too high.

3. There is no data, there is nothing, database backup is a powerful means to prevent disasters.

There are a number of reasons for data loss while using the database:

Program errors, human errors, computer failures, disk failures, disasters (earthquakes, fires, etc.) and theft

Classification of database backups:

Example:

Create database school

Create table info (id int,name char (10))

Insert into info (id,name) values (1)

-full backup-

Insert into info (id,name) values (2)

-incremental backup-

Insert into info (id,name) values (3)

-incremental backup and differential backup

Insert into info (id,name) values (4)

-incremental backup and differential backup

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 when the backup is completed, which 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 are a lot of duplicate data, occupy a lot of backup space, and take a long time.

In the production environment, these two backup methods will be used, it is necessary to develop a reasonable and efficient scheme to achieve the purpose of backing up data to avoid serious consequences caused by data loss.

Here are two ways to back up

1. Backup using tar packaged folder: the database files of MySQL are saved under the data folder of the installation directory by default. You can save the data folder directly, but it takes up a large space and can be saved using tar packaging and compression. Here are some related operation commands:

[root@localhost mysql] # yum install xz-y / / install the xz compression format tool

[root@localhost mysql] # date +% F / / specified compressed file date format

[root@localhost mysql] # tar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data / / A pair of database folders are compressed and packaged under / opt

[root@localhost ~] # du-sh / opt/mysql-2018-08-30.tar.xz / / du View the amount of memory consumed after packaging

[root@localhost ~] # du-sh / usr/local/mysql/data / / du View the amount of memory occupied by the original folder before packaging

[root@localhost ~] # tar Jxvf / opt/mysql-2018-08-30.tar.xz / usr/local/mysql/data/ restore the packaged backup files to the database folder

The specific operation is as follows:

two。 Backup using the mysqldump tool: you can use mysqldump to control the contents of the backup more flexibly, for example, several tables or libraries can be backed up separately. Here are some related operation commands:

(1) backup table data:

Mysqldump-u username-p password option database name table name > / backup path / backup file name

Mysqldump-u root-p school info > / opt/info.sql

(2) backup of a single library:

Mysqldump-u username-p password option database name > / backup path / backup file name

Mysqldump-uroot-p school > / opt/school.sql # only writes the creation and data records of all tables in the library

(3) multiple library backups:

Mysqldump-u user name-p password option-- databases library name 1 library name 2 > / backup path / backup file name

Mysqldump-uroot-p-- databases school kgc > / opt/school_kgc.sql

(4) make a full backup of all libraries:

Mysqldump-u username-p password option-- all-databases > / backup path / backup file name

Mysqldump-uroot-p-- all-databases > / opt/all.sql

(5) backup table structure:

Mysqldump-u user name-p password-d database name table name > / backup path / backup file name

Mysqldump-u root-p-d school info > / opt/info-d.sql

Full recovery of MySQL data

When you need to restore a database, you can use the source command and the MySQL command

(1) use the source command to restore the backed-up library school to MySQL:

Mysqldump-u root-p school > / opt/school.sql

(2) use the MySQL command to restore the entire database data, and also restore the database chool if it has been backed up in advance:

Mysqldump-u root-p school > / opt/school.sql

(3) mysqldump-u-root-p-- databases school > / opt/school01.sql # together with the library school itself and the tables in it will be backed up and can be directly restored:

Restore table:

(1) use the source command to restore the table, and perform the recovery operation if the data table has been backed up:

Mysqldump-u root-p school info > / opt/info.sql # backup table

(2) when you use MySQL to restore a data table, you can also restore it if the data table has been backed up:

Mysqldump-u root-p school info > / opt/info.sql # backup table

Mysql-u user name-p password library name < / path of library backup textbook

This article is supplemented.

The file after backup is a sql script, and the sql ends in a fixed format

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.