| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
# |
| 7 |
# TC1. Add one of the columns after `CREATE` with `ALTER TABLE` |
| 8 |
# |
| 9 |
SET @@global.binlog_row_image = FULL; |
| 10 |
# |
| 11 |
# Add one of the columns in the master, with binlog_row_image equal to |
| 12 |
# FULL and aditional columns props equal to 'PRIMARY KEY' |
| 13 |
# |
| 14 |
[connection master] |
| 15 |
SET @@session.sql_log_bin = 0; |
| 16 |
CREATE TABLE t (a INT PRIMARY KEY, |
| 17 |
INDEX ((a+1)), KEY ((a+10))); |
| 18 |
ALTER TABLE t ADD COLUMN b INT; |
| 19 |
SET @@session.sql_log_bin = 1; |
| 20 |
[connection slave] |
| 21 |
SET @@session.sql_log_bin = 0; |
| 22 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 23 |
INDEX ((a+1)), KEY ((a+10))); |
| 24 |
SET @@session.sql_log_bin = 1; |
| 25 |
[connection master] |
| 26 |
INSERT INTO t VALUES (1, 2); |
| 27 |
UPDATE t SET b = 1 WHERE a = 1; |
| 28 |
INSERT INTO t VALUES (2, 2); |
| 29 |
DELETE FROM t WHERE b = 2; |
| 30 |
include/sync_slave_sql_with_master.inc |
| 31 |
include/diff_tables.inc [master:t, slave:t] |
| 32 |
[connection master] |
| 33 |
DROP TABLE t; |
| 34 |
include/sync_slave_sql_with_master.inc |
| 35 |
# |
| 36 |
# Add one of the columns in the master, with binlog_row_image equal to |
| 37 |
# FULL and aditional columns props equal to '' |
| 38 |
# |
| 39 |
[connection master] |
| 40 |
SET @@session.sql_log_bin = 0; |
| 41 |
CREATE TABLE t (a INT , |
| 42 |
INDEX ((a+1)), KEY ((a+10))); |
| 43 |
ALTER TABLE t ADD COLUMN b INT; |
| 44 |
SET @@session.sql_log_bin = 1; |
| 45 |
[connection slave] |
| 46 |
SET @@session.sql_log_bin = 0; |
| 47 |
CREATE TABLE t (a INT , b INT, |
| 48 |
INDEX ((a+1)), KEY ((a+10))); |
| 49 |
SET @@session.sql_log_bin = 1; |
| 50 |
[connection master] |
| 51 |
INSERT INTO t VALUES (1, 2); |
| 52 |
UPDATE t SET b = 1 WHERE a = 1; |
| 53 |
INSERT INTO t VALUES (2, 2); |
| 54 |
DELETE FROM t WHERE b = 2; |
| 55 |
include/sync_slave_sql_with_master.inc |
| 56 |
include/diff_tables.inc [master:t, slave:t] |
| 57 |
[connection master] |
| 58 |
DROP TABLE t; |
| 59 |
include/sync_slave_sql_with_master.inc |
| 60 |
# |
| 61 |
# Add one of the columns in the slave, with binlog_row_image equal to |
| 62 |
# FULL and aditional columns props equal to 'PRIMARY KEY' |
| 63 |
# |
| 64 |
[connection slave] |
| 65 |
SET @@session.sql_log_bin = 0; |
| 66 |
CREATE TABLE t (a INT PRIMARY KEY, |
| 67 |
INDEX ((a+1)), KEY ((a+10))); |
| 68 |
ALTER TABLE t ADD COLUMN b INT; |
| 69 |
SET @@session.sql_log_bin = 1; |
| 70 |
[connection master] |
| 71 |
SET @@session.sql_log_bin = 0; |
| 72 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 73 |
INDEX ((a+1)), KEY ((a+10))); |
| 74 |
SET @@session.sql_log_bin = 1; |
| 75 |
[connection master] |
| 76 |
INSERT INTO t VALUES (1, 2); |
| 77 |
UPDATE t SET b = 1 WHERE a = 1; |
| 78 |
INSERT INTO t VALUES (2, 2); |
| 79 |
DELETE FROM t WHERE b = 2; |
| 80 |
include/sync_slave_sql_with_master.inc |
| 81 |
include/diff_tables.inc [master:t, slave:t] |
| 82 |
[connection master] |
| 83 |
DROP TABLE t; |
| 84 |
include/sync_slave_sql_with_master.inc |
| 85 |
# |
| 86 |
# Add one of the columns in the slave, with binlog_row_image equal to |
| 87 |
# FULL and aditional columns props equal to '' |
| 88 |
# |
| 89 |
[connection slave] |
| 90 |
SET @@session.sql_log_bin = 0; |
| 91 |
CREATE TABLE t (a INT , |
| 92 |
INDEX ((a+1)), KEY ((a+10))); |
| 93 |
ALTER TABLE t ADD COLUMN b INT; |
| 94 |
SET @@session.sql_log_bin = 1; |
| 95 |
[connection master] |
| 96 |
SET @@session.sql_log_bin = 0; |
| 97 |
CREATE TABLE t (a INT , b INT, |
| 98 |
INDEX ((a+1)), KEY ((a+10))); |
| 99 |
SET @@session.sql_log_bin = 1; |
| 100 |
[connection master] |
| 101 |
INSERT INTO t VALUES (1, 2); |
| 102 |
UPDATE t SET b = 1 WHERE a = 1; |
| 103 |
INSERT INTO t VALUES (2, 2); |
| 104 |
DELETE FROM t WHERE b = 2; |
| 105 |
include/sync_slave_sql_with_master.inc |
| 106 |
include/diff_tables.inc [master:t, slave:t] |
| 107 |
[connection master] |
| 108 |
DROP TABLE t; |
| 109 |
include/sync_slave_sql_with_master.inc |
| 110 |
SET @@global.binlog_row_image = MINIMAL; |
| 111 |
# |
| 112 |
# Add one of the columns in the master, with binlog_row_image equal to |
| 113 |
# MINIMAL and aditional columns props equal to 'PRIMARY KEY' |
| 114 |
# |
| 115 |
[connection master] |
| 116 |
SET @@session.sql_log_bin = 0; |
| 117 |
CREATE TABLE t (a INT PRIMARY KEY, |
| 118 |
INDEX ((a+1)), KEY ((a+10))); |
| 119 |
ALTER TABLE t ADD COLUMN b INT; |
| 120 |
SET @@session.sql_log_bin = 1; |
| 121 |
[connection slave] |
| 122 |
SET @@session.sql_log_bin = 0; |
| 123 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 124 |
INDEX ((a+1)), KEY ((a+10))); |
| 125 |
SET @@session.sql_log_bin = 1; |
| 126 |
[connection master] |
| 127 |
INSERT INTO t VALUES (1, 2); |
| 128 |
UPDATE t SET b = 1 WHERE a = 1; |
| 129 |
INSERT INTO t VALUES (2, 2); |
| 130 |
DELETE FROM t WHERE b = 2; |
| 131 |
include/sync_slave_sql_with_master.inc |
| 132 |
include/diff_tables.inc [master:t, slave:t] |
| 133 |
[connection master] |
| 134 |
DROP TABLE t; |
| 135 |
include/sync_slave_sql_with_master.inc |
| 136 |
# |
| 137 |
# Add one of the columns in the master, with binlog_row_image equal to |
| 138 |
# MINIMAL and aditional columns props equal to '' |
| 139 |
# |
| 140 |
[connection master] |
| 141 |
SET @@session.sql_log_bin = 0; |
| 142 |
CREATE TABLE t (a INT , |
| 143 |
INDEX ((a+1)), KEY ((a+10))); |
| 144 |
ALTER TABLE t ADD COLUMN b INT; |
| 145 |
SET @@session.sql_log_bin = 1; |
| 146 |
[connection slave] |
| 147 |
SET @@session.sql_log_bin = 0; |
| 148 |
CREATE TABLE t (a INT , b INT, |
| 149 |
INDEX ((a+1)), KEY ((a+10))); |
| 150 |
SET @@session.sql_log_bin = 1; |
| 151 |
[connection master] |
| 152 |
INSERT INTO t VALUES (1, 2); |
| 153 |
UPDATE t SET b = 1 WHERE a = 1; |
| 154 |
INSERT INTO t VALUES (2, 2); |
| 155 |
DELETE FROM t WHERE b = 2; |
| 156 |
include/sync_slave_sql_with_master.inc |
| 157 |
include/diff_tables.inc [master:t, slave:t] |
| 158 |
[connection master] |
| 159 |
DROP TABLE t; |
| 160 |
include/sync_slave_sql_with_master.inc |
| 161 |
# |
| 162 |
# Add one of the columns in the slave, with binlog_row_image equal to |
| 163 |
# MINIMAL and aditional columns props equal to 'PRIMARY KEY' |
| 164 |
# |
| 165 |
[connection slave] |
| 166 |
SET @@session.sql_log_bin = 0; |
| 167 |
CREATE TABLE t (a INT PRIMARY KEY, |
| 168 |
INDEX ((a+1)), KEY ((a+10))); |
| 169 |
ALTER TABLE t ADD COLUMN b INT; |
| 170 |
SET @@session.sql_log_bin = 1; |
| 171 |
[connection master] |
| 172 |
SET @@session.sql_log_bin = 0; |
| 173 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 174 |
INDEX ((a+1)), KEY ((a+10))); |
| 175 |
SET @@session.sql_log_bin = 1; |
| 176 |
[connection master] |
| 177 |
INSERT INTO t VALUES (1, 2); |
| 178 |
UPDATE t SET b = 1 WHERE a = 1; |
| 179 |
INSERT INTO t VALUES (2, 2); |
| 180 |
DELETE FROM t WHERE b = 2; |
| 181 |
include/sync_slave_sql_with_master.inc |
| 182 |
include/diff_tables.inc [master:t, slave:t] |
| 183 |
[connection master] |
| 184 |
DROP TABLE t; |
| 185 |
include/sync_slave_sql_with_master.inc |
| 186 |
# |
| 187 |
# Add one of the columns in the slave, with binlog_row_image equal to |
| 188 |
# MINIMAL and aditional columns props equal to '' |
| 189 |
# |
| 190 |
[connection slave] |
| 191 |
SET @@session.sql_log_bin = 0; |
| 192 |
CREATE TABLE t (a INT , |
| 193 |
INDEX ((a+1)), KEY ((a+10))); |
| 194 |
ALTER TABLE t ADD COLUMN b INT; |
| 195 |
SET @@session.sql_log_bin = 1; |
| 196 |
[connection master] |
| 197 |
SET @@session.sql_log_bin = 0; |
| 198 |
CREATE TABLE t (a INT , b INT, |
| 199 |
INDEX ((a+1)), KEY ((a+10))); |
| 200 |
SET @@session.sql_log_bin = 1; |
| 201 |
[connection master] |
| 202 |
INSERT INTO t VALUES (1, 2); |
| 203 |
UPDATE t SET b = 1 WHERE a = 1; |
| 204 |
INSERT INTO t VALUES (2, 2); |
| 205 |
DELETE FROM t WHERE b = 2; |
| 206 |
include/sync_slave_sql_with_master.inc |
| 207 |
include/diff_tables.inc [master:t, slave:t] |
| 208 |
[connection master] |
| 209 |
DROP TABLE t; |
| 210 |
include/sync_slave_sql_with_master.inc |
| 211 |
# |
| 212 |
# TC2. Add one extra column with `ALTER TABLE` |
| 213 |
# |
| 214 |
SET @@global.binlog_row_image = FULL; |
| 215 |
# |
| 216 |
# Add one extra column in the master, with binlog_row_image equal to |
| 217 |
# FULL and aditional columns props equal to 'PRIMARY KEY' |
| 218 |
# |
| 219 |
[connection master] |
| 220 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 221 |
INDEX ((a+1)), KEY ((a+10))); |
| 222 |
include/sync_slave_sql_with_master.inc |
| 223 |
[connection master] |
| 224 |
SET @@session.sql_log_bin = 0; |
| 225 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 226 |
SET @@session.sql_log_bin = 1; |
| 227 |
[connection master] |
| 228 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 229 |
UPDATE t SET b = 1 WHERE a = 1; |
| 230 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 231 |
DELETE FROM t WHERE b = 2; |
| 232 |
include/rpl_diff.inc |
| 233 |
[connection master] |
| 234 |
DROP TABLE t; |
| 235 |
include/sync_slave_sql_with_master.inc |
| 236 |
# |
| 237 |
# Add one extra column in the master, with binlog_row_image equal to |
| 238 |
# FULL and aditional columns props equal to '' |
| 239 |
# |
| 240 |
[connection master] |
| 241 |
CREATE TABLE t (a INT , b INT, |
| 242 |
INDEX ((a+1)), KEY ((a+10))); |
| 243 |
include/sync_slave_sql_with_master.inc |
| 244 |
[connection master] |
| 245 |
SET @@session.sql_log_bin = 0; |
| 246 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 247 |
SET @@session.sql_log_bin = 1; |
| 248 |
[connection master] |
| 249 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 250 |
UPDATE t SET b = 1 WHERE a = 1; |
| 251 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 252 |
DELETE FROM t WHERE b = 2; |
| 253 |
include/rpl_diff.inc |
| 254 |
[connection master] |
| 255 |
DROP TABLE t; |
| 256 |
include/sync_slave_sql_with_master.inc |
| 257 |
# |
| 258 |
# Add one extra column in the slave, with binlog_row_image equal to |
| 259 |
# FULL and aditional columns props equal to 'PRIMARY KEY' |
| 260 |
# |
| 261 |
[connection master] |
| 262 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 263 |
INDEX ((a+1)), KEY ((a+10))); |
| 264 |
include/sync_slave_sql_with_master.inc |
| 265 |
[connection slave] |
| 266 |
SET @@session.sql_log_bin = 0; |
| 267 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 268 |
SET @@session.sql_log_bin = 1; |
| 269 |
[connection master] |
| 270 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 271 |
UPDATE t SET b = 1 WHERE a = 1; |
| 272 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 273 |
DELETE FROM t WHERE b = 2; |
| 274 |
include/rpl_diff.inc |
| 275 |
[connection master] |
| 276 |
DROP TABLE t; |
| 277 |
include/sync_slave_sql_with_master.inc |
| 278 |
# |
| 279 |
# Add one extra column in the slave, with binlog_row_image equal to |
| 280 |
# FULL and aditional columns props equal to '' |
| 281 |
# |
| 282 |
[connection master] |
| 283 |
CREATE TABLE t (a INT , b INT, |
| 284 |
INDEX ((a+1)), KEY ((a+10))); |
| 285 |
include/sync_slave_sql_with_master.inc |
| 286 |
[connection slave] |
| 287 |
SET @@session.sql_log_bin = 0; |
| 288 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 289 |
SET @@session.sql_log_bin = 1; |
| 290 |
[connection master] |
| 291 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 292 |
UPDATE t SET b = 1 WHERE a = 1; |
| 293 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 294 |
DELETE FROM t WHERE b = 2; |
| 295 |
include/rpl_diff.inc |
| 296 |
[connection master] |
| 297 |
DROP TABLE t; |
| 298 |
include/sync_slave_sql_with_master.inc |
| 299 |
SET @@global.binlog_row_image = MINIMAL; |
| 300 |
# |
| 301 |
# Add one extra column in the master, with binlog_row_image equal to |
| 302 |
# MINIMAL and aditional columns props equal to 'PRIMARY KEY' |
| 303 |
# |
| 304 |
[connection master] |
| 305 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 306 |
INDEX ((a+1)), KEY ((a+10))); |
| 307 |
include/sync_slave_sql_with_master.inc |
| 308 |
[connection master] |
| 309 |
SET @@session.sql_log_bin = 0; |
| 310 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 311 |
SET @@session.sql_log_bin = 1; |
| 312 |
[connection master] |
| 313 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 314 |
UPDATE t SET b = 1 WHERE a = 1; |
| 315 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 316 |
DELETE FROM t WHERE b = 2; |
| 317 |
include/rpl_diff.inc |
| 318 |
[connection master] |
| 319 |
DROP TABLE t; |
| 320 |
include/sync_slave_sql_with_master.inc |
| 321 |
# |
| 322 |
# Add one extra column in the master, with binlog_row_image equal to |
| 323 |
# MINIMAL and aditional columns props equal to '' |
| 324 |
# |
| 325 |
[connection master] |
| 326 |
CREATE TABLE t (a INT , b INT, |
| 327 |
INDEX ((a+1)), KEY ((a+10))); |
| 328 |
include/sync_slave_sql_with_master.inc |
| 329 |
[connection master] |
| 330 |
SET @@session.sql_log_bin = 0; |
| 331 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 332 |
SET @@session.sql_log_bin = 1; |
| 333 |
[connection master] |
| 334 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 335 |
UPDATE t SET b = 1 WHERE a = 1; |
| 336 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 337 |
DELETE FROM t WHERE b = 2; |
| 338 |
include/rpl_diff.inc |
| 339 |
[connection master] |
| 340 |
DROP TABLE t; |
| 341 |
include/sync_slave_sql_with_master.inc |
| 342 |
# |
| 343 |
# Add one extra column in the slave, with binlog_row_image equal to |
| 344 |
# MINIMAL and aditional columns props equal to 'PRIMARY KEY' |
| 345 |
# |
| 346 |
[connection master] |
| 347 |
CREATE TABLE t (a INT PRIMARY KEY, b INT, |
| 348 |
INDEX ((a+1)), KEY ((a+10))); |
| 349 |
include/sync_slave_sql_with_master.inc |
| 350 |
[connection slave] |
| 351 |
SET @@session.sql_log_bin = 0; |
| 352 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 353 |
SET @@session.sql_log_bin = 1; |
| 354 |
[connection master] |
| 355 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 356 |
UPDATE t SET b = 1 WHERE a = 1; |
| 357 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 358 |
DELETE FROM t WHERE b = 2; |
| 359 |
include/rpl_diff.inc |
| 360 |
[connection master] |
| 361 |
DROP TABLE t; |
| 362 |
include/sync_slave_sql_with_master.inc |
| 363 |
# |
| 364 |
# Add one extra column in the slave, with binlog_row_image equal to |
| 365 |
# MINIMAL and aditional columns props equal to '' |
| 366 |
# |
| 367 |
[connection master] |
| 368 |
CREATE TABLE t (a INT , b INT, |
| 369 |
INDEX ((a+1)), KEY ((a+10))); |
| 370 |
include/sync_slave_sql_with_master.inc |
| 371 |
[connection slave] |
| 372 |
SET @@session.sql_log_bin = 0; |
| 373 |
ALTER TABLE t ADD COLUMN c INT DEFAULT 1; |
| 374 |
SET @@session.sql_log_bin = 1; |
| 375 |
[connection master] |
| 376 |
INSERT INTO t (a, b) VALUES (1, 2); |
| 377 |
UPDATE t SET b = 1 WHERE a = 1; |
| 378 |
INSERT INTO t (a, b) VALUES (2, 2); |
| 379 |
DELETE FROM t WHERE b = 2; |
| 380 |
include/rpl_diff.inc |
| 381 |
[connection master] |
| 382 |
DROP TABLE t; |
| 383 |
include/sync_slave_sql_with_master.inc |
| 384 |
SET @@global.binlog_row_image = INITIAL_ROW_IMAGE; |
| 385 |
include/rpl_end.inc |