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

5. Backup and recovery

Related keywords:

Strategic planning, recovery drills, restore and recovery

Recovery Point objective (PRO): tolerate how much data is lost

Recovery time objective (RTO): how long it will take to recover the data

Reasons for backup: disaster recovery, error correction, audit, testing

5.5.1 logical backup and recovery 5.1.1 backup # # View help mysqldump-- help## backup a table mysqldump-uroot-h227.0.0.1-paired password 'sakila test > sakila_test.sql## backup a database mysqldump-uroot-paired password`-h227.0.0.1-- single-transaction-- triggers-- routines-- databases sakila > sakila.sql## backs up all databases mysqldump-- single-transaction- -quick-- flush-logs-- master-data=2-- all-databases-- triggers-- routines-- events-- set-gtid-purged=off > backup.sql5.1.2 recovery 1. Full recovery # # switch logs when viewing current binary log mysql > show master status;## backup, and record subsequent logs, such as new log starting from mysql-bin.000007 # # read incremental binary log mysqlbinlog-- skip-gtids mysql-bin.000007 > bin7.sqlmysqlbinlog-- skip-gtids mysql-bin.000008 > bin8.sql## full recovery mysql > source backup.sql;mysql > source bin7.sql;mysql > source bin8.sql;2. Recovery based on time shell > mysqlbinlog-- stop-date= "2019-09-11 9:59:59" mysql-bin.000007 > bin7_pos1.sqlshell > mysqlbinlog-- start-date= "2019-09-11 10:01:01" mysql-bin.000007 > bin7_pos2.sqlshell > mysqlbinlog-- skip-gtids mysql-bin.000008 > bin8.sqlmysql > source backup.sql;mysql > source bin7_pos1.sql;mysql > source bin7_pos2.sql;mysql > source bin8.sql;3. Location-based recovery shell > mysqlbinlog-- start-date= "2019-09-11 9:59:59"-- stop-date= "2019-09-11 10:01:01" mysql-bin.000007 > bin7_pos.sql# find location number, such as 3601 and 3609shell > mysqlbinlog-- stop-position= "3601" mysql-bin.000007 > bin7_pos1.sqlshell > mysqlbinlog-- start-position= "3609" mysql-bin.000007 > bin7_pos2.sqlmysql > source backup.sql;mysql > source bin7_pos1.sql;mysql > source bin7_pos2.sql Mysql > source bin8.sql;5.1.3 mysqlpump backup and recovery

```shell outline

# # 5.2 physical backup # 5.2.1 Xtrabackup installation 1. Add Source ```shellyum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm## check yum list | grep percona-xtrabackup-24.x86_64

two。 Installation

# # lack of dependency package libev.so.4 processing rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm## installation rpm-ivh percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm5.2.2 backup principle

5.2.3 full hot backup # # create backup user create user 'backup'@'%' identified by' Bk_123456';grant reload,lock tables,replication client,create tablespace,process,super on *. * to 'backup'@'%'

# # ordinary backup innobackupex-- default-file=/data/mysql/3306/my3306.cnf-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- datadir=/data/mysql/3306/data-- port=3306-- slave-info-- no-timestamp / home/mysql/backup/dbfull_3306_ `date'+% Y% m% dashes% H% M'` # # streaming compressed backup innobackupex-- default-file=/data/mysql/3306/my3306. Cnf-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- datadir=/data/mysql/3306/data-- port=3306-- no-timestamp-- stream=xbstream-> / home/mysql/backup/ backup.xbstream.`date'+% Y% m% dice% H% M``

# # encrypted backup innobackupex-- default-file=/data/mysql/3306/my3306.cnf-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- datadir=/data/mysql/3306/data-- port=3306-- encrypt=AES256-- encrypt-threads=10-- encrypt-key=111111111111111111111111-- encrypt-chunk-size 512-- no-timestamp-- stream=xbstream-> / home/mysql/backup/ backup.xbstream.encrypt.`date'+% Y% m% dice% H% M``

5.2.4 incremental hot backup # # ordinary incremental innobackupex-- defaults-file=/data/mysql/3306/my3306.cnf-- host=192.168.56.16-- user=root-- password='password'-- datadir=/data/mysql/3306/data-- incremental-basedir=/data/mysql/backup/2019-09-11 encryption 16-47-59-- incremental / data/mysql/backup/increment## streaming encryption incremental innobackupex-- defaults-file=/data/mysql/3306/my3306. Cnf-host=192.168.56.16-user=root-password='password'-datadir=/data/mysql/3306/data-incremental-basedir=/data/mysql/backup/2019 09 11 11 16 47 59 incremental--compress- compress-threads=10 encrypt=AES256 encrypt=AES256 encrypt-threads=10 encrypt-key=111111111111111111111111 stream=xbstream. / > / data/mysql/backup/increment/increment.stream

If it is the second increment,-incremental-basedir= the directory of the last increment

5.3 physical recovery 5.3.1 recovery of full backup # # streaming decompression mkdir / home/tempxbstream-x < / data/mysql/backup/backup.xbstream-C / home/tempinnobackupex-- decompress-- decrypt=AES256-- encrypt-key=1111111111111111111111111 / home/tempinnobackupex-- copy-back-- defaults-file=/data/mysql/3306/my3306.cnf / home/temp## Application redoinnobackupex-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- apploy-log / home/mysql/backup/2019-09-12 October 10-18-57 recovery full (shut down the database Innobackupex-- default-file=/data/mysql/3306/my3306.cnf-- datadir=/data/mysql/3306/data-- copy-back / home/mysql/backup/2019-09-12 hours 10-18-57chown-R mysql:mysql / data/mysql5.3.2 recovery of incremental backup # # innobackupex-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- apply-log-- redo-only / home/mysql/backup / 2019-09-12 September 10-18-57##innobackupex-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- apply-log-- redo-only / home/mysql/backup/2019-09-12 September 10-18-57-- incremental-dir=/data/mysql/backup/increment/2019-09-12 September 09-11-23 # innobackup-- host=192.168.56.16-- user=backup-- password='Bk_123456'-- apply-log / home/mysql/backup/2019-09-12 September 10-18-57-- incremental-dir=/data/mysql/backup/increment/2019-09-13 September 09-11-50 # # restore full (shut down the database Clear the data catalog) innobackupex-- default-file=/data/mysql/3306/my3306.cnf-- datadir=/data/mysql/3306/data-- copy-back / home/mysql/backup/2019-09-12 hours 10-18-57chown-R mysql:mysql / data/mysql

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.