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

Why do backups occur in daily production environments?

   backup can enable effective disaster recovery of data in production: hardware failures, software failures, natural disasters, misoperation testing and other data loss scenarios.

Backup attention points can tolerate the maximum amount of data lost to restore data need to be completed within how long to restore which key points to restore to do the restore test, used to test the availability of backup restore exercise backup type full backup, regardless of backup full backup: partial backup of the entire dataset: only a subset of data is backed up For example, partial database or table incremental backup, differential backup incremental backup: only back up the data that has changed since the last full backup or incremental backup (if there is an incremental backup), the backup is faster, restore complex differential backup: only backup the data that has changed since the last full backup, the backup is slow, restore simple cold, warm and hot backup cold backup: read and write operations cannot be warm: read operation can be performed Write operations can not be executed hot standby: read and write operations can be performed

MyISAM: warm backup, hot backup is not supported

InnoDB: both physical and logical backups are supported physical backup: directly copy data files for backup, related to the storage engine, take up more space, fast logical backup: "export" data from the database to save the backup, independent of the storage engine, take up less space, slow speed Factors to be considered when losing precision backup: how long to hold the lock for warm backup, how long to restore the backup process, what data binary log, InnoDB transaction log program code (stored procedures, functions, triggers, event schedulers) server configuration file backup tools cp, tar and other replication and archiving tools: physical backup tools, applicable to all storage engines Only cold backup is supported; full and partial backup of LVM snapshots: lock, snapshot and then unlock, almost hot backup; backup with the help of file system tools mysqldump: logical backup tool, suitable for all storage engines, warm backup; support full or partial backup Support hot backup for InnoDB storage engine, combined with binlog's incremental backup xtrabackup: tools provided by Percona to support hot backup (physical backup) for InnoDB, support full backup and incremental backup MariaDB Backup: integrated from MariaDB 10.1.26, based on Percona

XtraBackup 2.3.8 implement mysqlbackup: hot backup, MySQL Enterprise Edition component mysqlhotcopy:PERL language implementation, almost cold backup, only suitable for MyISAM storage engine, use LOCK TABLES, FLUSH TABLES and cp or scp to quickly back up database experimental operation part of the physical backup: perform cold backup with cp and tar (suitable for businesses that can stop database service)

1. Stop the database service process on the server

[root@CentOS7 ~] # service mysqld stopStopping mysqld (via systemctl): [OK]

2. Considering the problem of space resources, pack and compress the backed up data files.

[root@CentOS7 data] # tar-zcvf mysql.tar.gz mysql/*mysql/aria_log.00000001mysql/aria_log_controlmysql/hello/mysql/hello/db.optmysql/hello/classes.frmmysql/hello/classes.ibdmysql/hello/coc.frmmysql/hello/coc.ibdmysql/hello/courses.frmmysql/hello/courses.ibdmysql/hello/scores.frm....

3. Backup binaries to the backup host

[root@CentOS7 data] # mkdir / data/bin [root@CentOS7 data] # chown-R mysql.mysql / data/bin/scp-p can retain the permissions of the file [root@CentOS7 data] # scp-p / data/bin/* 192.168.36.6:/data/bin/

4. Scp the backup data compression file to the backup host

[root@CentOS7 data] # scp-p / data/mysql.tar.gz 192.168.36.6:/data/

5. Back up the configuration file

[root@CentOS7 data] # scp-p / etc/my.cnf 192.168.36.6:/etc/

6. Start the service

[root@CentOS7 data] # service mysqld startStarting mysqld (via systemctl): [OK]

7. Stop the service and delete data

[root@CentOS7 data] # service mysqld stopStopping mysqld (via systemctl): [OK] [root@CentOS7 data] # rm-rf / data/mysql/*

8. Extract the backed-up data files to the / data/mysql directory

[root@CentOS7 data] # gzip-d mysql.tar.gz [root@CentOS7 data] # tar-xvf mysql.tar-C. /

9. Start service testing and check data

[root@CentOS7 data] # service mysqld startStarting mysqld (via systemctl): [OK] MariaDB [(none)] > show databases +-+ | Database | +-+ | hello | | information_schema | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.01sec) MariaDB [(none)] > use hello;Database changedMariaDB [hello] > show tables +-+ | Tables_in_hello | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in set (0.00 sec) MariaDB [hello] > select * from students +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | + -+-+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Xie Yanke | 53 | M | 2 | 16 | 4 | Ding Dian | 32 | M | 4 | 4 | 5 | Yu Yutong | 26 | M | 3 | 1 | 6 | Shi Qing | 46 | M | 5 | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 13 | Tian Boguang | 33 | M | 2 | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | 15 | Duan Yu | 19 | M | | | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | 24 | Xu Xian | 27 | M | NULL | NULL | 25 | Sun Dasheng | 100 | M | NULL | NULL | + -+-+ 25 rows in set (0.00 sec) LVM-based backup

1. Request to lock all tables

Mysql > FLUSH TABLES WITH READ LOCK

2. Record binary log files and event locations

Mysql > FLUSH LOGS;mysql > SHOW MASTER STATUS;mysql-e'SHOW MASTER STATUS' > / PATH/TO/SOMEFILE

3. Create a snapshot

Lvcreate-L #-s-p r-n NAME/ DEV/VG_NAME/LV_NAME

4. Release the lock

Mysql > UNLOCK TABLES; mount the snapshot volume. After the backup of the data is completed, delete the snapshot volume and make a good policy for backing up the binary log through the original volume. Note: the premise of this experiment is that the data file is in the LVM logical volume to backup mysqldump common options based on mysqldump tool.

  -A,-- all-databases back up all databases, including create database

  -B,-- databases db_name... Specify the database to back up, including create database statements

  -E,-- events: backup all related event scheduler

  -R,-- routines: back up all stored procedures and custom functions

  -- triggers: backup table-related triggers. Enable by default. Use-- skip-triggers. Do not back up triggers.

  -- default-character-set=utf8 specified character set

  -- master-data [= #]: binary logging must be enabled for this option

     1: the backed-up data is preceded by a CHANGE MASTER TO statement, which is non-annotated and does not specify #. The default is 1.

     2: CHANGE MASTER TO statements recorded as comments this option automatically turns off-- lock-tables function and automatically turns on-x |-- lock-all-tables function (unless-- single-transaction is enabled)

  -F,-- flush-logs: scroll the log before backup, and after locking the table, execute the flush logs command to generate a new binary log file, which will cause the database to be refreshed multiple times with the-An or-B option. It is recommended to perform dump and log refresh at the same time, which can be achieved by using it together with-- single-transaction or-x _ mastering _ mastering _ data. In this case, the log is refreshed only once.

  -compact without comments, suitable for debugging, not used in production

  -d,-- no-data backup table structure only

  -t,-- no-create-info only backs up data, not create table

  -n horse talk create create database db does not back up and can be overwritten by-An or-B

  -flush-privileges needs to be used to back up mysql or related

  -f,-- force ignores SQL error and continues execution

  -hex-blob uses hexadecimal symbols to dump binary columns, when there are columns of data types including BINARY,VARBINARY,BLOB,BIT, to avoid garbled

  -Q,-- quick does not cache queries and outputs directly to speed up backup

Backup of a single database in mysqldump experiment

1. Use mysqldump to generate backup files.

[root@CentOS7 data] # mysqldump hello > / data/backup/hello.sql

2. Realize the function of deleting the database and running away.

[root@CentOS7 data] # mysql-e 'drop database hello'

3. Create a database to realize data recovery.

[root@CentOS7 data] # mysql-e'create database hello' [root@CentOS7 data] # mysql hello show databases +-+ | Database | +-+ | hello | | information_schema | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > use hello;Database changedMariaDB [hello] > show tables +-+ | Tables_in_hello | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in set (0.00 sec) Note: mysqldump hello command It can list the data table structure and character set of database hello. The backup and recovery of the data can be completed by using these data, but the defect is that there is no function to create the database, and the database name needs to be created manually. Therefore, there is no guarantee that the created database name is consistent with the previous database name. The solution to the above defects is to use the-B parameter (- B is equivalent to-- databases) to back up the definition of the database. It is recommended to use [root@CentOS7 data] # mysqldump-B hello > / data/backup/hello_ bak.sql [root @ CentOS7 data] # grep'^ CREATE DATABASE' / data/backup/hello_bak.sqlCREATE DATABASE /! 32312 IF NOT EXISTS*/ `hello` / *! 40100 DEFAULT CHARACTER SET latin1 * / Backup of tables in the database

The backup function of the specified table in the database can be realized by mysqldump.

1. Back up the data of students table in hello library

[root@CentOS7 data] # mysqldump hello students > / data/backup/students.sql View backup data [root@CentOS7 data] # cat / data/backup/students.sql-- MySQL dump 10.16 Distrib 10.2.23-MariaDB For Linux (x86 / 64)-Host: localhost Database: hello-- Server version 10.2.23 Muhammad MariaDB @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * / / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /;-Table structure for table `students`-- DROP TABLE IF EXISTS `students`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * / / *! 40101 SET character_set_client = utf8 * /; CREATE TABLE `students` (`StuID` int (10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar (50) NOT NULL, `Age`tinyint (3) unsigned NOT NULL, `Gender` enum ('Fidd`M') NOT NULL, `ClassID` tinyint (3) unsigned DEFAULT NULL, `TeacherID` int (10) unsigned DEFAULT NULL, PRIMARY KEY (`StuID`) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSETconversation utf8bot head 40101 SET character_set_client = @ saved_cs_client * / -Dumping data for table `students`-- LOCK TABLES `students`WRITE`WRITETrached40000 ALTER TABLE `students` DISABLE KEYS * / INSERT INTO `students` VALUES (1djinshi Zhongyu',22,'M',2,3), (2meme Xie Yanke',53,'M',2,16), (4meme Xie Dian',32,'M',4,4), (5memorie Yu Yutong',26,'M',3,1), (6djinshi Qing',46,'M',5,NULL), (7jingshi Ren',19,'F',3,NULL), (8) 'Lin Daiyu',17,'F',7,NULL), (9), (10), (10), (11), (11), (11), (12), (13), (13), (13), (13), (14, Wushuang',17,'F',3,NULL, Lu Wushuang',17,'F',3,NULL) (15 Yu',19,'M',4,NULL Duan Baochai',18,'F',6,NULL), (16 Yu',19,'M',4,NULL Xu Yueying',22,'F',6), (17 Rong',23,'M',7,NULL Rong',23,'M',7,NULL), (19 Baochai',18,'F',6,NULL Baochai',18,'F',6,NULL), (21 Yueying',22,'F',6) NULL), (22 Qiao',20,'F',1,NULL), (23 Chao',23,'M',4,NULL), (24 Xian',27,'M',NULL,NULL), (25 Dasheng',100,'M',NULL,NULL) / *! 40000 ALTER TABLE `students`students` ENABLE KEYS * /; UNLOCK TABLESTABLESTABLES TABLESTABLESShield / 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /; / *! 40101 SET SQL_MODE=@OLD_SQL_MODE * /; / *! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /; / *! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /; / *! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /; / *! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * / / *! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /; / *! 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /;-- Dump completed on 2019-05-06 15:22:32

2. Delete the students table in the database

[root@CentOS7 data] # mysql-e 'drop tables hello.students'

3. Data recovery

MariaDB [hello] > show tables +-+ | Tables_in_hello | +-+ | classes | | coc | | courses | | scores | | teachers | | toc | +-+ 6 rows in set (0.00 sec) MariaDB [hello] > source / data/backup/students.sqlQuery OK 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec).... MariaDB [hello] > show tables +-+ | Tables_in_hello | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in set (0.00 sec) backup all data in the database

1. Use mysqldump-A function to back up all the data in the database.

[root@CentOS7 data] # grep'^ CREATE DATABASE' / data/backup/all.sqlCREATE DATABASE / *! 32312 IF NOT EXISTS*/ `hello` / *! 40100 DEFAULT CHARACTER SET latin1 * /; CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `mysql` / *! 40100 DEFAULT CHARACTER SET latin1 * /; CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `test` / *! 40100 DEFAULT CHARACTE

2. Clear the database data

[root@CentOS7 data] # rm-rf / data/mysql/*

3. Restart the service and restore the data

[root@CentOS7 data] # service mysqld restartRestarting mysqld (via systemctl): [OK] [root@CentOS7 data] # mysql

< /data/backup/all.sql[root@CentOS7 data]#ll /data/mysql/total 110620-rw-rw---- 1 mysql mysql 16384 May 6 15:41 aria_log.00000001-rw-rw---- 1 mysql mysql 52 May 6 15:41 aria_log_controldrwx------ 2 mysql mysql 272 May 6 15:41 hello-rw-rw---- 1 mysql mysql 860 May 6 15:41 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 May 6 15:41 ibdata1-rw-rw---- 1 mysql mysql 50331648 May 6 15:41 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 May 6 15:41 ib_logfile1drwx------ 2 mysql mysql 4096 May 6 15:41 mysqldrwx------ 2 mysql mysql 20 May 6 15:41 test关于mysqldump的扩展MyISAM备份选项：   支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作   锁定方法如下：     -x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--singletransaction或--lock-tables选项会关闭此选项功能     注意：数据量大时，可能会导致长时间无法并发访问数据库     -l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能 会造成数据不一致   注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用 InnoDB备份选项：   支持热备，可用温备但不建议用   --single-transaction     此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务     此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE     此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥     备份大型表时，建议将--single-transaction选项和--quick结合一起使用 InnoDB建议备份策略mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >

$BACKUP/fullbak_$BACKUP_TIME.sqlMyISAM recommends backup policy mysqldump-uroot-A-F-E-R-x-master-data=1-- flush-privileges-- triggers-- default-character-set=utf8-- hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql due to the backup of data by a company at a specified time, but the data is modified after the backup, and the data is lost due to failure. Please try to restore to the latest data.

1. Back up and compress the data

[root@CentOS7 data] # mysqldump-A-single-transaction-- master-data=2 | xz > / data/all.sql.xz [root@CentOS7 data] # ll / data/all.sql.xz-rw-r--r-- 1 root root 105104 May 6 16:42 / data/all.sql.xz

2. Add data

MariaDB [hello] > insert students (name,age) values ('Darius',23); Query OK, 1 row affected (0.03 sec) MariaDB [hello] > select * from students +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | + -+-+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 |.... | 26 | Darius | 23 | F | NULL | NULL | +- -+ 26 rows in set (0.00 sec)

3. Delete the library

[root@CentOS7 data] # rm-rf mysql/* [root@CentOS7 data] # ll mysql/total 0

4. Restart the service

[root@CentOS7 data] # service mysqld restart

5. The binary log is temporarily closed because there is no need for binary log changes.

MariaDB [(none)] > set sql_log_bin=off;Query OK, 0 rows affected (0.00 sec)

6. Restore the database and restore the full backup first

Decompress [root@CentOS7 data] # xz-d all.sql.xz to restore full backup data [root@CentOS7 data] # mysql show databases +-+ | Database | +-+ | hello | | information_schema | | mysql | | test | +-+ 4 rows in set (0.00 sec) MariaDB [(none)] > use helloDatabase changedMariaDB [hello] > show tables +-+ | Tables_in_hello | +-+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-+ 7 rows in set (0.00 sec) > > at this time, the data after backup has not been restored.

7. View the location of the binaries during the full backup

[root@CentOS7 data] # vim all.sql....-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=4882960;....

8. View the location of the binary log at the time of the full backup, and export the log from this location to inc.sql

[root@CentOS7 data] # mysqlbinlog-- start-position=4882960 / data/bin/mysql-bin.000005 > / data/inc.sql

9. Import data

MariaDB [mysql] > source / data/inc.sql; enable binary log MariaDB [(none)] > set sql_log_bin=on;Query OK, 0 rows affected (0.00 sec)

10. Successfully recover the data

MariaDB [hello] > select * from students +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | + -+-+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 |.... | 26 | Darius | 23 | F | NULL | NULL | +- -+ 26 rows in set (0.00 sec) introduction to full backup and restore based on xtrabackup tool

The mysql database backup tool provided by    percona, the only open source tool capable of hot standby for innodb and xtradb databases

The backup and restore process is fast and reliable. The backup process does not interrupt ongoing transactions. It can save disk space and traffic based on functions such as compression, and automatically realize backup verification and open source. Free information about xtrabackup

   xtrabackup is used to back up InnoDB tables. Non-InnoDB tables cannot be backed up. There is no interaction with MySQL Server.

   innobackupex scripts are used to back up non-InnoDB tables, invoke xtrabackup commands to back up InnoDB tables, and interact with commands sent by MySQL Server, such as adding global read locks (FTWRL), acquiring points (SHOW SLAVE STATUS), and so on. That is, innobackupex is implemented with a layer of encapsulation on top of xtrabackup.

Xtrabackup backup proc

Changes in the new version of xtrabackup

After the xtrabackup version is upgraded to 2.4, there is a big change compared with the previous 2.1. all the functions of innobackupex are integrated into xtrabackup, and there is only one binary program. In addition, for the sake of compatibility, innobackupex as the soft link of xtrabackup, that is, xtrabackup now supports non-Innodb table backup, and Innobackupex is removed in the next version. It is recommended to replace innobackupex through xtrabackup.

Xtrabackup install xtrabackup tool in EPEL, you need to download through the EPEL source [root@CentOS7 ~] # yum install percona-xtrabackup download the latest version of https://www.percona.com/downloads/XtraBackup/LATEST/ through the official website, sweet people, give you Aliyun's EPEL source [epel] name=epelbaseurl= http://mirrors.aliyun.com/epel/7/x86_64gpgcheck=0xtrabackup usage

   backup: innobackupex [option] BACKUP-ROOT-DIR

   option description: https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html

     -- user: this option indicates the backup account

     -- password: this option indicates the backup password

     -- host: this option indicates the address of the backup database

     -- databases: the parameter accepted by this option is the database name. If you want to specify multiple databases, they need to be separated by spaces, such as "xtra_test dba_test". At the same time, when you specify a database, you can specify only one of the tables. Such as "mydatabase.mytable". This option is not valid for innodb engine tables, or will all innodb tables be backed up

     -- defaults-file: this option specifies which file to read the MySQL configuration from, and must be placed in the first option location on the command line

     -- incremental: this option means to create an incremental backup. You need to specify-- incremental-basedir.

     -- incremental-basedir: this option specifies the directory of the previous full or incremental backup and is used in conjunction with-- incremental

     -- incremental-dir: this option indicates the directory of the incremental backup at restore time

     -- include=name: specifies the table name, format: databasename.tablename

   Prepare:innobackupex-- apply-log [option] BACKUP-DIR

The    option description:

  -- apply-log: in general, after the backup is completed, the data cannot be used for the restore operation because the backup is available.

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 still handles the inconsistent state at this time. This option makes the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file

  -- use-memory: used together with-- apply-log option. When prepare is backed up, the memory allocated by crash recovery (in bytes) can also be 1MB, 1m, 1GB, 1GB, etc. 1G is recommended.

  -- export: indicates that you can export separate tables and then import them into other Mysql.

  -- redo-only: this option is used when merging incremental backups into prepare base full backup, but does not include merging the last incremental backup

   restore: innobackupex-- copy-back [option] BACKUP-DIR

   innobackupex-- move-back [options] [--defaults-group=GROUP-NAME] BACKUP-DIR

The    option description:

  -- copy-back: datadir that copies backup data files to the MySQL server during data recovery

  -- move-back: this option is similar to-- copy-back, except that it does not copy files, but moves them to their destination. This option removes backup files and must be used with care. Usage scenario: there is not enough disk space for colleagues to keep data files and Backup copies

Considerations for    restore:

The       1.datadir directory must be empty. Unless specified by the innobackupex-- force-non-emptydirectorires option, the-- copy-backup option does not override

two。 Before restore, you must shutdown MySQL the instance. You cannot restore a running instance to the datadir directory.

3. Since file attributes are retained, in most cases you need to change the owner of the file to mysql before starting the instance, and these files will belong to the user who created the backup

   chown-R mysql:mysql / data/mysql

The above    needs to be completed before the user calls innobackupex.

  -- force-non-empty-directories: when this parameter is specified, the innobackupex-- copy-back or-- move-back option transfers the file to a non-empty directory, and existing files will not be overwritten. If the-- copy-back and-- move-back files need to copy a file that already exists in datadir from the backup directory, an error will fail.

Back up the relevant files generated

When using innobackupex backup, it invokes xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the table structure definition, and related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory named after time, and during backup, innobackupex also creates the following files in the backup directory:

Information about the execution of the    (1) xtrabackup_info:innobackupex tool, including version, backup options, backup duration, backup LSN (log sequence number log serial number), location of BINLOG

   (2) xtrabackup_checkpoints: backup type (such as full or incremental), backup status (such as whether it is already prepared status), and LSN scope information, each InnoDB page (usually 16k size) contains a log sequence number LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can show how the page has changed recently.

   (3) the binary log files currently in use by the xtrabackup_binlog_info:MySQL server and the location of the binary log events up to the moment of backup can be used to achieve binlog-based recovery.

   (4) backup-my.cnf: configuration option information used by backup commands

   (5) xtrabackup_logfile: back up the generated log files

Backup and restore using older versions of xtrabackup tools

1. On the original host

Innobackupex-- user=root / backupscp-r / backup/2018-02-23 11-55-57 / Target host: / data/

2. On the target host

Innobackupex-- apply-log / data/2019-05-06-20-34-35/systemctl stop mariadbrm-rf / var/lib/mysql/*innobackupex-- copy-back / data/2019-05-06-20-34-35/chown-R mysql.mysql / var/lib/mysql/systemctl start mariadb using the new version of xtrabackup tool for backup and restore

1. Make a full backup to / backups on the original host

Xtrabackup-backup-target-dir=/backup/scp-r / backup/* target host: / backup

2. On the target host

   1) preparation: ensure data consistency, commit completed transactions, and roll back outstanding transactions

Xtrabackup-prepare-target-dir=/backup/

   2) copy to the database directory

     Note: database directory must be empty and MySQL service cannot be started

Xtrabackup-copy-back-target-dir=/backup/

   3) restore attributes

Chown-R mysql:mysql / var/lib/mysql

   4) start the service

Systemctl start mariadb previous version of xtrabackup full, incremental backup and restore

   1, on the original host

Innobackupex / backupmkdir / backup/inc {1,2}

   modifies database content

Innobackupex-- incremental / backup/inc1-- incrementalbasedir=/backups/2019-05-06 backup 20-34-35 (path generated by full backup)

   modifies the database content again

Innobackupex-- incremental / backup/inc2-- incrementalbasedir=/backup/inc1/2019-05-06. 20-34-35 (path generated by the last incremental backup) scp-r / backup/* destination host: / data/

   2, on the target host

Do not start mariadbrm-rf / var/lib/mysql/*innobackupex-- apply-log-- redo-only / data/2019-05-06-20-34-35/innobackupex-- apply-log-- redo-only / data/2019-05-06-20-34-35 /-- incremental-dir=/data/inc1/2018-02-23-14-26-17innobackupex-apply-log / data/2019-05-06 20-34-35 /-- incrementaldir=/data/inc2/2018-02 -23mm 14-28-29/innobackupex-- copy-back / data/2019-05-06mm 20-34-35/chown-R mysql.mysql / var/lib/mysql/systemctl start mariadb New Edition xtrabackup complete Incremental backup and restore

1. Backup process

   1) full backup:

Xtrabackup-backup-target-dir=/backup/base

   2) modify the data for the first time

   3) first incremental backup

Xtrabackup-backup-target-dir=/backup/inc1-incrementalbasedir=/backup/base

   4) modify data for the second time

   5) second increment

Xtrabackup-backup-target-dir=/backup/inc2-incrementalbasedir=/backup/inc1

   6) scp to the target host

Scp-r / backup/* target host: / backup/

The    backup process generates three backup directories

/ backup/ {base,inc1,inc2}

two。 Reduction process

   1) prepares to complete the backup. This option-- apply-log-only prevents the rollback of outstanding transactions

Xtrabackup-prepare-apply-log-only-target-dir=/backup/base

   2) merge the first incremental backup to a full backup

Xtrabackup-prepare-apply-log-only-target-dir=/backup/base-incremental-dir=/backup/inc1

   3) merge the second incremental backup to the full backup: no option is required for the last restore-- apply-log-only

Xtrabackup-prepare-target-dir=/backup/base-incremental-dir=/backup/inc2

   4) copy to the database directory, note that the database directory must be empty and the MySQL service cannot be started

Xtrabackup-copy-back-target-dir=/backup/base

   5) restore attributes

Chown-R mysql:mysql / var/lib/mysql

   6) start the service

Systemctl start mariadbxtrabackup single table import and export

1. Single table backup

Innobackupex-include='hellodb.students' / backups

2. Backup list structure

Mysql-e 'show create table hellodb.students' > student.sql

3. Delete the table

Mysql-e'drop table hellodb.students'

4 、

Innobackupex-- apply-log-- export / backups/2019-05-06 20-34-35 /

5. Create a table

Mysql > CREATE TABLE `students` (`StuID` int (10) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar (50) NOT NULL, `Age` tinyint (3) unsigned NOT NULL, `Gender` enum ('Fiddler M') NOT NULL, `ClassID` tinyint (3) unsigned DEFAULT NULL, `TeacherID` int (10) unsigned DEFAULT NULL,PRIMARY KEY (`StuID`) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

6. Delete tablespace

Alter table students discard tablespace

7. Copy

Cp / backups/2019-05-06. 20-34-35/hellodb/students. {cfg,exp,ibd} / var/lib/mysql/hellodb/

8. Add group permissions

Chown-R mysql.mysql / var/lib/mysql/hellodb/

9 、

Mysql > alter table students import tablespace

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.