MySQL Backup and recovery

There are many types of backup methods for database backups, from the physical and logical point of view, backup is divided into the following categories:

(1) Physical backup: refers to the database operating system of physical files (such as data files, log files, etc.) backup. Physical backups can also be divided into offline backups (cold backups) and online copies (hot backup).

• Cold backup: When the database is closed, the backup operation can ensure the integrity of the database well.
• Hot backup: Operates in the database running state, which relies on the log files of the database.

(2) Logical backup: Refers to a backup of a database logical component, such as a database object, such as a table.

From a database backup policy perspective, backups can be divided into full, differential, and incremental backups.

(1) Full backup: Every time a full backup of the data. 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 a full backup cycle takes longer.

(2) Differential backup: Backs up files that have been modified since the last full backup, backing up only the contents of the database section. It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup. It has the advantage of faster storage and recovery.

(3) Incremental backup: Only those files that were modified after the last full or incremental backup are backed up.

Mysqldump Backup Walkthrough

A backup of the MySQL database can take two ways, because the database is actually a file, the database folder is packaged directly, or the backup is done using a dedicated backup tool mysqldump

First, use the TAR Package folder Backup 1, install the XZ compression format tool

MySQL database files by default are saved in the installation directory of the Data folder, you can directly save the data folder, but occupy a large space, you can use the TAR packaging compression to save.

Database file is large, you can use the compression ratio of the large XZ format compression, first need to install the XZ compression format tool

yum install xz -y

2, the database folder/usr/local/mysql/data/to packaging operations

tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/      //将/usr/local/mysql/data/目录下所有内容进行备份到/opt目录下以带日期格式命名

3, if the database file corruption data loss, you can extract the backup files, equivalent to do the data recovery work.

tar Jxvf /opt/mysql-2018-07-18.tar.xz /usr/local/mysql/data/

Second, use the Mysqldump tool Backup 1, view the/usr/local/mysql/data/directory, temporarily no new files

2, into the database, create a school database, create an Info data table, and add several records

mysql -u root -p     //以管理员身份进入数据库create database school；      //创建school数据库use school；       //进入school数据表create table info (id int(4) primary key,name varchar(10) not null);       //创建info数据表#添加三条记录insert into info (id,name) values (1,‘jack‘); insert into info (id,name) values (2,‘tom‘);insert into info (id,name) values (3,‘xxy‘);mysql> select * from info;         //查看info表的信息+----+------+| id | name |+----+------+|  1 | jack ||  2 | tom  ||  3 | xxy  |+----+------+

3, exit the database, and then to the/usr/local/mysql/data/directory, you can see the school folder

4, we can carry out the following series of backup operations

mysqldump -u root -p school > /opt/school.sql     //将school数据库备份到/opt目录下命名为school.sql#若有多个数据库需要备份，则使用如下命令mysqldump -u root -p --databases school01 school02 > /opt/school.sql      //备份school01 school02两个数据库#可使用如下命令备份所有数据库mysqldump -u root -p --opt --all-databases > /opt/all.sql     //备份所有数据库#还可以对表结构进行备份mysqldump -u root -p -d school info > /opt/desc-info.sql //备份school数据库中info表结构mysqldump -u root -p school info > /opt/info.sql //备份school数据库中的info表

Iv. Recovery

If the original data corruption in the database, want to return the data back, need to establish an empty database can be returned, or the original corrupted database deleted, set up a database with the same name to return data

mysql -u root -p school < /opt/info.sql  //将/opt/info.sql表导回数据库school中

Back to the database to see that the original data has been restored

MySQL Backup and recovery