Skip to content

CREATE PUBLICATION

Grammar Description

CREATE PUBLICATION Adds a new publish to the current database.

Grammar Structure

CREATE PUBLICATION pubname
    DATABASE database_name ACCOUNT
    [ { ALL
    | account_name, [, ... ] }]
    [ COMMENT 'string']

Syntax Explanation

  • pubname: publication name. The publish name must be different from the name of any existing publisher in the current database.
  • database_name: The name of a database that already exists under the current tenant.
  • account_name: Get the tenant name of the publication.

Example

1.Publisher: Example A Create a database mall and table customer and publish this database as pub_mall:

```sql
-- Example A
create database mall;
CREATE TABLE mall.customer (
customer_id INT,
customer_name VARCHAR(255)
);
create publication pub_mall database mall;

mysql> show publications;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| publication | database | create_time | update_time | sub_account | comments |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pub_mall | mall | 2024-05-27 09:02:49 | NULL | * | |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 row in set (0.17 sec)
```

2.Subscriber: Instance B and Instance C both create the subscription library sub_mall (pub_mall subscribed from Instance A), so all the data in the instance A database mall:

```sql
-- Instance B && Instance C
create database sub_mall from 018fb8d3_2b05_7be8_b526_bb62576dxxxx publication pub_mall;
use sub_mall;

mysql> show subscriptions;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pub_name | pub_account | pub_database | pub_time | sub_name | sub_time |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pub_mall | 018fb8d3_2b05_7be8_b526_bb62576dxxxx | mall | 2024-05-27 09:02:49 | sub_mall | 2024-05-27 09:05:44 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.08 sec)

mysql> show tables;
+-------------------------+
| Tables_in_sub_mall |
+-------------------------+
| customer |
+-------------------------+
```

Note

If you need to unsubscribe, you can directly delete the subscribed database name and use DROP DATABASE.