“This is the ninth day of my participation in the August Gwen Challenge.

1. Introduction

1.1 Backup Policy Design

  • Backup period: Based on data volume
  • Backup tool:
    • mysqldump
    • mysqlbinlog
    • MEB (MySQL Enterprise BACKUP)
  • Backup mode:

    • Logical backup:

      • All: the mysqldump
      • Incremental: binlog (flush logs, cp)

    • The physical backup

      • Full: Percona Xtrabackup
      • Incremental: Percona Xtrabackup

1.2 Checking backup Availability

  • Crontab -l: displays the backup script
  • Finding the Backup Path
  • View backup logs and check the size and content of backup files

1.3 Regular recovery drills

1.4 Data Recovery

1.5 Data Migration

  • mysql -> mysql
  • Other – > mysql
  • Mysql – > other

2. Introduction to backup

2.1 Backup Types

  • Hot standby: Has minimal impact on services and is currently only supported by InnoDB
  • Warm backup: long time lock table backup, MyISAM
  • Cold backup: Backup when services are shut down

3. mysqldump

3.1 Connecting a Database

-u
-p
-s
-h
P
Copy the code

3.2 Basic Backup Parameters

  • Perfect: – A

    • mysqldump -uroot -A > /tmp/all.sql

  • Back up multiple single libraries: -b

    • mysqldump -uroot -B account goods > /tmp/db.sql

  • Single table backup: library table table… The first one is a library, followed by tables

    • mysqldump -uroot account student > /tmp/student.sql

3.3 Special Backup Parameters

  • -r: backs up stored procedures and functions

  • -e: backup time

  • Triggers: Backup trigger

  • --master-data=2

    • During backup, the binary log file name and location number are automatically recorded

      • 0: default value
      • 1: the change master to command can be used as the primary/secondary replication
      • 2: record the file name + position number of the backup time in the form of comments

    • Automatic lock table

    • With –single-transaction, InnoDB tables can be backed up without shrinking (snapshot backup).

    • Do not add –single-transaction, is warm backup

  • single-transaction

    • InnoDB storage engine enables hot backup (snapshot backup)
      • Do not add--single-transactionParameter to start a warm backup for all tables and lock all tables
      • add--single-transactionParameter, for InnoDB snapshot backup, for non-InnoDB tables can achieve automatic table lock function.

4. Expand parameters

  • --set-gtid-purged=AUTO/ON

    • Use AUTO/ON when building master/slave
    • This parameter can be added only for normal local backup and restore--set-gtid-purged=OFF

  • --max_allowed_packet=128M

    • It controls the size of packets that are transferred during backup

5. Physical backup — Xtrabackup

5.1 installation

5.1.1 Installing dependency packages

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
Copy the code

5.1.2 Download and install the software

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4. 12-1.el7.x86_64.rpm wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4 -1.el6.x86_64. RPM yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64. RPMCopy the code

5.2 Backup Commands

  • innobackupex

5.3 innobackupex use

5.3.1 Backup Core Concepts

  1. For non-InnoDB, lock table backup, copy all non-InnoDB table files
  2. For InnoDB table, start CKPT immediately, copy all InnoDB table related files (IBDATA1, IBD, FRM). In addition, the redo file is backed up with any new data changes generated during the backup
  3. During restore, XBK invokes the CSR process of InnoDB engine to equalize data and REDO LSN, and then performs consistency restore.

5.3.2 XBK is fully prepared and restored

  1. innobackupex –user=root –no-timestamp /tmp/full

  2. innobackupex –apply-log /tmp/full

5.4 Backup Files

  1. xtrabackup_binlog_info

    1. The file name
    2. Position number at the time of backup
    3. Gtid #

  2. xtrabackup_checkpoints

    1. From: indicates the starting point of the LSN contained in the backup. Full backup is 0. The delta is where the last backup ended
    2. To: INDICATES the LSN of CKPT
    3. Last-9: indicates the LSN at the end of the backup and the start position of the next incremental backup