Classification of database backup

There are many backup methods. From the physical and logical point of view, backup is divided into the following categories:
(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: the backup operation when closing the database can better ensure the integrity of the database.
Hot backup: operate in the running state of the database. This backup method depends on the log file of the database.
(2) Logical backup: refers to the backup of database logical components (such as tables and other database objects).

From the perspective of database backup strategy, backup can be divided into full backup, differential backup and incremental backup.
(1) Full backup: complete backup of data every 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 a full backup takes longer.

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

(3) Incremental backup: only those files modified after the last full backup or incremental backup will be backed up.

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

Packaging folder backups using tar

1. Install xz compression format tool
MySQL database files are saved under the data folder of the installation directory by default. You can directly save the data folder, but it takes up a large space. You can use tar packaging and compression to save.

The database file is very large. You can use the xz format with high compression rate. First, you need to install the xz compression format tool

yum install xz -y

1. Package the database folder / usr/local/mysql/data /

tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/      //Back up all contents in the / usr/local/mysql/data / directory to the / opt directory and name it with date format

1. If the database file is damaged and the data is lost, you can decompress the backup file, which is equivalent to data recovery.

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

Enter the database, create the school database, create the info data table, and add several records

mysql -u root -p     //Enter the database as Administrator
create database school；      //Create school database
use school；       //Enter the school data sheet
create table info (id int(4) primary key,name varchar(10) not null);       //Create info data table
#Add three records
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;         //View info table information
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | tom  |
|  3 | xxy  |
+----+------+

mysqldump -u root -p school > /opt/school.sql     //Back up the school database to the / opt directory and name it school sql

#If multiple databases need to be backed up, use the following command
mysqldump -u root -p --databases school01 school02 > /opt/school.sql      //Backup two databases of school01 and School02
#You can back up all databases using the following command
mysqldump -u root -p --opt --all-databases > /opt/all.sql     //Back up all databases

#You can also back up the table structure
mysqldump -u root -p -d school info > /opt/desc-info.sql //Back up the info table structure in the school database

mysqldump -u root -p school info > /opt/info.sql //Back up the info table in the school database

recovery

If the data in the original database is damaged and you want to import back the backed up data, you need to create an empty database to import back, or delete the original damaged database and create a database with the same name to import back the data

mysql -u root -p school < /opt/info.sql  //Add / opt / info The SQL table is imported back to the database school

Summary: restoring databases

Method 1: interactive – use the source command
Log in to MySQL database
The path to execute the source backup sql script
Mode 2: no interaction – use the musql command
No need to log in to MySQL database
Use - e to delete the data table
Path to execute mysql backup script