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

Blog catalogue

Full backup and recovery of MySQL

II. Type of database backup

Third, common backup methods

IV. Database full backup operation

V. MySQL incremental backup and recovery

Full backup and recovery of MySQL

The main purpose of backup is disaster recovery. Backup can also test applications, roll back data modifications, query historical data, audit and so on.

1. The importance of data backup

In the enterprise, the value of data is very important, and the data ensures the normal operation of the enterprise business. Therefore, the security and reliability of data is the top priority of operation and maintenance, and any loss of data may have serious consequences for the enterprise. In general, there are several reasons for data loss:

Program error

Human operation error

Operation error

Disk failure

Disasters (fires, earthquakes) and theft

Third, common backup methods

Second, database backup type 1, from the physical and logical point of view:

Database backup can be divided into physical backup and logical backup. A physical backup is a backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be quickly recovered when problems occur.

Physical backup can be divided into cold backup (offline backup), hot backup (online backup) and warm backup.

Cold backup: backup operation when the database is closed

Hot backup: performs a backup operation while the database is running, which relies on the log files of the database

Warm backup: backup in a database locked table (not writable, but readable)

A logical backup is a backup of database logical components (such as tables and other database objects), expressed as logical database structure (create database, create table statements) and content (insert statements or delimited text files). This type of backup is used to edit data values or small amounts of data with a table structure, or to recreate data on different machine systems.

2. From the point of view of database backup strategy:

From the point of view of database backup strategy, database backup can be divided into full backup, differential backup and incremental backup. Among them, full backup is the basis of differential and incremental backup.

Full backup: a complete backup of the data each time, that is, a backup of the entire database. The operation of backup and recovery is very simple, but there is a lot of data duplication, which will take up a lot of disk space, and the backup time is also very long.

Differential backup: back up all files that have been modified since the last full backup, the backup time is from the last full backup, the backup data will become larger and larger, when you restore the data, just restore the last full backup and the most recent differential backup.

Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up, taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the amount of data backed up is small, the space occupied is small, and the backup speed is fast, but when restoring, all the increments from the last full backup to the last incremental backup need to be restored in turn. Once the intermediate data is damaged, it will lead to the loss of data. Third, common backup methods

MySQL database can be backed up in many ways, such as direct packaging of database files (physical cold backup), dedicated backup tool (mysqldump), binary log incremental backup, third-party tool backup and so on.

1. Physical cold backup

Physical cold backup needs to be in a closed state of the database, which can better ensure the integrity of the database. Physical cold backup is generally used for non-core business, which generally allows interruption. Physical cold backup is characterized by fast speed and the simplest recovery.

2. Dedicated backup tool mysqldump or mysqlhotcopy

Both the mysqldump program and mysqlhotcopy can be backed up. Mysqqldump is a common logical backup program on the client side, which can produce a set of SQL statements that are executed to reproduce the original database object definition and table data. It can dump one or more MySQL databases, back them up or transfer them to a remote SQL server. Mysqldump is more generic because it can back up various tables. Mysqlhotcopy is only available for some storage engines.

3. Incremental backup by enabling binary logs

MySQL supports incremental backups, and binary logging must be enabled for incremental backups. Binary log files provide users with replication and recovery of the information needed for database changes made after the backup point is executed. If you perform an incremental backup, which includes data modifications that have occurred since the last full backup or incremental backup, you need to refresh the binary log.

4. Database full backup operation 1. Prepare [root@centos01 ~] # mysqladmin-uroot password New password: Confirm new password: [root@centos01 ~] # mysql-uroot-ppwd@123 mysql > create database benet; mysql > use benet before backup Mysql > create table class 1 student scores (name char (3), class char (5), student number char (8), Chinese char (3), mathematics char (3), English char (3), physics ensemble char (3), primary key (student number); mysql > insert into class 1 student scores value ('Zhang San', 'class one', '20170822', '110', '105','92', '235') Mysql > insert into Class one students' scores value ('Li Si', 'Class one', '20170820', '20170820', '20170820', '95',' 115', '115',' 110', '260'), students' scores of mysql > insert into ('Wang Wu', 'Class one', '20170818',' 20170818', '20170818', '103',' 103', '108',' 270') Mysql > insert into Class 1 students' scores value ('Zhao Liu', 'Class 1', '20170816', '20170816', '20170816', '109',' 112', '265'), mysql > select * from benet. The scores of the first class of students

2. Physical cold backup and recovery

Physical cold backup usually packages the database folder directly with the tar command, while the mysql service needs to be turned off using the "systemctl stop mysqld" command before the backup.

1) back up the database

Create a / bak directory as the backup data storage path and use tar to create backup files. The entire database folder backup is a full backup.

[root@centos01 ~] # systemctl stop mysqld [root@centos01 ~] mkdir / bak/ [root@centos01 ~] # tar zcf / bak/mysql_all-$ (date +% F). Mysql.gz / usr/local/mysql/data/ [root@centos01 ~] # ls / bak/-rw-r--r-- 1 root root 766598 October 31 03:57 / bak/mysql_all-2019-10-31.mysql.gz2) Restore database [root@centos01 ~] mkdir test [root@centos01 ~] # tar zxvf / bak/mysql_all-2019-10-31.mysql.gz-C. / test/ [root@centos01 data] # cd / usr/local/mysql/data/ [root@centos01 data] # rm-rf. / * [root@centos01 ~] # cd. / test/usr/local/mysql/data/ [root@centos01 date] # mv. / * / usr/local/mysql/data/ [root@centos01 ~] # systemctl start mysqld 3, Mysqldump backup and recovery

The mysqldump command allows you to export specified libraries, tables, or all libraries as SQL scripts, making it easy to use on different versions of MySQL servers.

1) backup and restore all databases [root@centos01 ~] # mysqldump-uroot-ppwd@123-- opt-- all-databases >. / test/benet_databases.sql [root@centos01 ~] # mysql-uroot-p Enter password: mysql > show databases +-+ | Database | +-+ | information_schema | | benet | | mysql | | performance_schema | | test | +-+ mysql > drop database benet; mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ [root@centos01 ~] # mysql-u root-p

Show databases +-+ | Database | +-+ | information_schema | | benet | | mysql | | performance_schema | | test | +-+ mysql > source. / test/benet_databases.sql 2) the table [root@centos01 ~] # mysqldump-uroot-ppwd@123 benet in the backup and recovery database >. / test/benet_ Class 1 student scores. SQL [root@centos01 ~] # mysql-uroot-p Enter password: mysql > use benet Mysql > drop table Class 1 students' scores; mysql > show tables; Empty set (0.00 sec) [root@centos01] # mysql-uroot-p benet

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.