表空间

作者：肖亚洲 爱可生 DBA 团队成员，负责我的项目中数据库故障与平台问题解决，对数据库高可用与分布式技术情有独钟。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 背景介绍客户生产环境因为运维人员误操作，将 MySQL 数据库中，某个业务表 ibd 文件给 rm 掉了。因为历史起因该环境没有可用从库。针对这种状况，咱们怎么解决呢？ 情景复现在单实例中，将表 sbtest1 的数据文件 rm 删掉后，观测数据库的运行状态【文件复原前不要重启 mysql 】。 前提条件筹备数据库与测试表数据 创立一个 MySQL-5.7.28 的单实例数据库，并用 sysbench 筹备1张100w的测试表。[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql --mysql_storage_engine=innodb --db-ps-mode=disable --test=/usr/local/sysbench1.0/share/sysbench/oltp_insert.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=1 --time=1 prepareWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)Creating table 'sbtest1'...Inserting 1000000 records into 'sbtest1'Creating a secondary index on 'sbtest1'...[root@localhost ~]#查看数据库中数据：[root@localhost ~]# mysql -uroot -p -S /data/mysql/data/8484/mysqld.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.21 sec)查看sbtest1表的ibd文件：[root@localhost test]# ll -h /data/mysql/data/8484/test/total 241M-rw-r----- 1 actiontech-mysql actiontech-mysql 60 Jun 7 07:55 db.opt-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun 7 07:57 sbtest1.frm-rw-r----- 1 actiontech-mysql actiontech-mysql 240M Jun 7 07:57 sbtest1.ibd发展测试通过 sysbench 对数据库发展读写测试，继续5分钟，期间对 sbtest1.ibd 文件进行 rm 操作，观测数据库运行状态： ...

作者：姚远 MySQL ACE，华为云 MVP ，专一于 Oracle、MySQL 数据库多年，Oracle 10G 和 12C OCM，MySQL 5.6，5.7，8.0 OCP。当初鼎甲科技任技术顾问，为共事和客户提供数据库培训和技术支持服务。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 MySQL 的表在进行了屡次 delete 、update 和 insert 后，表空间会呈现碎片。定期进行表空间整顿，打消碎片能够进步拜访表空间的性能。 检查表空间碎片上面这个试验用于验证进行表空间整顿后对性能的影响，首先查看这个有100万记录表的大小： mysql> analyze table sbtest1;+----------------+---------+----------+-----------------------------+| Table | Op | Msg_type | Msg_text |+----------------+---------+----------+-----------------------------+| sbtest.sbtest1 | analyze | status | Table is already up to date |+----------------+---------+----------+-----------------------------+1 row in set (0.06 sec)mysql> show table status like 'sbtest1'\G*************************** 1. row *************************** Name: sbtest1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 1000000 Avg_row_length: 729 Data_length: 729000000Max_data_length: 205195258022068223 Index_length: 20457472 Data_free: 0 Auto_increment: 1000001 Create_time: 2021-05-31 18:54:22 Update_time: 2021-05-31 18:54:43 Check_time: 2021-05-31 18:55:05 Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*-rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD-rw-r----- 1 mysql mysql 20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI命令 show table status 和从 OS 层看到的数据文件大小统一，这时的 Data_free 为零。 ...

本文目录： 一、表空间加密概述 应⽤场景加密插件加密限制注意事项二、加密表空间 安装加密插件配置表空间加密查看表空间被加密的表三、更新 master encryption key四、导⼊导出 案例五、备份恢复 innobackupex六、参考文档 表空间加密概述从 5.7.11 开始，InnoDB 支持对独立表空间进行静态数据加密。该加密是在引擎内部数据页级别的加密手段，在数据页写入文件系统时加密，从文件读到内存中时解密，目前广泛使用的是 YaSSL/OpenSSL 提供的 AES 加密算法，加密前后数据页大小不变，因此也称为透明加密。 它使用两层加密密钥架构，包括 master encryption key 和 tablespace key： master encryption key 用于加密解密 tablespace key。当对一个表空间加密时，一个 tablespace key 会被加密并存储在该表表空间的头部tablespace key 用于加密表空间文件。当访问加密表空间时， InnoDB 会先用 master encryption key 解密存储在表空间中的加密 tablespace key，得到明文的 tablespace key 后，再用 tablespace key 解密数据tablespace key 解密后的明文信息基本不变（除非进行 alter table test_1 encrytion=NO, alter table test_1 encrytion=YES）。而 master key 可以随时改变（比如使用 ALTER INSTANCE ROTATE INNODB MASTER KEY;），这个称为 master key rotation。因为 tablespace key 的明文不会变，所以更新 master encryption key 之后只需要把 tablespace key 重新加密写入第一个页中即可。应用场景未配置表空间加密时，当发生类似拖库操作时，数据极可能会泄漏。配置表空间加密时，如果没有加密时使用的 keyring（该文件由 keyring_file_data 参数设定），是读取不到加密表空间数据的。所以当发生类似拖库操作时，没有相关的 keyring 文件时，数据基本不会泄漏的。这就要求存储的 keyring 一定要严加保管，可以采取以下措施来保存 keyring： ...

本文目录一、使用背景1.可传输表空间基本流程2.使用前提条件/限制二、技术要点三、实施步骤1.实验环境2.源端操作 1) 造测试数据并模拟小压力 2) 备份单库数据 3) 备份表结构 4) 批量生成可传输表空间命令3.目标端操作 1) 备份数据恢复准备 2) 恢复表结构至目标端 3) 舍弃目标端对应表空间文件 4) 拷贝表数据及配置文件到目标端sbtest库数据目录 5) 执行导入表空间文件操作4.建立复制(多源复制)四、参考链接Keywords：MySQL xtrabackup Transportable Tablespaces MySQLdump multi-source replication一、使用背景 MySQL在5.7以后引入了多源复制(Multi-Source)功能，可用于将多个单独的数据库汇聚到一个数据库实例下，方便用户进行数据分析、汇总或推送至其他数据库平台。早期针对汇聚场景初始化各源端数据到汇聚库，为了提升效率通常会使用使用开源并行逻辑导入导出工具myloader/mydumper进行数据导入/导出，当源端多实例多库数据量较大(100G以上)情况下，使用myloader/mydumper花费的时间则可能无法满足时间要求，需要考虑是否有更高效的方式进行数据初始化同步操作，以及当复制通道异常时更便捷快速修复。ps：多源复制使用物理备份(xtrabackup)做数据初始化时，常规方式只有第一个通道可做覆盖还原，后续通道需逻辑还原或用其他方式还原，如该方案所描述的方式。MySQL在5.6以后支持了可传输表空间,以及Xtrabackup针对该功能在备份时提供了一个对应参数export,该参数支持对InnoDB存储引擎表的备份数据export转换，且与常规备份操作一样可生成备份时间点的binlog信息(GTID信息),结合这两特性可以更高效和快速的方式实现多库数据汇聚的初始导入操作。▽场景架构图如下▽1.可传输表空间基本流程先简单梳理一下可传输表空间基本流程：在源端将InnoDB表进行ibd数据文件导出处理(export tablespace))在目标端创建与源端相同表结构的表将目标端的表数据文件舍弃(discard tablespace)用源端的相对应ibd文件覆盖到目标端在目标端执行表空间文件的导入/置换(import tablespace)单纯使用可传输表空间功能无法记录对应表的事务点，也就是如果需要进行汇聚复制同步，无法知道从binlog哪个文件的哪个position点进行数据同步，这也就是需要引入xtrabackup及export功能的原因。备份元数据信息本身会记录复制同步点信息。2.使用前提条件/限制MySQL须为5.6以上版本表存储引擎须为InnoDB存储引擎(支持可传输表空间)导入完成后建议执行ANALYZE TABLE更新统计信息二、技术要点sysbench 源端造测试数据并模拟压力mysqldump 备份源端表结构concat()拼接批量可传输表空间SQLxtrabackup 备份源端单库(部分库)数据Transportable Tablespace可传输表空间功能Multi-source Replication 多源复制mysql-error.log 错误信息日志校验ANALYZE TABLE更新统计信息三、实施步骤 1.实验环境 2.源端操作 1) 造测试数据并模拟小压力使用sysbench创建4张各100W记录的测试表，并使用2个并发持续模拟业务压力：## 造数据shell> /opt/sysbench-0.9/sysbench/sysbench –test=/opt/sysbench-0.9/sysbench/tests/db/oltp.lua –oltp-table-size=1000000 –oltp-tables-count=4 –mysql-user=sysbench –mysql-password=sysbench –mysql-host=10.186.60.16 –mysql-port=3333 prepare## 模拟小压力shell> /opt/sysbench-0.9/sysbench/sysbench –test=/opt/sysbench-0.9/sysbench/tests/db/oltp.lua –oltp-table-size=1000000 –oltp-tables-count=4 –mysql-user=sysbench –mysql-password=sysbench –mysql-host=10.186.60.16 –mysql-port=3333 –num-threads=2 –max-requests=0 –max-time=0 –report-interval=1 run2) 备份单库数据使用xtrabackup备份工具对源端sbtest库进行单库备份，保存至/data/mysql/backup/目录下：shell> innobackupex –databases=sbtest /data/mysql/backup/3) 备份表结构为可传输表空间做准备，将源端表结构备份并后续在目标端导入：shell> cd /data/mysql/backupshell> mysqldump –no-data –set-gtid-purged=off sbtest>sbtest_schema.sql4) 批量生成可传输表空间命令discard使用concat函数拼接出批量DISCARD TABLESPACE的SQL：shell> cd /data/mysql/backupshell> mysql -ssre “select concat(‘ALTER TABLE ‘,TABLE_SCHEMA,’.’,TABLE_NAME,’ DISCARD TABLESPACE;’) from information_schema.tables where TABLE_SCHEMA=‘sbtest’;” >discard_tbs.sql## 输出文件如下所示shell> cat discard_tbs.sqlALTER TABLE sbtest.sbtest1 DISCARD TABLESPACE;ALTER TABLE sbtest.sbtest2 DISCARD TABLESPACE;ALTER TABLE sbtest.sbtest3 DISCARD TABLESPACE;ALTER TABLE sbtest.sbtest4 DISCARD TABLESPACE;- import使用concat函数拼接出批量IMPORT TABLESPACE的SQL：shell> cd /data/mysql/backupshell> mysql -ssre “select concat(‘ALTER TABLE ‘,TABLE_SCHEMA,’.’,TABLE_NAME,’ IMPORT TABLESPACE;’) from information_schema.tables where TABLE_SCHEMA=‘sbtest’;">import_tbs.sql## 输出文件如下所示shell> cat import_tbs.sqlALTER TABLE sbtest.sbtest1 IMPORT TABLESPACE;ALTER TABLE sbtest.sbtest2 IMPORT TABLESPACE;ALTER TABLE sbtest.sbtest3 IMPORT TABLESPACE;ALTER TABLE sbtest.sbtest4 IMPORT TABLESPACE;拷贝源端/data/mysql/backup目录下生成的所有相关文件到目标端/data/mysql/backup下3.目标端操作1) 备份数据恢复准备对备份数据进行apply-log日志应用及将数据进行export转换生成配置文件：shell> innobackupex –apply-log –export /data/mysql/backup/2019-02-22_15-26-20/## export执行完后sbtest库下备份文件如下所示## exp结尾的文件为Percona针对Percona XtraDB做export的配置文件## cfg结尾的文件为Percona针对MySQL可传输表空间export的配置文件shell> ll /data/mysql/backup/2019-02-22_15-26-20/sbtest/总用量 999556-rw-r—– 1 root root 67 2月 22 15:40 db.opt-rw-r–r– 1 root root 569 2月 22 15:53 sbtest1.cfg-rw-r—– 1 root root 16384 2月 22 15:53 sbtest1.exp-rw-r—– 1 root root 8632 2月 22 15:40 sbtest1.frm-rw-r—– 1 root root 255852544 2月 22 15:53 sbtest1.ibd-rw-r–r– 1 root root 569 2月 22 15:53 sbtest2.cfg-rw-r—– 1 root root 16384 2月 22 15:53 sbtest2.exp-rw-r—– 1 root root 8632 2月 22 15:40 sbtest2.frm-rw-r—– 1 root root 255852544 2月 22 15:53 sbtest2.ibd-rw-r–r– 1 root root 569 2月 22 15:53 sbtest3.cfg-rw-r—– 1 root root 16384 2月 22 15:53 sbtest3.exp-rw-r—– 1 root root 8632 2月 22 15:40 sbtest3.frm-rw-r—– 1 root root 255852544 2月 22 15:53 sbtest3.ibd-rw-r–r– 1 root root 569 2月 22 15:53 sbtest4.cfg-rw-r—– 1 root root 16384 2月 22 15:53 sbtest4.exp-rw-r—– 1 root root 8632 2月 22 15:40 sbtest4.frm-rw-r—– 1 root root 255852544 2月 22 15:53 sbtest4.ibd2) 恢复表结构至目标端将源端表结构在目标端数据库创建：shell> cd /data/mysql/backup## 手工创建sbtest库shell> mysql -e “create database sbtest;”## 导入源端对应的表结构shell> mysql sbtest< sbtest_schema.sql## 验证shell> mysql -e “show tables from sbtest;“3) 舍弃目标端对应表空间文件shell> cd /data/mysql/backupshell> mysql <discard_tbs.sql## 执行后效果如下所示，ibd文件已被舍弃shell> ll /data/mysql/data/sbtest/-rw-r—– 1 mysql mysql 67 2月 22 15:58 db.opt-rw-r—– 1 mysql mysql 8632 2月 22 15:58 sbtest1.frm-rw-r—– 1 mysql mysql 8632 2月 22 15:58 sbtest2.frm-rw-r—– 1 mysql mysql 8632 2月 22 15:58 sbtest3.frm-rw-r—– 1 mysql mysql 8632 2月 22 15:58 sbtest4.frm4) 拷贝表数据及配置文件到目标端sbtest库数据目录## 拷贝ibd文件shell> cp /data/mysql/backup/2019-02-22_15-26-20/sbtest/.ibd /data/mysql/data/sbtest/## 拷贝cfg文件shell> cp /data/mysql/backup/2019-02-22_15-26-20/sbtest/.cfg /data/mysql/data/sbtest/## 修改文件权限为mysql用户shell> chown -R mysql:mysql /data/mysql/data/sbtest5) 执行导入表空间文件操作shell> cd /data/mysql/backup## 出现Warning为可传输表空间正常输出，可忽略,详情可参考下图所示note说明shell> mysql <import_tbs.sqlWarning (Code 1814): InnoDB: Tablespace has been discarded for table ‘sbtest1’Warning (Code 1814): InnoDB: Tablespace has been discarded for table ‘sbtest2’Warning (Code 1814): InnoDB: Tablespace has been discarded for table ‘sbtest3’Warning (Code 1814): InnoDB: Tablespace has been discarded for table ‘sbtest4’## 可通过MySQL错误日志查看import期间是否存在导入异常shell> less /data/mysql/data/mysql-error.log4.建立复制(多源复制)mysql> CHANGE MASTER TO MASTER_HOST=‘10.186.60.16’, MASTER_USER=‘repl’, MASTER_PORT=3333, MASTER_PASSWORD=‘repl’,MASTER_LOG_FILE=‘mysql-bin.000004’,MASTER_LOG_POS=149327998 FOR CHANNEL ‘10-186-60-16’;mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘sbtest.%’);mysql> START SLAVE FOR CHANNEL ‘10-186-60-16’;mysql> SHOW SLAVE STATUS FOR CHANNEL ‘10-186-60-16’\G;四、参考链接Percona xtrabackup export功能介绍https://www.percona.com/doc/p…MySQL 可传输表空间功能说明https://dev.mysql.com/doc/ref…场景示例思路来源博客http://www.cnblogs.com/xuanzh…开源分布式中间件DBLE 社区官网：https://opensource.actionsky…. GitHub主页：https://github.com/actiontech… 技术交流群：669663113开源数据传输中间件DTLE 社区官网：https://opensource.actionsky…. GitHub主页：https://github.com/actiontech… 技术交流群：852990221 ...