MYSQL xtrabackup 增量备份
祖仙教小凡仙 海鲨数据库架构师
Mysql 物理备份Xtrabackup
新的xtrabackup命令 8.0 跟过去的innobackupex 命令很大的不同,网上一堆关于INNOBACKUP的参数不适合于XTRABACKUP 先搞个增量表
mysql> show databases; +--------------------+ | Database | +--------------------+ | bookstore | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.02 sec) mysql> use bookstore; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables_in_bookstore | +---------------------+ | books | | books2 | +---------------------+ 2 rows in set (0.00 sec) mysql> create table books3 as select * from books; Query OK, 6 rows affected (0.13 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from books3; +----+------------------------+-------+---------------------+ | id | title | price | publishDate | +----+------------------------+-------+---------------------+ | 1 | Java编程思想 | 98.50 | 2005-01-02 0 | | 2 | HeadFirst设计模式 | 55.70 | 2010-11-09 0 | | 3 | 第一行Android代码 | 69.90 | 2015-06-23 0 | | 4 | C++编程思想 | 88.50 | 2004-01-09 0 | | 5 | HeadFirst Java | 55.70 | 2013-12-17 0 | | 6 | 疯狂Android | 19.50 | 2014-07-31 0 | +----+------------------------+-------+---------------------+ 6 rows in set (0.00 sec)
增量备份
xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=123456 --backup --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup
--backup --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup 第一个是备份的目标目录,第二个是备份的基础目录,从这个目录获得起点LSN
备份过程信息如下:
[root@localhost backup_increment]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=123456 --backup --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --user=root --password=* --backup=1 --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210128 23:12:12 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). 210128 23:12:12 version_check Connected to MySQL server 210128 23:12:12 version_check Executing a version check against the server... 210128 23:12:12 version_check Done. 210128 23:12:12 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock Using server version 8.0.20-debug incremental backup from 18216058 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /u01/mysql/mysql8020debug/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 Number of pools: 1 210128 23:12:12 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock xtrabackup: Redo Log Archiving is not set up. 210128 23:12:12 >> log scanned up to (18264119) xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. xtrabackup: using the full scan for incremental backup 210128 23:12:12 [01] Copying ./ibdata1 to /u01/mysql/mysql8020debug/backup_increment/ibdata1.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./sys/sys_config.ibd to /u01/mysql/mysql8020debug/backup_increment/sys/sys_config.ibd.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./bookstore/books.ibd to /u01/mysql/mysql8020debug/backup_increment/bookstore/books.ibd.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./bookstore/books2.ibd to /u01/mysql/mysql8020debug/backup_increment/bookstore/books2.ibd.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./bookstore/books3.ibd to /u01/mysql/mysql8020debug/backup_increment/bookstore/books3.ibd.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./mysql.ibd to /u01/mysql/mysql8020debug/backup_increment/mysql.ibd.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./undo_002 to /u01/mysql/mysql8020debug/backup_increment/undo_002.delta 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying ./undo_001 to /u01/mysql/mysql8020debug/backup_increment/undo_001.delta 210128 23:12:13 [01] ...done 210128 23:12:13 >> log scanned up to (18264119) 210128 23:12:13 Starting to backup non-InnoDB tables and files 210128 23:12:13 [01] Copying mysql/slow_log_203.sdi to /u01/mysql/mysql8020debug/backup_increment/mysql/slow_log_203.sdi 210128 23:12:13 [01] ...done 210128 23:12:13 [01] Copying mysql/slow_log.CSM to /u01/mysql/mysql8020debug/backup_increment/mysql/slow_log.CSM 210128 23:12:13 [01] ...done 210128 23:12:14 [01] Copying performance_schema/events_statement_113.sdi to /u01/mysql/mysql8020debug/backup_increment/performance_schema/events_statement_113.sdi 210128 23:12:14 >> log scanned up to (18264119) 210128 23:12:14 [01] ...done 210128 23:12:14 [01] Copying performance_schema/events_transacti_129.sdi to /u01/mysql/mysql8020debug/backup_increment/performance_schema/events_transacti_129.sdi 210128 23:12:14 [01] ...done 210128 23:12:14 [01] Copying performance_schema/setup_consumers_96.sdi to /u01/mysql/mysql8020debug/backup_increment/performance_schema/setup_consumers_96.sdi 210128 23:12:14 [01] ...done 210128 23:12:14 Finished backing up non-InnoDB tables and files 210128 23:12:14 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 210128 23:12:14 Selecting LSN and binary log position from p_s.log_status 210128 23:12:14 [00] Copying /u01/mysql/mysql8020debug/data/binlog.000030 to /u01/mysql/mysql8020debug/backup_increment/binlog.000030 up to position 156 210128 23:12:14 [00] ...done 210128 23:12:14 [00] Writing /u01/mysql/mysql8020debug/backup_increment/binlog.index 210128 23:12:14 [00] ...done 210128 23:12:14 [00] Writing /u01/mysql/mysql8020debug/backup_increment/xtrabackup_binlog_info 210128 23:12:14 [00] ...done 210128 23:12:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '18264119' xtrabackup: Stopping log copying thread at LSN 18264129. 210128 23:12:14 >> log scanned up to (18264149) Starting to parse redo log at lsn = 18264109 210128 23:12:15 All tables unlocked 210128 23:12:15 [00] Copying ib_buffer_pool to /u01/mysql/mysql8020debug/backup_increment/ib_buffer_pool 210128 23:12:15 [00] ...done 210128 23:12:15 Backup created in directory '/u01/mysql/mysql8020debug/backup_increment/' MySQL binlog position: filename 'binlog.000030', position '156' 210128 23:12:15 [00] Writing /u01/mysql/mysql8020debug/backup_increment/backup-my.cnf 210128 23:12:15 [00] ...done 210128 23:12:15 [00] Writing /u01/mysql/mysql8020debug/backup_increment/xtrabackup_info 210128 23:12:15 [00] ...done xtrabackup: Transaction log of lsn (18264119) to (18264149) was copied. 210128 23:12:17 completed OK!
看来并不是从TO_LSN开始的, 这点有些奇怪了
[root@localhost backup]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 18216058 last_lsn = 18216078 flushed_lsn = 0
[root@localhost mysql8020debug]# cd backup_increment/ [root@localhost backup_increment]# ll 总用量 1236 -rw-r----- 1 root root 475 1月 28 23:12 backup-my.cnf -rw-r----- 1 root root 156 1月 28 23:12 binlog.000030 -rw-r----- 1 root root 16 1月 28 23:12 binlog.index drwxr-x--- 2 root root 4096 1月 28 23:12 bookstore -rw-r----- 1 root root 3621 1月 28 23:12 ib_buffer_pool -rw-r----- 1 root root 49152 1月 28 23:12 ibdata1.delta -rw-r----- 1 root root 64 1月 28 23:12 ibdata1.meta drwxr-x--- 2 root root 4096 1月 28 23:12 mysql -rw-r----- 1 root root 638976 1月 28 23:12 mysql.ibd.delta -rw-r----- 1 root root 73 1月 28 23:12 mysql.ibd.meta drwxr-x--- 2 root root 4096 1月 28 23:12 performance_schema drwxr-x--- 2 root root 4096 1月 28 23:12 sys -rw-r----- 1 root root 311296 1月 28 23:12 undo_001.delta -rw-r----- 1 root root 69 1月 28 23:12 undo_001.meta -rw-r----- 1 root root 196608 1月 28 23:12 undo_002.delta -rw-r----- 1 root root 69 1月 28 23:12 undo_002.meta -rw-r----- 1 root root 18 1月 28 23:12 xtrabackup_binlog_info -rw-r----- 1 root root 100 1月 28 23:12 xtrabackup_checkpoints -rw-r----- 1 root root 619 1月 28 23:12 xtrabackup_info -rw-r----- 1 root root 2560 1月 28 23:12 xtrabackup_logfile -rw-r----- 1 root root 39 1月 28 23:12 xtrabackup_tablespaces
和全量备份文件差不多,在首层上多了DELTA META结尾的文件
[root@localhost backup_increment]# cd bookstore/ [root@localhost bookstore]# ll 总用量 140 -rw-r----- 1 root root 16384 1月 28 23:12 books2.ibd.delta -rw-r----- 1 root root 64 1月 28 23:12 books2.ibd.meta -rw-r----- 1 root root 98304 1月 28 23:12 books3.ibd.delta -rw-r----- 1 root root 64 1月 28 23:12 books3.ibd.meta -rw-r----- 1 root root 16384 1月 28 23:12 books.ibd.delta -rw-r----- 1 root root 64 1月 28 23:12 books.ibd.meta
确实增加了BOOKS3文件
查看备份信息
[root@localhost backup_increment]# cat xtrabackup_binlog_info binlog.000030 156 [root@localhost backup_increment]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 18216058 to_lsn = 18264119 last_lsn = 18264129 flushed_lsn = 0 [root@localhost backup_increment]# cat xtrabackup_info uuid = 34ba8067-617b-11eb-b56a-080027d6e4bc name = tool_name = xtrabackup tool_command = --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=... --backup --target-dir=/u01/mysql/mysql8020debug/backup_increment --incremental-basedir=/u01/mysql/mysql8020debug/backup tool_version = 8.0.14 ibbackup_version = 8.0.14 server_version = 8.0.20-debug start_time = 2021-01-28 23:12:12 end_time = 2021-01-28 23:12:15 lock_time = 2 binlog_pos = filename 'binlog.000030', position '156' innodb_from_lsn = 18216058 innodb_to_lsn = 18264119 partial = N incremental = Y format = file compressed = N encrypted = N
哎这里还是从全备的TO_LSN开始 结束点是18264119,看来没有出乱子.
再来第二次增量备份
mysql> create table books4 as select * from books3; Query OK, 6 rows affected (0.46 sec) Records: 6 Duplicates: 0 Warnings: 0
备份参数的目录要互换一下,原来的增量备份目录当做增量基本目录 --backup --target-dir=/u01/mysql/mysql8020debug/backup_incr2 --incremental-basedir=/u01/mysql/mysql8020debug/backup_increment 红色 的LSN表示找到了起点LSN,黄色18284362是本次扫描到的LSN,也就是终点LSN
[root@localhost mysql8020debug]# mkdir /u01/mysql/mysql8020debug/backup_incr2 [root@localhost mysql8020debug]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=123456 --backup --target-dir=/u01/mysql/mysql8020debug/backup_incr2 --incremental-basedir=/u01/mysql/mysql8020debug/backup_increment xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --user=root --password=* --backup=1 --target-dir=/u01/mysql/mysql8020debug/backup_incr2 --incremental-basedir=/u01/mysql/mysql8020debug/backup_increment xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7) 210128 23:38:59 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES). 210128 23:38:59 version_check Connected to MySQL server 210128 23:38:59 version_check Executing a version check against the server... 210128 23:38:59 version_check Done. 210128 23:38:59 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock Using server version 8.0.20-debug incremental backup from 18264119 is enabled. xtrabackup: uses posix_fadvise(). xtrabackup: cd to /u01/mysql/mysql8020debug/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 50331648 Number of pools: 1 210128 23:38:59 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock xtrabackup: Redo Log Archiving is not set up. 210128 23:38:59 >> log scanned up to (18284362) xtrabackup: Generating a list of tablespaces xtrabackup: Generating a list of tablespaces Scanning './' Completed space ID check of 2 files. Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 Using undo tablespace './undo_001'. Using undo tablespace './undo_002'. Opened 2 existing undo tablespaces. xtrabackup: using the full scan for incremental backup 210128 23:38:59 [01] Copying ./ibdata1 to /u01/mysql/mysql8020debug/backup_incr2/ibdata1.delta 210128 23:38:59 [01] ...done 210128 23:39:00 [01] Copying ./mysql.ibd to /u01/mysql/mysql8020debug/backup_incr2/mysql.ibd.delta 210128 23:39:00 >> log scanned up to (18284362) 210128 23:39:00 [01] ...done 210128 23:39:00 [01] Copying ./undo_002 to /u01/mysql/mysql8020debug/backup_incr2/undo_002.delta 210128 23:39:00 [01] ...done 210128 23:39:00 [01] Copying ./undo_001 to /u01/mysql/mysql8020debug/backup_incr2/undo_001.delta 210128 23:39:01 [01] ...done 210128 23:39:01 >> log scanned up to (18284362) 210128 23:39:01 Starting to backup non-InnoDB tables and files 210128 23:39:01 [01] Copying mysql/slow_log_203.sdi to /u01/mysql/mysql8020debug/backup_incr2/mysql/slow_log_203.sdi 210128 23:39:01 [01] ...done 210128 23:39:02 Finished backing up non-InnoDB tables and files 210128 23:39:02 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 210128 23:39:02 Selecting LSN and binary log position from p_s.log_status 210128 23:39:02 [00] Copying /u01/mysql/mysql8020debug/data/binlog.000031 to /u01/mysql/mysql8020debug/backup_incr2/binlog.000031 up to position 156 210128 23:39:02 [00] ...done 210128 23:39:02 [00] Writing /u01/mysql/mysql8020debug/backup_incr2/binlog.index 210128 23:39:02 [00] ...done 210128 23:39:02 [00] Writing /u01/mysql/mysql8020debug/backup_incr2/xtrabackup_binlog_info 210128 23:39:02 [00] ...done 210128 23:39:02 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '18284362' xtrabackup: Stopping log copying thread at LSN 18284372. 210128 23:39:02 >> log scanned up to (18284382) Starting to parse redo log at lsn = 18284082 210128 23:39:03 All tables unlocked 210128 23:39:03 [00] Copying ib_buffer_pool to /u01/mysql/mysql8020debug/backup_incr2/ib_buffer_pool 210128 23:39:03 [00] ...done 210128 23:39:03 Backup created in directory '/u01/mysql/mysql8020debug/backup_incr2/' MySQL binlog position: filename 'binlog.000031', position '156' 210128 23:39:03 [00] Writing /u01/mysql/mysql8020debug/backup_incr2/backup-my.cnf 210128 23:39:03 [00] ...done 210128 23:39:03 [00] Writing /u01/mysql/mysql8020debug/backup_incr2/xtrabackup_info 210128 23:39:03 [00] ...done xtrabackup: Transaction log of lsn (18284362) to (18284392) was copied. 210128 23:39:04 completed OK!
看第二次的备份信息
[root@localhost backup_incr2]# cat xtrabackup_info uuid = f2fecd71-617e-11eb-b56a-080027d6e4bc name = tool_name = xtrabackup tool_command = --defaults-file=/u01/mysql/mysql8020debug/my.cnf --user=root --password=... --backup --target-dir=/u01/mysql/mysql8020debug/backup_incr2 --incremental-basedir=/u01/mysql/mysql8020debug/backup_increment tool_version = 8.0.14 ibbackup_version = 8.0.14 server_version = 8.0.20-debug start_time = 2021-01-28 23:38:59 end_time = 2021-01-28 23:39:03 lock_time = 2 binlog_pos = filename 'binlog.000031', position '156' innodb_from_lsn = 18264119 innodb_to_lsn = 18284362 partial = N incremental = Y format = file compressed = N encrypted = N [root@localhost backup_incr2]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 18264119 to_lsn = 18284362 last_lsn = 18284372 flushed_lsn = 0
查看更多关于MYSQL xtrabackup 增量备份的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did127965