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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
include/master-slave.inc
[connection master]
set @old_global_binlog_format= @@global.binlog_format;
set @old_session_binlog_format= @@session.binlog_format;
SET GLOBAL binlog_format = 'ROW';
SET SESSION binlog_format = 'ROW';
select @@global.binlog_format, @@session.binlog_format;
@@global.binlog_format ROW
@@session.binlog_format ROW
connection slave;
set @old_global_binlog_format= @@global.binlog_format;
set @old_session_binlog_format= @@session.binlog_format;
SET GLOBAL binlog_format = 'ROW';
SET SESSION binlog_format = 'ROW';
select @@global.binlog_format, @@session.binlog_format;
@@global.binlog_format ROW
@@session.binlog_format ROW
include/stop_slave.inc
include/start_slave.inc
connection master;
CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb';
CREATE TABLE t2(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb'
PARTITION BY KEY(id) partitions 5;
CREATE TABLE t3(id MEDIUMINT NOT NULL AUTO_INCREMENT,
dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB,
fkid MEDIUMINT, filler VARCHAR(255),
PRIMARY KEY(id)) ENGINE='innodb'
PARTITION BY RANGE(id)
SUBPARTITION BY hash(id) subpartitions 2
(PARTITION pa1 values less than (10),
PARTITION pa2 values less than (20),
PARTITION pa3 values less than (30),
PARTITION pa4 values less than (40),
PARTITION pa5 values less than (50),
PARTITION pa6 values less than (60),
PARTITION pa7 values less than (70),
PARTITION pa8 values less than (80),
PARTITION pa9 values less than (90),
PARTITION pa10 values less than (100),
PARTITION pa11 values less than MAXVALUE);
CREATE PROCEDURE p1()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user= CURRENT_USER();
SET local_uuid= UUID();
WHILE ins_count > 0 DO
INSERT INTO t1 VALUES (NULL, NOW(), USER() , UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) INTO del_count FROM t1;
WHILE del_count > 0 DO
DELETE FROM t1 WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user= CURRENT_USER();
SET local_uuid= UUID();
WHILE ins_count > 0 DO
INSERT INTO t2 VALUES (NULL, NOW(), USER() , UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) INTO del_count FROM t2;
WHILE del_count > 0 DO
DELETE FROM t2 WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
CREATE PROCEDURE p3()
BEGIN
DECLARE ins_count INT DEFAULT 1000;
DECLARE del_count INT;
DECLARE cur_user VARCHAR(255);
DECLARE local_uuid VARCHAR(255);
DECLARE local_time TIMESTAMP;
SET local_time= NOW();
SET cur_user = CURRENT_USER();
SET local_uuid=UUID();
WHILE ins_count > 0 DO
INSERT INTO t3 VALUES (NULL, NOW(), USER(), UUID(),
ins_count,'Going to test MBR for MySQL');
SET ins_count = ins_count - 1;
END WHILE;
SELECT MAX(id) INTO del_count FROM t3;
WHILE del_count > 0 DO
DELETE FROM t3 WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
begin;
CALL p1();
commit;
SELECT count(*) as "Master regular" FROM t1;
Master regular 500
begin;
CALL p2();
commit;
SELECT count(*) as "Master bykey" FROM t2;
Master bykey 500
begin;
CALL p3();
commit;
SELECT count(*) as "Master byrange" FROM t3;
Master byrange 500
connection slave;
connection slave;
show create table t3;
Table t3
Create Table CREATE TABLE `t3` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`dt` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`user` char(255) DEFAULT NULL,
`uuidf` longblob DEFAULT NULL,
`fkid` mediumint(9) DEFAULT NULL,
`filler` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
SUBPARTITIONS 2
(PARTITION `pa1` VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION `pa2` VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION `pa3` VALUES LESS THAN (30) ENGINE = InnoDB,
PARTITION `pa4` VALUES LESS THAN (40) ENGINE = InnoDB,
PARTITION `pa5` VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION `pa6` VALUES LESS THAN (60) ENGINE = InnoDB,
PARTITION `pa7` VALUES LESS THAN (70) ENGINE = InnoDB,
PARTITION `pa8` VALUES LESS THAN (80) ENGINE = InnoDB,
PARTITION `pa9` VALUES LESS THAN (90) ENGINE = InnoDB,
PARTITION `pa10` VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION `pa11` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
include/check_slave_is_running.inc
SELECT count(*) "Slave norm" FROM t1;
Slave norm 500
SELECT count(*) "Slave bykey" FROM t2;
Slave bykey 500
SELECT count(*) "Slave byrange" FROM t3;
Slave byrange 500
connection master;
set @@global.binlog_format= @old_global_binlog_format;
set @@session.binlog_format= @old_session_binlog_format;
DROP TABLE t1, t2, t3;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
connection slave;
set @@global.binlog_format= @old_global_binlog_format;
set @@session.binlog_format= @old_session_binlog_format;
include/rpl_end.inc
|
