背景:
要了解xbk这个物理备份工具,可以通过它的执行过程来实现;在执行备份命令的时候,屏幕会输出这个执行过程;general_log也会记录备份期间执行的sql;
操作:
查看屏幕输出:
1.准备一些测试数据:创建了一个库zss,并在库中通过sysbench造了一些表
2.执行全备,输出如下:
[root@localhost xbk]# xtrabackup --defaults-file=/etc/my.cnf --target-dir=/xbk/full/ --user=root --password=11111 --socket=/tmp/mysql.sock --backup
xtrabackup: recognized server arguments: --datadir=/app/mydata/data --open_files_limit=65535 --innodb_buffer_pool_size=1024M --innodb_file_per_table=1 --innodb_write_io_threads=4 --innodb_read_io_threads=4 --innodb_flush_log_at_trx_commit=1 --server-id=2 --log_bin=mysql-bin
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --target-dir=/xbk/full/ --user=root --password=* --socket=/tmp/mysql.sock --backup=1
230531 10:19:09 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).
230531 10:19:09 version_check Connected to MySQL server
230531 10:19:09 version_check Executing a version check against the server...
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at - line 237.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at - line 237.
# A software update is available:
230531 10:19:11 version_check Done.
230531 10:19:11 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 5.7.26-log
xtrabackup version 2.4.28 based on MySQL server 5.7.40 Linux (x86_64) (revision id: 44a8f7b)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /app/mydata/data
xtrabackup: open files limit requested 65535, set to 65535
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
InnoDB: Number of pools: 1
230531 10:19:11 >> log scanned up to (11960441849)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 279 for mysql/innodb_index_stats, old maximum was 0
230531 10:19:12 [01] Copying ./ibdata1 to /xbk/full/ibdata1
230531 10:19:12 [01] ...done
230531 10:19:12 [01] Copying ./mysql/innodb_index_stats.ibd to /xbk/full/mysql/innodb_index_stats.ibd
230531 10:19:12 [01] ...done
230531 10:19:12 [01] Copying ./mysql/time_zone_name.ibd to /xbk/full/mysql/time_zone_name.ibd
230531 10:19:12 [01] ...done
。。。。。。(此处省略相同内容)。。。。。。
230531 10:19:16 [01] ...done
230531 10:19:16 [01] Copying ./zss/sbtest5.ibd to /xbk/full/zss/sbtest5.ibd
230531 10:19:16 [01] ...done
230531 10:19:16 >> log scanned up to (11960441849)
230531 10:19:17 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
230531 10:19:17 Executing FLUSH TABLES WITH READ LOCK...
230531 10:19:17 Starting to backup non-InnoDB tables and files
230531 10:19:17 [01] Copying ./mysql/db.opt to /xbk/full/mysql/db.opt
230531 10:19:17 [01] ...done
230531 10:19:17 [01] Copying ./mysql/plugin.frm to /xbk/full/mysql/plugin.frm
。。。。。。(此处省略相同内容)。。。。。。
230531 10:19:17 [01] Copying ./zss/sbtest2.frm to /xbk/full/zss/sbtest2.frm
230531 10:19:17 [01] ...done
230531 10:19:17 [01] Copying ./zss/sbtest3.frm to /xbk/full/zss/sbtest3.frm
230531 10:19:17 [01] ...done
230531 10:19:17 [01] Copying ./zss/sbtest4.frm to /xbk/full/zss/sbtest4.frm
230531 10:19:17 [01] ...done
230531 10:19:17 [01] Copying ./zss/sbtest5.frm to /xbk/full/zss/sbtest5.frm
230531 10:19:17 [01] ...done
230531 10:19:17 Finished backing up non-InnoDB tables and files
230531 10:19:17 [00] Writing /xbk/full/xtrabackup_binlog_info
230531 10:19:17 [00] ...done
230531 10:19:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '11960441840'
xtrabackup: Stopping log copying thread.
.230531 10:19:17 >> log scanned up to (11960441849)
230531 10:19:17 Executing UNLOCK TABLES
230531 10:19:17 All tables unlocked
230531 10:19:17 [00] Copying ib_buffer_pool to /xbk/full/ib_buffer_pool
230531 10:19:17 [00] ...done
230531 10:19:17 Backup created in directory '/xbk/full/'
MySQL binlog position: filename 'mysql-bin.000022', position '955085369'
230531 10:19:17 [00] Writing /xbk/full/backup-my.cnf
230531 10:19:17 [00] ...done
230531 10:19:17 [00] Writing /xbk/full/xtrabackup_info
230531 10:19:17 [00] ...done
xtrabackup: Transaction log of lsn (11960441840) to (11960441849) was copied.
230531 10:19:17 completed OK!
[root@localhost xbk]#
general_log输出:
1.先设置开启general_log记录
mysql> set global general_log=on;
2.监听日志输出
[root@localhost mysql]# tail -f /app/mydata/data/localhost.log
2023-05-31T16:24:09.730826+08:00 9 Connect root@localhost on using Socket
2023-05-31T16:24:09.731715+08:00 9 Query SET SESSION wait_timeout=2147483
2023-05-31T16:24:09.732437+08:00 9 Query SELECT CONCAT(@@hostname, @@port)
2023-05-31T16:24:09.734054+08:00 9 Quit
2023-05-31T16:24:09.739002+08:00 10 Connect root@localhost on using Socket
2023-05-31T16:24:09.739226+08:00 10 Query SET SESSION wait_timeout=2147483
2023-05-31T16:24:09.739415+08:00 10 Query SET SESSION autocommit=1
2023-05-31T16:24:09.739615+08:00 10 Query SET NAMES utf8
2023-05-31T16:24:09.739946+08:00 10 Query SHOW GLOBAL VARIABLES LIKE 'wsrep_sync_wait'
2023-05-31T16:24:09.754405+08:00 10 Query SHOW VARIABLES
2023-05-31T16:24:09.756430+08:00 10 Query SHOW ENGINE INNODB STATUS
2023-05-31T16:24:09.761824+08:00 10 Query SELECT PLUGIN_NAME, PLUGIN_LIBRARY FROM information_schema.plugins WHERE PLUGIN_STATUS = 'ACTIVE' AND PLUGIN_TYPE = 'KEYRING'
2023-05-31T16:24:09.764027+08:00 10 Query SELECT
CONCAT(table_schema, '/', table_name), engine
FROM information_schema.tables
WHERE engine NOT IN (
'MyISAM', 'InnoDB', 'CSV', 'MRG_MYISAM'
)
AND table_schema NOT IN (
'performance_schema', 'information_schema', 'mysql'
)
2023-05-31T16:24:16.044995+08:00 10 Query SET SESSION lock_wait_timeout=31536000
2023-05-31T16:24:16.045436+08:00 10 Query FLUSH NO_WRITE_TO_BINLOG TABLES
2023-05-31T16:24:16.046594+08:00 10 Query FLUSH TABLES WITH READ LOCK
2023-05-31T16:24:16.361255+08:00 10 Query SHOW MASTER STATUS
2023-05-31T16:24:16.361658+08:00 10 Query SHOW VARIABLES
2023-05-31T16:24:16.364775+08:00 10 Query FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS
2023-05-31T16:24:16.569996+08:00 10 Query UNLOCK TABLES
2023-05-31T16:24:16.581344+08:00 10 Query SELECT UUID()
2023-05-31T16:24:16.582552+08:00 10 Query SELECT VERSION()
2023-05-31T16:24:16.789710+08:00 10 Quit
总结:
由以上执行过程做出如下大概总结:
1.通过命令参数和配置文件,获取备份期间使用到的参数
2.连接到数据库
3.拷贝innodb的ibd文件
4.FTWRL
5.拷贝非innodb的frm/MYI/MYD文件
6.unlock tables;
7.备份配置文件、记录binlog位置等信息
8.备份完成
综上所述,XBK在备份期间,对于innodb表不加锁,对非innodb表是加锁的