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

rui

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

+ R. Z, W$ v; l( o+ [mysql 主从同步原理：
1.  master 将操作记录到二进制日志(binary log)中；
7 W- N0 l. T; f! [0 l' z& s: t2.  slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
3.  slave SQL进程读取中继日志，将重做记录数据到数据库中。

                               
登录/注册后可看大图

% ], b. r+ Q  K2 C下面配置：【主从复制】
+ {. N. N. ^8 [! U$ r$ V
MySQL Version : 5.6.22
主库：centos152 / 192.168.1.152
从库：centos153 / 192.168.1.153
) Q3 c* C4 J6 E* s+ e" T( h- F7 ~/ K

配置 mysql 文件：
3 G6 [- d7 p) ~【centos152 主库】
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
' V* ~/ C3 F$ }, x9 Obasedir =/usr/local/mysql
datadir =/usr/local/mysql/data
- c9 C- f* D7 [. A4 a. Sport = 3306
server_id = 1
log-bin= mysql-bin
" z5 T$ _* j8 M4 j! ?* g6 Sbinlog_format = mixed

4 I, `8 k: y5 G4 Sread-only=0
% R2 }: Z/ r6 Q1 M. f/ o#binlog-do-db=test
binlog-ignore-db=mysql
/ ^; v- W1 u4 x: Q. F8 V* Gbinlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
7 U0 ~/ [& t3 N$ _0 x& r, J- Z9 o; I* ~auto-increment-increment=2

#添加后重启mysql服务
[root@centos152 ~]# service mysqld restart
3 O& U8 G% u) H) i  h; x. p- C
: m, ?5 S, `3 Z( W
【centos153 从库】
# H5 H6 A7 I- c6 h0 x0 X1 E: q8 Q# F[root@centos153 ~]# vi /etc/my.cnf
) _; n$ ]* G/ w. J6 d4 Q[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
# W0 M8 h$ h* f* R" tport = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

9 i2 {4 N8 Z+ V- i' rread-only=0
+ X$ Z/ E- K( s. |7 j#replicate-do-db=test
replicate-ignore-db=mysql
) q" M) |5 v- Y/ |7 ~replicate-ignore-db=information_schema
0 m8 b, n" w! f1 Jreplicate-ignore-db=performance_schema
1 ^, Y- m. @( `4 Prelay_log=mysql-relay-bin
log-slave-updates=on
#auto-increment-offset=2
3 u/ u3 \9 _& k# `) o" z8 w, q  U# W#auto-increment-increment=2
% j# p3 y* V9 w' b6 P0 D7 K* U

, |8 `: |- s, ?#添加后重启mysql服务
' h* C, x) S/ y/ A2 b[root@centos153 ~]# service mysqld restart
2 ^4 i4 \4 Q: B4 u$ O& ]+ V" g* L
  }) }5 `3 d3 z, V7 z+ ]0 F
3 ]: z( g7 l( g9 @  z8 \说明：
0 Z+ J% I; J9 @) z' z! b4 ^log-bin ：需要启用二进制日志
3 R" y2 m9 H) \server_id : 用于标识不同的数据库服务器

) s: M: p6 U% v; U1 }binlog-do-db : 需要记录到二进制日志的数据库
binlog-ignore-db : 忽略记录二进制日志的数据库
auto-increment-offset :该服务器自增列的初始值。
auto-increment-increment :该服务器自增列增量。
: K0 a" d8 T. M' f# P; V. ?
replicate-do-db ：指定复制的数据库
replicate-ignore-db ：不复制的数据库
' a6 ?, |% D% e. u8 R' F1 p  Wrelay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
# D. _5 |( Z- r' slog-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。
* h+ e6 e, e. }0 t
" v, p; g& v% |$ N8 s如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
如上面为双主的设置：
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）
1 U. N! w2 p" ?3 G2 ~
/ @5 C: ~, z* h0 `+ f+ A3 z8 C
注意：文字图片来自《高性能 mysql》：
4 a- e$ c9 C$ o- E* R/ e8 J

6 g: X" }/ q2 j" X# o                               
登录/注册后可看大图

#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。

: f$ r1 V- z- a

• 
  
  mysql> flush logs;
  1 V- x6 {' |- a3 V6 l1 P( W# D
  ) k. D6 q' _" Q4 k7 j
• 
  
  mysql> show master status;
  6 }: L- P5 _* Q) e3 X4 h
  . X& s: p2 r/ [/ ^8 ~$ N+ R
• 
  
    B& q- E5 ]( N& U( W  w' x3 w9 W+------------------+----------+--------------+---------------------------------------------+-------------------+
  + H0 }; W4 t7 d/ W4 }8 z! o8 J
  
• 
  
  1 `$ a( |$ O9 X# e! K/ d+ w| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
  
  - ^$ {$ z' K: k9 O; Z1 G. `0 m
• 
  : ]) \) e/ u. Q0 x9 O* ?
  +------------------+----------+--------------+---------------------------------------------+-------------------+
  / f( o* K- h3 q( J! S$ {. s. C  h
  
• 
  0 _/ U1 [" y$ ^" _8 J' Z  o
  | mysql-bin.000006 |      120 |              | mysql,information_schema,performance_schema |                   |
    u0 v9 E/ Y; H, ^. c4 e
  , G& h* }) ?: `+ b5 y* U
• 
  1 ?; \" u6 b: G' r9 R& J
  " d* p8 x6 m: N8 i+------------------+----------+--------------+---------------------------------------------+-------------------+
  
  
  

  `5 G2 |! O. V" [+ JFile :当前正在记录的二进制日志文件
Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
Executed_Gtid_Set ：已执行的事务ID
9 k+ E6 |, |( {3 i

9 w( O& S4 \2 b
二进制日志情况：
( s+ [2 t) A: W0 w) j/ a. O/ G: b

• 
  8 x/ z8 |6 ^6 o$ u3 l8 _5 x  y2 e
  ; P5 M+ D1 A7 u. V7 W+ L1 \1 zmysql> show variables like '%log_bin%';
  
  4 B. n# L9 y2 S* t- C" _1 |' ]
• 
  
  % v7 H2 @1 @8 K6 F: g+---------------------------------+---------------------------------------+
  7 ^% @  R: z- [& [4 K
  
• 
    t" S% ^7 K) S6 t
  5 [1 q  {, z! [4 t" y# V) C| Variable_name                   | Value                                 |
  
  
• 
  
  +---------------------------------+---------------------------------------+
  3 _3 B4 z$ U0 X. K3 I" B: J: i2 c
  % k1 s0 Q1 K/ K
• 
  - x. o8 j  u* I$ B7 l$ l
  - X# x5 S" ~: a) ?# P1 A7 h: R| log_bin                         | ON                                    |
  0 H5 z: y0 O! [9 D+ S
    l( o' M3 r; g3 S
• 
    \) V* w, v" J+ I9 p
  3 _" E8 w% n  c' ?3 Z3 I| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
  * i2 u, b' T  T+ r1 Z  ~
  , ~- H* h9 P; h+ [
• 
  ( [- i& f* c: H' D: o
  4 [8 i; z3 W. x  q| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
  
  
• 
  3 J  T" b7 d) w2 B
  ' J4 E' \9 K& f. P/ Z% M- v| log_bin_trust_function_creators | OFF                                   |
  
  6 X2 B: N" r) y
• 
  # x* l* T' ~4 A  Y0 M1 r9 [
  | log_bin_use_v1_row_events       | OFF                                   |
    D" T7 D" @& D; h9 o9 v
  % k: k! L4 T3 _  ~" J8 J( }
• 
  
  ' Z: e8 {$ o2 Z, R; {2 [| sql_log_bin                     | ON                                    |
  
  2 E2 J3 X, z+ X/ e* r
• 
  / v8 L8 \4 }# x) C1 N
  +---------------------------------+---------------------------------------+
  % ?9 k* O5 O1 S- L0 ?4 N
  
  

日志位置：
! m$ a0 t2 y/ h. V
" m- r+ r- P) u
  W) \8 N& P  E7 l

• 
  - [9 V' D" z% s: G0 A5 {/ [
    E0 L  \5 c1 c6 B6 V[root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*
  
  . |5 x" ?+ ^: B( d8 T* e
• 
  - j8 C/ D4 M! ^4 W6 B* I
  , G/ `, Y0 u1 ~9 f& r-rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001
  
  # n6 I9 F3 ^# c$ S8 P2 k
• 
  0 T" l! W, v6 E: Y
    {2 e7 @2 z8 k) {-rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002
  2 d8 n" y1 d, f5 Q* E* s1 Z
    `9 E4 a* w8 e& P) y9 ]
• 
  
  5 i! e  T- O# @-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003
  ; [3 J. ], \! o% t
  4 k, j6 S3 L/ w/ b9 n  H
• 
  
  ' v7 X5 z* A9 R5 G/ f( S* q-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004
  $ b3 J8 B9 K+ g% _
  ; q4 S0 z% _" O! z' r6 u5 v6 I$ B8 ?- [
• 
  
  % `* m' r/ D) j- C5 }  |-rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005
  
  0 C- _5 h- `' ?& L" g; n
• 
  % S7 k) J) E# L3 r' [8 ~# a
  -rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006
  4 a) d. y1 J# i) S) ~4 A0 V
  $ ?/ M, E5 k, B
• 
  
  -rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
  
  & t! {& _! a. x. k  B5 |
  ( S  ]& L2 a) L/ V: K. `2 c

& N2 f. a9 _$ ^7 A
- A1 @4 e  J5 u1 g#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！
# r- S  k/ E; g8 s$ q+ x3 g$ J* N
2 G- S- j+ b0 n7 N0 x3 `, Nmysql> flush tables with read lock;
- k( R, @4 U5 u7 d: E9 f
2 q9 O$ h! Y+ o& \备份主库(执行以下一行脚本即可)

. h* g4 ^0 t; c5 Y  _

• 
  
  " E7 K* m( W3 A1 C, j[root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  / p% h+ ]4 [' T3 B
  
• 
  . m  u9 N- u' m* Q* |8 t
  ( L" U3 V- b( B# b6 g[root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  ! c- l4 R8 u8 M' P
  5 j4 v* a, N7 l. V9 P+ k: C
  " h( m" ~3 C; T* T' {

* I  s5 q* |/ C0 _6 D9 ^; y
! t5 q- @8 x% s查看当前日志记录的位置：
. a& H2 S1 d9 R# \+ f
& ~9 g# j: w9 w2 A9 q2 G
4 c( y- Y! z; N; Q4 p! v0 r5 X2 m4 S
' N+ R' a2 e+ I/ i  w

• 
  ) ^* N* i  E" d% u" D* w
  ) e3 T: \: U) t6 X& h1 j: I$ ]mysql> show master status \G;
  ; X: |; _% j- P2 p
  $ C# J7 I0 \0 I
• 
  
  *************************** 1. row ***************************
  # l/ x# \- Q+ O
  / ]; T4 A/ {, y8 Y5 s
• 
  8 L9 X) f5 ~. a( G. ]0 Q5 i
               File: mysql-bin.000009
  
  & |, ^, b  {4 S& s6 S8 |
• 
  
  ! ]* w0 V  n9 ?) g0 ~         Position: 120
  & R# ~+ n3 b, D: r
  5 L+ x7 x# e5 f- |/ U% ~/ K
• 
  
       Binlog_Do_DB:
  
  8 w( k- Z, ?# y9 t
• 
  
  Binlog_Ignore_DB: mysql,information_schema,performance_schema
  6 u" v8 N2 m6 h; \5 Z' B; g
  
• 
  
  Executed_Gtid_Set:
  , U2 g0 O; H( c% M* X0 e3 V4 _
  
• 
  
  1 row in set (0.00 sec)
  
  " h# k. \( W& `. w# S5 t4 S& M
  7 P1 k8 B8 s2 S( |0 G. z+ l

备份完后，可以解锁了
0 t# P- e5 d9 ?  M6 |
+ V; u1 u9 d: i' x; I2 ?

mysql> unlock tables;
  w4 _3 |: d! [9 |; x, W& _. n) ]
在主服务器拷贝备份到从服务器：



" `" [8 Z1 d; |6 d) s

• 
  
  7 P$ c5 k3 D8 f, Q' e[root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp
  - q+ t: `, `7 B$ t5 N! A
  / h# }  q3 Y7 g# N, V/ P5 }, T( q
• 
  
  
  
  
• 
  ( ^3 |5 ?2 ?1 \9 _
    y3 ?  ?7 y( w+ z% V) Y#不行则先关闭主从服务器防火墙
  , z* S4 _  r- |0 m3 p- J! ^" P
  / [2 f& \/ w$ P
• 
  
  ( \0 c* M8 t. n/etc/init.d/iptables status
  ; t6 i) `% l6 O1 n  H
  
• 
  
  /etc/init.d/iptables stop
  . H$ l8 k& I" n+ B! H
  4 i0 ]( Z  V/ p- _
  

  }( q, `$ z+ ]- t
/ }/ N) K, P. V! \7 S在从库中还原数据库：



# U/ Y" s0 v% }, ]5 E3 }[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql

; T8 @2 b- p* G2 O主库创建用于连接到本地的用户：

* R  X) E3 V4 g- g9 Y* g

[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';

在从库中连接到主库：
) `  R: u7 [  p
* R! \3 p1 r* N3 }* ?
  x; I4 [- b2 {! {. z1 x, K7 ~& P

• 
  
  mysql>
  5 o' o' u+ B& h9 q/ t9 J
  
• 
  
  & c9 O& c" @7 E, E1 aCHANGE MASTER TO
  
  ; N; V0 }2 G( A- N
• 
  6 x; Z9 {: B* |8 f1 J
         MASTER_HOST='192.168.1.152',
  
  ) x6 `, y, ^5 h1 H
• 
  3 e3 I# K5 f; v3 ^' g
         MASTER_USER='repl_user',
  , _  v" [8 Y4 x* q
  
• 
  6 G/ J- O+ [. i: h1 x, R
  & G9 |7 f( \; d6 N7 Q3 ]3 s! Z       MASTER_PASSWORD='slave@153',
  
  . H. t. d2 w1 r
• 
  
  ) `& z$ n/ [! N' Q9 X; K       MASTER_LOG_FILE='mysql-bin.000009',
  
  7 N- A* |1 J8 G8 p3 ^  k" k" T" r8 M
• 
  3 S' |* y0 }) k5 n0 f& D
         MASTER_LOG_POS=120;
  
  5 V) `- \6 a; {: z, {
  

1 I" d. E. G: T" g  x# F
8 q( ^, m. ]+ K0 B3 Q" P) Q+ ~重启从库mysql服务
/ l; Y  Q4 I  v8 u. H, X3 d2 l. R. v[root@centos153 ~]# service mysqld restart

9 K4 o. V; Y/ Z4 P% ]

查看从库同步信息：

8 j. R8 J# W3 H. \8 H0 J3 ~& R

• 
  : W4 o- t2 F1 O1 |' q8 X
  mysql> show slave status \G;
  
  
• 
  ( E3 ]; h$ j0 @3 j+ c
  *************************** 1. row ***************************
  
  
• 
  & m0 S8 N" e9 z/ w% P* M! V8 S
  9 S& }, i" G, ~8 u0 t7 j               Slave_IO_State: Waiting for master to send event
  ! f; e8 V- {: E2 v/ O4 R  p
  . d) g  _' `9 ~/ S7 j
• 
  : K7 @5 l" y* O0 |2 n1 {+ e
  / D8 y, v( S/ o4 Y" p                  Master_Host: 192.168.1.152
  ( ^9 M: l$ y- q5 q$ }
  3 J7 Y+ }' @7 j$ b: D, k
• 
  
  8 v  U4 O  o! [0 Q                  Master_User: repl_user
  
  
• 
  6 y% U2 L" Z; J- a
                    Master_Port: 3306
  2 Z( `5 B7 W2 \: \, m5 f  j" i6 p
    ?5 O/ S; l- G$ G
• 
  
  1 i& n* f  v+ \' i                Connect_Retry: 60
  4 n9 s- p3 T$ Y5 g0 t
  
• 
  
  # A/ k0 V3 I9 }# U: r# z              Master_Log_File: mysql-bin.000009
  
  
• 
  
  ! U  N# h+ `4 }# N          Read_Master_Log_Pos: 120
  9 e% E! T/ ?; O4 f: {* w
  
• 
  
  ( z  N3 M9 R7 ?               Relay_Log_File: mysql-relay-bin.000003
  8 Z7 V% A( L% K6 V1 x. w& M. ^/ O6 L
  2 J. C8 P' u6 B! V
• 
  4 a; z" ]9 l! Q% M: }
  ) j3 A( _+ \1 @$ S" s! [                Relay_Log_Pos: 283
  
  
• 
  
  ( T% ^- q3 c6 ]0 i, y& ^3 @4 Y        Relay_Master_Log_File: mysql-bin.000009
  7 M% u; ^% f* _
  8 x+ b- P' k- m9 g* G- |
• 
  
  % d1 F9 z/ y6 @1 k4 g. h" M3 O             Slave_IO_Running: Yes
  " Y% r7 z8 R5 b* }) u) h. c  w
  ; v0 K+ Q/ H/ J- f0 f0 z0 @
• 
  
              Slave_SQL_Running: Yes
  9 S7 S  s4 p( _6 K
  
• 
  
  " H$ X- L2 |2 `4 h+ X) z/ B" _4 I              Replicate_Do_DB:
  
  
• 
  . x& o8 Y  k) a2 z
            Replicate_Ignore_DB: mysql,information_schema,performance_schema
  
  
  $ D) P: N/ u4 Q( e( T2 V+ L6 ~1 X

可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
% v' y/ H8 g' L, Q7 @6 A


8 |* j/ ~2 j7 H; y( o, i! v现在测试同步情况：

• 
  
  % D4 _' d" l# N9 ^8 ]1 y#主库创建测试表
  
  
• 
  
  create table tabdemo(
  
  3 b+ C' \, Y$ s
• 
  / _) v/ H7 E* h5 V9 S% r# v
  ( _- v0 _. i; C8 d' [! Kid int primary key auto_increment,
  9 }' o( _. g) m, `+ x) V
  
• 
  
  value int default 0
  
  
• 
  * {# ~; {* E3 q2 t9 |1 r" Z
  ) auto_increment= 1 engine=innodb default charset=utf8;
  : H8 x4 U8 a; Q4 x$ [8 d4 ~
  
• 
  
  3 f& u2 ]% F# e' n
  ! l) }5 ?0 U% v% H* ^
  1 K4 S! n, V5 a! M( P1 V- Q% F
• 
  ! I& C" F% B' c' L# I0 @$ B
  / T% u5 U& D- {9 k: ^) t+ s4 A. S
  
  
• 
  5 j. H) ^$ M$ h6 [7 D
  insert into tabdemo(value) values(1),(1),(1),(1),(1);
  2 o) t, u: D, f$ l9 a
  
• 
  
  2 F: j, J) N- L9 p0 r
  
  5 Y" F, n% j# `9 B- M* m+ [
• 
  
  
  
  
• 
  ( V8 i  W8 q; {6 h9 a% t3 G
  select * from tabdemo;
  ) B1 }6 G. F/ X$ z0 w
  
• 
  $ |$ r& b; }; x$ Z& l
  0 h6 F1 G5 y9 W5 J( F0 Q% G+----+-------+
  " H/ W( f$ q5 E  L
  
• 
  
  | id | value |
  . n7 V: J1 G- `) o7 N) W
  - b6 Z8 y' G/ i; q
• 
  * Y, i( b7 [! l/ p5 |
  +----+-------+
  
  , c! X% s4 C1 }% R  v: @8 U2 R
• 
  
  |  1 |     1 |
  4 r$ n' a( P; Q7 e" @1 y8 ?
  9 V0 I- M2 ]3 q9 Z/ M
• 
  & F1 r5 d) A" X1 j1 ^# J/ t# N
  |  3 |     1 |
  " Q; C2 Q! U* S3 j, P
  + p! P! {# C: `- l1 _! W! D7 C
• 
  $ t2 y  g1 j9 ?: G6 k7 E6 ]+ y7 v
  . g: m+ ?) c7 Q9 U# d|  5 |     1 |
  
  
• 
  
  |  7 |     1 |
  
  4 J/ f0 j! X! `
• 
  
  |  9 |     1 |
  * l/ j, Q2 P' Q/ W7 U! U6 }
  9 y5 I6 l3 m' e) h9 ]) Y  U
• 
  
  , V3 h4 E1 N4 N& X+----+-------+
  
  
  

) }) I! a. K; c2 G( y3 H
! d1 |) B; H+ B6 z主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~
  O4 D2 f1 {( L7 v& {


- P' S* A4 ~0 L$ E( h==========================================================================
5 O2 `! o- r7 h# F% u3 y0 w# f+ l==========================================================================

0 y% W& O7 x9 t- d下面配置：【主主复制】


" W, G( K+ @( t- A  v  |# |3 z更改配置文件：
2 M- T$ e# ^' |9 d) T
【centos152 原主库】更改配置文件
8 t- g. Y. @6 f) {& y1 k[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
1 r* L5 }9 s- z6 [  Z* Adatadir =/usr/local/mysql/data
port = 3306
) @2 }2 o8 c: n( n0 x5 pserver_id = 1
8 f1 l. e4 N- Ilog-bin= mysql-bin
binlog_format = mixed
& u# G- G/ g3 t' \7 P
9 _1 q3 _" S1 X, a  O6 _read-only=0
) B! @0 z# R* C5 e) F2 v! F( ~9 V#binlog-do-db=test
binlog-ignore-db=mysql
7 p5 a% c4 x( N$ rbinlog-ignore-db=information_schema
0 e" S6 g  j! n7 e+ ?0 Bbinlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#主主复制的从库设置（新增）
#replicate-do-db=test
replicate-ignore-db=mysql
# e- u$ C* {# x, creplicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
2 S7 |) L$ _3 {relay_log=mysql-relay-bin
6 Y, h+ S0 B% i4 _- n3 Alog-slave-updates=on
$ ~4 K! w: m9 \7 w3 g, r6 T- t
, l4 }' u8 T0 }; o( D& W8 [
【centos153 原从库】更改配置文件
: J  d4 ]9 ]. f8 X) [& T[root@centos153 ~]# vi /etc/my.cnf
4 o  _$ w1 b: }1 P9 f' C% F$ N[mysqld]
basedir =/usr/local/mysql
% l7 D3 w) q1 s5 e3 x9 [* z5 ^" Fdatadir =/usr/local/mysql/data
port = 3306
3 n1 i8 T. E# E4 b, ~3 |. |server_id = 2
log-bin= mysql-bin
binlog_format = mixed
- l& m7 ]6 Q" @7 p' y+ x
: C3 w: b$ r# U" Fread-only=0
* c  N/ r# F) q3 Y2 _#replicate-do-db=test
replicate-ignore-db=mysql
3 @5 v7 V! I* d1 W' nreplicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
2 L  g  p& G( t3 Q* q5 f" K! h5 Dlog-slave-updates=on
4 w; ^1 }( j( Q5 L
#主主复制的主库设置（新增）
, ]: P6 X; C. Y+ Y9 X+ b  c#binlog-do-db=test
binlog-ignore-db=mysql
; `. ]- P1 L+ q0 a. h% U+ @binlog-ignore-db=information_schema
5 v9 i# ~- P+ f: K7 kbinlog-ignore-db=performance_schema
auto-increment-offset=2
3 {0 w4 a5 W5 }. q7 w3 Q; Mauto-increment-increment=2


. i7 d! ?  @  p添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart
6 h1 D: ^& Q5 p9 K3 m, Y4 _& u$ h

& L2 a5 ]$ D, d* l原从库(153)中创建用于连接的用户：
# O! o' `9 q$ Z0 J

* D( S! n& _( a: W8 w[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';

" Q, Y+ i: }: r, p/ _7 k$ O原从库(153)查看当前日志记录的位置：

2 {7 C# f6 }! F* u) W0 A% i" j

% |; Y( Y# T. a* @, ~

• 
  $ i- u. L! {: n# G$ ]% E. P/ U
  mysql> show master status \G;
  0 Z% [. Z# Q* d3 h
  
• 
  2 |: X+ O* Z4 }
  ; S# q; p" _* g. ]0 T2 z" F*************************** 1. row ***************************
  
  ' j9 P7 H: J7 T$ a! Q6 \* v0 S
• 
    ]# t5 g9 V6 N# ]
               File: mysql-bin.000005
  & ^& L$ p+ q& m8 }# a
  % R  _1 {: ]& Y/ n+ W
• 
  
  9 Q( |3 `; [8 c, c6 |         Position: 120
  
  
• 
  * c8 S& F* [- N/ h. s! w
    `3 F# n2 ?  c2 g* |2 X" }     Binlog_Do_DB:
  0 F+ J: ]3 h4 S: x6 [; K4 Z
  
• 
  
  + \, @6 U3 P6 U1 a! R( v- W Binlog_Ignore_DB: mysql,information_schema,performance_schema
  7 q; I* {+ k# y/ o3 _7 _/ a/ n+ Z
  7 F0 K( }' {: o/ d, k
• 
  % w( k; h5 W  f$ y; o
  / b+ k3 ~! z2 h; `/ _Executed_Gtid_Set:
  ( Z+ q6 w( m! B3 f# a
  
• 
  
  1 row in set (0.00 sec)
  
  / N' j" f/ T* Z2 K( a* l- y' f
  " d* E1 p" M7 _/ c5 P

4 ]1 C& R0 Q, `0 _2 j
在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：
- }; Q; f. n- |3 ?; H7 t

/ H6 w4 N' f6 \
1 d; f6 ^. l" ~! l3 R6 @4 g; Y

• 
  , \6 u( l" g0 a) m  ]
  mysql>
  $ P5 N4 J) E, E
  
• 
  
  # U* T8 N: i6 R, ?9 g: H/ [& xCHANGE MASTER TO
  4 ?7 _( ~. ^( \& |& F' q
  
• 
  
  $ B8 x* ^# |2 p: e$ y; x       MASTER_HOST='192.168.1.153',
  5 I0 I' w! U: W$ d$ E$ M
  $ g) T( C( d/ t; d* g. b0 K! z
• 
  $ k* v5 J1 s1 f
  5 A. D8 \8 ^& X$ k0 {2 ~" q       MASTER_USER='repl_user',
  8 z6 N- x7 q$ l! ]8 c  ~$ u
  
• 
  4 N* Z) W# Y) b; N& E: l) M9 J
  * K: T2 B0 Q0 l. ?       MASTER_PASSWORD='slave@152',
  # f# g- A3 M, x; F% }
  
• 
  
         MASTER_LOG_FILE='mysql-bin.000005',
  5 J! G$ ~! y4 b& V
  5 ?) Z4 N; @! ?$ f/ c  c
• 
  6 `" P& |3 R! j, `
         MASTER_LOG_POS=120;
  5 h4 Z" k& H* z2 W  E! r) V: C6 U
  & r: _# I! q6 G1 X
  - s# e* ~: V7 r9 A, \0 f

7 O% c% g& b7 P2 G! E6 B重启从库(152)mysql服务：
0 Q* w. k1 G% z  s; L3 _2 `0 C6 {[root@centos152 ~]# service mysqld restart



查看从库(152)的同步信息：


0 M, x* V1 Z! d

• 
  
  mysql> show slave status \G;
  ' ~+ Q& j8 \2 m. i) I0 z
  
• 
  
  *************************** 1. row ***************************
  0 s& ]* B& ~0 s- D
  
• 
  4 Y$ t2 a- D6 x, b
                 Slave_IO_State: Waiting for master to send event
  6 Z, f) K1 [* T) `5 q% _
  . l0 T9 \4 X1 G/ w  t6 ^6 q9 h. x
• 
  ) b/ `: q6 X' h$ X
                    Master_Host: 192.168.1.153
  
  
• 
  2 A% t( p# t9 o; }' H% G8 E
  : t& o: }% O' E! n                  Master_User: repl_user
  2 u* r1 ~% ]5 T8 F& q6 Z
  : j( h, u  j: ~( r, i
• 
  
  . i! `, g: ?6 z8 y/ I                  Master_Port: 3306
  0 a/ v) N! ^( K
  # b3 l+ W2 `, P! N7 F
• 
  
                  Connect_Retry: 60
  
  
• 
  
                Master_Log_File: mysql-bin.000005
  
  
• 
  . g# @- w9 X! T; |9 l
  6 W% Y6 }: g  S6 \) r/ K          Read_Master_Log_Pos: 120
  % m4 J  {0 {( j" \
  7 h7 n* J0 v/ m5 [# @7 P
• 
  
                 Relay_Log_File: mysql-relay-bin.000003
  
  
• 
  
  7 ?: m% q: _2 Q4 p  k                Relay_Log_Pos: 283
  $ ~2 E; L2 ?5 _  |' \& t
  9 v6 W. o7 n1 j
• 
  
  , W- e( C! O1 q2 T0 s4 m  K- U- E        Relay_Master_Log_File: mysql-bin.000005
  
  9 u4 B) r" R& U! _# I9 |* y& d
• 
  6 N/ O, j/ A$ ^$ W2 h
               Slave_IO_Running: Yes
  
  
• 
  ' m' S/ C0 S" I' N( ^- a
              Slave_SQL_Running: Yes
  
  0 k6 m( @, `  E
• 
  8 M, \! N* j5 {1 h0 a! b- S! z
                Replicate_Do_DB:
  8 z: c+ T# g- d8 y
  * a8 d/ b$ S' R- `( P
• 
  + f* z! w5 ~/ y5 n: V
  + M7 `/ M7 o' V7 N0 d9 G          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  6 |0 l: }( K- }- [5 F
  
  0 h5 y# T# s/ n) \

现在已经配置好双主了！现在相互在两个数据库中都操作数据：
6 s5 T7 Y$ Y0 N1 i4 L! N8 p

• 
  " ]7 }2 Q- v9 b3 u' a
  0 }" I: z7 d7 @#当前在原来的从库(153)操作数据。
  
  4 C6 P6 I) I, _* x* G% r1 o
• 
  
  
  
  & p% w6 `: H. Q2 M2 [, L
• 
  
  " L0 I4 o0 E$ @5 Einsert into tabdemo(value) values(2),(2),(2),(2),(2);
  # x) s0 E' I9 a) A: b
  " C8 _- j! J% ~; v
• 
  
  & y& c& @2 _2 }0 L+ j; g4 N- G, x
    v; Y6 Z# F$ [9 X
  
• 
    S) U' Q  d: b1 \' b7 g
  4 U, R$ K% |2 ?: m( c#数据同步正常了！
  . a) _8 V6 u4 ?7 M9 z% F/ q
  
• 
  # C$ ^  t3 F7 a( @+ h( ~
  / p8 \" \* @* ~3 I4 V; `8 O( U" w$ ?select * from tabdemo;
  ; _- f6 {: @) ]5 o6 ~- ^
  ) g8 a* }3 E% B5 m+ F# C
• 
  
  ; G/ J9 |  s- Y5 {* S+----+-------+
  
  
• 
  $ y8 \0 @# |- o3 ]# r; Y
  | id | value |
  2 i7 R5 i: r) k' C& @  h; O5 q
  + M' A& R2 V0 p. J# v
• 
  ) q; r( b3 d( Z. v9 s
  +----+-------+
  
  . U1 l' |6 b" M& H1 E- H2 ?/ k/ [
• 
  3 L7 D# L2 Q4 _8 Z* m7 _! V4 t1 t0 ^
  6 a$ F" ~; ], D. w# h|  1 |     1 |
  . T( j! q' {- Y3 R5 ~( O
  
• 
  3 M. \* {0 D8 o; i% k! C/ S
  & [1 J& W" m# g  H+ s$ K0 d|  3 |     1 |
  7 F4 @& a- p$ M& L
  8 b8 x, z+ K5 G( \% i
• 
  6 L$ K/ t- {" K$ n& Z
  * ~$ e9 l1 f1 u  t|  5 |     1 |
  
  6 C7 W# Y% C4 C" }! y3 T
• 
  . W0 G, S& J& t6 h
  * f7 S! i5 |' j" m3 k' O|  7 |     1 |
  . a5 F+ E; V( w% [3 y
  
• 
  + |" ]6 N" b% ^1 w3 p& n( g/ E
  |  9 |     1 |
  ( S& o( ]6 \; C5 _) t" l% R7 g
  
• 
  
  5 I, p" {  H& c( V/ ~4 {& ?| 10 |     2 |
  , A( P' f1 s- B. Q3 U- n2 w1 b" p
  
• 
  # T- u! f! I& @. g0 X, i
  | 12 |     2 |
  ; v9 ~* Q2 z, |( N
  
• 
  7 e$ f  Y' u" [5 O: q
  | 14 |     2 |
  ( c. {. \( V- G" Q" V
  : \* a" }! n- h1 ^; x' n6 |
• 
  
  | 16 |     2 |
  ! X: f9 Q0 K1 U. E3 _
  
• 
  
  | 18 |     2 |
  4 J% L7 V, k/ o1 I! L' ]" c
  
• 
    ^" o6 O( k; v% M  r2 w
    s1 ]3 C1 T7 }6 @& C+----+-------+
  % r( T" k. ^$ a: ]$ {
  
• 
  
  8 b4 g0 C$ q1 n; R
  5 E/ z! e9 l# R9 R+ `
  
• 
  
  在原来的主库(152)操作数据。
  
  
• 
  
  insert into tabdemo(value) values(3),(3),(3),(3),(3);
  
  2 L2 g3 [  M4 s* _
• 
  
  
  
  ( E, h! [/ `% Q9 Z$ @+ ?6 f0 u
• 
  & v: l6 s6 d( B, F' a
  / |$ q5 d! J9 E3 Q/ r, T5 W#也正常！
  - V8 N  A  P6 [7 L9 X
  
• 
  
    i9 I' ^  @8 y6 k6 l+ H; z4 [4 hselect * from tabdemo;
  
  
• 
  9 c+ q( v8 H5 W9 j, ~  \. T
  + Y, Z' |, h+ o5 h3 j+ S- v) v+----+-------+
  
  
• 
  
  1 i+ ]. ]  I9 T2 J, x5 w! R) Y2 D| id | value |
  
  . {' u8 X1 y' J+ Y
• 
  
  4 X; ~7 \# p% u" f/ O+----+-------+
  
  + d, w: _4 z& G
• 
  2 m$ u0 S  f8 t
  |  1 |     1 |
  , X5 z" @% j6 U5 J. W' y
  
• 
  
  |  3 |     1 |
  ! @9 c; _  [! y+ p6 x0 d1 B" n
  3 o- H% w* }) l; J* u
• 
  
  3 l3 u5 m. [' \- z& i8 N. @6 c|  5 |     1 |
  . y2 u2 G& v* t  H& m5 j
  
• 
  
  2 _0 b. F5 l' B( x|  7 |     1 |
  ; w- X( w' b! A5 _% O
  
• 
  
  , B) J& C( U3 X. \|  9 |     1 |
  4 s9 Y% [( \3 L1 p7 j8 B
  ( `; T$ m, c' ?2 e
• 
  + U! L# m/ A0 L  A* ?
  " ^; G( x# F" f; S: r9 `+ S| 10 |     2 |
  
  8 n3 B: I$ \% n
• 
  - Q+ l0 `- r* ^( s
  | 12 |     2 |
  ' `* g0 e* v& C1 Q+ v
  
• 
  
  | 14 |     2 |
  
  
• 
  8 }6 Z- h; \5 j3 P- h) x  E) x6 x
  | 16 |     2 |
  
  
• 
  
  | 18 |     2 |
  3 B( t+ k( ]" M; Q' i2 P: U
  " @* z' M- j+ z. Y' K' f+ D7 o1 q
• 
  5 f  g, b9 w" q( y( G3 p* M, x
  , p# m* i5 d+ H, C2 X| 19 |     3 |
  ( O& w# b# F6 Q" d9 p& _5 c
  
• 
  ( G4 l$ N+ `' S" k1 {
  | 21 |     3 |
  
  ; z8 C0 B, K) @6 j* g! @
• 
  
  | 23 |     3 |
  # \& }! g3 n8 c: E. i
  
• 
  
  | 25 |     3 |
  
  
• 
  , L# I) }0 \# k5 C2 H
  | 27 |     3 |
  
  ; M3 Q. b! M9 {0 h& @
• 
  
  1 R( t9 F5 z' z* b) R* \0 C+----+-------+
  
  
• 
  9 Q. w# ]5 V9 c. x! p  Q
  
  
  # j2 |8 G1 `$ m+ F/ {
  

, W6 W; H" w/ Y& M/ J& D
至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践

% p/ `% [3 l9 f9 H6 L