MYSQL/MARIADB from Delete to run-backup

Backup Policy 1, type of backup

Type 1:

• Hot backup: Read/write not affected (MyISAM does not support warm-up, INNODB support)
• Warm backup: Can only perform read operations
• Cold backup: Offline backup, read and write operations are aborted

Type 2:

• Physical Backup: Copy data files for backup, take up more space, fast
• Logical backup: Export data to a text file with little space, slow speed, and possible loss of precision

Type 3:

• Full backup: Back up all data
• Incremental backup: Backs up only data that has changed since the last full or incremental backup, faster backups, more complex restores
• Differential backup: Backs up only data that has changed since the last full backup, slow backup, simple restore

2, backup needs to consider the factors

• How long the Win Bei locks, unable to write data in the case of lock status
• Backup generated load, to tune idle time backup
• The length of the backup process, the amount of data when the time will be very long, to choose the right solution
• The duration of the recovery process, backup data needs to be tested in real time

3, the target of backup

• Database data, each table space is stored separately
• Binary logs, which need to be stored separately from the data
• Transaction log for InnoDB
• stored procedures, stored functions, triggers, or event schedulers, etc.
• Configuration file for server:/ETC/MY.CNF

4. Backup Tools

• 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.

usage :

Shell> mysqldump [Options] db_name [tbl_name ...] Shell> mysqldump [options]--databases db_name ...shell> mysqldump [options]--all-databases

Options :

-a: Back up all libraries-B db_name1,[db_name2,...] : Back up the specified library-e: Backup all event Scheduler-r: Back up all stored procedures and stored functions--triggers: Backup table related triggers, enabled by default, with--skip-triggers, do not back up triggers--master-data={1 1: The data is backed up before adding a record for the change master to statement, non-comment, do not specify the default is 1 2: Record as a note of the change MASTER to statement, note: This option will automatically turn off the--lock-tables feature, automatically open--lock -all-tables function (unless--single-transaction is turned on)-F: Log 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. Perform dump and log refreshes at the same time, you should use both--flush-logs and-x,--master-data or-single-transaction, and only one at a time; recommendations: and-x,--master-data or-- Single-transaction use--compact to remove comments, suitable for debugging, production does not use-D: Backup only table structure-T: Back up data only, do not back up create Table-n: Do not back up create DATABASE, Can be overridden by-a or-B--flush-privileges: Refreshes the authorization table before backup, backs up the MySQL library or requires the use of-F: Ignores SQL errors, and proceeds to--hex-blob: Dumps binary columns using hexadecimal notation (for example, "ABC" To 0x616263), the affected data types include binary, Varbinary,blob,bit-q: Do not cache queries, direct output, speed up backup speed 

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 a global read lock, lock all the tables of all libraries, while the Add--single-transaction or--lock-tables option turns off this option feature, note that when the data volume is large, May cause long periods of time when the database cannot be accessed concurrently
-L,--lock-tables: For each database that needs to be backed up, lock all of its tables separately before starting the backup, the default is on,--skip-lock-tables option is disabled, and 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 pending transactions) are mutually exclusive when backing up large tables, it is recommended to use the--single-transaction option together with--quick

InnoDB Recommended backup strategy:

Mysqldump–uroot–a–f–e–r  --single-transaction--master-data=1--flush-privileges  --triggers--hex-blob > $BACKUP/fullbak_$backup_time.sql

MyISAM Recommended backup strategy:

Mysqldump–uroot–a–f–e–r–x--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 tools for hot provisioning of InnoDB and XTRADB databases;

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), acquiring a bit (SHOW SLAVE STAT US) 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 you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. Files related to triggers and database configuration information are also backed up. 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 prepared status) and LSN (log sequence number) range information, each InnoDB page (usually 16k size) contains a log sequence number, That is, LSN. 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) xtrabackup_binlog_info:mysql The binary log file currently in use by the server and the location of the binary log event so far as the backup is made;

3) Xtrabackup_info:innobackupex Tools when the implementation of the relevant information;

4) BACKUP-MY.CNF: The configuration option information used by the backup command;

5) Xtrabackup_logfile: Backup the generated log file.

Usage:

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

Options:

--user: This option indicates the backup account--password: This option represents the password for the backup--host: This option represents the address of the backup database--databases: The option accepts the parameter as the data name , if you want to specify multiple databases, you need to separate spaces between them, 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 which file to read from the MySQL configuration and must be placed at the first option location in the command line--incremental: This option means creating an incremental backup, You need to specify--incremental-basedir--incremental-basedir: This option is specified as the directory for the previous full or incremental backup. Use--incremental-dir with--incremental: This option represents the directory--include=name for incremental backups when restoring: Specify the 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 data that is backed up may contain transactions that have not been committed or that 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 the data file to keep the data file in a consistent state--use-memory: This option represents the use with--APPLY-LOG option, prepare backup, Xtrabackup do crash Recovery The amount of memory allocated, in bytes. Also (1MB,1M,1G,1GB), recommended 1g--export: Indicates that a separate table can be exported and then imported into other MySQL--redo-only: This option is available at prepare base full backup, Where merge incremental backup uses--copy-back: When doing data recovery, copy the backup data file to the MySQL server's datadir--move-back: This option is similar to--copy-back, the only difference is that it does not copy files, Instead, move the file to the destination. This option removes the backup file and must be used with caution. Usage scenario: There is not enough disk space for colleagues to 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

• 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

MYSQL/MARIADB from Delete to run-backup