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