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

rui

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

mysql 主从同步原理：
) S/ e% J# x) _) E1.  master 将操作记录到二进制日志(binary log)中；
( r5 Y9 D3 R( ~: [; E, A' |, ^2.  slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
3.  slave SQL进程读取中继日志，将重做记录数据到数据库中。

6 F. I$ i3 v9 M& e; m
( A8 F. l1 C, W% x, J  `* j  V下面配置：【主从复制】

2 n3 ?! o, J- u) g7 JMySQL Version : 5.6.22
- R& n# d; \3 W$ H6 c5 t' M: t主库：centos152 / 192.168.1.152
从库：centos153 / 192.168.1.153
! S% ~/ H4 n/ s% c

配置 mysql 文件：
【centos152 主库】
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
& I( Z9 r- i) {% D6 Z9 n8 P- rserver_id = 1
log-bin= mysql-bin
binlog_format = mixed
* s/ h- |# }* X, D  Z
( F( k( Z: z( ^. p& Oread-only=0
! x7 g- d$ F; `% k: |#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
7 Y' _0 d# b6 Z& K* d( dauto-increment-increment=2

#添加后重启mysql服务
# n6 ~+ _. Y( V9 o  J[root@centos152 ~]# service mysqld restart


! A1 A/ m/ E: e5 ?【centos153 从库】
[root@centos153 ~]# vi /etc/my.cnf
9 Z/ g+ x! D3 J. H8 o- c[mysqld]
3 A7 o% [8 D5 Rbasedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
server_id = 2
; H( j0 V: Y# v4 q$ Ylog-bin= mysql-bin
1 Y! M/ m5 B& M# \$ a  f  K% Lbinlog_format = mixed
9 d  a% a+ t" Y$ Y) S
read-only=0
#replicate-do-db=test
+ u9 ]$ P. n' x# G1 U9 O5 t- Nreplicate-ignore-db=mysql
# B; E. H! G/ s+ V% U6 L6 ?replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
; P" T, u. b) O" ]4 \relay_log=mysql-relay-bin
/ Y# E! }2 v) v8 _log-slave-updates=on
#auto-increment-offset=2
#auto-increment-increment=2

: |/ C4 b: f+ ?3 ~4 J- z
9 h; A4 p6 e0 Y5 A1 l( H#添加后重启mysql服务
[root@centos153 ~]# service mysqld restart
* k5 c3 \" I( F2 z+ h7 n% T
" `1 R" j0 @! M  F
6 R4 Z) F' b3 H; l说明：
, n+ W- T8 h1 }log-bin ：需要启用二进制日志
/ W5 Z  Z5 T7 T" r  p8 F6 }server_id : 用于标识不同的数据库服务器
. g  W8 L8 C* N# u: {5 [! s
binlog-do-db : 需要记录到二进制日志的数据库
  A& o- H8 c+ Jbinlog-ignore-db : 忽略记录二进制日志的数据库
6 a5 E+ s& |; F- l7 m! D% R" |auto-increment-offset :该服务器自增列的初始值。
auto-increment-increment :该服务器自增列增量。
# _7 y: R$ x6 d3 I0 u9 W
replicate-do-db ：指定复制的数据库
replicate-ignore-db ：不复制的数据库
* O5 r" @/ G2 y! c5 z& e- u6 zrelay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
; V+ I# y& M9 T& U+ C+ slog-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。
2 u$ n2 L! E& m. }3 v
5 E4 [) O, M- @6 W6 v- l6 o2 T& x0 r如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
2 ^# F& g& s4 }6 P如上面为双主的设置：
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）


4 e; F. T* d9 V1 U9 D注意：文字图片来自《高性能 mysql》：
- Y% m# L: S5 F


#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。
6 _6 w" E. M  R, M5 j; h

3 W( S6 \7 e5 C; A) y/ w' n1 {% s; @

• 
  
  1 B" \& e" i( _3 rmysql> flush logs;
  & Y8 H, g+ b9 w; o- `
  4 N+ V1 i* j4 H/ T( p* N0 I, }
• 
  / |; M! L# B$ }3 R8 T' }
  mysql> show master status;
  
  
• 
  
  +------------------+----------+--------------+---------------------------------------------+-------------------+
  - m# X6 T9 p3 u+ g! ]
  
• 
  
  % Q& Y" q) {! H$ w0 i$ i1 O* p, y| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
  
  
• 
  
  +------------------+----------+--------------+---------------------------------------------+-------------------+
  
  
• 
  . B8 _: K, R  o* m
  * j2 N& x7 D0 K- D) U| mysql-bin.000006 |      120 |              | mysql,information_schema,performance_schema |                   |
  
  $ X, Y( e, c" H& [6 E( Y
• 
  , e+ J+ ?: O& z' P5 R0 {
  9 q" l: J8 ?6 D5 G7 z+------------------+----------+--------------+---------------------------------------------+-------------------+
  
  
  

# n$ r9 b4 N3 B* ~* p
/ R4 [2 ]- M( x5 U& ^: CFile :当前正在记录的二进制日志文件
2 D- J# ^6 x7 \Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
Binlog_Ignore_DB ：不记录日志的数据库
  b0 ~1 T, N' }! {Executed_Gtid_Set ：已执行的事务ID
4 j3 i0 z5 ?7 X: a4 K3 l# W
  e5 j2 V+ r' f. P7 C% c

2 V0 z/ `. x* @7 Q3 G二进制日志情况：
9 t9 m( b5 V! \& D

• 
  
  3 E8 {* [/ H7 T" g- O3 S2 z6 mmysql> show variables like '%log_bin%';
  $ m# r2 ]1 ?( [# _  @- w1 G
  
• 
  + ^  w  E- T, e3 o! S2 }
  +---------------------------------+---------------------------------------+
    H8 A) ?6 c" ], Q' k2 E
  
• 
  4 o* R# ~7 g- E5 m% E" t( ]
  ( h1 m2 ]6 r& u3 m! k- K$ ^, G1 [* p| Variable_name                   | Value                                 |
  + V- W. {+ v: z2 h7 ~9 K0 }
  ; l: F2 y! s  h! i7 n9 T. @1 ?
• 
  - v* n+ R% J- o! ~& D- d) r* k" o
  +---------------------------------+---------------------------------------+
  9 W( M: n3 j  P
  
• 
  
  , A) h/ W. @  _1 j8 [  D7 `4 A| log_bin                         | ON                                    |
  
  
• 
  
  | log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
  # z! [5 v' x+ _0 k
  
• 
  
  * e; A/ M6 ?2 _  Z) C9 d; W| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
  & T2 w9 ?6 f# ?/ E# i
  , }. r* c1 D! |5 s+ I
• 
  6 v0 ~1 z9 O1 E" o, ^! p
  ! T& k4 t( L2 w0 F| log_bin_trust_function_creators | OFF                                   |
  
  0 }9 J- J) @2 l* i" e8 M, [
• 
  
  ! l7 i- X& k0 K, s9 g" j7 s. g| log_bin_use_v1_row_events       | OFF                                   |
  
  , C) T4 p& v8 P% l
• 
  - e9 ^+ \4 q) F, w+ T8 x9 x+ R5 F
  | sql_log_bin                     | ON                                    |
  - v- @; a$ ~, U3 E, w
  
• 
  - l/ O0 \" ]1 z0 C7 H2 r
  . G% }+ P0 q7 I+---------------------------------+---------------------------------------+
  - i4 c" e- _- M1 @7 `( p' t9 C0 ]$ K
  
  

日志位置：

• 
  
  $ T# y3 C; _8 W6 @( M[root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*
  3 w2 }( ]) M$ I" T$ @
  9 f: e0 `) J$ _/ i; E& C
• 
  , C" B, z' E, \( P# n- O
  -rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001
  1 E0 X4 c7 ~" B% W0 N  ]" t1 R
  4 M6 t+ A; ~- C  f; r5 S2 a3 ]6 W
• 
  
  ' U4 C0 g: R- u1 h7 @, h2 x3 S-rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002
  
  
• 
  5 p( n' B0 _, `
    ^/ x/ v) S4 K-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003
  
  
• 
  
  -rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004
  
  
• 
  
  -rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005
  
  0 l+ Z' l8 P9 ]# K+ \+ @7 `; s
• 
  
  -rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006
  1 U& k1 V( B' B# @
  
• 
  
  -rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
  3 x: m9 }* Q( f0 s* I  c+ b
  
  

2 m' J% v9 t# ^' m2 M' Z2 V2 ~
#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！

mysql> flush tables with read lock;

备份主库(执行以下一行脚本即可)

• 
  
  [root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  & s% G+ E+ u$ G
  
• 
  * @6 J4 _/ \& k  G1 {# a1 J
  6 A- f0 c( L. m# ~, M[root@centos152 ~]# mysqldump -u root -p --databases test --lock-all-tables --flush-logs> /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  ' P9 I: |% D  s
  2 ~" ^  }4 M5 l1 R. h7 z) @
  

) g/ E" r. D# Y9 c) f) I查看当前日志记录的位置：
, S0 u- Z* q% _( a
2 }' a( i6 ^; R1 _7 e' Z

& ^# @0 M0 s$ D! u7 g/ V3 O* S1 T

• 
  # q* B' H# J& I& a' J7 c' a
  mysql> show master status \G;
  9 Z9 j2 N4 I" F' _, z: a
  * a; {0 j8 B" V2 g8 \9 i
• 
  
  *************************** 1. row ***************************
  # p- S  _5 n, r3 y
  
• 
  
               File: mysql-bin.000009
  1 `) h3 O/ J# _( e
  2 g/ g2 ]& M4 |; x3 j2 h0 }8 p
• 
    D  `0 f; U6 |* T* W
  5 v' s: s7 z+ z& R5 B         Position: 120
  
  - `# C4 y) g' C; L
• 
  , l$ ^0 o5 a  @5 e$ J7 R; _
  ) @# Q- P- c9 j     Binlog_Do_DB:
  
  $ k$ V! |& _1 e
• 
  
  9 a: c) d% Z# {, X1 c) S3 X Binlog_Ignore_DB: mysql,information_schema,performance_schema
  
  
• 
  & z6 G3 c' H& x: M( w
  Executed_Gtid_Set:
  8 Q9 a$ ?' n, j1 t2 y
  * y0 ^/ x  f8 h" S/ g' d5 g
• 
  
  1 row in set (0.00 sec)
  
  
  

- k1 E- u+ [( h4 A, W# G备份完后，可以解锁了

  M) n3 e3 A: |- j
. A. i8 ]  w/ i( d6 }' i
* N' q* K+ T5 ]mysql> unlock tables;

在主服务器拷贝备份到从服务器：
0 P/ d- j6 H* c

5 x* s/ b8 h: z6 ^

• 
  
  - f. i, W  u5 G* A[root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp
  & P; U5 e; H) O2 I* X
    }" \  O7 j! ~8 Y3 O; B& X
• 
    n  s# s& Z8 N# a. ?7 ~- A! E1 @
  9 y" T$ f: N2 k2 ?4 b
  
  
• 
  
  #不行则先关闭主从服务器防火墙
  , J2 O( {0 Q- w
  
• 
  9 C" q% f) M9 m0 R5 K
  + r/ t5 r% r5 X! U- J) m. ]/ F  }/etc/init.d/iptables status
  4 d8 E" m1 h1 @) v+ e: k- ]" }
  + D/ c6 ]1 y# i% M1 _
• 
  4 @. h$ f6 q* N" R- ^  b
  ( |3 A: ?5 [4 E5 A/etc/init.d/iptables stop
  
  
  

! ?, _+ b( v) }0 {3 z
在从库中还原数据库：

  ~% e& |- q. \. x# @$ w* N5 ^
- w# a5 Y) m9 l/ N( k; y! c; |
[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql

9 b$ _$ V% K, B, H3 @. y主库创建用于连接到本地的用户：



[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';
* L3 a8 ~/ d7 w$ Q( i
. t. }3 \) `' j3 |在从库中连接到主库：
6 A6 c1 a. i8 D. D' B; u' I

• 
  
  4 v, l" p2 I$ P4 b4 E( R+ R, lmysql>
  5 x) q+ L. R; R' I! {
  
• 
  
  # M) N; S# K( d! U# TCHANGE MASTER TO
  + e. p$ [* C: s; V) M
  
• 
  3 T/ R4 O; n9 m9 J# `
  ( a, |0 {* d% ?       MASTER_HOST='192.168.1.152',
  & W' q4 o: }+ Q
  
• 
  3 ~/ {8 A+ @& Z- R7 ^% C, X4 N
  8 E- a0 _1 @% D2 b1 s- D6 k       MASTER_USER='repl_user',
  
  
• 
  
  2 c1 `0 k+ @5 a/ Y: _       MASTER_PASSWORD='slave@153',
  5 P( S3 s" x$ U* g
  
• 
  8 Z& \! d  a. {
  2 T) G+ _* S% T& @3 d9 E% T       MASTER_LOG_FILE='mysql-bin.000009',
  / |& }1 P! ^1 s; S
  # g4 p9 c8 W5 r) x/ T, u
• 
  0 I& o$ I, m6 Z# J* G8 D9 e
         MASTER_LOG_POS=120;
  
  7 Q1 r. @% _7 M$ X# f: X
  + ^# [; E. k" s. {+ U$ i1 z* ]

! Y( G! ?; k  }  S& q5 f$ R重启从库mysql服务
[root@centos153 ~]# service mysqld restart


2 j% B6 }  E1 x8 G, k
0 @: r6 E9 |9 A1 x; ?查看从库同步信息：

" S  w7 e4 P* r2 l
  i8 ]% b5 X0 D9 _1 X+ E

• 
  ; ~$ d- s1 a# c0 B' t/ l( c  U$ ?
  & o( u( F1 v5 z/ L7 L/ qmysql> show slave status \G;
  
  
• 
  ; X$ E7 u. G: ?6 z( J" X4 a+ r* t
  *************************** 1. row ***************************
  
  
• 
  : l( D6 h% Y2 F, a* a; I9 ~
  . E' C6 N. S' b+ c               Slave_IO_State: Waiting for master to send event
  
    b# K- t" }: _3 M+ x5 u
• 
  / T2 F6 t8 D, K, g% v3 k
  & N4 a9 a  w+ U( ?' z3 f                  Master_Host: 192.168.1.152
  
  $ `! L5 T0 S$ Y
• 
  & r) R5 E, e" D$ K" V0 q. ?9 Q
                    Master_User: repl_user
  $ Y; v* A8 f: Q5 i  B4 u% K1 [
  8 C! b/ L' m' H3 v3 N
• 
  
                    Master_Port: 3306
  , B: S$ u" {1 x0 c& t: ~9 }8 g
  
• 
  
  & u: N! A1 A, p% o                Connect_Retry: 60
  
  - h* s# O7 o  `0 e% }
• 
  
  # T0 l5 g; X# ]/ }( k- X2 \              Master_Log_File: mysql-bin.000009
  
  
• 
  
            Read_Master_Log_Pos: 120
  5 @  H. t% A  B" n! ~: P; x( S1 H
  
• 
  / f2 t) z) G. u2 A9 A8 C" v
  2 A) B5 c' B$ c1 h$ _) L               Relay_Log_File: mysql-relay-bin.000003
  
  
• 
  / s8 s, y, j5 t: p+ `, S
                  Relay_Log_Pos: 283
  
  : c0 f' Y, N4 I! i  N% j
• 
  , _* ]* ^; o9 [" n
  & G, P+ T; j' {9 b! W. c        Relay_Master_Log_File: mysql-bin.000009
  
  
• 
  
               Slave_IO_Running: Yes
  , W4 ?# M8 q* _2 _7 i9 }5 A
  
• 
  
  7 C; ]2 f# V) l/ Q9 L  a( a4 W6 [5 C            Slave_SQL_Running: Yes
  
  ! o! y" N- V6 m6 T' t' p
• 
  " j% H3 d6 m9 g+ J! E- V6 \2 ^
    X3 F  N7 s8 m; K! ^: w0 W              Replicate_Do_DB:
  " Q# i5 _! Q5 b
  
• 
  . N1 `4 m7 F+ I1 a
  - n7 M5 {) j* _, T6 [8 Q; ~          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  
  
  

' R2 j6 A) x1 `, H# D6 t: h
可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
. @) M, Z1 [; U/ a% B0 t


* ^9 m6 v) P/ J# q1 q现在测试同步情况：
, c7 D! b+ h0 w* D6 g% G. Q
/ o9 \% R3 J7 ]4 j5 q

• 
  
  #主库创建测试表
  ; [2 w' N( u8 Y# ~9 w
  
• 
  
  ' O# s) Y+ f* jcreate table tabdemo(
  
  6 t, b: J: d+ Q9 Q1 x6 L8 O
• 
  
  $ ?) i5 _9 H9 M: V' t- |id int primary key auto_increment,
  - F+ _1 I7 }8 x% e& q* i5 ~
  
• 
  
  9 c( @' s% t% R! k1 |value int default 0
  
  ; p% L; D; W! z4 n6 l$ y
• 
  , D- ^. g: f: T4 v8 s3 g1 A) i
  # y3 s2 Z3 R( _' q: E6 G) auto_increment= 1 engine=innodb default charset=utf8;
  
  : S2 u0 K- J( O. u( N2 G
• 
  
  
  $ ~- H/ |1 h6 I
  0 c! |+ M: A  b2 E1 O) V0 k, ^+ x8 t
• 
  
  7 F2 ]' B/ c3 x; s9 g" w0 b3 V
  * k. e% n6 ^! x, k2 _& S
  
• 
  / |' T: }0 W, U
  insert into tabdemo(value) values(1),(1),(1),(1),(1);
  : C  l$ t; n( i) p: N0 q+ w1 }
  
• 
  9 B8 x" C: w8 j( ^* r; g# P
  
  
  
• 
  
  " a6 W0 Y# ?8 p  N1 D2 j
  ' a8 \/ ~3 o. s1 T
  ; a  o( @' ~/ h, n; t/ v
• 
  
  ) N3 }0 k& P6 \: b) E+ Y' kselect * from tabdemo;
  
  : L2 q( q+ j0 j  z" E
• 
  
  , S! T8 o0 r! m3 E4 ^% p5 d8 p+----+-------+
  $ ]% u( L0 g2 K: Q
  * n( x; Q+ I7 x- l1 G6 F/ y
• 
  $ w. C9 ]* {8 A7 ^3 |/ D* V- W6 U
  | id | value |
  " }# B4 [" e+ N" `$ U
  - H- q9 @  M5 e# t5 e. c! r; }  }7 a
• 
  
  +----+-------+
    I) C+ `  Y" ]! S, \4 k* f' m% R
  
• 
  
  |  1 |     1 |
  
  
• 
  ! T$ `7 W- L1 o
  0 N( ?; Z+ J9 I* v/ H- x|  3 |     1 |
  
  / N* s; c+ l: ^! y
• 
  
  |  5 |     1 |
  # E1 J9 q( w6 _  T& l
  
• 
  . K7 {' r! X' P; _0 a
  |  7 |     1 |
  4 N# p" ^$ [0 H5 y
  . D/ l& i2 K8 w- x4 u
• 
  
  |  9 |     1 |
  0 F1 s( s  @: ?
  ' T9 a. }: V$ `
• 
  
  +----+-------+
  . \8 |0 {& C3 T
  1 Q/ P8 S& V: c2 g  W! V
  % R8 K; w7 ^1 g" @2 D

% G" V% N0 V% @9 Z* q
" l( F4 e% ^8 g3 c4 m8 R' w' \主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~
5 Y1 P# D% ?) `2 e3 f8 E) R

9 U. B! P7 m! `8 S. z
==========================================================================
+ U2 F( x1 W2 w0 r4 C7 c; q==========================================================================
4 b+ L5 f6 x* E! Y3 F; c3 o4 d
6 m7 {5 A9 s: ~( I下面配置：【主主复制】

% v) D/ |1 E& N5 q9 H2 W
更改配置文件：
/ |5 O/ o0 a  _7 R
【centos152 原主库】更改配置文件
3 |- w& S7 K) _  c5 g! w% ?0 Q[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
0 e1 a& d. Q: N# I" }& c" f$ Ibasedir =/usr/local/mysql
datadir =/usr/local/mysql/data
port = 3306
; S+ w3 g0 P& fserver_id = 1
log-bin= mysql-bin
binlog_format = mixed
. A0 Q! S# E1 J- z' K  F
read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
# {, W; U' E0 zbinlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2
, q7 e" O; S: n/ p- p
#主主复制的从库设置（新增）
* r2 T. f+ Q, e+ D#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
7 `5 |. O; i; V1 P( N. treplicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
1 }: z' o: u6 b/ e5 Plog-slave-updates=on


! h" |' M, X/ k# b3 k9 V- b2 @# R【centos153 原从库】更改配置文件
[root@centos153 ~]# vi /etc/my.cnf
6 v6 \6 Y2 ?+ f5 D6 T[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
( [2 V. e/ P1 x# lport = 3306
) O* Q4 H* D  r, e' dserver_id = 2
( b0 n* D/ t* Y: e8 ~8 N8 o8 Mlog-bin= mysql-bin
binlog_format = mixed
1 n. e1 s! |: ~
# o' f: M2 U& n7 e- s9 l2 e" Tread-only=0
4 f  y) Q, b' X  F% K0 ^6 \0 A6 Y- |' k9 L#replicate-do-db=test
replicate-ignore-db=mysql
, {2 m+ X! H4 O( ?replicate-ignore-db=information_schema
1 Y% u4 S" ~8 Z; u, W" P$ |replicate-ignore-db=performance_schema
5 d1 i. m. H  s* u" erelay_log=mysql-relay-bin
9 d5 ]! d" ]0 D0 C9 qlog-slave-updates=on

% t$ j; {$ _3 d  L9 c, ^, z$ `#主主复制的主库设置（新增）
#binlog-do-db=test
: e9 F' W" l' B% \! P# ^binlog-ignore-db=mysql
5 U# Y# T& Z. B. m& ]3 Lbinlog-ignore-db=information_schema
8 I: T' X  y/ r+ t2 c  U2 U9 R+ zbinlog-ignore-db=performance_schema
auto-increment-offset=2
auto-increment-increment=2

5 B" H  j- Y- b' |! _1 O* u& P
添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart
  r1 h  V! _7 \
" F9 Z- r  L9 H. M9 `& }+ ~" b
4 X2 H/ w$ x3 j* W1 L' `原从库(153)中创建用于连接的用户：

# h, M+ U* q" k9 W# z
[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';
5 ]( |3 _6 U8 e6 l% o
9 h+ M# p4 V" p6 A' J; l& w6 D  X原从库(153)查看当前日志记录的位置：

5 r% t! I* a+ G! p" j

• 
  " q4 o/ R8 L9 h5 I8 O" Q+ ^# f+ [
  mysql> show master status \G;
  ' W+ Z( R/ p4 D$ a7 X. o0 X
  ! m* }5 |8 P7 S  Z
• 
  
  ' v& E/ Z1 q5 p  ]*************************** 1. row ***************************
  
  ) P* J' O/ z9 \- K" p4 }6 R
• 
  . j' h3 ~4 C1 M, u6 [0 H6 ^
               File: mysql-bin.000005
  
  ! n1 l8 f2 B! R3 X% e
• 
  6 s3 I) @4 A8 j% x
  1 Q% d+ `; s% Y8 G, e         Position: 120
  
  
• 
  ; o! Z9 @% [& Y) R0 P
  & F( _2 f( R+ O: E' o& t9 ]     Binlog_Do_DB:
  
  1 H! O! H& T0 g5 Y$ e  J6 v  d8 ?! S
• 
  
  Binlog_Ignore_DB: mysql,information_schema,performance_schema
  - ]8 _) r6 q5 ?7 V6 i
  
• 
  
  Executed_Gtid_Set:
  4 m; r3 d4 p, S9 {+ B
  
• 
  ( Y- m; ?' L& O# _# E
  ; G5 W* I8 e  s8 A) b1 row in set (0.00 sec)
  - @' j9 Z& t! Q. V9 Z
  
  

8 r3 A$ f- L! m' l* C# P在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：
3 i9 Z- o! I. E& [6 x
9 i3 a4 ]7 n# {

  P0 X0 N0 [. u1 n

• 
  
  9 R/ {+ Q* q, W3 [. hmysql>
  ; G- s& F! W3 v; Q( A$ W: i: U; d
  6 U+ {0 {8 t" e# l: _5 [- @
• 
  ! I- \) G0 M7 v9 a
  " t. S. G9 |2 |& ?CHANGE MASTER TO
  
  
• 
  
  ; c; O/ C+ S+ R- s       MASTER_HOST='192.168.1.153',
  $ t- D( K# Y/ i1 d" l
  # z7 O& E1 G* N" x# M
• 
  - P8 f* }( `% `1 l4 s
  5 A, q0 {3 P7 E1 S0 }' m( N# I% {       MASTER_USER='repl_user',
    k" V, Z. U/ e* L2 Z( T3 v
  3 P  `0 |7 y+ w6 G% {
• 
  
         MASTER_PASSWORD='slave@152',
  
  8 |) r) q/ m4 H+ \
• 
  - I3 O( I2 i3 v; Q5 ~7 E# e1 r3 U
         MASTER_LOG_FILE='mysql-bin.000005',
  
  : S6 }) h+ C; B
• 
  
         MASTER_LOG_POS=120;
  & Q0 e5 ]. }! C. E
  
  / U" \  o3 D5 H, W7 V. _/ p2 y0 }

5 e$ s2 K3 d1 ]( \, N3 x2 d3 T
; a$ P4 M+ j5 a重启从库(152)mysql服务：
[root@centos152 ~]# service mysqld restart
/ n7 ~0 S5 N$ Y8 v. i* H
0 }, K& f' y" D6 R. y6 ]

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


+ q) ~9 F" I, k) h% I

• 
  
  9 `6 [% B+ p' Q% Zmysql> show slave status \G;
  / @; p$ i  x. w$ |. P6 Z2 c7 T" e
  
• 
  
  *************************** 1. row ***************************
  
  
• 
  
                 Slave_IO_State: Waiting for master to send event
  
  % I. P7 [6 p) A/ H0 O0 C' h
• 
  
  ! i0 i4 R- \5 R& K4 `# B                  Master_Host: 192.168.1.153
  - V) H& l+ w0 H7 N! c
  
• 
  ) A/ E2 w+ u1 N5 \1 l5 s
                    Master_User: repl_user
  
  
• 
  6 [; T4 f4 L- s& f+ g
  7 g0 w% {. K# k                  Master_Port: 3306
  
  4 y( M3 r' N% t! c
• 
  8 {: j. o  `' Y9 d7 s$ g$ h0 I; C
                  Connect_Retry: 60
  3 W$ v! Z3 K3 e) F; |8 ?
  : a6 r1 t: t' e0 W/ u5 A' O$ I
• 
  - I/ p; N7 J' ^1 w7 l& U3 l. w& e2 _
                Master_Log_File: mysql-bin.000005
  1 M5 }( I) u7 f6 s' Y& R
  
• 
  
  8 P, N8 @0 ~6 n6 @/ h4 G4 q" O% n          Read_Master_Log_Pos: 120
  
  8 a) T: H& j, b) g0 v+ E* m$ N
• 
  7 g3 S( f0 p8 j0 A! s% y
  , n  K/ ?+ N8 c) I+ O               Relay_Log_File: mysql-relay-bin.000003
  ! o4 @0 A% f* y7 ?3 R2 p  \
  
• 
  - J& ?! F6 h2 \( W
                  Relay_Log_Pos: 283
  
  
• 
  * y# A* {2 i  a5 G9 X: L' m
  * R* B# q0 v( D1 o* ?6 S        Relay_Master_Log_File: mysql-bin.000005
  3 r7 q0 M6 D) f# |5 ]+ g! C
  : i2 f& O4 ~1 R! B
• 
    e; O! q1 o0 j0 t; [2 B
               Slave_IO_Running: Yes
  
  
• 
  
  7 G9 f  l: w  L            Slave_SQL_Running: Yes
  
  ( c: _" i3 u/ |, q$ K
• 
  5 H3 r1 g9 o& q! |( P, ?' [) M
                Replicate_Do_DB:
  - X$ y: n# P8 K; d
  
• 
  / r. D0 g8 L$ U: K
  * I& l" u; K6 l0 O6 w          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  
  
  7 x* ]  s& H1 t

( t  |5 \7 X0 w$ s5 Q0 w3 e) b
- R. p- g( b  a5 o! U; m
现在已经配置好双主了！现在相互在两个数据库中都操作数据：

• 
  ( \% C0 y# x5 @' L- q
  6 T3 ]2 n6 J1 a8 _! w1 w( O#当前在原来的从库(153)操作数据。
  
  + w, @, N( @1 s
• 
  % M, A4 E5 `9 Y2 g" M
  9 G- S' z; p, \! V% I0 c; Y7 t2 K5 a
  % k+ L$ O) f" ^4 q: l, L
  ! U4 N( @0 }; v. d% L
• 
  1 n' h& Q8 z) a' X1 ]0 Y6 V
  insert into tabdemo(value) values(2),(2),(2),(2),(2);
  0 P% H9 D# m: Z: `5 j& l6 }: i
  ) X% {8 X5 m* r8 ?+ |
• 
  
  6 N5 R  a8 d% ?
  . U' X8 |) m& @/ l' |/ i
  , z8 W5 _- w& E* ~$ M( A+ n
• 
  6 y3 a) k& l( O0 J, T! H
  1 e) D6 R) e- Q; H: F; H- W#数据同步正常了！
  
  
• 
  
  # ~) e* P4 G# }) m* i" wselect * from tabdemo;
  
  
• 
  
  9 R& e) e/ y) U: Q; j1 m+----+-------+
  
  
• 
  / _9 {; O/ _) z: |0 `* X3 \% \( |+ c
  | id | value |
  " ]6 z" {! x0 S. O' l3 D" p
  
• 
  2 N; ?2 ~: M! r. c. E; K
  +----+-------+
  # O7 X6 z/ @9 U6 C7 T/ S- r& ]
  
• 
  , q0 D5 b9 T0 l  x+ W
  |  1 |     1 |
  . m7 i2 K5 H. O3 _4 M# ^
  
• 
  
  ! d% r& v9 j9 F0 l5 t3 H  D|  3 |     1 |
  
  
• 
  
  |  5 |     1 |
  
  
• 
  9 Q% v  e. J2 E2 l
  |  7 |     1 |
  
  6 P! c3 m* R+ [0 v3 i8 m% T5 z
• 
  
  |  9 |     1 |
  
  
• 
  
  | 10 |     2 |
  % C2 M( ~) j1 ~  e, H2 {
  
• 
  
  | 12 |     2 |
  
  
• 
  
  | 14 |     2 |
  ( Q3 r% X+ \0 D6 s
  # C$ h8 h& s! F& X8 G+ z
• 
  
  | 16 |     2 |
  
  
• 
  
  0 G* R$ x$ ?. a) N8 |. H| 18 |     2 |
  ) ]1 e" ?2 j& g' E" x7 F" t+ c
  7 X% e) _& a7 |4 a" l
• 
  ; @" T* F$ H* `4 T( n- h. g" _
  7 q4 {& y3 S1 ^5 H0 n+----+-------+
  & L% x+ f/ s; y7 [2 f
  
• 
  
  4 h6 ^6 v- h' F) P' ], C
  / G6 J, e; Y8 t( m, p- b
  % _* e0 h  n/ z+ n- d4 ^
• 
  1 C2 x7 L- _7 G6 M) p
  . `6 I4 k$ l( Q1 C0 \: j- f7 {在原来的主库(152)操作数据。
  ; _. {. E  G: n7 _7 X$ M3 G' j7 w9 Z! x
  
• 
  : R. q$ H4 w/ `# I
  insert into tabdemo(value) values(3),(3),(3),(3),(3);
  ) Q+ q* v: Q- o) v
  . ?2 t& }" A: Z, B; ?9 F# D
• 
  
  
  
  
• 
  
  #也正常！
  * o; m: D: _- K2 G: {/ z/ P
  ( K( E; q# ~: F+ U
• 
  # |: A  E/ B6 o9 E0 _  M' w2 L
  + Y* d" m* F. D2 T% p3 Xselect * from tabdemo;
  1 K  H3 n: V# x( N* s
  6 b4 F# s1 r. A
• 
  # O  r, B$ C$ _0 ]9 I& \
  # c8 w8 P5 ~8 K2 \' o, a. e/ b5 w, ~+----+-------+
  
  
• 
  
  " ^5 S& V5 r: [% ^; I) q7 _/ f| id | value |
  / h" N* a1 i- c' \) B$ r. b  n! F
  
• 
  5 D' l8 P3 ?/ k, E3 \+ |$ ?
  +----+-------+
  
  / H( ?  f0 {9 ?, j( g
• 
  
  / D& T* [7 g6 B. L% w/ H& B; n2 c- e|  1 |     1 |
  
  6 i( ~& k# ?3 D% D6 B( L5 b& i
• 
  
  |  3 |     1 |
  
  $ z9 |* F  ?# B; U9 ^
• 
  
  |  5 |     1 |
  # m5 K$ M9 R) O1 x( t$ G7 o. a
  
• 
  ( Z5 S9 S* p( o# ]& A7 l
  |  7 |     1 |
  
  , u" `* Z- q8 d7 J- E( @# F
• 
  
  & l* g- ^3 f& A6 r( t& x7 E# \, O|  9 |     1 |
  ) M- T; f$ N' [' I# d# A
  
• 
  : x9 S( B9 L2 B6 K- @( _) W! @4 v
  # d" r' |# ^/ X5 J| 10 |     2 |
  
  * R8 a7 i) M; M
• 
  ' x  l! a6 z" ^% D+ U! A: X1 t
  - x! e; d0 b- M& Y9 I| 12 |     2 |
  
  % f8 S9 ~# o/ J% B) D9 ]5 B. }
• 
  
  | 14 |     2 |
  , w/ E, G8 x/ P+ h* U
  8 {7 u& y( p6 I
• 
  
  | 16 |     2 |
  6 H& j- a4 a+ c  i9 ]
  1 V6 V6 i- d# p" ?  P4 Z
• 
  7 \3 x9 P) Z$ R$ c8 X1 ~
  7 K" w! y6 {1 j! H( N5 b  B| 18 |     2 |
  4 d' Q# S" C) h- i- e( @
  
• 
  2 M' `, d/ @: @
  & T7 F- s, ~( r" R| 19 |     3 |
  
  . l$ Q) l3 p. b2 M: ~
• 
  # y+ B8 Y" L$ [8 K9 k9 N0 W. g7 H6 v
  5 h0 d; ~6 {5 I" ]) f  V% H9 }| 21 |     3 |
  8 @1 B. t. S: R
  . D3 {" ^& d; m
• 
  
  | 23 |     3 |
  
  9 j# L. j( ^! ~# @1 X7 g
• 
  
  | 25 |     3 |
  7 W9 D5 r* f& a; K( E
  ; [+ _: }9 W; E) c6 C
• 
  
  ' M+ y0 y# T) K; X: [& {| 27 |     3 |
  7 s* c) j) E3 M$ M( ]
  
• 
  & S) Z! d- O: p! W8 `6 h3 ^$ w
  ( Z1 O8 L& v4 R+ n6 U+----+-------+
  & A" O$ _. l+ ]" q
  2 X% L2 D, u& ?: M- j% C8 V% O( [
• 
  % ~' q& r/ b9 h( J7 {
  . Y5 D! i" `3 Z/ e& ^
  / |+ z3 D4 m9 O: f, o
  
  ) z- ?# @# B4 C3 Z

9 j8 V. x4 w& i" ?4 V& Z
至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践

# X) _  K  F1 {