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
|
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
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.
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
# Create table with different datatypes
CREATE TABLE wl529 (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, bt BLOB,
vt VARCHAR(25), tt TEXT, ft FLOAT, it INT, itu INT
UNSIGNED, jt JSON, dt DOUBLE, dc DECIMAL(16,8),
dat DATE, yt YEAR);
INSERT INTO wl529(bt, vt, tt, ft, it, itu, jt, dt, dc, dat, yt) VALUES
('string', '837498713.8472812', '8240921.0982421', 09385.82471, 1234, 12984, "3980921", 98453.8324732, 4824.89247, '2011-04-03', '2012'),
('varchar', '-494804.48724', '48744.84783', 1234.1223, -1234, 938432, "9218421", 384734.84732, 589239.948091, '2010-11-11', '2010'),
('text', '7848094.874821', '48973847.8743', 3875328.9824721, 3473, 3984013, "1", -8478.89247, -402984.8742, '1980-01-01', '1980'),
('blob', '908249214.8742', '7634109.9843040', -9824821.274921, -98347, 3814031, "8742.43", -384731.837438, -4824.89247, '1970-01-01', '1970'),
('json', '4827410.7404', '-87484.874897447', -4873284.98438, 5398432, 8549, "8392", -892472.8748931, 49852.53985, '2020-02-20', '2020');
CREATE TABLE wl529_insert (f1 FLOAT, f2 VARCHAR(15), f3 TEXT, f4 BLOB, f5 FLOAT,
f6 FLOAT, f7 DOUBLE, f8 DOUBLE, f9 DECIMAL(65,30),
f10 DECIMAL, f11 DECIMAL(16, 10));
INSERT INTO wl529_insert SELECT CAST(bt AS FLOAT), CAST(vt AS FLOAT), CAST(tt AS FLOAT), CAST(ft AS FLOAT), CAST(it AS FLOAT), CAST(itu AS FLOAT), CAST(jt AS FLOAT), CAST(dt AS FLOAT), CAST(dc AS FLOAT), CAST(dat AS FLOAT), CAST(yt AS FLOAT) FROM wl529;
INSERT INTO wl529_insert SELECT CAST(bt AS DOUBLE), CAST(vt AS DOUBLE), CAST(tt AS DOUBLE), CAST(ft AS DOUBLE), CAST(it AS DOUBLE), CAST(itu AS DOUBLE), CAST(jt AS DOUBLE), CAST(dt AS DOUBLE), CAST(dc AS DOUBLE), CAST(dat AS DOUBLE), CAST(yt AS DOUBLE) FROM wl529;
INSERT INTO wl529_insert SELECT CAST(bt AS REAL), CAST(vt AS REAL), CAST(tt AS REAL), CAST(ft AS REAL), CAST(it AS REAL), CAST(itu AS REAL), CAST(jt AS REAL), CAST(dt AS REAL), CAST(dc AS REAL), CAST(dat AS REAL), CAST(yt AS REAL) FROM wl529;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether table was created and values were inserted on slave
SELECT * FROM wl529;
id bt vt tt ft it itu jt dt dc dat yt
1 string 837498713.8472812 8240921.0982421 9385.83 1234 12984 3980921 98453.8324732 4824.89247000 2011-04-03 2012
2 varchar -494804.48724 48744.84783 1234.12 -1234 938432 9218421 384734.84732 589239.94809100 2010-11-11 2010
3 text 7848094.874821 48973847.8743 3875330 3473 3984013 1 -8478.89247 -402984.87420000 1980-01-01 1980
4 blob 908249214.8742 7634109.9843040 -9824820 -98347 3814031 8742.43 -384731.837438 -4824.89247000 1970-01-01 1970
5 json 4827410.7404 -87484.874897447 -4873280 5398432 8549 8392 -892472.8748931 49852.53985000 2020-02-20 2020
SELECT * FROM wl529_insert;
f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11
0 837498688 8240921 9385.8251953125 1234 12984 3980921 98453.8359375 4824.892578125000000000000000000000 20110404 2012.0000000000
0 -494804.5 48744.84765625 1234.122314453125 -1234 938432 9218421 384734.84375 589239.937500000000000000000000000000 20101112 2010.0000000000
0 7848095 48973848 3875329 3473 3984010 1 -8478.892578125 -402984.875000000000000000000000000000 19800100 1980.0000000000
0 908249216 7634110 -9824821 -98347 3814030 8742.4296875 -384731.84375 -4824.892578125000000000000000000000 19700100 1970.0000000000
0 4827410.5 -87484.875 -4873285 5398430 8549 8392 -892472.875 49852.539062500000000000000000000000 20200220 2020.0000000000
0 837498713.84728 8240921.0982421 9385.8251953125 1234 12984 3980921 98453.8324732 4824.892470000000000000000000000000 20110403 2012.0000000000
0 -494804.48724 48744.84783 1234.122314453125 -1234 938432 9218421 384734.84732 589239.948091000000000000000000000000 20101111 2010.0000000000
0 7848094.874821 48973847.8743 3875329 3473 3984010 1 -8478.89247 -402984.874200000000000000000000000000 19800101 1980.0000000000
0 908249214.8742 7634109.984304 -9824821 -98347 3814030 8742.43 -384731.837438 -4824.892470000000000000000000000000 19700101 1970.0000000000
0 4827410.7404 -87484.874897447 -4873285 5398430 8549 8392 -892472.8748931 49852.539850000000000000000000000000 20200220 2020.0000000000
0 837498713.84728 8240921.0982421 9385.8251953125 1234 12984 3980921 98453.8324732 4824.892470000000000000000000000000 20110403 2012.0000000000
0 -494804.48724 48744.84783 1234.122314453125 -1234 938432 9218421 384734.84732 589239.948091000000000000000000000000 20101111 2010.0000000000
0 7848094.874821 48973847.8743 3875329 3473 3984010 1 -8478.89247 -402984.874200000000000000000000000000 19800101 1980.0000000000
0 908249214.8742 7634109.984304 -9824821 -98347 3814030 8742.43 -384731.837438 -4824.892470000000000000000000000000 19700101 1970.0000000000
0 4827410.7404 -87484.874897447 -4873285 5398430 8549 8392 -892472.8748931 49852.539850000000000000000000000000 20200220 2020.0000000000
include/diff_tables.inc [master:wl529_insert, slave:wl529_insert]
[Connection Master]
# Update values inside the table
UPDATE wl529 SET dt = CAST(vt AS DOUBLE) WHERE id = 2;
UPDATE wl529 SET dt = CAST(vt AS REAL) WHERE id = 3;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether rows are updated
SELECT * FROM wl529 WHERE id < 4;
id bt vt tt ft it itu jt dt dc dat yt
1 string 837498713.8472812 8240921.0982421 9385.83 1234 12984 3980921 98453.8324732 4824.89247000 2011-04-03 2012
2 varchar -494804.48724 48744.84783 1234.12 -1234 938432 9218421 -494804.48724 589239.94809100 2010-11-11 2010
3 text 7848094.874821 48973847.8743 3875330 3473 3984013 1 7848094.874821 -402984.87420000 1980-01-01 1980
include/diff_tables.inc [master:wl529, slave:wl529]
[Connection Master]
DELETE FROM wl529 WHERE CAST(vt AS DOUBLE) = dt;
include/sync_slave_sql_with_master.inc
[Connection Slave]
# Check whether the rows are deleted
SELECT * FROM wl529;
id bt vt tt ft it itu jt dt dc dat yt
1 string 837498713.8472812 8240921.0982421 9385.83 1234 12984 3980921 98453.8324732 4824.89247000 2011-04-03 2012
4 blob 908249214.8742 7634109.9843040 -9824820 -98347 3814031 8742.43 -384731.837438 -4824.89247000 1970-01-01 1970
5 json 4827410.7404 -87484.874897447 -4873280 5398432 8549 8392 -892472.8748931 49852.53985000 2020-02-20 2020
include/diff_tables.inc [master:wl529, slave:wl529]
[Connection Master]
# Drop tables and clean up
DROP TABLE wl529, wl529_insert;
include/sync_slave_sql_with_master.inc
[Connection Slave]
include/rpl_end.inc
|
