|
|
mysql 的主从复制可以实现mysql的多种高可用性，如数据库的读写分离 ，在线热备，负载均衡，数据分布 等。& I6 `4 z6 ?' H; y5 t& u
; M0 ^2 u% e0 o( d \: jmysql 主从同步原理：
9 t3 L* Y7 F- v7 W- x q0 K- b1. master 将操作记录到二进制日志(binary log)中；6 h; c# \8 a7 ~* R( m
2. slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；! X2 e# |! Q# G' ~
3. slave SQL进程读取中继日志，将重做记录数据到数据库中。
+ v }4 N, I* d" j( J! Z, M 3 M, i* R1 A; ` O2 b: H# S$ S
* B, [! m$ j* b' x2 y4 d下面配置：【主从复制】
$ A- `" b" \. y, G9 F' ?
2 r( C( S) X c4 JMySQL Version : 5.6.22
5 J8 V0 A$ m0 Q% j' K主库：centos152 / 192.168.1.152
' @' ~" [2 m# W! Y) w8 \从库：centos153 / 192.168.1.153' A% W' B2 a7 S& E+ X' |
+ J* g! I: E+ g) j, ~! v
7 b0 i3 \9 o8 F% s. L
配置 mysql 文件：7 V# ~) h c5 ^: o, S/ p9 w
【centos152 主库】
, w) z0 W: |6 F) }) w5 B% A/ R[root@centos152 ~]# vi /etc/my.cnf- h: H- h6 G9 h8 c0 ~4 `( z
[mysqld]* v5 Y* r8 J7 y* P7 I. ^4 M
basedir =/usr/local/mysql+ F/ i8 K1 z+ F
datadir =/usr/local/mysql/data
5 [" n7 K: Z8 l3 j( _port = 3306( }) F5 f, k' j/ k/ B; K! f
server_id = 1. |+ Y: P5 V f- b2 j/ t n8 p% l
log-bin= mysql-bin
5 I7 i0 z$ v/ V) N3 g! Y+ l: mbinlog_format = mixed/ \. ~* }/ W$ c- A& m
* C4 f, \) ?/ z9 I Mread-only=0( r e" @: T& g9 N) z
#binlog-do-db=test: q. c% w0 ^; O$ q
binlog-ignore-db=mysql
, U( S0 o4 W' U: e; h, ^3 lbinlog-ignore-db=information_schema" s" z+ v) P$ K+ A
binlog-ignore-db=performance_schema
: i1 o7 V3 ]! \7 N+ d$ T3 o, _' F! fauto-increment-offset=1' m1 v9 q K7 T7 |
auto-increment-increment=2
8 s3 n1 C: y4 z. P" ?$ Y% N, c; T0 R' x9 {$ J
#添加后重启mysql服务
, `+ w3 F* ]3 b6 N) I W+ r0 a[root@centos152 ~]# service mysqld restart7 D1 a/ d3 t6 R/ t# Q- J! C J5 }
% j; [0 a; C' p0 u# Q/ k
- |3 z" L% U& p+ ~) j【centos153 从库】
" H3 ^9 V! i8 y2 f+ }/ l' c[root@centos153 ~]# vi /etc/my.cnf
; q2 [% ^7 M5 h w' T7 y[mysqld]7 ]& P6 Q/ f1 w
basedir =/usr/local/mysql7 c; K! i. q0 ]$ f
datadir =/usr/local/mysql/data
5 P0 n: v" q( T' Y) \port = 3306
9 O3 H' s+ K8 s! @8 Jserver_id = 2- a0 H+ v5 j$ a3 K! Y" K
log-bin= mysql-bin
' \1 f! o6 c9 ]' sbinlog_format = mixed/ L" y2 k% m% B* B9 M
! n4 Z0 L* o+ Qread-only=0
& l# x- {0 G R0 d5 l# m#replicate-do-db=test
% j* n, W+ d$ A1 h$ Dreplicate-ignore-db=mysql
' i2 g6 T# Z- R3 F* D4 Y! wreplicate-ignore-db=information_schema2 X' [' c0 ]; S; R$ F+ o, ]
replicate-ignore-db=performance_schema
2 e, Q& L' I6 N" t. v' l9 ]relay_log=mysql-relay-bin. E3 K2 N0 @! W! v5 ~+ A# r
log-slave-updates=on
9 f# }' D% u" u/ C8 |2 _#auto-increment-offset=2
, z+ |2 Z' h5 N#auto-increment-increment=2) G9 Y5 H5 w6 b7 y5 g
" P( l6 E; Z+ Q: z j. u9 } p8 J1 S. ^/ Y8 {+ s
#添加后重启mysql服务
; ~7 }+ j1 C# H2 [[root@centos153 ~]# service mysqld restart: r# N6 _ M, p4 W5 ?# ]( Y7 U
- q i: N. W% ~1 z2 P
/ ], F" G7 o% v% K说明：
# z2 K$ ?4 [. }. X. T% N* blog-bin ：需要启用二进制日志2 v5 n7 V! m) i
server_id : 用于标识不同的数据库服务器
8 l; M" f, C; m; b5 Z" G, _# q
0 P' Y8 J* Y" Lbinlog-do-db : 需要记录到二进制日志的数据库
# F' z( `3 d8 A% w* }5 kbinlog-ignore-db : 忽略记录二进制日志的数据库
$ y9 l( t* I6 a8 u. yauto-increment-offset :该服务器自增列的初始值。5 N! f2 C9 C6 |* A/ E
auto-increment-increment :该服务器自增列增量。
7 B) ?$ l: }! m1 G' ]# q; F- N/ _( m7 M" a
replicate-do-db ：指定复制的数据库9 P' h- k2 x* \
replicate-ignore-db ：不复制的数据库( S0 S" a2 i/ h5 J
relay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
) J3 \) ]1 E, e olog-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。' H" m; e4 N" H) M) Z7 {
- x' B8 `$ X, J4 i7 ^8 g
如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
9 _ U# k% t) v* n8 H如上面为双主的设置：
6 g" d0 I% m% n" M9 a1 i. B8 C服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
# _' e& C5 z' Z7 ^+ m3 X; ?) G服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）
3 g$ P' k6 M7 `% K+ g6 t- Y3 U" ^, i3 V( s
* W+ G6 }! i+ V/ N8 N4 [% ^+ s注意：文字图片来自《高性能 mysql》：4 i" O7 D8 ?% p. C. ] \+ h% d
 ( p; J1 f# {' u& C$ ~" R" G2 }
$ v( M% K. ?8 J! M0 \+ k; G2 b# d
% N( W6 c! C: a$ K. t/ w% n* Y#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。 |& B& x/ K' ]5 c) \/ j$ t
6 j, |! H* ^) X0 w4 r; Z6 u2 S+ p4 s$ i0 O3 r
- : L- }. a! T) l: z5 @+ [
. B4 a, g( s1 M: o: m
mysql> flush logs;. }$ D3 Z/ H1 C1 D2 n$ M
; M" q$ o; \! G: r - & \( v8 V7 q( R
1 y U3 x! v1 e6 A
mysql> show master status;
6 {& k( ^5 J+ t4 O5 m t7 J0 K* _1 L' w
C2 z+ a3 N2 t6 ?: w
4 U5 W+ }& ^3 F( S8 V+------------------+----------+--------------+---------------------------------------------+-------------------+& D9 M4 i& I* r" M; g( ^8 U
# S2 X6 ^* q& `+ l
# U8 h6 k+ L3 _+ q! g! q5 @
3 s8 b2 L! N( z6 _| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |4 I; G: r8 {- ~- Q9 m- B$ P/ A
) B) x+ H" K' J" u) O3 A) }
2 ]$ I* Q0 J0 z' P+ Z$ w0 q
- n, Z1 e% U9 ~6 F1 W9 \+------------------+----------+--------------+---------------------------------------------+-------------------+: W; l7 H4 V( z
2 r: P8 U. |/ ~4 ]% V& \7 K9 g
4 P( M+ F9 ]7 f/ k( p+ N' N6 z# W
| mysql-bin.000006 | 120 | | mysql,information_schema,performance_schema | |
6 V. W. J R# [; j" ~, n/ o
' M% k. w( q. _
* s# {5 \6 f; S7 `# S+ G* }5 v( o5 p5 S# [* S8 x; z/ O
+------------------+----------+--------------+---------------------------------------------+-------------------+
4 x4 A! O0 M* k9 ~) u9 T' p& Y" d; g, q! v
. A1 Y; O# w7 W) I
7 H$ H5 ?9 M; l2 v( r; h3 o* L Q9 _5 r
File :当前正在记录的二进制日志文件
8 w* ~+ }& I( }7 f+ V. f X/ A7 s9 O3 p) zPosition ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。0 z1 j0 j& e& ~5 X* v
Binlog_Do_DB ：要记录日志的数据库3 l# K' T, H/ ~; _! r: e; v6 \
Binlog_Ignore_DB ：不记录日志的数据库 G; I: I5 F# o( L% ^
Executed_Gtid_Set ：已执行的事务ID9 F* y; J$ _0 W( v$ d J: g
! h2 ^( x2 A- E; S. e( i+ q w
+ ^2 P+ W5 T+ x+ ?6 r1 ~) O
6 H+ c- X; ~; v9 U4 a M二进制日志情况：! ~8 b1 o4 U5 p# Y
& ?5 ?- a# b ~. _4 R* A
$ q s' L8 s7 S: q8 q4 X I2 @+ ^' c0 J4 }7 U3 I, E/ o$ k- _" I
mysql> show variables like '%log_bin%';
8 ^+ c( W; g) z5 c3 R3 _/ l" D1 @% d/ [7 x& e
- U1 m0 k* o0 y" V8 Z
- P7 n5 m6 L7 n1 t7 v0 t( g5 U3 i
+---------------------------------+---------------------------------------+) n5 C, y% x" o, K# G0 }5 i2 _4 P
* M. |0 s" l/ q; f( a5 Z
( y% f2 x+ H5 Y$ Y7 k! l) C# t* T( h
| Variable_name | Value |+ u: T9 {; A; B, u0 K$ |
* M% P' D. Z, W9 I$ {0 u6 u
! p3 W) I; {: ]1 t* V
) b; B2 u: r \% f% x- e# l+---------------------------------+---------------------------------------+6 ~3 x$ s$ H6 d' P
+ Z' [4 U$ K( B- 1 `. K7 L/ s0 Q8 g
" S5 } s: |/ ?2 P, O
| log_bin | ON |( t6 {4 V0 l6 D, a7 X
. B% K% q: K5 E; z - - J |% g8 j# M, B7 d
" s( F. ^6 A9 e5 T) Z; X* v
| log_bin_basename | /usr/local/mysql/data/mysql-bin |* o1 K; X& [8 N) l
3 Z, {$ E9 ~% G/ v) E
- - S) w& p9 r& Q. n; o' w' m% j
; X2 v$ ~2 F& A- c* J| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
6 _# D1 [, L( t8 @$ C+ K! J
( ]3 s" o2 J" Y1 k
- `4 ^9 a: w( `. c4 e. g
8 |; B* E/ x3 t4 M2 u6 E| log_bin_trust_function_creators | OFF | R8 N) L$ h* I) m5 H, c3 k3 ~
3 n {; t# u: m$ h- 0 L, g6 o0 U% Z. t+ |) h0 K
- V; |5 P% R. p' n
| log_bin_use_v1_row_events | OFF | u. o Y. h( k) o5 O
' _. B6 e4 [: } t - h5 x/ V0 `" Q! h" M
) Y& N' [8 z' `5 V& U: o* G| sql_log_bin | ON |: p6 x0 o7 `) P7 `5 @% p
! @ b0 e' i3 J6 K" g+ H7 z3 c2 t0 ~
9 P2 u! U; C6 }9 O# N3 C
8 }& A3 o' I! s( G+---------------------------------+---------------------------------------+
& q( M" U) f% ~6 L- _4 _6 U
3 h5 {8 O( B& z1 k1 _5 D) ?: k5 j' Z/ y) Q# j8 j3 v! j
. {5 \9 o4 p3 h4 f7 x7 \ E( b; ]3 G3 Z7 R, Q) J
日志位置：
1 `! l9 w; C% `, Z# L4 r- l
( j+ T" N" i, [: c1 a6 O- E! c# s. p' ~6 ?7 {9 E) H
0 X6 j3 l( g1 H8 ^. [" Z$ u
* T4 h6 V8 h6 J# [( x9 |0 S. Z5 z[root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin* B1 `, N% t3 n0 d
, c: {. I7 |/ B" A! a3 k- / S1 U2 y: m5 D, D" {) l
9 Z# k7 B1 i3 [3 z4 ^% ]- ?, p. N
-rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001
" s- J0 U/ B& j% K, l4 ~; {. X
1 X7 v( j; ?/ G4 z3 a) _ - * l0 ], o1 Z$ G% I' b) ^2 r
( j1 d7 R7 Z; f* [9 G- Q-rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002
' e( q7 Y$ L; j$ S* d6 l0 y! C; _- Q- ~
6 U# @0 E+ o- {( \8 P7 ], z) q4 T) O/ X. l
-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003
" q. v0 z" M- z% ]6 s) r" a, V2 H: t' i. q ]6 L8 l) C" H: F
- " z) V. z) b" F+ } J& A
% X5 P5 D* S2 V1 y0 Q
-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004
& n0 Q( ~+ L) J& A% C1 Y1 d% ]& G/ Q) _% C' Z
- " x( p, L7 o B$ V, H$ q4 l
! p2 v2 ^) h0 x& \-rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005
. Y$ ?4 U6 P) t5 q! ~* Q0 |! S- |5 p5 t$ I5 {7 ]
/ U8 T9 r# m: y1 \& K
: y( X$ N3 y, Z' \( `; I" J-rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006
& u6 B( Y8 x6 F; { @. ^4 C5 J0 U2 a" e0 c! A0 }: u
- / O0 b( { q2 ]( n& J% F
) d$ ~% M+ ?8 L; @, O- L4 Z+ [5 z8 e-rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
- `: [* v2 z4 S* {5 u
1 r: w7 V- ~6 D3 U
8 P% q5 @9 }8 A& B. K( P" A
) o, x/ d# Z5 [* I, K. I" [
2 u% `& t2 Z6 C' b/ q' o" {7 S
#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！
2 \8 I1 l0 y6 p0 ]
+ s9 t. q; V m" Wmysql> flush tables with read lock;
/ i7 x [+ q+ [, T) @6 x9 Q8 M
7 d- v( _2 a+ X, q0 B. _备份主库(执行以下一行脚本即可); R' {' u# e+ s, V
2 H5 u$ t, U3 z# T1 S4 [: B- 5 M& w6 m T3 P" r7 c" T5 c
( @/ u. @8 a& `* G ?- O' k5 s6 u
[root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
* ?! @, ?' [' J5 `# z' r( h$ B+ g: n* S( C' C. I9 B
- q+ h( n- m1 ]; L6 g
" b; ]7 Q, ~( h, y1 w
[root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql8 L2 b$ m- g P* R2 S7 g
* d1 Y. ~0 W$ H$ O8 U
: n! \% o1 c9 K( Q1 d
0 Y* Y2 W5 x3 m* C. x# Q' H/ H5 L% G {' V( J! P6 t& x
查看当前日志记录的位置：
! R( y, e# C7 \- e
- ]3 s+ z0 v ^! X% j/ F" g y4 E4 K6 F- ^; S* B; M
M" n, b2 Y- F* h3 h0 c
( r1 l" O2 U# z, C8 b6 U [4 D2 L7 K d3 h6 |
mysql> show master status \G;8 Y9 K. L1 U" [7 E4 c
' D. C( n& @: y3 J C& @
9 T* W6 _8 _' {3 `: w/ ^
2 a2 A6 [: f, W9 M# B0 k+ }*************************** 1. row ***************************
- L1 k: Y2 e% E/ t* ]
* j. S Y9 K5 R7 w; Z3 `
$ g B Y2 S- g4 r$ o, f% ]: [/ b2 a2 l+ m V [
File: mysql-bin.000009
7 e8 x. E/ W) Q/ o# n8 R2 ?) \9 v9 p0 {. N" |) V8 h* e
- + Z7 T. S) _" d7 y. [# M
% l9 d5 H) T" W1 S; S1 N1 { Position: 120
; c |& u7 P8 H" G- O3 ~
3 m/ v: G9 R' \8 P: s - 5 S& |7 G/ S( k
* ~: B% E5 j$ X& D Binlog_Do_DB: ( x. U' T! P. o1 O
2 K4 i$ D" s# [5 K
- * a1 _/ Z, M$ O1 ]$ \" O% m
# }7 k) x. d2 [, t/ B4 u$ ?
Binlog_Ignore_DB: mysql,information_schema,performance_schema
1 E- `+ I7 q o% \: w& u
( `1 Y% w3 t0 M4 D) {% Q9 x - 1 {+ J$ d* i$ j, ~' i1 f0 B1 f
; q$ n9 [$ T% t6 X8 L8 h0 z
Executed_Gtid_Set: * @2 n0 d4 E; p2 O3 `5 y; x' ]7 m
0 D: H0 E. Z# t- `" Y3 I
- - }# k; D5 f) R7 P# I
5 L; Z, l# g B, V/ y: Q7 q/ w1 row in set (0.00 sec)
+ O: l5 @' i, Y% T; h$ ~
# D6 s$ J, v) \# U9 m$ `: n: L" @ J* V: P
2 U1 A9 A/ h0 j1 |
5 K/ p8 U8 D1 k! T: y# w$ |) z
备份完后，可以解锁了
: C. x4 u& i0 K
/ c4 X+ y4 r7 M. \) j& `5 a3 v# U) }, Y8 c% ^8 m
0 L) f, \1 g3 H" z# t6 E) p( I. ymysql> unlock tables;* }& f4 ^' }- Z. M
3 b* S/ L. D5 D3 s( n j1 t
在主服务器拷贝备份到从服务器：$ O7 ^0 r; ?5 J: @& Y
% ?& o/ S* R, a8 K1 d5 J* V4 Y* h6 s( z4 L9 G4 x; n
+ Y0 @# O# Q3 H* w2 o3 L, E9 f
1 D( t6 m& Y' ^% m
# C! w3 e; b% h# G0 T+ ]& g[root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp- Q& [8 \9 h$ |4 s$ _" i: u2 h2 p
- B* P9 f6 T$ U A6 ^: t
2 l' T% j, O: D/ R$ s6 K
2 a( J, Z: l6 D0 g! A$ j$ Z9 K) q- c, b( g
# m" q2 O/ w1 N2 f
- 2 Q- M* j5 i4 e7 u& z/ e
7 N9 q$ k: U0 y* E4 ?8 u: e; Z: W
#不行则先关闭主从服务器防火墙
' ]# ^$ r2 m% i; N
# G3 [" a9 k4 g. h4 ]6 n# [( d+ o - + t" s* f' p& ?5 m
# b2 A3 t/ _& k* ~8 l1 R4 A/ i/etc/init.d/iptables status
+ d+ V" X0 K8 H6 ^. x( d& A9 X a9 R6 O% w; S+ ~
: U% v8 i1 t+ t( T
+ C4 I, [; J, P* W6 R5 Q+ R+ C1 d/etc/init.d/iptables stop+ i9 H7 T- \& z' H3 L) u" ^
7 k; Y1 N+ l3 b7 N* [
* R- X) ^0 U2 F" z# h/ @4 c7 z8 [
J3 d- G) L, P( ~! M
3 _/ B2 [- Y+ \; `+ m
在从库中还原数据库：% |- }! T2 b9 {. z! D
8 w* R8 T' T4 ?5 l
" I' q( k& d/ X2 s/ Y* H
0 d/ s. D: }. [$ Y' R0 @; ^8 q
[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql% L3 J" t' K2 e; S$ q0 ~$ h
8 {: T" J' ^) q, t
主库创建用于连接到本地的用户：& Q: @+ {# V9 ^4 G1 W
! m% u' s# f1 G2 i4 y; ]
6 l! v1 U5 \! y3 V
/ H' S" p% \$ D[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';
1 N) t* I! g8 E/ V a t
5 `6 D4 I% O% E+ Q3 ~# Y在从库中连接到主库：
# E7 ~; i$ |) }& w9 d
3 q f3 \0 U2 d0 p1 }" V3 h
; S" W: x8 o# i% r7 E/ u
5 E6 J( s+ ]7 Q: D# H, H- * a) [" j( Z5 ^1 O7 X+ F; M
# z! ? I. B7 l& d! ^mysql>
) K# W Y* P$ l' I. g7 \# {/ l% _# v2 L$ K- d
/ }$ I3 r8 b9 K2 i
' n2 b. N3 E: y, ECHANGE MASTER TO ( B8 n( T0 S) N7 N" |/ e4 M0 |
j' T9 G, m0 E) s8 i
3 [3 o! L, G0 \# y; f, ?8 V% C1 x- h* S5 k0 J
MASTER_HOST='192.168.1.152',
- m# A" `* x; G1 T* X* v* H& H9 Z' {' E6 Y4 Q( p/ t
/ W$ [8 i. w) N g! [2 @6 z( a) Y3 l) `& ^2 o# I
MASTER_USER='repl_user',
8 g, ?! d/ T. |, ]# X% F! O% t% L; y' U
; X, h5 O* ]" \, y8 S0 O7 p' g% c
9 p1 C2 S& D2 I* p- B MASTER_PASSWORD='slave@153', 9 ^- \ x9 T$ U% @6 {) r
, ^4 Q9 p8 S n/ f( i- , w1 C4 o1 F: a9 P
6 e2 S0 \/ V0 [. [) X MASTER_LOG_FILE='mysql-bin.000009',
4 @% z3 v* }, j7 q/ U \! M0 M- k# Y1 D5 {8 A1 t% `& }
- 6 K" q1 G D$ D0 E) ` v- d
/ w; h0 S5 U9 _
MASTER_LOG_POS=120;
& w" H+ E1 b5 F+ V9 x! P! ~) h5 ^2 o( {) b& _% t
, Q) J: N' [+ a1 x% D* @
$ y2 w2 Z: U9 o% c3 X. U
- [0 K, W$ i( E" g: u; e重启从库mysql服务3 X+ b. u. R2 g. I6 n9 r8 W
[root@centos153 ~]# service mysqld restart7 M( ~: K# Y: _, ~8 r
# y$ }; ^, C( Q/ V p" m( ] p, Y1 P% e, m; Q2 a3 F' H
. c$ `7 t* c! p. |查看从库同步信息：0 ~) ^. u! P3 `8 w2 Q; D
0 g' X# E: u3 E& r! q# y
$ J3 K( o5 S n5 `8 {3 {) V9 p+ B* E
8 l+ c% H( Z6 F6 O) F( G7 A# F: t3 [) j) _; J
mysql> show slave status \G;
$ z: J; i' A( X, \; n2 N8 x' I& o A/ N1 ?6 ~
! [6 V; g/ D# y4 c% c! K+ a* Y* P) U4 Y* z/ [0 x& G
*************************** 1. row ***************************; g' y& g) B7 D I# z% r
3 G4 j* O x/ c- K7 V- g- 9 a2 D) l: \/ D; o/ D
; u3 `- _ Y2 |. w6 Y+ q( t3 l Slave_IO_State: Waiting for master to send event/ G! O% `' m1 b$ e* U7 v8 O, ~- Q
/ _) x! E* A, G% ]1 v2 \
& m, Q! X, T* s# Q" r7 J! t6 H; D$ T3 G: I; ]5 a, q
Master_Host: 192.168.1.152
0 }2 L7 z6 p3 B8 I! N( n p( N
* m) Q ~& ]$ q# }/ B. ^1 x
/ z6 j( q- A7 C9 U3 q6 r" _
1 ^+ `. G* ]8 C0 o Master_User: repl_user/ j/ p& k7 ^ V& F2 k
) u! ?) S0 @3 y$ J6 Y
/ o" t5 v" E- Y8 [9 o: S0 H9 q; b. P V5 C3 t2 b: C9 _
Master_Port: 3306- ?- S9 W1 r0 u" _1 [
. k$ O& ]* x- j( y3 G0 ?* K0 R- , U; C5 {9 G, s: j# d8 P! {
, |% h/ I1 L) K6 `% T+ h% g Connect_Retry: 60
0 _/ [# e" l$ d) d" n
B* F- a% i [% \' Z
$ d7 B4 b7 w6 i8 \+ c
/ p; K0 b" c6 n9 m/ T; S3 B% h Master_Log_File: mysql-bin.000009+ H# ~9 ]- ~/ E9 p7 i. R8 J" K
^! D, H4 B y; i2 r$ ^7 |
# W' b% V4 I3 v$ _8 [
3 G2 J& f* [1 R Read_Master_Log_Pos: 120( d# _2 J2 {) {5 B
" q/ ^0 d0 J3 j2 A/ A
- ' {. |# x x0 K4 j
/ s# Q* i9 z% p/ F3 B1 g, K/ B Relay_Log_File: mysql-relay-bin.000003
, o% ~6 m9 `$ l; h5 ~/ S
8 z! W2 |; n, G - ) ~+ W( l1 U2 X
1 E0 r4 t+ Q* k) X+ W Relay_Log_Pos: 283$ z) d' u- d, I6 Q6 {, k
; `4 u* p* R$ R( G# T4 P
" S M) |0 j4 P9 ]" d: p. s8 L( y2 ~# c1 O
Relay_Master_Log_File: mysql-bin.0000098 h; B x8 _: I2 \) H/ Y
; ^. X5 I5 J5 N
- 5 g; g, S+ y' m1 Q$ e# b
4 {8 X3 ^+ x- E$ T+ V Slave_IO_Running: Yes' n' @$ u7 N) s1 G
+ s- i7 L- h, u- X X/ x - * v3 _* z) s) F# j6 W. l6 ^
8 {/ j; f) P5 z5 r1 k* a Slave_SQL_Running: Yes7 J$ x T' j, D, r) [: x
+ g; ?) c& x; d; L) [ d& n+ _
$ p C# o$ {$ Y( m* ?' y J; ], v# L' ]: F4 G' Q3 X
Replicate_Do_DB: - u( m: V O$ ]. z% n
3 U. q* p8 Q# ?, B9 y) H f
- ( F2 O. z2 @$ U% y4 U! d; p- m) C& q
2 }) Z$ o/ X6 w5 V Replicate_Ignore_DB: mysql,information_schema,performance_schema
& P1 M8 r& k; B% z( O: R) i: `; a1 g4 b' m8 B8 L8 R
# V# ?. A) d! R5 T2 j
, g; ?7 Y; ~, u' M
! e* x3 y0 |' v3 Z& e可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
, Z. [5 {* h2 q$ g g: {3 f- k
# p) M- U) F4 t6 m/ b2 I
) r+ h- `/ o# Z/ u0 b# b: Y1 Y9 ~: `: f) c, A8 Q
现在测试同步情况：
0 E- p8 I% a7 _, Q, i5 {9 t( k9 S8 N& @" h7 o
- 9 V+ R& I& k) G G/ b2 W
( f) [: b1 H ]; G2 f( W#主库创建测试表8 Z7 U, E: ]1 ?* E0 P
' _& t% F& u8 m2 e, k - 3 n1 c v7 ^6 T- V2 U2 D: A7 @
' g4 X" a4 o+ ?/ J5 h' @4 Z, e
create table tabdemo(
) g/ a# W# c/ e6 q! |: L7 _% x: a* W M6 ?6 p# U
! D. q' H% j+ B
% ^' q2 n9 C* c* |! R! Lid int primary key auto_increment,. H7 q! ^6 y+ T) w$ {
% V; v- w2 D9 Q( H! G/ G2 M- " f8 r8 J0 R2 t7 v! w3 [
5 q o& ~. K6 A- {& l3 K, \
value int default 08 G8 L8 X# q$ q5 m$ A& m" Q* P0 \
, j* I5 ]4 X- h M* ^! K' A
5 V$ \# ~+ o d) {
) ]( P( S6 L$ N/ ]$ D) auto_increment= 1 engine=innodb default charset=utf8;
2 T) n. i2 ]& r1 @( l; V* e4 x: [8 M2 v$ I! s+ M5 Z
0 M+ w, K4 j* l" D
, l! {: F, m6 M
5 }+ Q, z/ o; Z7 V8 q' w% g) S$ X5 _ K8 W8 R
- & ?* C. l( t8 S0 n" u: B
2 v5 [1 D" G0 Q' L6 [" ?# V& q
0 L" ?9 s( q( U! s2 B+ `- ^! w1 J" T
- ; `. e+ w" [9 ~* f& i4 A
* ^4 W. v5 @* K8 i
insert into tabdemo(value) values(1),(1),(1),(1),(1);% y5 Z- b: G5 J( Y2 j
) M5 `$ p; M& y9 T" i3 N
5 _( P) k8 X8 w/ P* M J- w
! d2 l4 Q9 S7 ^ x% H1 O2 t6 q9 L L# q% n( D9 j
" ~7 N; ?& {6 h$ E# H4 v- , C% P5 P/ E. V W: _
5 D, ?3 ~- x4 a" x/ W+ ?
! ]" R% U8 H6 r2 i5 j8 H$ q* U% C6 @ s# d5 |5 V7 y1 L
' r9 O* A+ R/ W X& {) u9 w8 f. a; C4 b
select * from tabdemo;7 Z% s8 U- T& V; P3 V- U
, u7 D6 N. ~: R S+ G- 6 V8 J2 K" z6 x$ o {6 g# U( R
0 i3 H. [$ X: z! o, U+----+-------+3 R9 O( v8 k/ O8 P
) D4 k. \+ q) O; y* @8 g% g) x
/ Z/ j2 }/ a5 r3 c& w+ Z! U* v0 U# U. ^0 l2 s$ J
| id | value |
6 _$ h# ], J' l9 L) S# W3 \$ x1 a
- 0 f u6 F' B2 U3 K9 Q1 @
- v4 I, `3 t- L2 s; \! `
+----+-------+" e7 j E5 L4 r t! h5 i7 J
" C- y9 j2 A: {, f: C' ~& Z
" F' M3 r% ~/ l
- X6 H y+ y% w" y8 l| 1 | 1 |# ^. N5 X6 Q' D) @3 B# N, j7 f
4 q8 d/ n4 L, r. P$ _+ i6 H) `3 W3 Q$ }
$ q/ P/ k/ d7 M; Q. d! e) r+ s3 o
7 [+ ^2 _$ F d, a8 i| 3 | 1 |; l7 c1 x( f" M
- |) a- D/ h& g# ^
/ _( n2 c2 j; H4 T1 ^9 k# x7 [: y3 q, |. C3 }5 W
| 5 | 1 |
2 c J" J% P1 K$ c2 v5 ^4 m! L1 e
- % s8 Y6 h# l% }/ b, S( z, M
3 k8 I6 h) J" Q. V8 }| 7 | 1 |
5 f/ _0 \4 I" N& o. E5 W
$ z. ~/ `" ^4 H$ ~. A - / V2 s O( X' [& [6 V
& _3 e5 N# T: Q4 M1 u
| 9 | 1 |
9 j" }& t2 R' t' l' x* q# o! S; Q+ F2 u- Y& b4 g/ `: Y
- f, s6 B0 X! j- b; I. l# {
V* X/ k! V! V8 D+----+-------+/ N3 ~1 E& T4 p$ _! @/ u1 K# @: G
9 Q* |2 E! V) }8 l, n
$ G( K7 U5 ]: ?
) s D, H* I6 S! s* }0 T' d, u' W0 W z) Z( A- {
主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~0 Z0 J Z: d: V8 u
* V8 x( R! e$ k' n
; m p6 \: a! K+ w9 d+ z8 C7 N% e/ q; p" ]* K' d2 w
==========================================================================
) P# b2 w w5 q" b( v/ Y: l2 q==========================================================================
4 c; O7 C0 B( j+ v9 b' t9 e& _ M+ p8 R
下面配置：【主主复制】7 g. A6 ?4 W5 k8 ^, ~
3 E, k g& |1 v) t% h
8 a/ I/ B9 j) x7 U& {4 v0 h
更改配置文件：: P, U& P+ z6 S+ ^" S
/ \" W0 @! l8 v4 B7 t
【centos152 原主库】更改配置文件& p; `7 G! x3 R2 X4 l
[root@centos152 ~]# vi /etc/my.cnf! k& O z* e& n& n
[mysqld]
o9 y1 }: F0 U# t( qbasedir =/usr/local/mysql$ m8 e( {7 v* [' W$ g G
datadir =/usr/local/mysql/data
# y8 w' Q4 [ x' F! @port = 3306/ u" C( B5 [- P( N1 R* Y
server_id = 1
0 n( u k9 Y$ f8 Qlog-bin= mysql-bin5 i9 l' I# B9 I: n" O% m2 f5 L
binlog_format = mixed; ?; Q# E! {7 C" b. X0 V
4 j8 k: Z, Y. C0 ]- m' W: D) X
read-only=0
$ U2 K& }* B; W6 M0 I9 F#binlog-do-db=test0 E$ w: l2 C$ [- N8 h, d2 Y5 |2 u
binlog-ignore-db=mysql! n8 A6 B- t0 A5 o0 D, b% B
binlog-ignore-db=information_schema# Z; W4 @+ p! |$ {2 p
binlog-ignore-db=performance_schema
9 a1 C. E4 W- t: [auto-increment-offset=1
$ p: L- }7 D8 g1 oauto-increment-increment=2 A! F+ \/ u0 n* m( n8 ~4 n& w
1 F# i6 V, h* w: ~' Z }. H#主主复制的从库设置（新增）
/ ~( V/ J& A. I" c2 h#replicate-do-db=test
* z: k( Q( i, D9 \! n8 ]6 b, M6 W* f2 Ereplicate-ignore-db=mysql
2 n" T- q1 H4 H; F' h- B8 z) `; nreplicate-ignore-db=information_schema2 e4 x* j6 y. U
replicate-ignore-db=performance_schema& l0 Q8 }, k5 D$ m2 e7 ]
relay_log=mysql-relay-bin
3 S6 S$ ` i6 B9 c! xlog-slave-updates=on- b; d* p+ p, K$ b' L
% H0 a$ _6 L. w: T6 s) I
$ L1 W4 Y% i0 P5 j+ l3 B2 Y. J【centos153 原从库】更改配置文件
* K0 Q, V9 b: g i[root@centos153 ~]# vi /etc/my.cnf
# P% x" i% y1 ][mysqld]
' \; ]" X/ ^! ~% ?7 d) kbasedir =/usr/local/mysql
* V7 ]2 C* J7 u9 Edatadir =/usr/local/mysql/data
1 M& |; r$ Z! B0 |/ r8 U1 K9 V' Hport = 3306
+ ?0 l$ C8 q) M/ M+ v1 yserver_id = 2
2 ^* I4 H( e# ?: k7 P/ \log-bin= mysql-bin8 M; ~6 [0 z6 n) ~, w& I7 z
binlog_format = mixed7 k7 A8 f4 A/ @6 x% W
: n! s4 `4 v7 `+ K' vread-only=02 E t) s& M8 J3 b- n! u
#replicate-do-db=test
q( ^6 n/ o) w# {; creplicate-ignore-db=mysql
2 m: c. |0 k C, E/ X/ |1 rreplicate-ignore-db=information_schema; f$ W# A8 b# |, v
replicate-ignore-db=performance_schema. @" i3 {6 y. ]* \ |# B6 @
relay_log=mysql-relay-bin8 `- R2 l. g. C; f0 p
log-slave-updates=on+ l) H- d' y: K' V. E
8 A& l, N/ {, c( Z% I2 A
#主主复制的主库设置（新增）- B1 o- v1 J8 z/ P& I9 I1 K% C+ P
#binlog-do-db=test
1 j4 Z# x$ M# {0 Hbinlog-ignore-db=mysql
9 n; s l( q* _) `# Zbinlog-ignore-db=information_schema
( m s. B- X7 M9 H! O, f0 H4 J/ tbinlog-ignore-db=performance_schema
% K/ p( \( ?2 Y) q, lauto-increment-offset=2
; s% `2 e8 o, O4 r) [6 ~auto-increment-increment=2
8 y- U/ i7 \* z$ J
2 t6 e( K9 b9 e8 Z; j, P' y) i ^+ `0 g, O K. i
添加后都重启mysql服务：3 o$ T. w3 X- c; S0 r
[root@centos153 ~]# service mysqld restart
% \* Q1 p7 ^' @9 O! x
+ t5 g: j- p9 _* z% x1 z) _
, w6 M6 Z6 d1 t; Y6 o2 ]" k原从库(153)中创建用于连接的用户：2 _+ n) \8 ?, @# T2 i! C( x
9 a! T, [6 k6 |2 s8 a# q* {- W
- m+ i. e2 o4 _$ n[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';# C' D) Z6 v" a. g, Y
- E7 n: e& L: V% P& s$ B0 q
原从库(153)查看当前日志记录的位置：9 `" o8 S% G/ v" c. I+ `
! U2 [& d% E; n8 k, {
. K" }; X3 I$ U) l( D
2 B0 J% L' a3 `0 O- B# A4 E- i* ?, H' F6 Q; v
) G( U; g8 r. T: Imysql> show master status \G;( `% P# c3 s! \) o$ K0 \/ D
0 Z# z9 d3 P9 C6 E+ ^; r
. n' q9 L- \! M: i5 i- l/ V6 l& \8 X% L
*************************** 1. row ***************************- @: u6 g+ L7 ^ Q4 H' `/ W) P- C
$ p/ [. i% m4 e) }5 X! G, t
/ h" X0 x! F$ C' k6 O' Q
' F9 K6 P( A: S8 J2 {! ?1 E File: mysql-bin.0000058 R( n- u2 H5 Z: o
; l, \0 v" l9 {8 q" y% U
- ' ?; Z9 v/ w$ g: S' H
: _6 a: x' Z* ]8 }
Position: 120* G2 l9 Z$ S1 w( S
' H$ X6 a( c7 d
5 m. N4 q; m4 J! }2 E8 Q+ ~" V. ]8 b! c
Binlog_Do_DB: 3 F2 q [7 y6 F3 j8 z: V+ k9 o2 d6 b1 Q
s; n8 V H( ?" I
7 ~3 P- @7 s: |4 Z) E# S
2 I0 k* k1 s6 i: v: F+ u Binlog_Ignore_DB: mysql,information_schema,performance_schema8 z; E" l. |1 f3 ?* H0 ~
& g |* U0 z4 s. P
# d6 \& }3 ~6 Y& k, E. e( U" T5 U8 C$ _* U
Executed_Gtid_Set: 6 N! A- ^% _+ y2 b% Q1 [ I( T; |
. h5 _' h0 R6 i
- " M. q. q. \4 x% e$ D
) c/ n. y* |2 f' ~5 U. `
1 row in set (0.00 sec)" d0 V% \, c! K. z
" a4 s9 Y- C0 w3 y1 \
* z% K' o/ _ }7 l
( g8 G" E' y; V% ^8 t; ~8 d, C
8 U" X# B' r3 H" H在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：$ e! w3 r6 E3 f
& t8 Y* c3 z5 v" }6 |9 L c# n! f. d
. T' Y" S2 l! I7 X. u$ f4 f; E
/ T3 z+ W1 `: n* j* M/ C _! P1 ]8 K5 f& s7 W2 w
mysql> ' d: C4 @; u! `2 X" |6 K
* B8 e- g: Z( l5 b8 J7 p
- ) D+ {1 q5 \# _" q& j3 R1 ?4 T" _1 f
; i& ^$ q' u8 r( G% f# cCHANGE MASTER TO # v- R' u9 b p0 L+ V. ^
" p/ U0 x' T- q- i$ X; H, j
9 Z- Z# C. T6 o" D) [
4 V6 i4 e/ ~1 E. u5 j MASTER_HOST='192.168.1.153',
$ K1 Y$ K0 e% }, p; U' _7 u" @/ r6 ^% d! r" n5 O% i( |
- ) F# {! [+ e4 a Y+ m3 b- ]8 Y7 h+ H
- _- D+ _8 e) m" U( ]* P) U6 t
MASTER_USER='repl_user',
5 `8 ?% w, T1 J6 B. \3 j* [) J9 t/ u/ S' c3 F) Y# A
- ! G+ F8 X- T/ ^" L1 ]) M
^- k5 v" u4 ]7 M2 c7 j8 g' h8 ^
MASTER_PASSWORD='slave@152',
. i0 T! |9 }( E" [8 |1 _% D# P- \) l2 S; V2 T! `
- & f9 c9 H9 N1 Z! I
9 i/ Z; l+ X8 n% } MASTER_LOG_FILE='mysql-bin.000005',
# f5 I* B( o1 W9 z4 p* j" Z1 e" j
- : e/ s, r2 i4 R; R* Q
5 E' v% z$ Q4 w' F0 g# r
MASTER_LOG_POS=120;
: z5 _# z& O, ~( S! r' V* g5 g4 w! i [9 D" t, U
) w D# K( `$ L4 P5 H
. H# A. W2 }7 Y2 T! d
2 E- X3 x+ P8 J& U# T z6 k. F
重启从库(152)mysql服务：
: l0 q/ N. P$ B- z Y- b[root@centos152 ~]# service mysqld restart; g# Q, |: l8 G( e c
& i* @: D8 X3 d0 S
x( c' v; E6 g& n2 b
k$ k' O2 D% u查看从库(152)的同步信息：0 ?% J0 o$ M( R
! A$ P8 G. @0 O
: m/ r& J5 e8 o0 [% S3 W' O# o# _ u2 ~, v
- " a. S- M3 p% M$ j* |- \
# x5 b* l2 n9 k/ ~mysql> show slave status \G;; [7 x4 y2 ~# m) m
' O2 o, i7 J+ s( W7 Q' |, \
0 f( R2 x' H6 C% H$ q; a6 ^
* e% G/ C% ]) X" \*************************** 1. row *************************** ?) |$ \& m* x0 r; W& A
2 b/ z7 {. ^ J
- . y2 \: e4 h% I( B
. g0 x& g5 o# M, H0 ^- @. A& n
Slave_IO_State: Waiting for master to send event3 [) g: S: \8 k1 _2 J
9 d: D: N8 y: S1 w5 \/ t! J
- 5 g: g* f6 R; s V6 i6 B
3 `2 L2 G! Y% C, [# { Master_Host: 192.168.1.153% ]/ D: T2 i9 F* Y
; f q3 D+ E, f) m i+ C. ~
/ W- A, P: G/ O$ o/ M4 f6 P
$ W9 `/ k* L0 J; }; A7 F5 [ Master_User: repl_user% U0 H) q1 w& S; d0 F* `2 j) N
6 w1 B5 [9 }! }/ c; ^# v+ i
: u* V* o; G' w7 Q4 v* ~+ C; I4 y! F$ G5 h
Master_Port: 3306% l4 W* n% T7 g& f. [ m6 S1 f
2 N& A- \4 z$ E. ?
! n0 H( P; e7 _( b7 s7 u( o% q+ D6 w& _4 e. w+ X# a
Connect_Retry: 607 y% }5 e# r# v! J i0 Z
) G8 k9 A# l! n; I' F3 g, M
7 D* f5 {" P6 O2 q: B. T$ z7 X. ~" e S# o# M" Y1 O C3 B8 T
Master_Log_File: mysql-bin.000005
: r+ k; z% K. m7 E/ t& e* L, Z/ {9 e, G+ |
- ; e2 q# ~7 e2 q
5 \+ y+ t$ I! V( s( Y Read_Master_Log_Pos: 120
( }. X2 z* G) U' z: J* m6 Q/ _) P! H2 j- c& z- v
- v8 [7 t# E! n2 ^0 u
- G1 n6 N* K4 q& [4 G. Q Relay_Log_File: mysql-relay-bin.000003$ ^- a3 m. Y6 k: I2 Q7 d
+ `$ v: J! \, W$ h' Q, {
. h% b: ^3 q4 U7 s% O
7 s* ~9 d, x4 E/ z/ V9 c* i3 _) k Relay_Log_Pos: 283 ~& g X9 ?( V# w. Q4 Q
, |6 k' b$ u6 W7 q- 5 s' X9 w6 n5 {" H: [
2 a- t% Y# A& i9 s
Relay_Master_Log_File: mysql-bin.000005
: ]6 n ?+ M: L+ g7 |; z7 v
8 Y7 p* |% g9 [. O
2 Z& S+ H% f& k1 {! _0 \6 D
( A8 H6 t }2 J) A* K" a" L Slave_IO_Running: Yes
0 F. D/ K! J! H1 E, G
. ~/ o" w' T1 J+ @% Z
: {# d6 F) Z, L7 c" f: @9 k
+ ~8 k4 ~& \- ]7 Y8 V3 G2 j+ H Slave_SQL_Running: Yes2 z D" N8 l0 z7 w! V
; R9 n( g* Z5 g8 V! \% K/ b
- 0 |8 B9 q- `& O2 u8 T9 {, m
' O2 |9 b5 d0 { Replicate_Do_DB: 6 a8 Z1 _2 B8 X8 \
# C# m: y9 g- R: t }, R8 a
- 6 v) o- w5 |. s5 _' q2 y
( P/ A' y+ G. _! o Replicate_Ignore_DB: mysql,information_schema,performance_schema
, \/ J+ q5 B6 E' p/ g7 v: q% i0 B, B5 I6 C9 d
. i: |% ?) q2 j9 e6 {! z
5 |3 [3 t( `/ u( k
' x. { S; U4 u! w
; V8 h4 _5 D: b" `4 u# B9 M8 H: Y$ e4 ?6 a: R8 u S6 [
现在已经配置好双主了！现在相互在两个数据库中都操作数据：
3 R, d% y7 t: k$ h+ v1 j- |! t) q9 v5 }3 K$ k% }' W5 s# C
- 7 N' q; Q' T' ` o9 u! c
& G( y5 o% D0 {. i0 C
#当前在原来的从库(153)操作数据。
2 b' S/ _2 b( i0 r- `
: ~; p3 a. A' [* _6 J- s
7 T K& s1 D e8 V9 y: }5 ~
" v: ^1 [) G5 h7 [- _ x2 \1 w( G' G
) n3 N& q, B+ a# Y
- ) ]# U) }8 o8 _1 e6 F4 o6 U# v! |
4 \' {: S+ i- f: o) \insert into tabdemo(value) values(2),(2),(2),(2),(2);8 ?2 U4 g( d' l8 R% ?! a! S
& A* B9 E1 q$ a- P) i' U* N - . ?6 S! q: _' E/ r' \3 b
+ h' n, S- o. `3 e& g& q$ c# @% C2 l, ^. g- e& e4 ^
' p4 w7 O2 V0 @1 j* ~: b; ]# h& O - 9 a( ], D; N: F; e& ?
, n$ O: a, j0 r q#数据同步正常了！
: p; O& D8 ~/ B8 m3 e9 H/ f) ^
! F, ?- H8 Z- W. v
1 }( P3 X9 m5 k8 y2 a# S
7 M. Y2 {% s7 L: |8 E& pselect * from tabdemo;
, J7 e2 Y* P9 G: [4 |% [0 l/ c7 S# m( n
- . P& U% d* e6 z M5 T# \
K. {. M& O7 R% ?: G% N# i+----+-------+
. J4 |/ ^$ T- U5 K9 \; c/ b' J1 n; D
5 v! u0 j+ Y9 {: k' O5 {
0 F6 p3 x1 Z; L) z| id | value |
; Y' U( }& ~7 K7 ~+ I$ |( ?5 R% Q+ F; Z
- 7 V$ J9 O, i! @
% a( W4 S% i* r3 P
+----+-------+
( m3 j y1 s: V3 M4 G; P9 }8 P. ]& @3 Y" M6 z
0 d9 b$ s: ~# U9 r( u
; \: p' n, A3 P; k* H6 M1 U| 1 | 1 |
) a# ], p7 S, T8 e. e+ m8 a
, D* Z: g0 } G5 }/ o+ E, G( ?
( F& \* L5 o) o3 P) a: c' G O
: [+ [ U6 w1 Z| 3 | 1 |
% Z5 } \- S% @8 l+ y4 P o6 b
7 C& a$ k8 d7 P0 L- - E6 K, N9 w2 V v6 [
$ Y; V- v: Y( a" B; {- y
| 5 | 1 |8 y# Y1 B9 r9 k; X# b$ D
% x/ F' {# o/ P# j8 g6 b: H0 X - * S: I: H+ N d2 K# `
3 b2 Y: H, a( g% Z8 |
| 7 | 1 |
! ^" ?3 a! g- m1 \5 \# _4 X0 f0 R. ? ~8 j2 c$ ?$ Q. E$ h
2 W. D \! A! I$ L" y2 `# |) \' s+ d3 K9 W
| 9 | 1 |/ U% u, F9 n! S" E* _7 W) n
( q5 \; F6 z1 u7 i- 9 n! {* r9 N& S* a0 X/ p9 x( ^) I* N9 Z# @
2 T. g0 ~) _, J" B2 C. T5 M0 I% u
| 10 | 2 |6 \' ]% a+ y& T* f, {0 [
8 j' O3 Q; P6 b0 o/ a4 j+ E' k% @
' o% T4 J2 {: K" y/ p: ?/ g* ~+ C ]4 Z! s" R8 K% s
| 12 | 2 |3 S6 R/ A- z0 w. S Y* H
4 o$ u. z* Z9 w6 e: ]! v
" Z+ U! k$ q# [: h6 U! t8 X! S" {4 C. h
| 14 | 2 |! _1 y. Z/ B X* r, I) t) a
+ [& {8 s1 u# `5 C) D# W2 C
- . G; J$ A$ A' I7 L2 K
- [6 _* y' x% `. u2 D2 H& t$ m, l
| 16 | 2 |% k0 B4 K7 d% e% X; e7 h# X
# Q( [2 \+ Y. ^/ r
g6 \4 u9 A/ G) X0 E- \% c4 T4 [8 y3 U; w9 S$ F( }( v; p
| 18 | 2 |( C8 h* m, \4 B/ `1 O
, @( M0 t* H* Q) R V
: y: Y. J+ [8 M% p8 ^3 g' f3 E
7 B0 ~: j# [$ R, e2 E+----+-------+. z2 l8 v. D5 Y8 q' `& G
+ M/ S: T7 z- Q! d5 j' @! t# f# u- 1 Z z! |" B, J. c
1 {% D( B d* P, i+ U
) X& e x. T' I! x9 p3 m
. _* Q8 m5 M+ Q7 d2 g9 \ - 1 z0 @: B4 Z7 L9 ~$ n, \8 \) v' Z3 l5 X
0 u9 L9 O6 j" p5 d: j
在原来的主库(152)操作数据。% n+ S2 I+ }5 L: b A/ `
% H5 [' G8 m3 n$ Y k* V" ~ - 8 Z% o j0 ?. \
* y, h3 [8 B5 L( n5 Z7 G2 Jinsert into tabdemo(value) values(3),(3),(3),(3),(3);
+ i' ] e0 n! p, B6 T# t' `1 j) q2 B4 `0 S5 e$ Z U& B2 v
! C. `' B8 x! \# \! R f- J+ D2 d$ B2 ]
. r0 \8 W3 V* K8 A. Y5 ~
8 `7 `7 g) N% T' x6 f$ Y
- % I( o- `- G/ L( F2 W! B! i. { l
) F, E! b" u, |4 x: \0 P
#也正常！
2 D! X6 S8 l( f, l8 F% N( |- T. }9 R) Q
. j# V2 f. [$ w: s& _4 @0 f: \. P( }- ]6 \5 s i& @+ x
select * from tabdemo;
' G! x9 Z& B: _# a
4 Q% q6 b! N) J- @2 A( M( P. i
+ o, q8 }2 U! f- C w
6 _2 u2 o' a) ?) g8 U/ E* W+----+-------+
8 _! R" M# ^' A1 v' B. X6 `5 @) w1 B! c, j
- $ U4 D. t* z0 k/ \$ C) T. V
+ ?1 j, c% I* N' o7 k
| id | value |
, w! l& t1 O1 o
7 }8 E) K6 Q& }- |; H
. ~8 u. }1 E4 P. s) S
0 M. r0 f! v! J+----+-------+
U6 r! ^$ @# w+ c H g- w0 z7 ~9 o" _4 o
- / n7 c1 R' v5 Z# ` @
6 q; Q; d' L' `! e
| 1 | 1 |
# S2 j; N" _/ V+ L% S
+ z% G# g( I# u) z4 ?$ Z4 c6 Z" L - , S/ g' m Q# M( S
* R0 V% y$ X# q7 j| 3 | 1 |
0 x' e1 g2 i7 C! z Y) g0 W0 _4 ^' C- E
& w" T- l: D% V4 K8 E8 C; ?: L' J/ m; N% y! L
| 5 | 1 |
% v: Q4 j4 Y8 [8 z5 v
! H6 K3 e2 x" i& q+ Q" ]
% ^5 y6 T8 K2 M4 t, U9 t8 @& t2 K. G) b( O3 ~
| 7 | 1 |2 K' O1 h+ c1 ?6 j' T+ `
0 E8 I; P, j: [2 k
; v3 O4 J8 o! m1 t% D0 k3 ~, n/ P; G5 z' J
| 9 | 1 |- M5 J( \$ h0 [+ M' a
1 B& @# E) Z- V) e* m% w# ?& }7 U
, \4 D5 E* h1 G/ i. [* [0 T/ ^" k) H3 a( y& J" d
| 10 | 2 |, F/ B# g0 ?, p* \
3 o2 V8 u+ T* t, S3 u7 \
: z; K/ K# S* _! I" a# i2 @/ w: u# {0 t% Z
9 G& @) f, A0 x! L/ G| 12 | 2 |
. n* D# R. B! U: X
" z( b+ ^+ R, ~: ?/ i6 I- % S# B, ~8 x0 H* n* a, ^
) E5 P! y) [2 A7 j- f2 u
| 14 | 2 |! n) z1 x% v3 @% I' j* U' F
5 H! W0 Y) c2 e1 Z( k/ n% S - + c: V3 i6 g4 P6 `+ @7 H B7 {, H
- g9 g/ m$ F- C F# p i
| 16 | 2 |
8 M7 R D8 @( ]9 y' z" m% F8 G" C- o* b' Y5 v. z
1 {+ v% f5 |* \7 ]1 M/ D
8 A% I$ d5 M4 C. ] o; i| 18 | 2 |& B* Q* X/ d ?/ N& P
6 ~5 N4 v% U0 ~8 ^& h
- 7 U) ~" M1 [6 Z6 R$ T
; H6 d# u% F, N| 19 | 3 |
- E U2 ~" @4 f* \$ e
! c V) Y% X/ H; {" @+ L
) E7 r! s/ j3 i+ J4 H3 t2 `/ v* h3 K) L. A( o
| 21 | 3 |) k% K, ]+ y+ |$ x
4 R- ?# P+ l8 a7 q* Z V- . S. H3 d& P. M; ?, M' G
- h; |9 s9 K7 U& H4 i" x/ L3 r| 23 | 3 |7 J; B. q* D# ]; P# R" L
. } [3 w8 E9 f; G% X$ V
: @* v( q0 N/ t
) c* z* T: I2 C( ?+ A3 B' d| 25 | 3 |
3 @2 n, O# \( I- B) e" O: V8 P% J1 u
- , M, T- w: i! e3 ]/ i8 o' A% ?' H
! n4 I6 y# \* l" U| 27 | 3 |5 ]" M) v2 l" q4 y5 N$ @4 S+ F
- N- V9 T b$ m+ H8 Z
/ n: H: V6 [ \& b6 B: |8 e1 N
. ]# y$ X g7 {, h2 i4 x: c6 X y+----+-------+/ P( C3 P M7 P0 V% Z
# s' C5 g, q0 Q! }% W
$ r- c; ~3 D- z3 V' E0 p1 v
: I1 _; X3 ~% L& S
" K. F, P; I- u# x4 `
. d" y- c5 y/ ?# l
4 P; [1 u) j: Y9 K9 T2 ^9 H
" O: t9 J6 f! p. k
. w& l2 O' U8 q6 R6 m* N p) H至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践
6 \0 E4 i) H' j( H: b2 m. a/ } w, }* |. m, y/ N* h |) X
|
|
发表于 2018-7-5 11:27:35