mariadb/mysql主从复制
一、mysql的复制原理
至少需要两个节点，一主（master）一从（slave）
那么从节点如何从主节点获得数据呢？
1、主节点开启二进制日志，记录了修改数据的所有语句
2、从节点扮演mysql客户端请求主节点的二进制日志，默认是第一个文件的最开始位置，通常要指定二进制文件的指定位置。从节点收到二进制日志后保存在本地的中继日志中，并且会记录已经读取到哪个二进制日志的哪个位置，用作再次请求二进制日志的起始位置。从节点会读取中继日志中的事件完成重放后从而形成一模一样的数据保存在数据库中。
3、slave节点上负责从master节点上请求二进制日志的线程叫做IO_THREAD，从节点收到二进制日志后，保存在本地中继日志中并由SQL_THREAD做重放后将数据保存在数据库中。
4、主节点会为每个请求二进制日志的从服务器线程IO_THREAD启动一个响应线程dump_thread，该线程负责读取主服务器上的二进制日志并发送给从服务器。
二、mysql基于二进制日志主从复制特点：
1、异步复制
当客户端写入数据时，主节点记录完二进制日志便回复客户端已经写好了，而无需等待从节点写入完成才回复。因此，数据刚刚在主服务器上写入完成，而没有来得及同步到从节点的话，从服务器上是读取不到该数据的。
2、从节点可能会落后于主节点/主从数据不一致
有时候，落后也是有好处的，万一主服务器上误删了数据，从服务器上还是可以保留一段时间的。可以专门部署一台服务器落后于主节点的。
主节点上，事务是可以并行执行提交的，但是二进制日志还是只能串行写入的，而等到从节点过来读取二进制日志并完成重放以后，就必然是落后了。
根据经验：同一机房内，主从之间最多落后1秒，可以接受
问题一：从节点上的中继日志完成重放后需要保留吗？
不需要，SQL_THREAD把中继日志重放完了以后
问题二：从节点上需要保存二进制日志吗？
需要看情况而定
1、二进制日志是用来发给从服务器做事件重放使用，所以从服务器如果没有从服务器的话，是不需要保留二进制日志的，因为保存二进制日志会增加了IO压力。
2、如果mysql从服务器拥有自己的从服务器，这种架构就是mysql的级联复制
补充知识点
二进制日志的事件记录格式：
STATEMENT：只记录修改的语句，虽然节省空间但不推荐，极有可能导致数据不一致
ROW：记录被修改的行的数据，记录数据最精确，需要更多存储空间，推荐
MIXED：默认的记录格式，mysql自行判定以STATEMENT还是ROW的方式来记录，推荐
三、主从复制模型配置过程演示
主节点：开启二进制日志，设定全局唯一的SERVER_ID,创建拥有复制权限的用户账号(需要REPLICATION SLAVE,REPLICATION CLIENT)
从节点：开启中继日志，设定全局唯一的SERVER_ID,使用有复制权限的用户账号连接至主服务器并启动复制线程
node1: 172.16.92.1/16 mariadb主服务器
node2: 172.16.92.2/16 mariadb从服务器
以上节点均为CentOS 7.1
配置环境
1. 配置好光盘yum源
2. 关闭selinux和iptables
|
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
|
node1: mariadb主服务器
[root@node1 ~]# yum -y install mariadb-server
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links
is
recommended
to
prevent assorted security risks
symbolic-links=0
# Settings
user
and
group
are ignored
when
systemd
is
used.
# If you need
to
run mysqld under a different
user
or
group
,
# customize your systemd unit file
for
mariadb according
to
the
# instructions
in
http://fedoraproject.org/wiki/Systemd
#######以下的内容为添加########
#二进制变更日志
log-bin=mysql-bin
#二进制日志格式为混合模式
binlog_format=mixed
#为主服务器node1的ID值
server-id = 1
port = 3306
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
innodb_file_per_table =
on
skip_name_resolve =
on
###############################
###### 以下的内容可选 ########
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no
-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
#############################
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include
all
files
from
the config directory
#
!includedir /etc/my.cnf.d
###############
End
for
my.cnf #################
[root@node1 ~]# systemctl start mariadb
[root@node1 ~]# mysql
MariaDB [(none)]>
grant
replication client,replication slave
on
*.*
to
'repluser'
@
'172.16.92.2'
identified
by
'replpass'
;
MariaDB [(none)]> flush
privileges
;
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 497
Binlog_Do_DB:
Binlog_Ignore_DB:
##### 记下mysql-bin.000003 和 497 , 设置从服务器中继日志时有用 ####
node2: mariadb从服务器
[root@node2 ~]# yum -y install mariadb-server
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links
is
recommended
to
prevent assorted security risks
symbolic-links=0
# Settings
user
and
group
are ignored
when
systemd
is
used.
# If you need
to
run mysqld under a different
user
or
group
,
# customize your systemd unit file
for
mariadb according
to
the
# instructions
in
http://fedoraproject.org/wiki/Systemd
########## 添加以下内容 ##########
log-bin=mysql-bin
binlog_format=mixed
server-id = 2
relay-log = relay-bin
log_slave_updates = 1
read_only =
on
port = 3306
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
innodb_file_per_table =
on
skip_name_resolve =
on
###################################
######### 以下内容可选 ############
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no
-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
####################################
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include
all
files
from
the config directory
#
!includedir /etc/my.cnf.d
#############
End
of
my.cnf ###############
[root@node2 ~]# systemctl start mariadb
[root@node2 ~]# mysql
MariaDB [(none)]> show
global
variables
like
'%read_only%'
;
| read_only |
ON
|
MariaDB [(none)]> show
global
variables
like
'%read_only%'
\G
*************************** 1. row ***************************
Variable_name: read_only
Value:
ON
MariaDB [(none)]> change master
to
master_host=
'172.16.92.1'
,master_user=
'repluser'
,master_password=
'replpass'
,master_log_file=
'mysql-bin.000003'
,master_log_pos=497,master_connect_retry=5,master_heartbeat_period=2;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.92.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 497
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running:
No
Slave_SQL_Running:
No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 497
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
NULL
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 172.16.92.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 497
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........ 其余信息略 ........
MariaDB [(none)]> show processlist\G
*************************** 3. row ***************************
Id: 4
User
: system
user
Host:
db:
NULL
Command:
Connect
Time
: 144
State: Slave has
read
all
relay log; waiting
for
the slave I/O thread
to
update
it
Info:
NULL
Progress: 0.000
#说明: 从节点已经接收到所有的中继日志
node1 主节点上可查看到此进程
MariaDB [(none)]> show processlist\G
*************************** 2. row ***************************
Id: 4
User
: repluser
Host: 172.16.92.2:56821
db:
NULL
Command: Binlog Dump
Time
: 212
State: Master has sent
all
binlog
to
slave; waiting
for
binlog
to
be updated
Info:
NULL
Progress: 0.000
在主节点上创建数据库测试是否能主从同步
MariaDB [(none)]>
create
database
testdb;
在从节点上可看到testdb数据库, 说明主从同步成功!
MariaDB [(none)]> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+
--------------------+
再来看一下 从节点 的状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master
to
send event
Master_Host: 172.16.92.1
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 584 #497->584
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 616 #529->616
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
############# mysql主从复制结束 ##############
复制过滤器
(1) 基于库的白名单的实现
在从节点上设置
MariaDB [(none)]> stop slave;
MariaDB [(none)]>
set
global
replicate_do_db=
'testdb'
;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show
global
variables
like
'%replicat%'
;
| replicate_do_db | testdb | #只同步该数据库的数据
在主节点上创建新数据库, 查看是否能同步过来
MariaDB [(none)]>
create
database
mydb;
从节点上并未看到mydb数据库
MariaDB [(none)]> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+
--------------------+
在主节点的testdb中插入数据, 查看是否能同步过来
MariaDB [(none)]> use testdb;
MariaDB [testdb]>
create
table
t1(id
int
);
MariaDB [testdb]>
desc
t1\G
*************************** 1. row ***************************
Field: id
Type:
int
(11)
Null
: YES
Key
:
Default
:
NULL
Extra:
从节点上查看一样的数据, 说明从服务器现在只能同步一个数据库
MariaDB [(none)]> use testdb;
MariaDB [testdb]> show tables\G
*************************** 1. row ***************************
Tables_in_testdb: t1
MariaDB [testdb]>
desc
t1\G
*************************** 1. row ***************************
Field: id
Type:
int
(11)
Null
: YES
Key
:
Default
:
NULL
Extra:
|
本文转自 zhuhc1988 51CTO博客，原文链接：http://blog.51cto.com/changeflyhigh/1711200，如需转载请自行联系原作者
