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

Data backup type

By business: can be divided into full backup, incremental backup, differential backup.

Full backup: is to back up the data and data structure of the entire database, the advantage is very intuitive, easy to understand. Inadequacies: because the system is fully backed up every day, a large number of backup data are repeated, which takes up space, increases costs, has a large backup volume, and takes a long time.

Incremental backup: the data backed up each time is only equivalent to the data that has been added and modified since the last backup. Advantages: no duplicate data, save space, shorten backup time. Disadvantages: it is troublesome to recover data in the event of a disaster. In addition, the reliability of this kind of backup is also poor, under this kind of backup, the relationship between the backup data is like a chain, one link after another, and any problem with the backup data will lead to the disconnection of the whole chain.

Differential backup: the data of each backup is relative to the newly added and modified data since the last full backup.

Data backup method: can be divided into hot backup, warm backup, cold backup.

Hot backup: refers to a direct backup while the database is running, without any impact on the running database.

Cold backup: refers to the backup when the database is stopped. This kind of backup is the simplest. Generally, you only need to copy the relevant database physical files.

Warm backup: the backup is also carried out while the database is running, but it will affect the operation of the current database, such as adding a global read lock to ensure the consistency of the backup data.

Backup flow chart

Introduction to mysql backup tool

Mysqldump: logical backup tool, suitable for all storage engines, can be used for warm backup, can achieve full backup, partial backup, hot backup support for IonoDB storage engine

File system tools such as cp,tar: physical backup tool, suitable for all storage engines, for cold backup, full backup and partial backup

Snapshot of lvm2: the backup is almost hot, and physical backup is achieved with the help of file system tools.

Mysqlhotcopy: almost cold standby, only suitable for myisam storage engine

Backup and recovery of Database based on mysqldump+binlog

1. Prepare the backup directory

[root@centos7] # mkdir-p / backup/binlog

two。 Prepare the database and tables for backup

MariaDB [(none)] > create database datas;Query OK, 1 row affected (0.05sec) MariaDB [(none)] > use datas;Database changedMariaDB [datas] > create table tb1 (id int,name char (20)); Query OK, 0 rows affected (0.41 sec) MariaDB [datas] > desc tb1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | int ( 11) | YES | | NULL | | name | char (20) | YES | | NULL | | +-+-+ MariaDB [datas] > insert into tb1 values (11) '')-> Query OK, 1 row affected (0.03 sec) MariaDB [datas] > select * from tb1;+-+-+ | id | name | +-+-+ | 11 | | +-+-+ 1 row in set (0.00 sec)

3. Make a full backup of the database

[root@centos7 ~] # mysqldump-- all-databases-- lock-all-tables-- flush-log-- master-data=2 > / backup/ `date +% qualified% T`-all.sql--lock-all-tables: lock all tables-- flush-logs: execute the flush logs command after locking the table-- master-data= {0 | 1 | 2} 0: do not record 1: record change master to statement; this statement is not commented 2: recorded as comment statement

4. Insert data into the table

MariaDB [datas] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000007 | 245 | +- + 1 row in set (0.00 sec) MariaDB [datas] > insert into tb1 values (12 'hehe'), (13) Query OK, 2 rows affected (0.23 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [datas] > select * from tb1;+-+-+ | id | name | +-+-+ | 11 | | | 12 | hehe | | 13 | yaya | +-+-+ 3 rows in set (0.00 sec)

5. Make incremental backups, backup binary logs

[root@centos7 mysql] # mysqlbinlog-- start-position=245-- stop-position=451 mysql-bin.000008 > / backup/binlog/ `date +% Flying% T`.sql [root@centos7 mysql] # cd / backup/binlog/ [root@centos7 binlog] # ls2017-12-03_16:43:29.sql

6. Continue to insert data, delete the database without backup, and simulate misoperation.

MariaDB [datas] > insert into tb1 values; Query OK, 1 row affected (0.00 sec) MariaDB [datas] > drop database datas;Query OK, 1 row affected (0.09 sec)

7. To restore the data, since the database was deleted without backup at the end, we first need to protect the last binary log and check the position value before deletion.

[root@centos7 binlog] # cd / var/lib/mysql/ [root@centos7 mysql] # mysqlbinlog mysqlBluebin.000008Universe / 50530 SET @ @ SESSION.PSEUDOAs SLAVEMODE1 / "40019 SET @ @ session.maxinsertion delayeddelayedthreads0" Universe 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / # at 4 '171203 16:35:29 server id 2 end_log_pos 245 Start: binlog v 4, server v 5.5.52-MariaDB created 171203 16:35:29 at startup# Warning: this binlog is either in use or was not closed proper.ROLBACKAAAAAAAAAAAABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlcards, modewords, etc. * / @ session. Session. Autoincrement increment off set @ @ session.auto_increment_increment=1!\ C utf8 * / / *! SET @ @ session.session. Collationalization timestamp namespace namespace 0pxxxxxxxxxxxxxxxxxxxxxxxx; SET Timeset 1512290198 Query thread_id=2 exec_time=0 error_code=0use 1512290198 Insert into tb1 values (14 Xid Xid) / *; # at 424 games 171203 16:36:38 server id 2 end_log_pos 451 Xid = 10COMMITDUBY words server id # at 451 "171203 16:54:34 server id 2 end_log_pos 520 Query thread_id=3 exec_time=0 error_code=0SET TimESTAMPs 1512291274 leads BEGINUnites / # at 520 "171203 16:54:34 server id 2 end_log_pos 618 Query thread_id=3 exec_time=0 error_code=0SET timestamp 1512291274 * /; # at 618" 171203 16:54:34 server id 2 end_log_pos 645 Xid = 20COMIT * * /; # end_log_pos 645 "171203 16:54:56 server id 2 end_log_pos 728 Query thread_id=3 exec_time=0 error_code=0SET timestamp 15122996 Drop database datasabet matching engine / DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET completion SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Back up the last binary log

[root@centos7 mysql] # mysqlbinlog-- start-position=520-- stop-position=645 mysql-bin.000008 > / backup/binlog/ `date +% Flying% T`.sql

8. Import all previous backups

[root@centos7 ~] # mysql

< /backup/2017-12-03_15\:05\:00-all.sql [root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16\:43\:29.sql [root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16\:51\:11.sql[root@centos7 ~]# mysql < /backup/binlog/2017-12-03_17\:10\:02.sql 9.查看数据库及数据 MariaDB [datas]>

Select * from tb1;+-+-+ | id | name | +-+-+ | 11 | | | 14 | hehe | | 15 | yaya | 12 | hehe | 13 | yaya | | 16 | yuyu | +-+-+ 6 rows in set (0.00 sec)

The data has been fully restored.

Database backup and recovery of xtrabackup

Xtrabackup is a mysql database backup tool provided by percona. It is an open source tool that can provide hot backup for innodb and xtradb databases.

Features:

(1) the backup process is fast and reliable.

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) Fast reduction speed.

Installation

[root@centos7 ~] # yum install xtrabackup

Full backup

[root@centos7 backup] # innobackupex-- user=root / backup/

If you want to set permissions for backup users, you can create backup users with minimum permissions

MariaDB [(none)] > grant reload,lock tables,replication client on *. * to 'backuser'@'localhost' identified by' 123456 MariaDB query OK, 0 rows affected (0.07 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.04 sec)

Incremental backup

Each InnoDB page contains a storage information, and each time the relevant data changes, the storage of the related pages will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

The innobackupex command creates a new time-named directory in the backup directory to hold all the incremental backup data, and when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located.

Note: incremental backups can only be applied to InniDB or XtraDB tables. For MyISAM tables, incremental backups are actually performed as full backups.

Add data

MariaDB [datas] > insert into tb1 values; Query OK, 1 row affected (0.00 sec) MariaDB [datas] > insert into tb1 values; Query OK, 1 row affected (0.00 sec) MariaDB [datas] > select * from tb1 +-+-+ | id | name | +-+-+ | 11 | | | 14 | hehe | | 15 | yaya | 12 | hehe | | 13 | yaya | | 16 | yuyu | | 17 | laoshi | 18 | jiangchen | +-+-+ 8 rows in set (0.00 sec)

Incremental backup

[root@centos7 2017-12-03 / 18-08-41] # innobackupex-- user=root-- incremental / backup-- incremental-basedir=/backup/2017-12-03 / 18-08-41 [root@centos7 backup] # ls2017-12-03 18-08-41 2017-12-03 18-36-10

Data recovery preparation

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main role of "prepare" is to roll back uncommitted transactions and synchronize committed transactions to data files. There are some differences between "prepare" incremental backups and collating full backups.

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay

All backup data will be merged into a full backup

(2) "rollback" uncommitted transactions based on all backups.

Data recovery preparation: performing action

[root@centos7 backup] # innobackupex-apply-log-redo-only / backup/2017-12-03 / 18-08-41 /

Then execute the increment

[root@centos7 backup] # innobackupex-- apply-log-- redo-only / backup/2017-12-03, 18-08-41 /-- inctrmental-dir=/backup/2017-12-03, 18-36-10/backup/2017-12-03, 18-08-41 / (directory for full backup) / backup/2017-12-03, 18-36-10 (the directory where the first increment is located)

Restore phase, restore data

[root@centos7 mysql] # innobackupex-- copy-back / backup/2017-12-03 / 18-08-41 / [root@centos7 mysql] # chown-R mysql.mysql / var/lib/mysql [root@centos7 mysql] # systemctl restart mariadb

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.