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

The importance of data backup

In a production environment, the security of data is critical, and any loss of data may have serious consequences.

The cause of data loss

Program error

Human error (common)

Computer failure

Disk failed

Disaster

Classification of database backup

Physical backup

This copy of the 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: when actually shutting down the database

Hot backup: the database is running, and this backup method depends on the log files of the database

Logical backup: backup of database logical components (such as tables, etc.)

From the point of view of database backup strategy, backup can be divided into

Full backup: make a full backup of the data each time

Differential backup: back up files that have been modified since the last full backup

Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up

Incremental backup, the first point is on the basis of full backup. After the incremental modification, it will be baked and c+ incremental. After the incremental backup of these two files, back up the changes.

Full backup

A full backup is a backup of the entire database, database structure and file structure.

A full backup saves the database at the time when the backup is completed.

Full backup is the basis of incremental backup

Advantages and disadvantages of full backup

Advantages: simple and convenient backup and recovery operation

Disadvantages: there are a lot of duplication of data, taking up a lot of backup space, and long backup and recovery time.

Mysqldump backup library

Mysql database can be backed up in a variety of ways

Package database folders directly, such as / usr/local/mysal/data

Use the dedicated backup tool mysqldump

Mysqldump command

Mysql's own backup tool, which is quite convenient to back up mysql.

Through the command tool, you can export specified libraries, tables, or all libraries to SQL scripts, and you can restore data when you need to recover.

Mysql incremental backup

Characteristics

No duplicate data, small backup, short time.

Restore trouble: all incremental backups after the last full backup and full backup are required to be restored, and all incremental backups are restored one by one

Incremental backup can be achieved indirectly through the binary log (binary logs) provided by mysql

Incremental recovery of mysql database

General recovery

Add data-perform full backup-enter new data-perform incremental backup-simulate failure-restore operation

Location-based recovery

Is to import the binary log at a certain starting time into the database, thus skipping a certain point in time where an error occurs to achieve data recovery.

Point-in-time recovery

Using point-in-time recovery, there may be both correct and wrong operations at a point in time, so we need a more accurate recovery.

Let's first create a database and table to experiment with mysql > create database shcool; # create shcool library Query OK, 1 row affected (0.51 sec) mysql > use shcool; # enter shcool library Database changedmysql > create table info (# create info table-> id int (4) not null primary key auto_increment,-> name varchar (10) not null,-> score decimal (4) not null); Query OK, 0 rows affected (0.21 sec) mysql > desc info # View the table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | id | int (4) | NO | PRI | NULL | auto_increment | | name | varchar (10) | NO | | NULL | | score | decimal (4) | NO | | NULL | | +-+- -+ 3 rows in set (0.01sec) mysql > insert into info (name Score) values ('stu01',88), (' stu02',77) # insert data Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from info +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.00 sec) mysql > select * from info limit 1 # View the first line of the data table +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | +-+ 1 row in set (0.00 sec) mysql > quit # exit the full backup physical backup [root@localhost ~] # cd / usr/local/mysql/ [root@localhost mysql] # lsbin COPYING-test docs lib mysqld.pid mysql.sock.lock README share usrCOPYING data include man mysql.sock mysql-test README-test support-files [root@localhost mysql] # cd data/ [root@localhost data] # lsauto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema shcool sys [root@localhost data] # cd shcool/ [root@localhost shcool ] # cd. /.. / [root@localhost mysql] # tar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data/tar: Removing leading `/ 'from member names/usr/local/mysql/data//usr/local/mysql/data/ibdata1/usr/local/mysql/data/ib_ logfile1 [root @ localhost mysql] # ls / opt/mysql-2019-11-24.tar.xz mysql-5.7.20 rh logical backup [root @ localhost mysql] # cd data/ [root@localhost data] # mysqldump-u root-p shcool > / opt/shcool.sqlEnter password: [root@localhost data] # ls / opt/mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql for integrity backup of multiple databases [root@localhost opt] # mysqldump-u root-p123123-databases shcool mysql > / opt/db_shcool_mysql.sqlmysqldump: [Warning] Using a password on the command line interface Can be insecure. [root@localhost opt] # lsdb_shcool_mysql.sql mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql back up all databases [root@localhost opt] # mysqldump-u root-p123123-- opt-- all-databases > / opt/all.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt] # lsall.sql db_shcool_mysql.sql mysql-2019-11-24. Tar.xz mysql-5.7.20 rh shcool.sql backs up a table [root@localhost opt] # mysqldump-u root-p123123 shcool info > / opt/shcool_info.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. Backup the structure of a table [root@localhost opt] # mysqldump-u root-p123123-d shcool info > / opt/shcool_info_secret.sql full backup restore mysql > use shcool;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > drop table info;Query OK, 0 rows affected (0.03 sec) mysql > source / opt/shcool.sql Query OK, 0 rows affected (0.00 sec) mysql > show tables;+-+ | Tables_in_shcool | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > select * from info +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.00 sec) restore the database under the console mysql > drop table info;Query OK, 0 rows affected (0.01 sec) mysql > show tables Empty set (0.00 sec) mysql > quit [root@localhost opt] # mysql-u root-p123123 shcool

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.