MySQL 高可用：主从配置或主主(双主)配置

rui

mysql 的主从复制可以实现mysql的多种高可用性，如数据库的读写分离 ，在线热备，负载均衡，数据分布 等。

mysql 主从同步原理：
) i1 S' [0 W( O0 z( p! U1.  master 将操作记录到二进制日志(binary log)中；
2.  slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
3.  slave SQL进程读取中继日志，将重做记录数据到数据库中。

                               
登录/注册后可看大图

7 N* H% l2 P: t% u8 x- Y: M7 p$ ~
5 S1 J* G" H. e  ]+ o; y下面配置：【主从复制】
, D  n4 G2 a$ A% w& z1 J
MySQL Version : 5.6.22
主库：centos152 / 192.168.1.152
* i/ F; A% g* S6 F从库：centos153 / 192.168.1.153

' p7 k7 W3 D% g; {
配置 mysql 文件：
7 U) h' }& h9 x) T) p; G【centos152 主库】
# ^9 I9 o% C3 R  D" J2 a& `[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
, a4 |7 r" W7 O+ c* \datadir =/usr/local/mysql/data
8 Z2 p. j" r$ D; Qport = 3306
8 c) {( U3 g& w. [! z& Fserver_id = 1
log-bin= mysql-bin
3 D/ y/ W) P% M4 j! S) _binlog_format = mixed

read-only=0
/ U5 E  J% r* K& \8 {% o#binlog-do-db=test
binlog-ignore-db=mysql
% g. r& w. N' T. jbinlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#添加后重启mysql服务
[root@centos152 ~]# service mysqld restart

' Z+ }& l6 a9 S$ D
【centos153 从库】
( s/ W7 f7 ~% k% A9 v[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
$ N( Q# V2 G6 x3 nport = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

' L5 h7 i0 @; C8 s7 Yread-only=0
: `# Z/ m7 W. m9 z/ h#replicate-do-db=test
replicate-ignore-db=mysql
! _- q- k$ s5 }4 F- Q2 M# C  |replicate-ignore-db=information_schema
; l% Z, v& x! d3 d& w/ ^+ Treplicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on
#auto-increment-offset=2
0 V" _7 V% k' ^% J; _1 A; g#auto-increment-increment=2
- Z& ]+ W; K& e  D, K7 w1 [9 g

- B& I! \6 X4 z7 a  F. w4 D#添加后重启mysql服务
7 q# q) Q9 g7 M6 U5 `$ J/ a/ S[root@centos153 ~]# service mysqld restart


说明：
log-bin ：需要启用二进制日志
server_id : 用于标识不同的数据库服务器

binlog-do-db : 需要记录到二进制日志的数据库
binlog-ignore-db : 忽略记录二进制日志的数据库
auto-increment-offset :该服务器自增列的初始值。
% k) x4 t- U5 y% }" lauto-increment-increment :该服务器自增列增量。

6 j- |9 B( Q4 L' h6 H0 ^replicate-do-db ：指定复制的数据库
replicate-ignore-db ：不复制的数据库
" @9 V( }2 x7 r4 |/ j& ^0 J# |0 lrelay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。
3 G1 u# k$ @/ Z9 X. [. L3 T. f
如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
0 G/ M+ `! ]2 l7 U0 `9 |# j如上面为双主的设置：
/ T. I6 B- M. G. `2 r! ~7 n. m服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
2 V# K; T0 y" \% ?$ N: X服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）
( O' K4 [' e. b8 J4 w" ]; |

注意：文字图片来自《高性能 mysql》：
0 K' _) l; C4 a) ^: a5 T* J

; t$ C0 n0 @( J2 x+ t" `9 h                               
登录/注册后可看大图

; k1 B1 D& g4 W/ m  R#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。

4 w. {4 S4 T) e$ n1 C: h' Z" W

• 
  
  mysql> flush logs;
  
  2 F6 @, T9 I/ \4 }% A% }% R
• 
  
  * V+ f8 ^, M; {& Mmysql> show master status;
  
  . u$ y. H! o8 U& _
• 
  
  +------------------+----------+--------------+---------------------------------------------+-------------------+
  1 c  Z8 i& x4 O( e5 D( J
  
• 
  ) f* S2 i& A0 o
  2 N0 k; l: n4 S| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
  0 M( V, }& R! s' z3 W0 q4 {- u
  8 X$ _, ]6 W6 K+ a; `1 p
• 
  
  +------------------+----------+--------------+---------------------------------------------+-------------------+
  ! m' d4 k; j& f$ S
  / J( m/ a! H  ^0 N# J4 E
• 
  
  | mysql-bin.000006 |      120 |              | mysql,information_schema,performance_schema |                   |
  + Q; g3 i- D* o1 x5 H
  - |5 G+ Q# @; L. ~( q: f
• 
  
  ) B3 J! w4 e3 M  N2 ]* B1 a+------------------+----------+--------------+---------------------------------------------+-------------------+
  & `4 M' R8 B. _2 e, l0 D
  
  % t5 X; ^% y. c$ H$ H

  H% z) I, n+ K& c, l9 f
+ R& w* B" ?. B( s1 YFile :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
# S; m( v, ]2 x3 a' jBinlog_Do_DB ：要记录日志的数据库
* l) c( W' S: R6 p- E9 e0 j6 oBinlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID
) R* j0 I- ^$ R: |
9 g5 G8 }3 P4 a0 j
' O9 g* Z0 W1 R) R) c& L" T" \
二进制日志情况：
+ ~. D3 N- z+ o$ `! m0 P8 R2 n

• 
  
  mysql> show variables like '%log_bin%';
  
  1 t8 A( N5 p# C
• 
  
  ; l( c" j% R- f& s7 \8 Q+ t/ s" m+---------------------------------+---------------------------------------+
  
  9 i1 R9 M, D  K6 e, o% i
• 
  ; R( U# v0 ~' {# j
  | Variable_name                   | Value                                 |
  
  / x: n5 ?6 \' w% T% P
• 
  
  +---------------------------------+---------------------------------------+
  
  $ P$ P8 T, v* @9 t
• 
  * Z" c0 s/ U' |! J) K* m; |
  ! D- ]0 f' @; Q7 f5 s) F4 d| log_bin                         | ON                                    |
  7 H# U; U3 \1 [8 V# T
  $ g! r2 A* V; P. V4 A
• 
  
  - p5 g* A$ K" r3 l+ p  w" h| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
    l+ a' G6 ~9 T
  4 W* F/ }* Z  ?9 g+ c
• 
  2 [" k; u% P0 m
  | log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
  
  
• 
  - v( L' C% D8 m0 D4 \4 k" Z* j
  & i( ]* f: @1 |# S7 L| log_bin_trust_function_creators | OFF                                   |
  & [" O" Q2 g- i1 h" M, h
  ) }' i( O' b) C- d/ r3 l0 p( f
• 
  
  ( ?9 f( ]" E- i8 O  C| log_bin_use_v1_row_events       | OFF                                   |
  3 ~9 r# n6 C1 P  L
  
• 
  ! J" O2 L/ H6 h: g* q: R5 j/ G' E
    y3 K$ h0 w3 S4 _- T- Q0 G9 V| sql_log_bin                     | ON                                    |
    @* E% D9 T5 m8 _& F
  
• 
  
  +---------------------------------+---------------------------------------+
  $ H  f7 o6 ~3 o$ T5 C# f: Z; D2 d9 N
  $ ~7 Z6 s- x; ]* \& c
  ! Q6 q& _( L1 X0 U% g

/ P/ n& L& y+ Q5 Z' u5 l; u4 m
日志位置：
2 l7 X. _! x) O* |
& A. R& `2 Q: c2 A. ~3 t4 x5 G
7 Y1 m- M9 }, T8 {

• 
  
  7 i% m4 i# u8 |- P, G7 V3 f[root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*
  8 K( V0 R2 n( s. b
  , ~' S( T+ @' g# W
• 
  ( P  w/ a4 V) h& u* S8 p
  -rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001
  
  0 ^4 o; y. F0 @3 i; _+ [$ g) ]' V" E
• 
  
  -rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002
  * g7 t0 g$ f. r/ @5 K0 L6 s+ ?3 m
  
• 
  
  -rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003
  ) v& \" X4 X2 m' g! M
    f8 j8 Y$ o5 l  A. E
• 
  4 P& Q: {1 Y6 L* `) t
  -rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004
  0 k5 B# m$ l4 o  f
  
• 
  & n5 f; K, h) @
  . x7 |4 i7 X  ^  A-rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005
  
  
• 
  
  -rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006
  9 n2 s( ^0 Z: O, k
  
• 
  ' B* ~% n' J: ~  f2 r( p
  -rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
  
  5 r% T% N' C' R1 m
  

4 Y$ O2 P, \: x- ]: n
#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！
8 I2 E+ i5 L7 l% j
mysql> flush tables with read lock;

; w) g6 f6 q' r0 j/ J$ I备份主库(执行以下一行脚本即可)

) x- q) {- P4 x; k: O

• 
  4 \1 Q  f" L% a- I$ J9 M% j* Z! d
  . W6 R. w8 |  M% r  K[root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  / _3 j8 r2 Q5 v
  # x) f+ n5 I8 m9 s2 W" [& S4 @* m
• 
  8 O+ m# P& N  J
  [root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  - G6 I5 U5 g! a- W5 K3 ~
  
  

查看当前日志记录的位置：

) `' a( U' l+ l; d3 Q

' W  t/ K: d- y+ R& y# H

• 
  
  & y2 `! l5 V; Q/ j( [* F3 y* wmysql> show master status \G;
  / _: E: `2 n4 `9 a" V
  9 W* T% a. E/ O3 B1 b( B/ N
• 
  ' \# v! [2 B1 W% V0 S! y9 Q
  *************************** 1. row ***************************
  , l9 g; V  g: p( q5 L& k5 W
  
• 
  2 A. a$ I8 C6 B6 j' i6 O6 W
               File: mysql-bin.000009
  
  ; Y5 W; _; t# |6 Q! h- F
• 
  . U$ p* N. V3 z  ?# s& c  p: _$ X
  3 n* R; c9 [* p         Position: 120
  
  + i+ z$ A8 D" R5 l3 t4 l- v
• 
  
       Binlog_Do_DB:
  
  % J9 e1 R. ~) L" i
• 
  9 C* Q" K, l9 z- r9 T: K$ T
  ) b! l7 Z8 N1 }) `! j: g& k* r Binlog_Ignore_DB: mysql,information_schema,performance_schema
  
  
• 
  
  Executed_Gtid_Set:
  
  
• 
  $ E3 }: k/ Y& E0 i( Y
  1 H" C  j( {) w% _$ @7 P1 row in set (0.00 sec)
  
  
  

. `% Y( f( o5 k: T! Z
备份完后，可以解锁了

" @- Z; A0 {. o& ?4 L4 N
3 {# ]* X8 C: F( d
3 E8 `" ~/ ?/ e2 b) [2 qmysql> unlock tables;
9 s6 |5 j% X' q; d# B+ r
在主服务器拷贝备份到从服务器：
# C7 v$ m6 j& U# Z6 s  _; |$ B


" y$ m5 C8 b& K6 I, L2 {1 b

• 
  
  [root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp
  
  
• 
  ' g$ C, \0 n  f$ X' ?- n1 ~/ _
  ' o8 ^: k/ T7 t, |/ @
  
  
• 
  8 m8 f9 r1 \$ W4 u) G
  ! N3 q3 S. S% n; a' f/ D#不行则先关闭主从服务器防火墙
  
  : I, y# S6 L) ~! K
• 
  
  /etc/init.d/iptables status
  ' d8 S; O0 ?0 z5 j  t/ f
  3 v6 J8 a8 ^3 \5 ?1 ]& G! j. k+ |
• 
  ( t5 \9 T- s" r  @' l/ W: O
  * n' l$ O* I4 Q* n8 d/etc/init.d/iptables stop
  / b- o3 _+ M1 f( |6 M' y' ]6 a
  
  

. `5 B% K4 z/ D: P) O1 c  g; b
% {8 J8 m/ ^: A8 O+ S在从库中还原数据库：
6 J5 @1 k' @$ @2 C( y6 k


[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql
/ J4 n; z; v8 p  ]
; k& e% R' }2 _' v; b$ v) ~主库创建用于连接到本地的用户：
8 z# M+ T% a0 i$ }1 J- l: l1 {0 G7 z

: \) t1 Q5 p- S6 S& E9 e
6 T3 @/ f! p( w7 n: M: Z! k7 u; @[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';

  B9 o0 s1 ?& G( o1 l在从库中连接到主库：

: c( D) [, Y4 n; L) j$ Y2 R% L
' l' c6 b  @, U/ ~& ?
( q0 p6 Q3 S) O

• 
  
  mysql>
  + |  _- e' I+ r* R3 ~( t4 q
  ! S7 n' Z1 V: ]$ h7 b
• 
  
  5 N9 m4 k, |& `1 ^* R: {CHANGE MASTER TO
  9 y  y; e% U1 A7 I4 V
  
• 
  # c5 _& s* z9 f7 j1 @
  ) C9 W4 T( C( @, s       MASTER_HOST='192.168.1.152',
  
  + e: \! Z: A+ ^3 b5 c6 h$ m5 P$ N
• 
  " O# {+ U) v" C. D8 f+ {# S
         MASTER_USER='repl_user',
  ) _0 O/ x  Y6 w
  
• 
  . ~6 c- U  z% D2 H2 C! k4 @
  1 l4 ~% g: Q1 W2 F: q, H       MASTER_PASSWORD='slave@153',
  % r' t2 W' V; Q+ f1 O
  
• 
  ( t1 e+ h8 w8 Z! v5 n% c( X
  1 s$ N  s0 a- t       MASTER_LOG_FILE='mysql-bin.000009',
  8 O; Z5 _) x5 k5 }* s
  ' v, a# b1 M$ V& K8 G# C& }. Y
• 
  
  " ^7 K- D  _* O- x# J0 F       MASTER_LOG_POS=120;
  
  - R: b$ R$ c0 U: F5 [, p! H
  ! m2 Z! }  M  Z+ l0 M# L

6 w9 O' K+ x6 K; ~
重启从库mysql服务
$ z9 A- s. M. X' Z) g0 |2 V1 D[root@centos153 ~]# service mysqld restart

, {# x* |( i8 F: n% k& M  a
2 M( B1 Y* t4 Y5 f
& f* @) }4 i" C$ Z6 z4 O查看从库同步信息：

& p" c# y/ p' U+ L
5 D7 W3 m+ [0 D' }" @$ V& @

• 
  1 v4 b: [0 r: \# A
  % {9 |2 Q$ ?3 Y: O0 Z" e+ bmysql> show slave status \G;
  
  1 _8 L6 L3 a: V  C: J
• 
  & A$ O9 M" l" J4 |! x" K9 H2 X
  *************************** 1. row ***************************
    N" C8 O- D( R
  
• 
  
  & N  Y/ K8 B8 @2 v& ~               Slave_IO_State: Waiting for master to send event
  " M, C5 A* o" x4 Y# M$ y
  
• 
    F1 G4 p1 R5 Y
  3 W$ M5 f& \" V4 T; O- g  B                  Master_Host: 192.168.1.152
  # d# ~2 f5 v  E. J. m+ U5 y8 H% F. p
  
• 
  
                    Master_User: repl_user
  - _* V; i0 a; s+ c: S9 X
  2 X+ c+ k4 p3 L% B$ `
• 
  
                    Master_Port: 3306
  
  
• 
  % C- @2 B( w. ^
                  Connect_Retry: 60
  
  
• 
  
                Master_Log_File: mysql-bin.000009
  
  6 w& Q$ o2 q. w0 e2 P
• 
  
  6 o* b/ @+ A& g- I          Read_Master_Log_Pos: 120
  
  ! r; w( X( R  h0 W- U# F, o
• 
  ! X$ Q7 S3 P. u/ q
                 Relay_Log_File: mysql-relay-bin.000003
  6 O: p$ Q2 O% L
  
• 
  
  ; t0 R. E, }) j( F$ ~                Relay_Log_Pos: 283
  7 A# E9 D0 y$ t
  ; L& i- O9 y8 D$ M# }* F" A/ s. E& G9 o
• 
  6 ?9 e2 d. I0 H& V  p
          Relay_Master_Log_File: mysql-bin.000009
  + h" ]; V" |! U8 j6 `9 [4 S
  8 c4 v8 L( O5 d) @
• 
  , ?8 D  \: N& c- }
  . Z: E, G  ]0 F! X3 H( r             Slave_IO_Running: Yes
  
  
• 
  0 n) L. m" n, j5 S% _
              Slave_SQL_Running: Yes
  ! Q' I) v" U: X( ?4 p8 t2 z) b
  
• 
  
  ( E9 A' {7 U1 K; \) ^6 j, v              Replicate_Do_DB:
  ' J+ H( l  ~& o' R$ W& f
  3 y: q6 k$ y- ?4 e
• 
  ) c# y' c. B, I
  6 `5 y# Q9 l: Y* U" y          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  
    q+ I# b* Y6 r6 }% q
  . a9 {: `, W( h3 I5 f/ y  A. k

2 [7 [6 u" ^5 {' Z! X" c
可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
: j  g; F. W( S% P
, S" \" T& D5 H9 ], N+ ^
0 n6 E* w, l: R( O  F  }
, w# t; J$ K- w4 n$ h7 I( D, t9 A现在测试同步情况：

* M9 a  o( Y1 c

• 
  
  #主库创建测试表
  
  ; @( ]/ r. H* e% ]& Z+ f* u
• 
  & I) s$ i. m( F8 Q! Y: p7 Q: O
  ( ~$ S) @, R% G! R% G! l; w8 acreate table tabdemo(
  
  
• 
  7 U& Y. e( F1 X% t  ?0 r
  : V7 Z# h8 K! h( _' p3 Z7 i! jid int primary key auto_increment,
  / z. p) L2 ~; o
  
• 
  
  $ X" a& C4 [7 a' @8 Ivalue int default 0
  6 b3 B! e" r4 H; _, T: o9 ^
  
• 
  
  2 U5 v/ e8 s; c1 s1 F" Q( X) auto_increment= 1 engine=innodb default charset=utf8;
  
  
• 
  : G6 V: k% I" P( v
  
  
  
• 
  
  
  
  
• 
  
  insert into tabdemo(value) values(1),(1),(1),(1),(1);
  ! O5 R+ w; g* m& P& P6 E
  
• 
  
  
  
  
• 
  7 u' R8 V$ S$ V7 i0 d5 ~
  % S) r+ `1 m2 F5 F. h$ K
  % W8 m3 w  [+ N0 p
  
• 
  % X9 i' y& b! }0 Q
  select * from tabdemo;
  ; [& d+ S  A! Z% f# b1 i
  , w: @# B5 {4 a: q, P  b
• 
  
  +----+-------+
  5 @0 ^/ w1 Z, ~% }
  
• 
  ( ~: }5 j% `9 E, r# b$ T* R9 H
  | id | value |
  
  ( i" ^  J& B8 n, O7 g1 u
• 
  6 t9 ~$ @$ }. c, j1 s
  & ?2 x$ q7 I" ?6 I+----+-------+
  
  ! `3 u# W$ [/ ^( `) e9 Z, y
• 
  3 L$ X2 ~; z; i4 t; e$ H
  2 I% M2 r. _  Y+ k|  1 |     1 |
  
  8 a! r4 M( P1 \, v; X! P5 Q$ c% x9 c
• 
  8 X% e$ D4 h- o$ U4 ]
  |  3 |     1 |
  
  0 a  A" @1 x3 t- t
• 
  
  |  5 |     1 |
  
  
• 
  0 s0 ~/ n& h" K" x4 J: d# H
  |  7 |     1 |
  / h* r4 }" b$ x$ |/ P
  . F- `) _* l0 |, Z' ]9 Z# c( x# Y
• 
  * a1 r4 f+ `  t" z( T' _* P
  |  9 |     1 |
  
  
• 
  5 h% w6 v0 |. L% Q7 t% A9 F; l! c
  ! N9 x$ R' j/ j" Y8 B+----+-------+
  
  
  

3 j' w4 `6 J. H0 E# V
主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~

6 I9 e7 j: G9 C5 i7 L
* Y" I0 F$ O  r' {
- a5 D# p2 |: b4 `; F==========================================================================
/ M. D7 m4 D8 ^$ M: w1 c7 U! f==========================================================================
! j! W! ?4 W) R
下面配置：【主主复制】
. O" T5 x; A' }

- n* h2 a+ k  ]1 L2 O更改配置文件：
& g4 u: Z. B: m  u+ O" S
【centos152 原主库】更改配置文件
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
2 O8 |% g: l) K. A2 _basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
# G; N  c0 a& {  a# s: Uport = 3306
0 a. a8 h+ E% d0 l  q( N' x. b  userver_id = 1
9 t; ~2 O5 A  ^, u1 ^log-bin= mysql-bin
binlog_format = mixed
( x5 G2 c& a; Z
read-only=0
/ k& G% z9 V+ y) b: k#binlog-do-db=test
binlog-ignore-db=mysql
2 c9 _  N9 |1 C* a4 Qbinlog-ignore-db=information_schema
/ e4 ]5 u5 o( ~7 Pbinlog-ignore-db=performance_schema
auto-increment-offset=1
( P: m% Y6 l" h  i- m( L* Hauto-increment-increment=2
: m( o+ E+ [) s- Z5 v
#主主复制的从库设置（新增）
#replicate-do-db=test
' r6 n. z1 Q# S$ Y/ U0 N8 k3 m8 G' \replicate-ignore-db=mysql
$ I, A, v5 m3 k1 Kreplicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
7 y' ~$ ]: c' n& u  Zlog-slave-updates=on
6 n: e/ B' @$ X% l0 Q
3 `' s# r" A2 I0 X/ s; T* i4 Y
* P8 p9 ~- l, v7 x: n" ]- Q. ^【centos153 原从库】更改配置文件
/ t! U( r4 A& }! A[root@centos153 ~]# vi /etc/my.cnf
% x  w0 {& O4 B3 ~9 }" r[mysqld]
& I1 K# P0 R! X$ xbasedir =/usr/local/mysql
, j2 q* c3 f1 \4 U% `- A# ndatadir =/usr/local/mysql/data
3 ~; z% e8 E" }- L; T  ?* j6 ]port = 3306
server_id = 2
log-bin= mysql-bin
5 a( D; B/ ]$ \binlog_format = mixed
# J/ e% F* [4 y( _; P3 b1 U( {
  Q6 t! I* R& S! U4 O5 Q! J$ _read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
% m8 d% G, ?6 J! Qreplicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
( G% `9 r1 h; T( v4 g. T. I7 J, q$ L% @relay_log=mysql-relay-bin
9 a$ W  S0 Z% l4 j8 V0 y& nlog-slave-updates=on
/ ?% |4 l. d8 e( T7 Y
5 a3 f. [9 y! a6 f6 G& t#主主复制的主库设置（新增）
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
& O( V5 @$ R4 F: v1 B4 Nauto-increment-offset=2
auto-increment-increment=2
8 @/ d% {3 G0 b. v2 Y. r# U

添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart
* y0 B. j# x$ g& f/ e# y; G

0 n/ I% O: q! d3 d5 _原从库(153)中创建用于连接的用户：


3 F% x/ r* z* j  L' u; ]  L5 S( ~' S[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';
. q) _: H* }  n( e# K: F
原从库(153)查看当前日志记录的位置：


6 f) j% s1 H+ _0 `- b

• 
  / q1 X& @6 s, X, t
  ! u4 U* p4 C: u/ p7 Vmysql> show master status \G;
  
  
• 
  
  *************************** 1. row ***************************
  " w/ Y2 z. G) p3 K. I* [6 r/ w/ d
  
• 
  , p6 m' g% X0 X. }; @  g4 L* \
  % {- t2 H8 ^2 [3 I" e& L             File: mysql-bin.000005
  ; P  i' t5 Y. k* }' [. [
  ! _. D4 O8 R7 X2 n
• 
  
           Position: 120
  
  
• 
  
  , d( Q1 a9 z/ ^, ]     Binlog_Do_DB:
  
  ' s8 ^# e, A3 o4 V
• 
  ! p7 r: m! ^2 @0 e
  ! m4 i& `4 D7 }; R. ^ Binlog_Ignore_DB: mysql,information_schema,performance_schema
  
  
• 
  ! b/ H# r6 N5 \3 F2 Z
  / Q  K, c1 {0 oExecuted_Gtid_Set:
  
  $ u2 f: g4 p- `  U. A# s  x7 x+ H  t' [
• 
  5 Y7 {( z& I" u
  1 row in set (0.00 sec)
  
  7 F: H7 j) @9 [6 j
  

( Y+ m  C- ^% t% z9 a# A3 u
; N9 `( A  o# A  t$ b2 v在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：

• 
  ! _' I( A7 l- j; H+ S
  5 l9 D% D4 p8 w1 l/ }mysql>
  " _8 D# u( X9 B% f" x* S) ~# q
  ) O+ b* ]' [$ S: n, g% }/ ?
• 
  
  CHANGE MASTER TO
  
  - y  t/ O  c! c! W$ M3 F
• 
  
  ! P- j: S5 t* E" B' o. H! y       MASTER_HOST='192.168.1.153',
  
  
• 
  
  # ?& a7 y- t- T- F5 X: p! A       MASTER_USER='repl_user',
  
  " G9 J) K' v- f9 G5 s, V# z9 b
• 
  
  ) W% H! C. r  t  D: k: h       MASTER_PASSWORD='slave@152',
  
  + \! O! I6 w  u3 J. [- I
• 
  * `5 g  H6 Y! T: @5 T- g/ p
         MASTER_LOG_FILE='mysql-bin.000005',
  
  
• 
  
  2 c' A2 |( U% Z       MASTER_LOG_POS=120;
  0 {0 W+ p1 i' Y( Y. r
  
  % X8 z  W, D- y0 S& Q2 |9 `7 `. E

- Y7 q/ }: @8 |  P/ g( @8 N
8 m* i) ^3 B! H! g9 N) P7 C重启从库(152)mysql服务：
% ?* K+ U- P7 Z3 J- }[root@centos152 ~]# service mysqld restart
5 L  G& N  X4 E5 e( q  M2 N/ Y! g% M

0 G! G; J0 U3 B
" k8 a% P5 A) E4 @- ~查看从库(152)的同步信息：
( J. c/ H1 n; O1 k% d* a  S8 K
4 S) B9 m$ s" v" l, X) F+ w
0 ]7 W$ s' n7 F" J" E+ z% H# X0 K

• 
  & u1 `7 ]7 u& `  a% P; U
  mysql> show slave status \G;
  
  
• 
  
  8 j3 d* i$ }* i  O) Z! y/ O*************************** 1. row ***************************
  3 B2 j4 s8 X0 S
  # |* o- O: k2 p' T: q! _
• 
  
  & v6 \5 `8 J' ~  n8 x9 \) t; u               Slave_IO_State: Waiting for master to send event
  
  
• 
  2 q# z+ L/ W, B
                    Master_Host: 192.168.1.153
  * ~  {9 D6 ^& m2 ~# O1 ^
  : g9 s$ S. r" g
• 
  ; n3 V# x( N  T: R
                    Master_User: repl_user
  3 e% ?; p% F1 g1 M% e/ H; U
  8 }7 ~! x$ q1 g. i- A# g
• 
  + j% C5 l6 L, r7 i
  % R9 k# W* ~* _- t' R8 a, ]                  Master_Port: 3306
  / t: P1 i2 m" X; |
  $ q8 ^2 |" t* @6 }
• 
  
  7 C1 F. a8 H9 P& x                Connect_Retry: 60
  
  & W# Q0 E* V+ C$ J0 t: a7 }
• 
  
                Master_Log_File: mysql-bin.000005
  - n* h% r) c- ~2 W% O6 Y4 P/ Z
  . f( h2 N- n2 @, r
• 
  4 Q/ b, J3 X/ Z$ J2 m
  7 M1 L. h0 E1 N- i          Read_Master_Log_Pos: 120
  2 m5 s; a) n; k$ M) b& _" J% N2 m
  7 _: k+ b/ C6 I) Y( J6 D: g5 Z
• 
  
  1 X& |# X1 y1 b7 e1 G               Relay_Log_File: mysql-relay-bin.000003
  & z" I6 Q' l* T* V
  
• 
  + {8 E7 E9 |' \3 |% k2 E) b
  7 ?' e& b7 @7 l/ I- R* e                Relay_Log_Pos: 283
  % b" j1 y# \1 o1 A- K" z- Y5 {4 ]( k
  " I* M! A( J+ w% A+ Z" O# D' x
• 
  
    f6 c8 [2 k1 e( o$ ]6 F        Relay_Master_Log_File: mysql-bin.000005
  
  
• 
  
               Slave_IO_Running: Yes
  
  
• 
  
  ) A4 D& R5 Y+ A" K+ i5 Y            Slave_SQL_Running: Yes
  
  # q# f" c/ C% ^8 ?- E) `
• 
  
  6 L* \( d  t- a2 k9 ]" P              Replicate_Do_DB:
  
  
• 
  & u6 I% `" A# y' E* j5 e; V
            Replicate_Ignore_DB: mysql,information_schema,performance_schema
  ' I. Z+ J+ k) F; f
  
  ) p3 [+ q4 \' }

* d7 c1 o- `# _3 {3 ?+ P5 u3 ]( B+ U
" S3 f$ @9 }/ B, X! G现在已经配置好双主了！现在相互在两个数据库中都操作数据：

# R4 M8 ^, Y% T4 p

• 
  
  $ W' o" e$ ~: A! G# {2 I#当前在原来的从库(153)操作数据。
  
  
• 
  
  
  
  8 X! B# ~& l: V; g3 _
• 
  $ {/ x6 g! N9 w
  insert into tabdemo(value) values(2),(2),(2),(2),(2);
    s, y+ B2 w" {: R" `
  
• 
  9 F! T8 T; L# I* N8 {4 b; h
  
  + A6 {5 n# ?( o7 |' i
  
• 
    c* I4 d( |2 z8 ]3 N1 P
  , E$ g  w1 O! N8 v  L3 r* o#数据同步正常了！
  
  , B/ E/ H$ j5 X0 B) {: ]3 S
• 
  4 p% [, G4 i. D9 k9 c& |- F: ^
  ; j. C% E: a1 r+ K3 d8 z6 xselect * from tabdemo;
  ( }7 I! [! ?4 w+ r& B
  : ?# ?/ S0 @# c- b2 \# G( {
• 
  
  +----+-------+
  ) p+ T7 P; P7 n) Q# ~
  
• 
  ' a* P. ~  N  b$ t7 f" K6 y$ C
  ' J6 h% P$ j* z/ q' h| id | value |
  % v3 F1 a0 F# {7 |# Q9 P' \  c
  
• 
  / M( V4 N5 X2 D/ S! ^0 Q
  9 s; o3 r4 F  H" W* |+----+-------+
  & Y/ u, D: W2 P: g
  
• 
  
  . k( Q  y+ F4 h7 d0 Y0 _; y|  1 |     1 |
  & X) f8 s, Q+ ]: D( M8 |3 `7 ~* e
  
• 
  
  2 t; o) c$ }5 f) ?|  3 |     1 |
  
  ' q4 z$ r9 F2 y; q
• 
  . `+ x, P9 v: U
  |  5 |     1 |
  
  
• 
  
  ! z" u: [9 L5 u7 m- D; v! ]|  7 |     1 |
  
  7 c5 @$ G6 N7 m. G4 V! ~: J
• 
  5 T) ~  T- m7 S! f. B
  |  9 |     1 |
  
  
• 
  
  ! P5 \' K6 F3 L5 g: G| 10 |     2 |
  / V# s, t1 f3 x+ F  A/ Z
  % @- d7 K' J# P9 [. L8 \. {' C
• 
  
  : _- f* S- N( G6 N' x' U| 12 |     2 |
  
  5 U4 I" `6 U$ S# O1 o6 B
• 
  
  8 g5 o! c6 F) w" D4 ~0 S' o' A1 }| 14 |     2 |
  $ m  j8 H% z! \8 r2 [) a" G- E
  
• 
  + c1 V( t& h( Q1 c: ^
  # D4 i; X' H# ^! ?& S| 16 |     2 |
  
  
• 
  
  | 18 |     2 |
  . R! m# Y, ^- q
  
• 
  
  +----+-------+
  
  
• 
  
  2 l7 }0 Z$ E+ {0 w# e9 X* ]; j
  9 T2 {! x' Q# u. a; M, \
  " t  w: _. z5 q/ S+ ]% K
• 
  
  ! W/ M) T( Q- w/ w1 f  [在原来的主库(152)操作数据。
  
  : r# `0 X" K* y# D. o1 u1 S0 b
• 
  
  6 G7 L) O( w3 S  g. P$ T6 xinsert into tabdemo(value) values(3),(3),(3),(3),(3);
  $ p. s2 i8 ?; L) r3 m" a( H
  : h% W# [! M, P: q( _
• 
  
  & ~" B- S2 o$ G) [; G  w
  
  - ~( t. O% ~4 b2 |/ b& O6 g# D
• 
  & [' X" h* l+ N' f
  # g4 Y; I% q0 R0 m#也正常！
  
  
• 
  : P  M1 q8 p1 t, S" h% V' X
  select * from tabdemo;
  3 c  V) Z2 p: y6 C
  - s9 C" q: d0 n2 n1 x
• 
  
  +----+-------+
  0 i" ^; K. I0 x/ @6 U4 ]
  ; q8 [) t2 N3 |; d# Q% F, G! X
• 
  3 K& s( I9 A) }- f
  6 R. O# A2 j9 f8 p| id | value |
  , Z8 A/ J9 m! S; N
  4 }( o4 u; W+ Q8 R
• 
  ; l8 v% d- o5 E1 K
  : F  D7 O, g" [6 u+----+-------+
  
  
• 
  
  |  1 |     1 |
  
  
• 
  ( |4 J5 ]' M* w
  |  3 |     1 |
  
    F  j9 i9 t$ P' N& _9 p# j
• 
  # m) B- ~7 v6 i- c2 @
  $ O$ A2 R- h; K|  5 |     1 |
    _! K/ \9 V0 L$ G( z
  / m0 H6 e; o  a4 t' B
• 
  3 t! j  Y" U  u
  , L9 ]7 s) i$ n4 U  T* X|  7 |     1 |
  3 }1 Z4 Z  x" X: k" i% V
  
• 
  
  4 K' w# c" U  ]; f1 A0 w! ?7 E" w|  9 |     1 |
  2 v& a/ h- _0 m' [+ H5 L. o8 U# j
  7 J8 {( T+ ]% D$ ]- O" G& y4 l
• 
  % m# Q! y0 W& j* W( ^
  | 10 |     2 |
  
  4 E; }+ f3 Z& _8 c# q+ u
• 
  
  / ?, O/ C) b, I( R' J- H! W) [| 12 |     2 |
  & g) v! ~7 B7 r9 {; ^* ~$ I
  
• 
  
  7 e! }1 n+ L# \/ @- t| 14 |     2 |
  
  
• 
  
  8 \$ I' g, Q* A2 Q7 r* ]| 16 |     2 |
  4 ]0 a  i$ v% [5 Z8 f5 c" r, Y- S
  - T9 z8 u  S( x3 }8 }& ~
• 
  / c" e4 D3 [6 Q4 }: w3 Y9 c2 {7 ^
  | 18 |     2 |
  0 c' O5 Y9 ^9 R8 B+ a8 @/ o% L* A
  , l) x) u; Z3 |, G! I0 s* ?
• 
  & w% \. W+ n, ^2 v: c4 l
  | 19 |     3 |
  4 M2 f, e2 p: M) i
  
• 
  % C/ `/ Q4 f6 b* X' Y" L. j, f4 G% Q
  4 x. }7 g6 g6 E4 t- p| 21 |     3 |
  
  6 ~: c6 s+ W/ K' o" d$ F: ]& b
• 
  
  / i/ H+ ~5 M) i( i/ `| 23 |     3 |
  7 y- h( G) U; ~! M5 q2 @
  " i2 D0 ]9 F4 w$ J- C# g. G5 L
• 
  ( H+ Z' ^" X- n
  , i; @$ `; a  K; y9 d- D5 a| 25 |     3 |
  3 G7 w- M, |9 m6 H+ C4 a6 M
  
• 
  
  | 27 |     3 |
  
  7 o; F3 B0 |7 Q2 H7 r! A; b- r' @: o
• 
  
  7 u* k9 }8 q2 i" p6 e+----+-------+
  
  
• 
  $ n; h2 S" r5 i4 T  y# h
  
  4 J5 l6 U: E1 Z3 I$ \( F3 i7 w
  4 ~# s1 H, d0 o. ?
  1 x, A* l1 t* h/ o* A* H

! R) {9 x) u' L/ b
' m/ T, K; H# p$ o7 q至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践