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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
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]
==== Verify that alter database does not stop replication ====
create database temp_db;
use temp_db;
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
[connection slave]
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
==== Replication of default collation ====
[connection master]
alter database collate latin1_bin;
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
[connection slave]
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
==== Replication of read only ====
[connection master]
create table temp_db.t(i int);
alter schema temp_db read only=1;
insert into temp_db.t values(1);
ERROR HY000: Schema 'temp_db' is in read only mode.
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
[connection slave]
insert into temp_db.t values(2);
ERROR HY000: Schema 'temp_db' is in read only mode.
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
[connection master]
alter schema temp_db read only=0;
insert into temp_db.t values(1);
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
[connection slave]
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
insert into temp_db.t values(2);
select * from temp_db.t;
i
1
2
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
==== No replication of read only when turning off binlog ====
[connection master]
drop table temp_db.t;
create table temp_db.t(i int);
set sql_log_bin = 0;
alter schema temp_db read only=1;
set sql_log_bin = 1;
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
insert into temp_db.t values(1);
ERROR HY000: Schema 'temp_db' is in read only mode.
[connection slave]
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
insert into temp_db.t values(2);
select * from temp_db.t;
i
2
[connection master]
set sql_log_bin = 0;
alter schema temp_db read only=0;
set sql_log_bin = 1;
alter schema temp_db read only=1 collate utf8mb4_0900_ai_ci;
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
[connection slave]
select * from temp_db.t;
i
2
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
==== Slave schema read only will not affect replication ====
[connection master]
set sql_log_bin = 0;
alter schema temp_db read only=0;
set sql_log_bin = 1;
alter schema temp_db collate latin1_bin;
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
drop table temp_db.t;
create table temp_db.t(i int);
insert into temp_db.t values(1);
[connection slave]
show create schema temp_db;
Database Create Database
temp_db CREATE DATABASE `temp_db` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ /* READ ONLY = 1 */
insert into temp_db.t values(2);
ERROR HY000: Schema 'temp_db' is in read only mode.
select * from temp_db.t;
i
1
==== Clean up ====
[connection master]
drop database temp_db;
include/sync_slave_sql_with_master.inc
include/rpl_end.inc
|
