MySQL full backup and recovery

The importance of database backup, in the production environment, the security of data is very important, any loss of data can have serious consequences, database backup, timely recovery of important data. Causes of data loss:

• Program error
• Human error
• Computer failure
• Disk failed
• Disaster (fire, earthquake) theft,

From a physical and logical point of view, backups can be divided into physical and logical backups.

1. Logical backup: Refers to a backup of a logical component of a database, such as a table, database.
2. Physical Backup: A backup of the physical files (data files, log files) of the database operating system. Physical backups can also be divided into cold and hot backups.

Cold backup: When the database is closed, the backup operation can better ensure the integrity of the database. Hot backup: A backup of the database when it is running, and the backup method relies on the log files of the database. Backups can also be divided into full, differential, and incremental backups.

1. Full backup: It takes a long time to back up all objects in the entire database.
2. Differential backups: Backups after full and incremental backups. (Only the full backup is recognized, after the backup)
3. Incremental backup: Backup only for content that was modified after the backup. (only the one added after the full backup is backed up.) ）

One. Use tar to package a folder backup.

1. database file is large, you can use the high compression ratio of XZ format compression, the first installation of XZ compression tool

yum install xz -y

2. Package Operations on database folder/usr/local/mysql/data/

3. If the database file corruption is lost, you can decompress and compress the backup file to do the data recovery work.

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

Second, use the Mysqldump tool to back up.

1. Make a full backup of a single library .

mysqldump -u 用户名 -p[密码] --database[数据库] > /备份路径/备份文件名

Cases:

mysqldump -uroot -pabc123 --database school > /opt/school.sql

2. Make a full backup of some tables.

mysqldump -u用户名 -p[密码][数据库名][表名]>/备份路径/备份文件名

3. Make a full backup of multiple libraries .

mysqldump -u用户名 -p[密码] --databases [库名1][库名2] >/备份路径/备份文件名

4. Make a full backup of all the libraries.

mysqldump -u用户名 -p[密码] --all-databases >/备份路径/备份文件名

5. Back up only the table structure of a table.

mysqldump -u用户名 -p[密码] -d [库名][表名]>/备份路径/备份文件名

Iii. Recovery of the database

1. After logging into the database, go to the database and use the source command to restore the whole library directly .

source 备份脚本路径

Cases:

source /opt/school.sql

Note: Create a new database and go to the new database to execute the source command.

2. Restore with mysql command. (in the case of not logging in to the database)

Mysql-u user name-p[password] Library name </library backup path/backup file

There are 2 examples to say that the first one only backs up the table, need to create a new library in advance, and then restore, the second without having to create a new library in advance.

Cases:

1) The first kind

Backup first

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

Create a new library to view information about the new library.

mysql -uroot -pabc123 -e ‘create database school;show databases;‘

Recovery

mysql -uroot -pabc123 school < /opt/school.sql

2) The second type

Backup

mysqldump -uroot -pabc123 --databases school > school.sql

No need to create new libraries in advance

Recovery

mysql -uroot -pabc123 school < school.sql

Third, no need to log into the database to view, create databases, tables

mysql -u 用户名 -p[密码] -e ‘[命令]；[命令]；‘

Example: Creating a library View Access library

mysql -uroot -pabc123 -e ‘create database school;show databases;‘

MySQL full backup and recovery