mysql-Backup and Recovery

Purpose of the backup

　　Data loss scenarios such as disaster recovery, hardware failure, software failure, natural disaster, hacker attacks, misoperation testing, etc.

Backup Note points

　Can tolerate the maximum amount of data lost

How long does it take to recover the data

What data needs to be recovered

Restore Essentials

　　Do a restore test to test the availability of backups

Restore Walkthrough

Backup type:full backup, partial backup

　　Full backup : Entire data set

　　Partial backup : Back up only subsets of data, such as some libraries or tables

full backup, incremental backup, differential backup

　　Incremental backup : Backs up data that has changed since the most recent full or incremental backup (if there is an increment), faster backups, complex restores

　　differential backup : Backs up only data that has changed since the last full backup, slow backup, simple restore

Note: Binary log files should not be placed on the same disk as the data files

cold, warm, hot backup

Cold : Read and write operations are not allowed

Win Bei : read operation executable, but write operation not performed

Hot- Standby : Read and write operations can be performed

MyISAM: Win Bei, hot standby not supported

InnoDB: All support

physical and logical backups

Physical backup : directly copy data files for backup, related to storage engine, occupy more space, fast

logical Backup : The backup of "export" data saved from the database, regardless of the storage engine, takes up less space, is slow, and may lose precision.

Factors to consider when backing up

How long is the Win Bei locked?

Load generated by backup

The length of the backup process

Duration of the recovery process

Backup what

Data

Transaction log for binary log, InnoDB

program code (stored procedures, stored functions, triggers, event schedulers)

Configuration file for the server

The target of the backup

1, database data, each table space is stored separately

2, binary log, need and data storage separately

3. InnoDB transaction log

4, stored procedures, storage functions, triggers or event schedulers, etc.

5, the server configuration file:/etc/my.cnf

Backup tools

• Mysqlbackup Tools: Hot Backup, MySQL Enterprise Edition components
• Mysqlhotcopy tool: Almost cold, only for MyISAM storage engine
• LVM-based snapshot backup: Almost hot standby, need to lock the table before taking a snapshot
• Archive replication tools such as TAR + CP backup: Fully cold standby

mysqldump Tools : Logical Backup tool for all storage engine Win Bei, full or partial backup support, hot standby for InnoDB storage engine, Schema (definition of database) and data storage.

Mysqldump Reference: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html


mysqldump [Options]–b DB1 [DB2 DB3 ...]
mysqldump [Options]–a [OPTIONS]

　

Options:-A: Back Up all libraries-B db_name1,[db_name2,...] : Back up the specified library-E: All event Scheduler related to backup-R: Back up all stored procedures and storage functions--triggers: Backup table related triggers, enabled by default, with--skip-triggers, no backup triggers--master-data={1|2}:1: The backed up data is preceded by a record as change MASTER to statement, non-commented, default 1 is not specified2: The change MASTER to statement recorded as a comment, note: This option is automatically closed--Lock-tables function, open automatically--Lock-all-tables function (unless--single-is turned ontransaction)-F: Scroll before backup, after locking the table, execute the flush logs command, generate a new binary log file, with-A will cause the database to be refreshed multiple times, dump and log refresh at the same time, you should use both--flush-logs and-x,-- Master-data or-single-transaction, at which time only one is refreshed; recommendations: and-x,--master-data or--single-Transaction Use together--Compact to remove annotations, suitable for commissioning, production does not use-D: Back up the table structure only-T: Back up data only, do not back up create table-N: Do not back up create database, can be-a or-B Coverage--flush-Privileges: Refresh authorization table before backup, backup MySQL library or need to use when related-f: Ignore SQL error, continue execution--hex-blob: Dumps binary columns using hexadecimal notation (for example, "ABC" becomes 0x616263), the affected data types include binary, Varbinary,blob,bit-Q: Do not cache queries, direct output, speed up backup

MyISAM backup option: Support Win Bei, do not support hot standby, so you must first lock the library to be backed up, and then start the backup operation
- x,--lock-all-tables: Add global read lock, lock all tables of all libraries, plus --single-transaction or --lock- The tables option turns off this option feature, and note that when the data volume is large, it can cause long-time access to the database
- L,--lock-tables: For each database that needs to be backed up, lock all of its tables separately before starting the backup, by default the on ,--skip-lock-tables option can be disabled, multiple libraries MyISAM to the backup, may result in inconsistent data

InnoDB backup option: Support hot-standby, available, but not recommended
--single-transaction: This option is recommended in InnoDB, not applicable MyISAM, this option will start the backup before
Line start TRANSACTION instruction to open a transaction This option creates a consistent snapshot by storing all tables in a single transaction. Applies only to tables stored in a storage engine that supports multi-versioning (currently only innodb available); Dumps are not guaranteed to be consistent with other storage engines.

? To ensure that a valid dump file (the correct table content and binary log location) is in place for a single transaction dump, you need to ensure that no other connection uses the following statement:ALTER table,drop table,rename table,truncate TABLE

? This option and --lock-tables(this option implicitly commits the pending transactions) option is mutually exclusive when backing up large tables, it is recommended that the--single-transaction option and --quick Combined use

InnoDB Recommended backup strategy:    mysqldump–uroot–a–f–e–r  --single-transaction--master-data=1 -- Flush-privileges  --triggers--hex-blob > $BACKUP/Fullbak_$backup_time.sqlmyisam recommended backup strategy:    -- Master-data=1 --flush-privileges  --triggers--hex-blob > $BACKUP/fullbak_$backup_time.sql

xtrabackup tool : Percona provides support for InnoDB to do hot spare (physical backup) tools, support full backup, incremental backup

MySQL database backup tool provided by Percona Company, Open source can be a tool for hot spare of InnoDB and XTRADB database;

Xtrabackup is used to back up InnoDB tables and cannot back up non-InnoDB tables;

The Innobackupex script is used to back up non-InnoDB tables, calls the Xtrabackup command to back up the InnoDB table, and interacts with the MySQL Server to send commands, such as adding global read locks (FTWRL), fetching the bit points (SHOW SLAVE STATUS) and so on. That is, Innobackupex is done on the xtrabackup of a layer of encapsulation implementation;

Although the MyISAM table is generally not used at present, only the system table under the MySQL library is MyISAM, so the backup is basically through the Innobackupex command;

Xtrabackup version upgrade to 2.4, compared to the previous 2.1 has a relatively large change: Innobackupex functions are all integrated into the xtrabackup, only a binary program, in addition for compatibility considerations, Innobackupex as Xtrabackup's soft link, xtrabackup now supports non-InnoDB table backups, and Innobackupex is removed in the next release, it is recommended to replace Xtrabackup with Innobackupex.

Reference manual: Https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

  When using a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and MyISAM, Files related to MERGE, CSV, and archive tables, as well as files related to triggers and database configuration information. 
 These files are saved to a time-named directory, and Innobackupex also creates the following files in the backup directory when you back up:  1  ) Xtrabackup_checkpoints: Backup type (such as full or incremental), backup status (such as whether it is already a prepared state) and LSN (log sequence number) range information, Each InnoDB page (typically 16k size) contains a log sequence number, the LSN. The 
 LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently;  2   3  ) Xtrabackup_info:innobackupex the information about when the tool was executed;  Span style= "COLOR: #800080" >4 ) Backup-MY.CNF: Configuration option information used by the backup command;  5 ) Xtrabackup_logfile: Backs up the generated log files. 

Usage:    Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

　　

Options:--User: This option indicates the backup account--Password: This option indicates the password for the backup--Host: This option represents the address of the backup database--databases: This option takes a data name, and if you want to specify multiple databases, you need to separate them with a space, such as:"xtra_test dba_test", and when you specify a database, you can specify only one of the tables.
Such as:"mydatabase.mytable". This option is not valid for the InnoDB engine table, or it backs up all InnoDB tables--defaults-File : This option specifies from which files to read the MySQL configuration, must be placed in the command line first option location--incremental: This option means that an incremental backup is created and you need to specify--incremental-Basedir--incremental-basedir: This option is specified as the directory for the previous full or incremental backup, with--Incremental Simultaneous use--incremental-dir: This option represents a directory for incremental backups when restoring--include=Name: Specify table name, format: Databasename.tablename--apply-log: In general, after the backup is complete, the data is not yet available for recovery operations because the backed up data may contain transactions that have not been committed or have been committed but have not been synchronized to the data file.
Therefore, the data file still handles the inconsistent state at this time. This option works by rolling back uncommitted transactions and synchronizing committed transactions to data files to keep the data files in a consistent state--use-memory: This option represents and--apply-The log option is used together, prepare backup time, Xtrabackup do crash recovery allocated memory size, Unit bytes. Also available (1MB,1M,1G,1GB), recommended 1G--Export : Indicates open to export a separate table before importing into other MySQL--redo-only: This option is prepareBaseFull backup, where merge incremental backup is used--copy-Back: Copy backup data files to MySQL server datadir when doing data recovery--move-back: This option is similar to--copy-back, the only difference is that it does not copy files, but moves the files to the destination. This option removes the backup file and must be used with caution.
Usage scenario: Not enough disk space colleagues keep data files and backup copies

Attention:

1) The DataDir directory must be empty. The--copy-backup option is not overwritten unless you specify the Innobackupex--force-non-empty-directorires option;

2) before restore, the MySQL instance must be shutdown and a running instance cannot be restore to the DataDir directory;

3) Since the file attributes are retained, in most cases it is necessary to change the owner of the file to MySQL before launching the instance.chown -R mysql:mysql /data/mysqldb

Backup scheme CP + tar = = Physical Cold standby

Package A Data Catalog compressed backup, need to stop service, not recommended

? 1) Backup:

~]# mkdir/backup~]# systemctl stop mariadb #停止服务~]# tar jcf/backup/mariadb_all.tar.xz/var/ lib/mysql/ #打包压缩backup]# systemctl start mariadb

? 2) Restore:

~]# systemctl Stop mariadb~]# rm/var/lib/mysql/-rf  #将损坏的库删除 ~]# cd/backup/backup]# tar xf mariadb_all.tar.xz  #解 Compression packed database file backup]# cp-av var/lib/mysql//var/lib/#还原backup]# systemctl start mariadb #启动服务, Recovery succeeded

　

LVM Snapshot + Binlog = almost physical hot standby + Incremental backup

? 1) Backup: The database directory needs to be stored on an LVM logical volume

Preparing the LVM environment:~]# pvcreate/dev/Sda5~]# vgcreate vg0/dev/Sda5~]# Lvcreate-n Lv_data-L 10G vg0~]# Lvcreate-n Lv_binlog-L 10G vg0~]# mkfs.xfs/dev/vg0/Lv_data~]# mkfs.xfs/dev/vg0/Lv_binlog~]# mkdir-pv/data/{Mysqldb,binlog} #创建数据目录和二进制日志存放目录~]# chown-r mysql:mysql/data/~]# vim/etc/fstab UUID=4E3D726A-D420-4C1E-812B-DA315012BA86/DATA/MYSQLDB XFS Defaults0 0UUID=6dd98866-769f-4369-8738-291fbcc94ca1/data/binlog XFS Defaults0 0 

MySQL < hellodb_innodb.sql #hellodb_innodb.sql Import Database

configuration database ~]# yum install mariadb-server-y~]# vim/etc/my.cnf    [mysqld]    =/data/  MySQLdb  #指定数据库存放路径     =/data/binlog/mariadb-bin  #开启二进制日志记录 and stored to the specified path     = on  #开启每个表单独的表空间~]# systemctl start mariadb~]# MySQL  #连接数据库, here is the user name and password omitted, the following is so

start Backup: MariaDB [School]>FLUSH TABLES with READ LOCK; #备份前切记锁表 to prevent users from continuing to write mariadb [school]>FLUSH LOGS; #滚动一下二进制日志MariaDB [School]>SHOW MASTER LOGS; #查看二进制日志的位置+--------------------+-----------+| Log_name | File_size |+--------------------+-----------+| Mariadb-bin.000001|30334|| Mariadb-bin.000002|1038814|| Mariadb-bin.000003|29178309|| Mariadb-bin.000004|528|| Mariadb-bin.000005|245|#将此出记录下来, we need to use it behind us.+--------------------+-----------+~]# lvcreate-l 5g-n lv_mysql_snap-s-P r/dev/vg0/Lv_data #需要再开一个终端创建快照, do not quit MySQL terminal mariadb [school]>UNLOCK TABLES; #创建快照后第一时间解锁, beware of user complaints~]# mount-o nouuid,norecovery/dev/vg0/lv_mysql_snap/mnt/#将快照挂载到/mnt~]# cp-av/mnt//Backup #拷贝数据到备份目录~]# umount/mnt/~]# lvremove/dev/vg0/lv_mysql_snap #拷贝完成后即时删除快照, affecting server performance, to this full backup complete ~

mysql-Backup and Recovery