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

1. The importance of database backup

In the production environment, the security of the database 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.

(2) when the database backup is used to restore the database, it provides the optimal scheme with the minimum cost of data recovery when the database crashes. If the user is allowed to add data again, the cost is too high.

(3) if there is no database, there is nothing, and the backup of the database is a powerful means of disaster.

In the process of using a database, there are many reasons for data loss: program errors, thought errors, computer errors, disk failures, disasters (such as fire, earthquake), and theft.

two。 Backup classification of database

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

(1) physical backup: value 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: the backup operation when shutting down the database can better ensure the integrity of the database.

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

(2) logical backup: refers to the 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.

Full backup: make a full backup of the database each time. You can back up the entire database, including user tables, system tables, indexes, views, and all database objects in stored procedures. But it takes more time and space, so it takes longer to make a full backup.

Differential backup: backing up files that have been modified since the last full backup, backing up parts of the database by value, is smaller than a full backup, so it is faster to store and recover.

Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up.

MySQL database can be backed up in two ways, because the database is actually a file, directly package the database folder, or use the special backup tool mysqldump for backup work.

1. Backup using tar packaged folders

The database file of Mysql is saved under the data folder of the installation directory by default, and the data folder can be saved directly, but it takes up a lot of space and can be saved using tar packaging and compression.

(1) the database file is very large, so you can use the xz format with high compression ratio. First, check whether the xz compression tool is installed.

[root@localhost opt] # rpm-Q xz / / zx compression tool has been installed

Xz-5.2.2-1.el7.x86_64

(2) all database folders / usr/local/mysql/data/ are packaged and placed in the / opt/ directory

[root@localhost opt] # tar Jcvf / opt/mysql-$ (date +% F) .tar.xz / usr/local/mysql/data/ / opt/

(3) check whether the packaging command is successful and the backup files have been produced. Compare the file size before and after compression.

[root@localhost opt] # ls

Mysql-2018-09-03.tar.xz mysql-5.7.17 rh / / compress the generated backup files in the format of year, month and day

[root@localhost opt] # du-sh / opt/mysql-2018-09-03.tar.xz

684K / opt/mysql-2018-09-03.tar.xz

[root@localhost opt] # du-sh / usr/local/mysql/data/ compressed backup files take up very little space

134M / usr/local/mysql/data/

(4) if the database file is lost, the backup file can be unzipped, which is equivalent to the work of data recovery.

[root@localhost opt] # tar Jxvf / opt/mysql-2018-09-03.tar.xz / usr/local/mysql/data/

two。 Back up using the mysqldump tool

The way to compress the entire database directory of MtSQL described earlier is to back up everything in the database. You can use mysqldump to control the backup contents more flexibly, for example, several tables or libraries can be backed up separately.

(1) use the mysqldump command to make a full backup of some tables in the following format:

Mysqldump-u user name-p [password] [option] [database name] [data table name] > / backup path / backup file name

The following is a demonstration of backing up the table info in the library school. The backup file is / opt/school.sql

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mogo |

| | mysql |

| | performance_schema |

| | school | / / A pair of info tables in the library school are backed up |

| | sys |

+-+

Mysql > show tables

+-+

| | Tables_in_school |

+-+

| | info |

+-+

1 row in set (0.00 sec)

[root@bogon ~] # mysqldump-uroot-p school info > / opt/school.sql

Enter password:

[root@bogon opt] # ls

Info.txt mysql-5.7.17 rh school.sql / / backup files

(2) use the mysqldump command to make a full backup of a single library in the following format:

Mysqldump-u user name-p [password]-- databases [database name] > / backup path / backup file name

The following is a demonstration of backing up the library mogo with the backup file / opt/mogo.sql

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mogo | / / A full backup of the library mogo (all tables in the library are backed up) |

| | mysql |

| | performance_schema |

| | school |

| | sys |

+-+

6 rows in set (0.00 sec)

[root@bogon opt] # mysqldump-uroot-p-databases mogo > / opt/mogo.sql

Enter password:

[root@bogon opt] # ls

Info.txt mogo.sql mysql-5.7.17 rh school.sql / / backup files

(3) use the mysqldump command to make a full backup of multiple libraries in the following format:

Mysqldump-u user name-p [password]-- databases [Database 1] [Database 2] > / backup path / backup File name

The following is a demonstration of backing up the libraries mogo and school, with the backup file / opt/school01.sql

[root@bogon opt] # mysqldump-uroot-p-databases school mogo > / opt/school01.sql

Enter password:

[root@bogon opt] # ls

Info.txt mogo.sql mysql-5.7.17 rh school01.sql school.sql / / backup files

(4) use the mysqldump command to make a full backup of all libraries in the following format:

Mysqldump-u user name-p [password]-- all-databases > / opt/all.sql

The following is a demonstration of backing up all libraries, the backup file is / opt/all.sql

[root@bogon opt] # mysqldump-uroot-p-all-databases > all.sql

Enter password:

[root@bogon opt] # ls

All.sql info.txt mogo.sql mysql-5.7.17 rh school01.sql school.sql / / all library backup files

(5) you can also directly back up the table structure using the mysqldump command. The command format is as follows:

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

There is only one more-d option. The following demonstrates how to back up the table structure of the table info in the library school. The backup file is / opt/info.sql

[root@bogon opt] # mysqldump-uroot-p-d school info > / opt/info.sql

Enter password:

[root@bogon opt] # ls

All.sql info.sql info.txt mogo.sql mysql-5.7.17 rh school01.sql school.sql / / table structure backup file

Analyze the contents of backup file school.sql

[root@bogon opt] # vim school.sql

CREATE TABLE `info` (/ / create table info

`id`int (11) NOT NULL AUTO_INCREMENT

`name` char (10) DEFAULT NULL

`score` decimal (5pm 2) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

/ * 40101 SET character_set_client = @ saved_cs_client * /

--

-- Dumping data for table `info`

--

LOCK TABLES `info` WRITE

/ *! 40000 ALTER TABLE `info` DISABLE KEYS * /

INSERT INTO `info` VALUES), (2) insert data into the table, (3) insert data into tables

/ *! 40000 ALTER TABLE `info` ENABLE KEYS * /

UNLOCK TABLES

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

Full recovery of 3.MySQL library

The full backup of the database was introduced earlier. Using the mysqldump tool, you can save the database creation statement to the backup script, and when the data has an error, you can use the following methods to restore it.

When you need to restore the entire library, you can use the source command and the mysql command

(1) source command to restore database

After logging in to MySQL, you can use the source command to restore the entire library in the following format:

Source backup script

Next, back up the library mogo, log in to the database, and delete the database mogo assuming that the database is corrupted

[root@bogon opt] # mysql-uroot-p / / log in to the database first

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mogo |

| | mysql |

| | performance_schema |

| | school |

| | sys |

+-+

6 rows in set (0.01 sec)

Mysql > drop database mogo; / / simulate database corruption, delete database mogo

Query OK, 1 row affected (0.02 sec)

Mysql > source / opt/mogo.sql; / / restore database mogo using the source command

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > show databases; / / check whether the database mogo is restored

+-+

| | Database |

+-+

| | information_schema |

| | mogo |

| | mysql |

| | performance_schema |

| | school |

| | sys |

+-+

6 rows in set (0.00 sec

(2) MySQL command to restore the whole database

Without logging in to MYSQL, you can make the mysql command restore the entire library directly.

Use backup files to restore the library mogo, assuming that the database mogo is damaged, delete the database mogo, and use the mysql command to restore directly.

Mysql > drop database mogo; / / simulate database corruption, delete database mogo

Query OK, 1 row affected (0.37 sec)

There is no mogo library in mysql > show databases; / / database.

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | school |

| | sys |

+-+

5 rows in set (0.00 sec)

Mysql > quit

Bye

[root@bogon opt] # mysql-uroot-p

< /opt/mogo.sql //使用mysql 命令直接恢复数据库 Enter password: [root@bogon opt]# mysql -uroot -p -e 'show databases;' //不登录数据库查询恢复情况 Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mogo | //库mogo 已经恢复 | mysql | | performance_schema | | school | | sys | +--------------------+ 4.恢复表操作 （1）使用source 命令恢复表操作与恢复库方式相同 使用备份文件 school.sql 进行表恢复操作，模拟数据库中的表损坏，删除数据库 school 中 的 表 Info,使用 source 命令恢复。 mysql>

Use school; / / enter the database school

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables; / / View tables in the database

+-+

| | Tables_in_school |

+-+

| | info |

+-+

1 row in set (0.00 sec)

Mysql > drop table info; / / Delete table info

Query OK, 0 rows affected (0.33 sec)

Mysql > show tables; / / there is no table info

Empty set (0.00 sec)

Mysql > source / opt/school.sql; / / use the source command to restore table operations

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables; View Table info has been restored

+-+

| | Tables_in_school |

+-+

| | info |

+-+

1 row in set (0.00 sec)

Mysql > select * from info

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 96.00 | |

| | 3 | test01 | 86.00 |

| | 4 | test02 | 86.00 | |

| | 5 | test03 | 86.00 |

+-- +

5 rows in set (0.00 sec)

(2) restore MySQL command table

You can make the mysql command restore the library directly without logging in to MYSQL

Use the backup school.sql file to restore the table, assuming that the table info in the database school is corrupted, delete the table info, and use the mysql command to restore directly.

Mysql > drop table info; / / Delete table info

Query OK, 0 rows affected (0.37 sec)

Mysql > quit

Bye

[root@bogon opt] # mysql-uroot-p school < / opt/school.sql / / use the mysql command to restore table info in library school

Enter password:

[root@bogon opt] # mysql-uroot-p-e 'use school;show tables;select * from info;' / / do not log in to mysql to view the recovery of table info

Enter password:

+-+

| | Tables_in_school |

+-+

| | info |

+-+

+-- +

| | id | name | score | |

+-- +

| | 1 | tom | 89.00 |

| | 2 | lili | 96.00 | |

| | 3 | test01 | 86.00 |

| | 4 | test02 | 86.00 | |

| | 5 | test03 | 86.00 |

+-- +

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.