1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
include/master-slave.inc
[connection master]
use test;
CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
fkid INT, filler VARCHAR(255),
PRIMARY KEY(id))
ENGINE='Archive';
CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
fkid INT, filler VARCHAR(255),
PRIMARY KEY(id))
ENGINE='Archive'
PARTITION BY RANGE(id)
(PARTITION pa100 values less than (100),
PARTITION paMax values less than MAXVALUE);
CREATE PROCEDURE test.proc_norm()
BEGIN
DECLARE ins_count INT DEFAULT 99;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
SET cur_user= "current_user@localhost";
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
WHILE ins_count > 0 DO
# Must use local variables for statment based replication
INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
ins_count,'Non partitioned table! Going to test replication for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
END|
CREATE PROCEDURE test.proc_byrange()
BEGIN
DECLARE ins_count INT DEFAULT 200;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
SET cur_user= "current_user@localhost";
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
WHILE ins_count > 0 DO
INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
ins_count + 100,'Partitioned table! Going to test replication for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
END|
CALL test.proc_norm();
SELECT count(*) as "Master regular" FROM test.regular_tbl;
Master regular
99
CALL test.proc_byrange();
SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
Master byrange
200
show create table test.byrange_tbl;
Table Create Table
byrange_tbl CREATE TABLE `byrange_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` char(255) DEFAULT NULL,
`uuidf` varbinary(255) DEFAULT NULL,
`fkid` int(11) DEFAULT NULL,
`filler` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
PARTITION BY RANGE (id)
(PARTITION pa100 VALUES LESS THAN (100) ENGINE = ARCHIVE,
PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
show create table test.regular_tbl;
Table Create Table
regular_tbl CREATE TABLE `regular_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` char(255) DEFAULT NULL,
`uuidf` varbinary(255) DEFAULT NULL,
`fkid` int(11) DEFAULT NULL,
`filler` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl;
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
id dt user uuidf fkid filler
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
id dt user uuidf fkid filler
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
id dt user uuidf fkid filler
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
id dt user uuidf fkid filler
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
show create table test.byrange_tbl;
Table Create Table
byrange_tbl CREATE TABLE `byrange_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` char(255) DEFAULT NULL,
`uuidf` varbinary(255) DEFAULT NULL,
`fkid` int(11) DEFAULT NULL,
`filler` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
PARTITION BY RANGE (id)
(PARTITION pa100 VALUES LESS THAN (100) ENGINE = ARCHIVE,
PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
show create table test.regular_tbl;
Table Create Table
regular_tbl CREATE TABLE `regular_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user` char(255) DEFAULT NULL,
`uuidf` varbinary(255) DEFAULT NULL,
`fkid` int(11) DEFAULT NULL,
`filler` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
SELECT count(*) "Slave norm" FROM test.regular_tbl;
Slave norm
99
SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
Slave byrange
200
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
id dt user uuidf fkid filler
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
id dt user uuidf fkid filler
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
id dt user uuidf fkid filler
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
id dt user uuidf fkid filler
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
DROP PROCEDURE test.proc_norm;
DROP PROCEDURE test.proc_byrange;
DROP TABLE test.regular_tbl;
DROP TABLE test.byrange_tbl;
include/rpl_end.inc
|
