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
|
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]
#
# Install plugin and add rewrite rule on replica
#
include/sync_slave_sql_with_master.inc
# Query rewrite plugin was installed.
SELECT PLUGIN_NAME, PLUGIN_TYPE, PLUGIN_VERSION, PLUGIN_TYPE_VERSION
FROM information_schema.plugins
WHERE plugin_name LIKE 'Rewriter';
PLUGIN_NAME PLUGIN_TYPE PLUGIN_VERSION PLUGIN_TYPE_VERSION
Rewriter AUDIT 0.2 4.1
INSERT INTO query_rewrite.rewrite_rules ( pattern, replacement)
VALUES ('INSERT INTO test.t1 (a,b) VALUES (?, ?)',
'INSERT INTO test.t1 (b,a) VALUES (?, ?)');
CALL query_rewrite.flush_rewrite_rules();
#
# Replicated INSERTs not rewritten when PRIVILEGE_CHECKS_USER has
# SKIP_QUERY_REWRITE privilege
#
SET @saved_binlog_format=@@global.binlog_format;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
SET SESSION binlog_format = 'STATEMENT';
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
include/sync_slave_sql_with_master.inc
include/stop_slave.inc
CREATE USER 'replication_applier'@'localhost';
GRANT SYSTEM_VARIABLES_ADMIN, REPLICATION_APPLIER, CREATE, INSERT, DELETE, SKIP_QUERY_REWRITE on *.* to 'replication_applier'@'localhost';
FLUSH PRIVILEGES;
CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER='replication_applier'@'localhost';
include/start_slave.inc
CREATE TABLE test.t1 ( a INT, b INT , c INT, PRIMARY KEY(a));
INSERT INTO test.t1 (a, b) VALUES (1, 2);
include/sync_slave_sql_with_master.inc
#
# Replicated INSERTs are not rewritten, so rows at replica match source
#
include/assert.inc [Replicated INSERT should not be rewritten]
#
# Replicated INSERTs rewritten when PRIVILEGE_CHECKS_USER does not have
# SKIP_QUERY_REWRITE privilege
#
REVOKE SKIP_QUERY_REWRITE ON *.* FROM 'replication_applier'@'localhost';
FLUSH PRIVILEGES;
DELETE FROM test.t1;
INSERT INTO test.t1 (a, b) VALUES (3, 4);
include/sync_slave_sql_with_master.inc
#
# Replicated INSERTs are rewritten, so rows at replica do not match source
#
include/assert.inc [Replicated INSERT should be rewritten]
#
# Replicated INSERTs not rewritten when PRIVILEGE_CHECKS_USER = NULL and
# rewriter_enabled_for_threads_without_privilege_checks = OFF
#
SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = OFF;
include/stop_slave.inc
CHANGE REPLICATION SOURCE TO PRIVILEGE_CHECKS_USER = NULL;
include/start_slave.inc
DELETE FROM test.t1;
INSERT INTO test.t1 (a, b) VALUES (5, 6);
include/sync_slave_sql_with_master.inc
#
# Replicated INSERTs are not rewritten, so rows at replica match source
#
include/assert.inc [Replicated INSERT should not be rewritten]
#
# Replicated INSERTs rewritten when PRIVILEGE_CHECKS_USER = NULL and
# rewriter_enabled_for_threads_without_privilege_checks = ON
#
SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = ON;
DELETE FROM test.t1;
INSERT INTO test.t1 (a, b) VALUES (7, 8);
include/sync_slave_sql_with_master.inc
#
# Replicated INSERTs are rewritten, so rows at replica do not match source
#
include/assert.inc [Replicated INSERT should be rewritten]
#
# Cleanup and uninstall plugin.
#
DROP USER 'replication_applier'@'localhost';
SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = DEFAULT;
DROP TABLE test.t1;
SET GLOBAL binlog_format=@saved_binlog_format;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
include/sync_slave_sql_with_master.inc
SET GLOBAL rewriter_enabled_for_threads_without_privilege_checks = DEFAULT;
Warnings:
Warning 1620 Plugin is busy and will be uninstalled on shutdown
# Query rewrite plugin was queued for uninstalling.
include/rpl_end.inc
|
