CREATE…FROM…PUBLICATION…

语法说明

CREATE...FROM...PUBLICATION... 是订阅方订阅一个由发布方创建的发布，用来获取发布方的共享数据。

语法结构

  1. CREATE DATABASE database_name
  2. FROM account_name
  3. PUBLICATION pubname;

语法解释

  • database_name：订阅方创建的数据库名称。
  • pubname：发布方已发布的发布名称。
  • account_name：可获取该发布的租户名称。

示例

  1. -- 假设系统管理员创建了一个租户 acc1 为订阅方
  2. create account acc1 admin_name 'root' identified by '111';
  4. -- 假设会话 1 为发布方，由发布方先发布一个数据库给租户
  5. create database sys_db_1;
  6. use sys_db_1;
  7. create table sys_tbl_1(a int primary key );
  8. insert into sys_tbl_1 values(1),(2),(3);
  9. create view v1 as (select * from sys_tbl_1);
  10. create publication sys_pub_1 database sys_db_1;
  11. mysql> show publications;
  12. +-----------+----------+
  13. | Name      | Database |
  14. +-----------+----------+
  15. | sys_pub_1 | sys_db_1 |
  16. +-----------+----------+
  17. 1 row in set (0.01 sec)
  19. -- 再开启一个新的会话，假设会话 2 为订阅方，由订阅方订阅已发布的数据库
  20. mysql -h 127.0.0.1 -P 6001 -u acc1:root -p  --登录租户账号
  21. create database sub1 from sys publication pub1;
  23. mysql> create database sub1 from sys publication sys_pub_1;
  24. Query OK, 1 row affected (0.02 sec)
  26. mysql> show databases;
  27. +--------------------+
  28. | Database           |
  29. +--------------------+
  30. | system             |
  31. | system_metrics     |
  32. | information_schema |
  33. | mysql              |
  34. | mo_catalog         |
  35. | sub1               |
  36. +--------------------+
  37. 6 rows in set (0.00 sec)
  39. mysql> show subscriptions;
  40. +------+--------------+
  41. | Name | From_Account |
  42. +------+--------------+
  43. | sub1 | sys          |
  44. +------+--------------+
  45. 1 row in set (0.01 sec)
  47. mysql> use sub1;
  48. Reading table information for completion of table and column names
  49. You can turn off this feature to get a quicker startup with -A
  51. Database changed
  52. mysql> show tables;
  53. +----------------+
  54. | Tables_in_sub1 |
  55. +----------------+
  56. | sys_tbl_1      |
  57. | v1             |
  58. +----------------+
  59. 2 rows in set (0.01 sec)
  61. mysql> desc sys_tbl_1;
  62. +-------+---------+------+------+---------+-------+---------+
  63. | Field | Type    | Null | Key  | Default | Extra | Comment |
  64. +-------+---------+------+------+---------+-------+---------+
  65. | a     | INT(32) | NO   | PRI  | NULL    |       |         |
  66. +-------+---------+------+------+---------+-------+---------+
  67. 1 row in set (0.01 sec)
  69. mysql> select * from sys_tbl_1 order by a;
  70. +------+
  71. | a    |
  72. +------+
  73. |    1 |
  74. |    2 |
  75. |    3 |
  76. +------+
  77. 3 rows in set (0.01 sec)
  78. -- 订阅成功

Note

如果需要取消订阅，可以直接删除已订阅的数据库名称，使用 DROP DATABASE。