rpl_partition.result 5.68 KB
Newer Older
1 2 3 4 5 6
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
7 8
set @old_global_binlog_format=  @@global.binlog_format;
set @old_session_binlog_format=  @@session.binlog_format;
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
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
DROP TABLE IF EXISTS t1, t2, t3;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;
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) FROM t1 INTO del_count;
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) FROM t2 INTO del_count;
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) FROM t3 INTO del_count;
WHILE del_count > 0 DO
DELETE FROM t3 WHERE id = del_count;
SET del_count = del_count - 2;
END WHILE;
END|
CALL p1();
SELECT count(*) as "Master regular" FROM t1;
Master regular	500
CALL p2();
SELECT count(*) as "Master bykey" FROM t2;
Master bykey	500
CALL p3();
SELECT count(*) as "Master byrange" FROM t3;
Master byrange	500
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,
  `fkid` mediumint(9) DEFAULT NULL,
  `filler` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
126
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
127 128 129
/*!50100 PARTITION BY RANGE (id)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
130 131 132 133 134 135 136 137 138 139 140
(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) */
141
show slave status;
142
Slave_IO_State	#
143 144
Master_Host	127.0.0.1
Master_User	root
145
Master_Port	MASTER_MYPORT
146 147
Connect_Retry	1
Master_Log_File	master-bin.000001
148 149 150
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
151 152 153 154 155 156 157 158 159 160 161 162
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	Yes
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	0
Last_Error	
Skip_Counter	0
163 164
Exec_Master_Log_Pos	#
Relay_Log_Space	#
165 166 167 168 169 170 171 172 173
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
174
Seconds_Behind_Master	#
175
Master_SSL_Verify_Server_Cert	No
176 177
Last_IO_Errno	#
Last_IO_Error	#
178 179
Last_SQL_Errno	0
Last_SQL_Error	
180 181
Replicate_Ignore_Server_Ids	
Master_Server_Id	1
182 183 184 185 186 187
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
188 189
set @@global.binlog_format=  @old_global_binlog_format;
set @@session.binlog_format=  @old_session_binlog_format;
190 191 192 193
DROP TABLE t1, t2, t3;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
DROP PROCEDURE IF EXISTS p3;