1
include/master-slave.inc
3
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
7
CREATE TABLE test.regular_tbl(id INT NOT NULL AUTO_INCREMENT,
8
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
9
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
10
fkid INT, filler VARCHAR(255),
13
CREATE TABLE test.byrange_tbl(id INT NOT NULL AUTO_INCREMENT,
14
dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
15
CURRENT_TIMESTAMP, user CHAR(255), uuidf VARBINARY(255),
16
fkid INT, filler VARCHAR(255),
19
PARTITION BY RANGE(id)
20
(PARTITION pa100 values less than (100),
21
PARTITION paMax values less than MAXVALUE);
22
CREATE PROCEDURE test.proc_norm()
24
DECLARE ins_count INT DEFAULT 99;
25
DECLARE cur_user VARCHAR(255);
26
DECLARE local_uuid VARCHAR(255);
27
SET cur_user= "current_user@localhost";
28
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
29
WHILE ins_count > 0 DO
30
# Must use local variables for statment based replication
31
INSERT INTO test.regular_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
32
ins_count,'Non partitioned table! Going to test replication for MySQL');
33
SET ins_count = ins_count - 1;
36
CREATE PROCEDURE test.proc_byrange()
38
DECLARE ins_count INT DEFAULT 200;
39
DECLARE cur_user VARCHAR(255);
40
DECLARE local_uuid VARCHAR(255);
41
SET cur_user= "current_user@localhost";
42
SET local_uuid= "36774b1c-6374-11df-a2ca-0ef7ac7a5f6c";
43
WHILE ins_count > 0 DO
44
INSERT INTO test.byrange_tbl VALUES (NULL, NOW(), cur_user, local_uuid,
45
ins_count + 100,'Partitioned table! Going to test replication for MySQL');
46
SET ins_count = ins_count - 1;
49
CALL test.proc_norm();
50
SELECT count(*) as "Master regular" FROM test.regular_tbl;
53
CALL test.proc_byrange();
54
SELECT count(*) as "Master byrange" FROM test.byrange_tbl;
57
show create table test.byrange_tbl;
59
byrange_tbl CREATE TABLE `byrange_tbl` (
60
`id` int(11) NOT NULL AUTO_INCREMENT,
61
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
62
`user` char(255) DEFAULT NULL,
63
`uuidf` varbinary(255) DEFAULT NULL,
64
`fkid` int(11) DEFAULT NULL,
65
`filler` varchar(255) DEFAULT NULL,
67
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
68
/*!50100 PARTITION BY RANGE (id)
69
(PARTITION pa100 VALUES LESS THAN (100) ENGINE = MyISAM,
70
PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
71
show create table test.regular_tbl;
73
regular_tbl CREATE TABLE `regular_tbl` (
74
`id` int(11) NOT NULL AUTO_INCREMENT,
75
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
76
`user` char(255) DEFAULT NULL,
77
`uuidf` varbinary(255) DEFAULT NULL,
78
`fkid` int(11) DEFAULT NULL,
79
`filler` varchar(255) DEFAULT NULL,
81
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
82
ALTER TABLE test.byrange_tbl EXCHANGE PARTITION pa100 WITH TABLE test.regular_tbl;
83
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
84
id dt user uuidf fkid filler
85
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
86
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
87
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
88
id dt user uuidf fkid filler
89
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
90
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
91
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
92
id dt user uuidf fkid filler
93
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
94
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
95
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
96
id dt user uuidf fkid filler
97
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
98
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
99
show create table test.byrange_tbl;
101
byrange_tbl CREATE TABLE `byrange_tbl` (
102
`id` int(11) NOT NULL AUTO_INCREMENT,
103
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
104
`user` char(255) DEFAULT NULL,
105
`uuidf` varbinary(255) DEFAULT NULL,
106
`fkid` int(11) DEFAULT NULL,
107
`filler` varchar(255) DEFAULT NULL,
109
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1
110
/*!50100 PARTITION BY RANGE (id)
111
(PARTITION pa100 VALUES LESS THAN (100) ENGINE = MyISAM,
112
PARTITION paMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
113
show create table test.regular_tbl;
115
regular_tbl CREATE TABLE `regular_tbl` (
116
`id` int(11) NOT NULL AUTO_INCREMENT,
117
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
118
`user` char(255) DEFAULT NULL,
119
`uuidf` varbinary(255) DEFAULT NULL,
120
`fkid` int(11) DEFAULT NULL,
121
`filler` varchar(255) DEFAULT NULL,
123
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
124
SELECT count(*) "Slave norm" FROM test.regular_tbl;
127
SELECT count(*) "Slave byrange" FROM test.byrange_tbl;
130
SELECT * FROM test.byrange_tbl ORDER BY fkid LIMIT 2;
131
id dt user uuidf fkid filler
132
99 date-time USER UUID 1 Non partitioned table! Going to test replication for MySQL
133
98 date-time USER UUID 2 Non partitioned table! Going to test replication for MySQL
134
SELECT * FROM test.byrange_tbl ORDER BY fkid DESC LIMIT 2;
135
id dt user uuidf fkid filler
136
100 date-time USER UUID 201 Partitioned table! Going to test replication for MySQL
137
101 date-time USER UUID 200 Partitioned table! Going to test replication for MySQL
138
SELECT * FROM test.regular_tbl ORDER BY fkid LIMIT 2;
139
id dt user uuidf fkid filler
140
99 date-time USER UUID 202 Partitioned table! Going to test replication for MySQL
141
98 date-time USER UUID 203 Partitioned table! Going to test replication for MySQL
142
SELECT * FROM test.regular_tbl ORDER BY fkid DESC LIMIT 2;
143
id dt user uuidf fkid filler
144
1 date-time USER UUID 300 Partitioned table! Going to test replication for MySQL
145
2 date-time USER UUID 299 Partitioned table! Going to test replication for MySQL
146
DROP PROCEDURE test.proc_norm;
147
DROP PROCEDURE test.proc_byrange;
148
DROP TABLE test.regular_tbl;
149
DROP TABLE test.byrange_tbl;