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

This article mainly introduces the detailed interpretation of mysql backup and recovery. The content of the article is carefully selected and edited by the author. The detailed interpretation of mysql backup and recovery has a certain pertinence and is of great significance to everyone. Let's learn about the next topic with the author.

The importance of backup:

In the production environment, our database may encounter a variety of accidents, resulting in data loss, which can be divided into the following categories. Hardware failure, software failure, natural disaster, misoperation (the largest proportion)

Backup type:

Physical and logical perspective:

Physical backup-refers to the backup of the physical files of the database operating system

Logical backup-refers to the backup of database logical components

Backup strategy perspective:

Full backup-back up the entire dataset

Differential backup-files modified since the last full backup

Incremental backup-backing up data that has changed since the last backup (incremental or full)

Physical backup walkthrough

1. Install the xz compression format tool

Yum install-y xz

2. Backup (that is, packaging the database folder / usr/local/mysql/data)

Tar Jcvf / opt/mysql-$ (date +% F) .tar.xz / usr/local/mysql/data/

3. Delete and restore operations (restore the backup to the database folder)

Tar Jxvf / opt/mysql-2018-08-30.tar.xz / usr/local/mysql/data/

Logical backup walkthrough

The preparatory work is as follows:

(1) first add a login-free operation for the database, and subsequent demonstrations will be displayed in this way.

Mysql-uroot-pabc123-e'show databases;' # single message input

Mysql-uroot-pabc123-e 'show databases;use school;show tables;' # multiple information inputs, note the order

(2) the following is the school database created before backup and restore, and the following operations are aimed at the school database.

Make a full backup of the database

Method 1: only create and record the tables, and you need to create data in advance during recovery.

Backup: mysqldump-uroot-pabc123 school > / opt/school.sql

Restore: mysql-uroot-pabc123 school

< /opt/school.sql 方法2：加入--databases 恢复时无需建库 备份： mysqldump -uroot -pabc123 --databases school >

/ opt/school.sql

Restore: mysql-uroot-pabc123

< /opt/school1.sql 方法3：source语句 ---必须在mysql环境中执行 备份：mysqldump -uroot -pabc123 --databases school >

/ opt/school.sql

Restore: mysql > source / opt/school.sql

Make a full backup of the tables in the database

Backup-mysqldump-uroot-pabc123 school info > / opt/info.sql

Restore-mysql-uroot-pabc123 school

< /opt/sinfo.sql 三、对表结构进行完全备份 mysqldump -uroot -pabc123 -d school info >

/ opt/school.sql # add the-d parameter

Make a full backup of all libraries

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

After reading the above detailed interpretation of mysql backup and recovery, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.