CREATE…FROM…PUBLICATION…

Description

CREATE...FROM...PUBLICATION... is when the subscriber subscribes to a publication created by the publisher to obtain the publisher’s shared data.

Syntax

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

Explanations

• database_name: The name of the database created by the subscriber.
• pubname: The name of the publication that the publisher has published.
• account_name: The account name of the publication can be obtained.

Examples

1. --Suppose the system administrator creates a account acc1 as the subscriber
2. create account acc1 admin_name 'root' identified by '111';

4. --Assuming session 1 is the publisher, the publisher first publishes a database to the account
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. --Open a new session again, assuming that session 2 is the subscriber and the subscriber subscribes to the published database
20. mysql -h 127.0.0.1 -P 6001 -u acc1:root -p  -- Log into the account
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. -- Subscribe successfully

Note

If you need to unsubscribe, you can directly delete the subscribed database. Refer to ‘DROP DATABASE`.