• 6. 将单独表空间（File-Per-Table Tablespaces）复制到另一个实例

    6. 将单独表空间复制到另一个实例

    本节介绍如何将单独表空间从一个MySQL实例复制 到另一个MySQL实例，也称为可传输表空间功能。

    将InnoDB单独表空间复制到其他实例的原因有很多：
    - 在不对生产服务器施加额外负载的情况下运行报告。

    • 在新的slave服务器上为表设置相同的数据 。

    • 在出现问题或错误后还原表或分区的备份版本。

    • 作为一种更快速的数据移动方式，比导入mysqldump命令的结果更快。数据立即可用，而不必重新插入并重建索引。

    • 将单独表空间移动到具有更适合系统要求的存储介质的服务器。例如，您可能希望在SSD设备上使用繁忙的表，或在高容量HDD设备上使用大表 。

    限制和使用说明，请参考官方文档。

    6.1 可传输表空间示例

    示例1：将InnoDB表复制到另一个实例

    • 1）在源实例上，创建一张表
    mysql> use test;
mysql> create table t(c1 int) Engine=InnoDB;
mysql> insert into t select 1;
mysql> insert into t select 2;
    • 2）在目标实例上，创建同样的表
    mysql> use test;
mysql> create table t(c1 int) Engine=InnoDB;
    • 3）在目标实例上，放弃现有表空间（在导入表空间之前， InnoDB必须丢弃现有表空间。）
    mysql> ALTER TABLE t DISCARD TABLESPACE;
    • 4）在源实例上，运行FLUSH TABLES TABLENAME FOR EXPORT以停顿表
    mysql> flush tables t for export;
    • 5）将.ibd文件从源实例复制到目标实例,并确保属主为mysql
    # cp /data/mysql/mysql3306/data/test/t.ibd /data/mysql/mysql3307/data/test/
# cd /data/mysql/mysql3307/data/test/
# ls -l
total 112
-rw-r----- 1 mysql mysql    67 Aug 10 16:53 db.opt
-rw-r----- 1 mysql mysql  8556 Aug 10 16:53 t.frm
-rw-r----- 1 root  root  98304 Aug 10 17:01 t.ibd
# chown mysql:mysql t.ibd
    • 6）在源实例上，使用unlock tables释放锁
    mysql> unlock tables;
    • 7）在目标实例上，导入表空间
    mysql> alter table t import tablespace;
mysql> select * from t;
+------+
| c1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

    注意
    该ALTER TABLE … IMPORT TABLESPACE功能不会对导入的数据强制执行外键约束。如果表之间存在外键约束，则应在相同（逻辑）时间点导出所有表。在这种情况下，您将停止更新表，提交所有事务，获取表上的共享锁，然后执行导出操作。

    示例2：将InnoDB分区表复制到另一个实例

    • 1）在源实例上，创建分区表.
    mysql> create table t1 (i int) Engine=InnoDB partition by key(i) partitions 3;
mysql> insert into t1 select 1;
mysql> insert into t1 select 2;
mysql> insert into t1 select 3;

    在datadir目录中，每个分区都有一个单独的*.ibd文件

    # cd /data/mysql/mysql3306/data/test
# ls -l
total 536
-rw-r----- 1 mysql mysql   8622 Aug  9 17:03 account.frm
-rw-r----- 1 mysql mysql 114688 Aug 10 09:28 account.ibd
-rw-r----- 1 mysql mysql     67 Aug  7 10:15 db.opt
-rw-r----- 1 mysql mysql   8554 Aug 10 17:11 t1.frm
-rw-r----- 1 mysql mysql  98304 Aug 10 17:11 t1#P#p0.ibd
-rw-r----- 1 mysql mysql  98304 Aug 10 17:11 t1#P#p1.ibd
-rw-r----- 1 mysql mysql  98304 Aug 10 17:11 t1#P#p2.ibd
-rw-r----- 1 mysql mysql   8556 Aug 10 16:51 t.frm
-rw-r----- 1 mysql mysql  98304 Aug 10 16:51 t.ibd
    • 2）在目标实例上，创建相同的分区表
    mysql> create table t1 (i int) Engine=InnoDB partition by key(i) partitions 3
    • 3）在目标实例上，丢弃分区表的表空间。
    mysql> ALTER TABLE t1 DISCARD TABLESPACE;
    • 4）在源实例上，运行FLUSH TABLES ... FOR EXPORT以停顿分区表
    mysql> FLUSH TABLES t1 FOR EXPORT;
    • 5）将.ibd文件复制到目标实例数据库目录中,并更改权限为mysql
    # cd /data/mysql/mysql3306/data/test
# cp t1*.ibd /data/mysql/mysql3307/data/test/
# chown mysql:mysql t1*.ibd
    • 6）在源实例上，使用unlock tables释放锁
    mysql> unlock tables;
    • 7）在目标实例上，导入分区表的表空间
    mysql> alter table t1 import tablespace;
Query OK, 0 rows affected, 3 warnings (0.42 sec)

 mysql> select * from t1;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

    示例3：将InnoDB表分区复制到另一个实例

    • 1）在源实例上创建分区表，包含4个分区
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
mysql> insert into t1 select 1;
mysql> insert into t1 select 2;
mysql> insert into t1 select 3;
mysql> insert into t1 select 4;
mysql> insert into t1 select 5;
#查看分区数据分布情况
mysql> select * from t1 partition(p0);
+------+
| i    |
+------+
|    1 |
|    5 |
+------+
2 rows in set (0.00 sec)
mysql> select * from t1 partition(p1);
+------+
| i    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)
 mysql> select * from t1 partition(p2);
+------+
| i    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
mysql> select * from t1 partition(p3);
+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
    • 2）在目标实例上创建相同的分区
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
    • 3）在目标实例上，丢弃计划从源实例导入的表空间分区
    mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
    • 4）在源实例上，运行 FLUSH TABLES … FOR EXPORT以停顿分区表
    mysql> flush tables t1 for export;
    • 5）将.ibd文件复制到目标实例数据库目录。在此示例中，仅将分区2（p2）和分区3（p3）的.ibd文件复制到data目标实例上的目录。分区0（p0）和分区1（p1）保留在源实例上。
    # cp t1#P#p2.ibd  /data/mysql/mysql3307/data/test/
# cp t1#P#p3.ibd  /data/mysql/mysql3307/data/test/
# chown mysql:mysql t1#P#*
    • 6）在源实例上，用于 UNLOCK TABLES释放通过FLUSH TABLES … FOR EXPORT以下方式获取的锁 ：
    mysql> unlock tables;
    • 7）在目标实例上，导入表空间分区（p2和p3）：
    mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
mysql> select * from t1;
+------+
| i    |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

    6.2 可传输表空间内部

    以下信息描述了常规InnoDB表的可传输表空间复制过程的内部和错误日志消息传递。

    当ALTER TABLE ... DISCARD TABLESPACE在目标实例上运行：
    - 该表被锁定在X模式下
    - 表空间与表分离

    当FLUSH TABLES ... FOR EXPORT在源实例上运行：
    - 导出的表以共享模式锁定
    - 协调器线程停止
    - 脏页面写入到磁盘
    - 表元数据被写入二进制 .cfg文件。

    此操作的log-error日志消息

    2018-08-11T00:57:56.363847Z 2 [Note] InnoDB: Sync to disk of `test`.`t1` /* Partition `p0` */ started.
2018-08-11T00:57:56.363876Z 2 [Note] InnoDB: Stopping purge
2018-08-11T00:57:56.364901Z 2 [Note] InnoDB: Writing table metadata to './test/t1#P#p0.cfg'
2018-08-11T00:57:56.365515Z 2 [Note] InnoDB: Table `test`.`t1` /* Partition `p0` */ flushed to disk
2018-08-11T00:57:56.365539Z 2 [Note] InnoDB: Sync to disk of `test`.`t1` /* Partition `p1` */ started.
2018-08-11T00:57:56.365559Z 2 [Note] InnoDB: Writing table metadata to './test/t1#P#p1.cfg'
2018-08-11T00:57:56.366225Z 2 [Note] InnoDB: Table `test`.`t1` /* Partition `p1` */ flushed to disk
2018-08-11T00:57:56.366240Z 2 [Note] InnoDB: Sync to disk of `test`.`t1` /* Partition `p2` */ started.
2018-08-11T00:57:56.366256Z 2 [Note] InnoDB: Writing table metadata to './test/t1#P#p2.cfg'
2018-08-11T00:57:56.366429Z 2 [Note] InnoDB: Table `test`.`t1` /* Partition `p2` */ flushed to disk
2018-08-11T00:57:56.366443Z 2 [Note] InnoDB: Sync to disk of `test`.`t1` /* Partition `p3` */ started.
2018-08-11T00:57:56.366458Z 2 [Note] InnoDB: Writing table metadata to './test/t1#P#p3.cfg'
2018-08-11T00:57:56.366509Z 2 [Note] InnoDB: Table `test`.`t1` /* Partition `p3` */ flushed to disk

    当unlock tables在源实例上运行：
    - 删除二进制.cfg文件
    - 将导入表上的共享锁释放，并重启清除协调器线程

    此操作的log-error日志消息

    2018-08-11T01:00:20.289266Z 2 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p0.cfg'
2018-08-11T01:00:20.289408Z 2 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p1.cfg'
2018-08-11T01:00:20.290116Z 2 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p2.cfg'
2018-08-11T01:00:20.291804Z 2 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p3.cfg'
2018-08-11T01:00:20.291857Z 2 [Note] InnoDB: Resuming purge

    当alter table ... import tablespace在目标实例上运行时，导入算法会为要导入的每个表空间执行以下操作

    • 检查每个表空间页面是否损坏
    • 每页上的空间ID和日志序列号(LSN)都会更新
    • 验证标志并更细标题页的LSN
    • Btree页面已更新
    • 页面状态设置为脏，以便将其写入磁盘

    此操作的log-error日志消息

    2018-08-11T00:52:37.332663Z 2 [Note] InnoDB: Sync to disk
2018-08-11T00:52:37.339950Z 2 [Note] InnoDB: Sync to disk - done!
2018-08-11T00:52:37.340015Z 2 [Note] InnoDB: Phase I - Update all pages
2018-08-11T00:52:37.340191Z 2 [Note] InnoDB: Sync to disk
2018-08-11T00:52:37.354408Z 2 [Note] InnoDB: Sync to disk - done!
2018-08-11T00:52:37.358280Z 2 [Note] InnoDB: Phase III - Flush changes to disk
2018-08-11T00:52:37.376256Z 2 [Note] InnoDB: Phase IV - Flush complete
2018-08-11T00:52:37.376368Z 2 [Note] InnoDB: `test`.`t1` /* Partition `p2` */ autoinc value set to 0
2018-08-11T00:52:37.435594Z 2 [Note] InnoDB: Sync to disk
2018-08-11T00:52:37.441117Z 2 [Note] InnoDB: Sync to disk - done!
2018-08-11T00:52:37.441414Z 2 [Note] InnoDB: Phase I - Update all pages
2018-08-11T00:52:37.441538Z 2 [Note] InnoDB: Sync to disk
2018-08-11T00:52:37.446679Z 2 [Note] InnoDB: Sync to disk - done!
2018-08-11T00:52:37.450051Z 2 [Note] InnoDB: Phase III - Flush changes to disk
2018-08-11T00:52:37.467124Z 2 [Note] InnoDB: Phase IV - Flush complete
2018-08-11T00:52:37.467232Z 2 [Note] InnoDB: `test`.`t1` /* Partition `p3` */ autoinc value set to 0
  • 相关阅读:
    微软的权限框架Asp.Net Identity
    排序算法
    在线编辑器
    It's only too late if you decide it is. Get busy living, or get busy dying（转）
    一个程序员的四年经历反思（转）
    wamp的安装使用（转）
    JDBC连接数据库经验技巧（转）
    重写ResultSet实现分页功能(最好的分页技术)（转）
    import android.provider.Telephony cannot be resolved
    linux-多线程
  • 原文地址：https://www.cnblogs.com/wanbin/p/9514656.html
Copyright © 2020-2023  润新知