MariaDB - Spider 分片 - hoyo 學習紀錄

# apt install mariadb-plugin-spider
# wget https://github.com/xiezhenye/mysql-plugin-spider-engine/raw/master/src/scripts/install_spider.sql

1 2	# apt install mariadb-plugin-spider # wget https://github.com/xiezhenye/mysql-plugin-spider-engine/raw/master/src/scripts/install_spider.sql

# mysql -u root -p < install_spider.sql
@have_spider_plugin := 1
1
@have_spider_i_s_alloc_mem_plugin := 1
1
@have_spider_direct_sql_udf := 1
1
@have_spider_bg_direct_sql_udf := 1
1
@have_spider_ping_table_udf := 1
1
@have_spider_copy_tables_udf := 1
1
@have_spider_flush_table_mon_cache_udf := 1
1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

# mysql -u root -p < install_spider.sql

@have_spider_plugin := 1

1

@have_spider_i_s_alloc_mem_plugin := 1

1

@have_spider_direct_sql_udf := 1

1

@have_spider_bg_direct_sql_udf := 1

1

@have_spider_ping_table_udf := 1

1

@have_spider_copy_tables_udf := 1

1

@have_spider_flush_table_mon_cache_udf := 1

1

# mysql -e 'SELECT engine, support, transactions, xa FROM information_schema.engines;'
+--------------------+---------+--------------+------+
| engine             | support | transactions | xa   |
+--------------------+---------+--------------+------+
| SPIDER             | YES     | YES          | NO   |
| MRG_MyISAM         | YES     | NO           | NO   |
| MEMORY             | YES     | NO           | NO   |
| Aria               | YES     | NO           | NO   |
| MyISAM             | YES     | NO           | NO   |
| SEQUENCE           | YES     | YES          | NO   |
| InnoDB             | DEFAULT | YES          | YES  |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   |
| CSV                | YES     | NO           | NO   |
+--------------------+---------+--------------+------+
9 rows in set (0.000 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

# mysql -e 'SELECT engine, support, transactions, xa FROM information_schema.engines;'

+--------------------+---------+--------------+------+

+--------------------+---------+--------------+------+

| SPIDER | YES | YES | NO |

| MRG_MyISAM | YES | NO | NO |

| MEMORY | YES | NO | NO |

| Aria | YES | NO | NO |

| MyISAM | YES | NO | NO |

| SEQUENCE | YES | YES | NO |

| InnoDB | DEFAULT | YES | YES |

| PERFORMANCE_SCHEMA | YES | NO | NO |

| CSV | YES | NO | NO |

+--------------------+---------+--------------+------+

9 rows in set (0.000 sec)

CREATE SERVER backend1 FOREIGN DATA WRAPPER mysql 
OPTIONS(
   HOST '172.16.1.170',
   DATABASE 'test',
   USER 'spider',
   PASSWORD 'spider',
   PORT 3306
);

CREATE SERVER backend2 FOREIGN DATA WRAPPER mysql 
OPTIONS(
   HOST '172.16.1.173',
   DATABASE 'test',
   USER 'spider',
   PASSWORD 'spider',
   PORT 3306
);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE SERVER backend1 FOREIGN DATA WRAPPER mysql

OPTIONS(

HOST '172.16.1.170',

DATABASE 'test',

USER 'spider',

PASSWORD 'spider',

PORT 3306

);

CREATE SERVER backend2 FOREIGN DATA WRAPPER mysql

OPTIONS(

HOST '172.16.1.173',

DATABASE 'test',

USER 'spider',

PASSWORD 'spider',

PORT 3306

);

FLUSH TABLES;

1	FLUSH TABLES;

create table opportunities (
    id int,
    accountName varchar(20),
    name varchar(128),
    owner varchar(7),
    amount decimal(10,2),
    closeDate date,
    stageName varchar(11),
    primary key (id),
    key (accountName)
) engine=spider COMMENT='wrapper "mysql", table "opportunities"'
 PARTITION BY HASH (id)
(
 PARTITION pt1 COMMENT = 'srv "backend1"',
 PARTITION pt2 COMMENT = 'srv "backend2"'
) ;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

create table opportunities (

id int,

accountName varchar(20),

name varchar(128),

owner varchar(7),

amount decimal(10,2),

closeDate date,

stageName varchar(11),

primary key (id),

key (accountName)

) engine=spider COMMENT='wrapper "mysql", table "opportunities"'

PARTITION BY HASH (id)

(

PARTITION pt1 COMMENT = 'srv "backend1"',

PARTITION pt2 COMMENT = 'srv "backend2"'

) ;

create table opportunities (
id int,
accountName varchar(20),
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11),
primary key (id),
key (accountName)
) engine=InnoDB;

1

2

3

4

5

6

7

8

9

10

11

create table opportunities (

id int,

accountName varchar(20),

name varchar(128),

owner varchar(7),

amount decimal(10,2),

closeDate date,

stageName varchar(11),

primary key (id),

key (accountName)

) engine=InnoDB;

create table opportunities (
id int,
accountName varchar(20),
name varchar(128),
owner varchar(7),
amount decimal(10,2),
closeDate date,
stageName varchar(11),
primary key (id),
key (accountName)
) engine=InnoDB;

1

2

3

4

5

6

7

8

9

10

11

create table opportunities (

id int,

accountName varchar(20),

name varchar(128),

owner varchar(7),

amount decimal(10,2),

closeDate date,

stageName varchar(11),

primary key (id),

key (accountName)

) engine=InnoDB;

DROP SERVER backend1;
DROP SERVER backend2;

1 2	DROP SERVER backend1; DROP SERVER backend2;

FLUSH TABLES;

1	FLUSH TABLES;

MariaDB - Spider 分片

設定

刪除

Error 1429: Unable to connect to foreign data source

Related Post

發佈留言

MariaDB - Spider 分片

設定

刪除

Error 1429: Unable to connect to foreign data source

Related Post

網頁的前、後端如何區分

PWA 漸進式網路應用程式 - 3. 桌面版瀏覽網頁通知

網頁前端 - 滑鼠點擊特效

發佈留言