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

rui

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

mysql 主从同步原理：
" b& ^2 O* L- x( T- ~9 l1.  master 将操作记录到二进制日志(binary log)中；
2.  slave IO 线程 将master的binary log events读写到它的中继日志(relay log)；
2 M, M; {( X9 }  s  ~7 H3.  slave SQL进程读取中继日志，将重做记录数据到数据库中。
' A: l7 R9 H! ?- M9 N) Y$ ^$ Q! g

                               
登录/注册后可看大图

$ e# ~6 M# v+ H$ x" F下面配置：【主从复制】

MySQL Version : 5.6.22
主库：centos152 / 192.168.1.152
从库：centos153 / 192.168.1.153


配置 mysql 文件：
【centos152 主库】
7 q/ r  D4 t" y8 _/ E6 v[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
6 d7 z6 s5 d6 cbasedir =/usr/local/mysql
datadir =/usr/local/mysql/data
: u" ]9 B: J! C# e- k$ n( oport = 3306
server_id = 1
log-bin= mysql-bin
4 B7 i7 m7 e6 m3 mbinlog_format = mixed
0 p  D3 |" t0 L( G" {9 K! W
5 h& F$ I7 R) C" M) W/ kread-only=0
2 A7 H+ C# q0 H  v" t9 h4 ]#binlog-do-db=test
binlog-ignore-db=mysql
- R5 c6 {. v, u4 h# [) ?binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

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

8 q" n5 I$ k/ |7 R8 c
1 M+ @% T8 q9 S; x: Y【centos153 从库】
3 t) x" `0 D" G# G9 W[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
& z+ g+ P! L" N; M# i9 O- Hdatadir =/usr/local/mysql/data
$ Y! A7 A1 h) [. ^& N& W1 Sport = 3306
server_id = 2
* b& o& g' j* X2 h  B9 C' j, B0 ^7 |log-bin= mysql-bin
9 S* N2 S. W) J' s, o$ B' T2 @binlog_format = mixed

read-only=0
* g+ r- P1 k5 f  R#replicate-do-db=test
; R/ l2 A0 n" ?0 t/ ~) ]replicate-ignore-db=mysql
2 F0 P- I( r5 h2 yreplicate-ignore-db=information_schema
6 v. ^6 X* _: `! r8 L6 x5 freplicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
2 e( Q* a  N. x2 c# n1 Y( _/ b8 Wlog-slave-updates=on
#auto-increment-offset=2
#auto-increment-increment=2
% j' @8 K- [5 y1 z6 [; T) K8 ~
6 o3 u; N5 q3 h2 s" c+ r, w' ?5 P
#添加后重启mysql服务
[root@centos153 ~]# service mysqld restart
) ]5 n+ D/ c! D+ T3 u
; O$ R' J  X: t) o/ f6 k2 ^
4 T( k* {2 h9 N9 W' u说明：
log-bin ：需要启用二进制日志
server_id : 用于标识不同的数据库服务器
2 b. e( Y3 D8 P
binlog-do-db : 需要记录到二进制日志的数据库
+ |! ~: B& A- F. Z, y4 qbinlog-ignore-db : 忽略记录二进制日志的数据库
auto-increment-offset :该服务器自增列的初始值。
auto-increment-increment :该服务器自增列增量。

replicate-do-db ：指定复制的数据库
/ c/ j7 T% E" a$ V  t7 g, O$ z/ B- lreplicate-ignore-db ：不复制的数据库
' p) H# Q8 f% Grelay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。
log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。
& q. O  K3 l$ G
* }1 j, h% W2 g) k2 U如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment
; Q- z6 N. Q' E4 A3 m5 v' M如上面为双主的设置：
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2）
) Q+ g" D# a/ t% J& p服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）


  `7 X! F8 @* x7 ?4 a5 M4 @注意：文字图片来自《高性能 mysql》：
7 p# b3 r9 Q5 ?. E& D

                               
登录/注册后可看大图

! L0 f1 J2 n& Z$ Y# L6 X

/ j6 q- a$ J1 B, b# [# h#在 [root@centos152 ~] 操作几笔数据后，数据有些变化。
. D* i2 _/ T+ C5 M  }5 @) I
7 Y3 Z" L4 {$ h
; _9 q+ `: _* B! c+ p+ h& ?. ^

• 
  2 J' f7 F, [" K1 ]
  mysql> flush logs;
  1 f. {- ]5 v' p" T
  . N& c6 o6 g5 `' r
• 
  
  mysql> show master status;
  0 n4 D0 ]; A! b& c& _! {" Y* l
  ) @7 Z7 o. t( p; ^8 J/ f% ^8 k( y' n/ ?
• 
  % V) S$ V0 e( G) j
  7 U! o, T9 l# M+------------------+----------+--------------+---------------------------------------------+-------------------+
  % Y; N) W+ Z, ]3 [* P( f
  ) z' U0 n6 T1 }7 P
• 
  6 I/ }6 v) {, g. ]7 s1 P3 r
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
  0 {. H3 o) w% N2 M& o- c) x
  
• 
  
  , C2 i5 A: b5 W( F1 q. T( T/ m& W+------------------+----------+--------------+---------------------------------------------+-------------------+
  
  
• 
  & J/ m) w8 Q: q
  | mysql-bin.000006 |      120 |              | mysql,information_schema,performance_schema |                   |
  . H1 ]" y4 J  \  f( {3 O1 E
  
• 
  
  5 ~: O' |! }: S' Y, f0 P; M9 r- f+------------------+----------+--------------+---------------------------------------------+-------------------+
  - B+ C2 u2 E; A' v
  " \# f# O' U, G8 C" r- k! C/ a
  

File :当前正在记录的二进制日志文件
4 w# L" G" B7 pPosition ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。
Binlog_Do_DB ：要记录日志的数据库
  A! `5 o. Z  k5 r6 S& bBinlog_Ignore_DB ：不记录日志的数据库
  E& N  _' b9 ]1 u0 AExecuted_Gtid_Set ：已执行的事务ID
( T2 F1 X7 ?7 N; E+ A$ s" T: N% @
* z  _; i0 W/ I5 E
, C# T7 d$ [% @5 i  d0 j% E  C
二进制日志情况：
0 v# h* q; ^% W8 P+ N$ `( A8 T6 U5 t8 v
6 K, [" [8 Z  q

• 
  8 b; o5 k; [7 l" i
  7 {5 A) y. ]3 o. e# w2 Bmysql> show variables like '%log_bin%';
  5 I0 ~6 J: v, L" I
  / _& ^% O" u$ e% G: h) X
• 
  
  5 X: Z+ i* n; C* g7 v4 D+---------------------------------+---------------------------------------+
  " `0 L8 }/ H- `1 Q* h% v# k! O
  
• 
  1 M6 u! L+ ~& ?0 x; o5 \# x
  | Variable_name                   | Value                                 |
  6 e% C* I0 \1 G# w% c" y3 T
  
• 
  $ X% a6 ]9 O* B" B+ t  M
  +---------------------------------+---------------------------------------+
  5 X* Y  E/ Y) y1 Z: K! W: N
  
• 
  9 c, w# a: e8 l2 O7 v6 n  T
  ) w! i# y3 e5 D% ~* H% X5 ]| log_bin                         | ON                                    |
  
  
• 
  
  | log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
  
  
• 
  
  | log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
  
  " F9 `, {& ]! W; I: o& y
• 
  
  6 a1 s/ A6 d- g| log_bin_trust_function_creators | OFF                                   |
  
  
• 
  
  . M9 M. O- O2 Y2 F' p" C| log_bin_use_v1_row_events       | OFF                                   |
  ' Z6 K' q- u& K  o0 z
  
• 
  * W( b9 V9 g! T: ^. S, T7 f
  | sql_log_bin                     | ON                                    |
  
  
• 
  
  +---------------------------------+---------------------------------------+
  1 _4 N* G3 P: V9 P* }- C3 N# o
  
  

日志位置：

0 {7 B' B; T% m. s& u5 g

• 
  
  [root@centos152 ~]# ll /usr/local/mysql/data/mysql-bin*
  : e1 \& E+ ^" T' `7 ?
  
• 
  # u6 a, {7 w( Q$ P& p
  . I# r1 k% ~1 L& m, v-rw-rw---- 1 mysql mysql 701 10-15 20:30 /usr/local/mysql/data/mysql-bin.000001
  : q# a  D* k# T) K
  . t9 I* S! ~1 g! d5 c+ A; t6 x3 V
• 
  
  / ~6 c8 r1 @2 Y* }-rw-rw---- 1 mysql mysql 167 10-15 20:58 /usr/local/mysql/data/mysql-bin.000002
  
  
• 
  
  ) L4 n- P1 E/ Y( u! K7 c! \-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000003
  ; g: w% O* u3 z. `/ H' g) A0 r
  
• 
  
  , C' a" h2 {1 m8 `  ^3 m  a-rw-rw---- 1 mysql mysql 167 10-15 21:02 /usr/local/mysql/data/mysql-bin.000004
  
  
• 
  % r6 h2 w! Q/ D: T- C2 _
  -rw-rw---- 1 mysql mysql 581 10-18 22:42 /usr/local/mysql/data/mysql-bin.000005
  5 i8 Y7 i; }( k; \9 h$ Q
  ! d0 E) W- k, a" m( g
• 
  : P1 E3 c5 P3 }. x% M  F+ e: R
  -rw-rw---- 1 mysql mysql 120 10-18 22:43 /usr/local/mysql/data/mysql-bin.000006
  
  
• 
  
  -rw-rw---- 1 mysql mysql 114 10-18 22:43 /usr/local/mysql/data/mysql-bin.index
  
  1 R" G; _; g* {: {& a, d
  

; h" {0 @$ O/ A2 W
5 c& ~! `1 G; D9 w1 c/ V( r#现在禁止操作主数据库！防止日志有变化，保证主从数据初始状态一致！

mysql> flush tables with read lock;

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

5 G. ?( M0 G$ B, p

• 
  3 U/ [; k( F/ f% M. R) z) T5 R
  [root@centos152 ~]# mysqldump -u root -p test -F > /tmp/test_backup_`date +%Y_%m_%d_%H_%M_%S`.sql
  ! Q' p; R2 _4 M6 `# D. H% 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
  
  
  

# c7 W6 z5 y7 j+ R查看当前日志记录的位置：
) s% `9 J3 S3 b4 A  O
5 P% n5 `. {7 c
% s1 W6 E4 D' ~
* q* Z  ?' P( H4 ~* y8 H9 F) G( v

• 
  
  mysql> show master status \G;
  
  
• 
  6 N* o9 w8 ^8 T( i# G: V( v
  *************************** 1. row ***************************
  2 W# b& U$ P& z- |
  5 z' j' N  e0 ^
• 
  
               File: mysql-bin.000009
  
  ' ~; @' p* S- Y1 a- I2 V! ^
• 
  2 j3 y5 U6 T1 U9 X' y2 V: w
  $ r# C; k8 k- l' t1 `         Position: 120
  # h2 Y0 w  D5 W- ]+ i* I) ?1 }
  ; \8 b) l) \/ f/ _4 k
• 
  ; b6 o! `) c# B/ [- m! @
  ) U3 ~5 i, \. ~' N/ i     Binlog_Do_DB:
  - U& D7 g, S4 t3 b3 v/ y8 H
  3 Y# k( v0 @6 {* l. _
• 
  & n, y  `" ^( g4 v
  Binlog_Ignore_DB: mysql,information_schema,performance_schema
  
  
• 
  
  Executed_Gtid_Set:
  4 W2 g- q9 `+ Q
  
• 
  " W) x! L" H1 w  V! s* T. @# D
  1 row in set (0.00 sec)
  
  4 B5 J; o$ O& A2 V
  + q" j/ l2 ]& r* Y% V) M: U

备份完后，可以解锁了

; h- j( q+ M0 b$ b, ]* q, @
4 _% X6 f9 {! u4 c1 e! R9 ]( T
mysql> unlock tables;
! D5 C' C( q& z7 T, P6 R5 o
* x  g- Z  }: L3 o( v9 o在主服务器拷贝备份到从服务器：
: L; |+ N8 `+ _9 B' Q
4 |! B6 r  H4 [; [" H- r. ^
. V% v2 P3 V8 M$ y3 |" T" f$ F
+ k& `" G" `9 g4 V% x9 w- O

• 
  
  [root@centos152 ~]# scp -r /tmp/test_backup_2015_10_18_23_33_30.sql root@192.168.1.153:/tmp
  0 e8 U$ w" d/ l7 \
  : v) ^9 t* x' a6 A0 \# `+ N
• 
  
  * t: ?8 W0 o; c" E. s
  
  4 U5 t$ G. {9 f# E! B; c" s
• 
  
  ; Z+ b) ]5 X  [. z* T" b& `$ ~% _+ Q#不行则先关闭主从服务器防火墙
  
  
• 
    L, ]; Z2 u* `
  /etc/init.d/iptables status
  
  5 @# S9 \4 J% Z" w) H! d" H# L
• 
  
  /etc/init.d/iptables stop
  
  - H9 T" v/ I$ i& u9 X" y" S
  

5 }% H7 V5 b' n( ~
在从库中还原数据库：
3 |8 G0 @  j& {5 \  i

! O' [, A2 _( |" \  A% Q
[root@centos153 ~]# mysql -u root -p test< /tmp/test_backup_2015_10_18_23_33_30.sql
' Y, r" e; m  Q5 i) m9 ]$ j4 R1 e
/ g' Q9 {, e4 n( N" j( ?主库创建用于连接到本地的用户：
9 R- y7 H2 K' w; P2 B
9 |8 h8 w- I# r' ~1 |7 p
& U2 y, a5 L6 h  l/ k* N! _
[root@centos152 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.153' identified by 'slave@153';
1 e/ Z1 D. {' v9 I9 R/ ~
- K0 l1 y% s+ p& I6 [在从库中连接到主库：
8 X9 a. l; S) t3 x

; ]9 d( h' k7 C4 e) b1 o

• 
  
  9 H! @9 g  \; O2 c: f9 Zmysql>
  
  , f0 E0 p4 |3 n* s" T) {
• 
  8 o- Z' x5 B4 d3 [2 j$ K
  0 }& I, o. ^7 b7 Q6 A6 O, fCHANGE MASTER TO
  6 |% k8 X* l# j( c5 Z
  3 S2 j; j, M$ a7 S( l5 t
• 
  # v& N$ u) l9 C- d! p/ S
         MASTER_HOST='192.168.1.152',
  
  
• 
  $ R" q" I- i' [2 {0 o8 @
  3 j2 [2 s5 g6 E+ R       MASTER_USER='repl_user',
  
  
• 
  ; i  X$ z. |: c4 C
         MASTER_PASSWORD='slave@153',
  
  5 W3 F. B% [1 N# @! y' G
• 
  
  ' V! c! D# X3 O9 n4 j- c& ^- Z3 S# @6 C8 W       MASTER_LOG_FILE='mysql-bin.000009',
  
  
• 
  
         MASTER_LOG_POS=120;
  
  
  

重启从库mysql服务
: o! x8 G" P7 J3 i$ O0 f5 Q[root@centos153 ~]# service mysqld restart


: y3 Q0 C" g( ?- z6 D4 Y
% k  t, z, V7 d. \5 h0 r2 F查看从库同步信息：
' ~- a: i! q1 C6 w/ [6 ^
! s4 T3 k$ h* E- b2 E

• 
  $ o6 L7 G! o+ ^9 s% M3 ~
  7 B5 C  d1 I8 T- ^mysql> show slave status \G;
  : ^( J3 X* R( ~" F  }! a
  
• 
  8 N1 r0 X; N6 \) H. I
  *************************** 1. row ***************************
  * h1 q' `) K$ c2 C- r3 @. U, `/ n
  
• 
    }% X  [) m7 e2 k( @
                 Slave_IO_State: Waiting for master to send event
  ) _: L) Z7 a7 y2 C* K
  
• 
  * [) _' _- ~' ]* _& g* b/ b
                    Master_Host: 192.168.1.152
  
  
• 
  ; N; B8 M' r" v: G; Q6 `! M
                    Master_User: repl_user
  6 p7 t  `1 x+ _! k* u
  
• 
  
  , S  y9 \0 I& i; h8 O! D! A                  Master_Port: 3306
  6 r0 P+ Z, c: @4 b
  
• 
  
  ' h( v% n7 d. D: ~; ~0 [$ @                Connect_Retry: 60
  + P% n2 a$ g4 k/ y
  
• 
  
  $ ?& g' v  U3 Z- Q' T% _              Master_Log_File: mysql-bin.000009
  
  % B& @3 o" L% }! J5 a
• 
  
  4 n$ ^2 N7 b: o: _0 N& W          Read_Master_Log_Pos: 120
  ' a$ Y; B6 T1 l3 D3 Z' K
  
• 
  
    F/ b" J3 }; Q- H3 p# G, `  Z( Z               Relay_Log_File: mysql-relay-bin.000003
  
  
• 
  
                  Relay_Log_Pos: 283
  & F) H3 i7 o: G: h0 T8 b2 [" n& n; f
  
• 
  
          Relay_Master_Log_File: mysql-bin.000009
    s! G% r3 Q0 e- `7 }% J
  
• 
  7 i8 Q# K( ]' Q% U& h- U' Y+ c* {
               Slave_IO_Running: Yes
  1 }5 L2 H- W+ o& F
  
• 
  
              Slave_SQL_Running: Yes
  ; {0 P0 C: w" T: C' T; G
  9 X# [& D, t9 N  p" X" h3 a& w/ H
• 
  , t7 B# N1 r! m5 ~: U4 @. ^2 ^. @
  6 |5 E6 |! W& M$ B4 n) a              Replicate_Do_DB:
  
  
• 
  
  7 P8 z! }" H$ n8 x. X, z( ]6 U          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  ( d  K2 I% r1 e4 P. M
  
  8 V& ~' X# ?% W. [- {4 F& y

) B5 F5 W. a1 c  C1 Y0 c; r
可以看到， IO读写线程 Slave_IO_Running 和 SQL重做线程Slave_SQL_Running 都为 yes，表示正常执行！
# e+ M( x3 G: x' |9 F) \9 V
/ \/ e; l2 Q. m8 ~( g. b
' u% Y9 n2 m7 z6 E" b# ?: \* E
$ L' l; B. n) g* E现在测试同步情况：

• 
  ! S2 f% [% X" `5 V
  6 e! h2 x7 }9 d; S#主库创建测试表
  + T  L+ K, b6 a3 W4 o
  . F9 G2 R" T! {4 a* A
• 
  8 p( m+ Z. O9 r' j
  3 }6 `( w/ \5 l) l4 B, [) Kcreate table tabdemo(
  : h) S7 t, C: v% ^, K
  " j" D0 W1 f- U% Z
• 
  ) o2 B& A) |# K' l, {  b* P# T! u/ f
  + l7 E! i# _$ S2 m$ ~2 tid int primary key auto_increment,
  8 ]5 E( {5 _4 ?- K. H7 i
  4 S+ r2 ~5 t  Z: W/ S8 b  T8 j
• 
  % b( b' u" J- g! M
  3 ]; A" F1 X) y- r0 yvalue int default 0
  % I9 X4 @: n; f$ O/ K" {: e) \! S
  
• 
  ( P% H0 P/ T0 P$ W7 r' [/ ^
  $ B: ^+ K. j2 w' t; f) auto_increment= 1 engine=innodb default charset=utf8;
  
  + W' S* i& C/ Q% T
• 
  
  
  ; W4 I, m9 z. ]3 N; a
  : Z2 X* A+ d# s- @! C# f, X
• 
  
  0 V4 ~) R9 j% Z
  4 k5 |9 V& g& o" F+ s# o$ |$ |
  
• 
  : e+ \5 @  g& A0 c. B
  insert into tabdemo(value) values(1),(1),(1),(1),(1);
  4 s8 r1 U. m1 {
  $ F8 N5 ^+ ^* N
• 
  8 Y7 C9 t2 |) ]/ x* u! e6 n
  
  
  
• 
  5 h& W( H5 G8 O! n6 O; }& N$ g4 |
  
  # O7 O; i, i3 U1 ~% L  L9 Y+ R
  
• 
  ) T5 \0 D7 `7 u6 y: G) _
  - a# [/ r$ O# U" `; dselect * from tabdemo;
  
  
• 
  # r# r+ o% C2 m3 B: V, B
  - L: C* R6 |4 }& V# d* s8 d+----+-------+
  
  2 f6 x* l' l/ z5 k: H; r
• 
  5 `' \! s5 h7 \5 C8 U
  1 M% K5 R0 r( N| id | value |
  
  
• 
  2 g/ J/ b( U5 |$ {
  +----+-------+
  9 @: ~/ n2 r; j6 m
  7 x/ B' j; v2 }3 ~% y* Q2 g
• 
  
  |  1 |     1 |
  
  . T8 ~( C5 |, k: e
• 
  
  - `/ s. W: u* e1 z! q|  3 |     1 |
  5 g# Y  y: P5 z" e! @, w
  
• 
  
  6 t' |7 Y0 c! j) k, D4 u|  5 |     1 |
  2 m. G+ x" M9 ~* [1 Y3 Q
  4 x) H" D( r+ w4 ?
• 
  $ N% f! s3 h! H- A# j# P
  4 [. N- p6 N* w|  7 |     1 |
  8 f: @5 o. a6 n7 ]+ G" q8 `
  , A. U0 }: c1 n
• 
  8 I: R1 _+ V) q. R6 H
  |  9 |     1 |
  
  0 X5 h9 l( }7 ^
• 
  
  +----+-------+
  1 A$ X; m( H* x* A; U+ ?6 k0 [
  
  / H2 z6 F: E) R: G1 N/ ]" c/ Z

, m1 j& Z+ N: i' A主库操作，主库和从库都有数据了！id 编号与上面所说的一样！~现在主从复制已经配置成功！~



2 J# E- b0 d% {. }4 ?% |3 l/ g==========================================================================
) N+ Z3 L! L# v. y/ P# i$ D* f==========================================================================

下面配置：【主主复制】
7 n4 z9 L* u. a! F

更改配置文件：

【centos152 原主库】更改配置文件
# I1 k* m  z# B: R* B' `' v[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
3 B# R2 |7 O9 H# l; Y; Hport = 3306
2 j* A! B, c5 @3 |: nserver_id = 1
log-bin= mysql-bin
# v  x/ }0 |! p$ y8 Mbinlog_format = mixed

0 m7 x/ @2 V( xread-only=0
+ a. p/ {: M6 E7 Q5 h: V$ r#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
$ K* u$ w- \4 Y, Wbinlog-ignore-db=performance_schema
auto-increment-offset=1
auto-increment-increment=2

#主主复制的从库设置（新增）
#replicate-do-db=test
/ f& b  I  L2 k3 ~; d2 |6 y( ^replicate-ignore-db=mysql
6 Z0 l8 S, W3 V# l. g& r3 f! Breplicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
; x- H" N2 P/ irelay_log=mysql-relay-bin
log-slave-updates=on

$ x) I+ x/ e2 V8 H& |* I2 M
【centos153 原从库】更改配置文件
' q% f  C; o; S, N! X1 L3 |[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
: L4 I; N( l1 _4 _- Jport = 3306
server_id = 2
, l& b# R% X0 ~& x! F  \3 z) alog-bin= mysql-bin
; q1 P7 r" p) |! e" Ibinlog_format = mixed
# [( a/ \% v& u& y
) z) F  P  d9 hread-only=0
9 S- _, A4 Z& }& B  w  c3 o4 G#replicate-do-db=test
* E7 W% c9 y: L: A; ^replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
7 c8 Q  X% T& e- `1 H' Wrelay_log=mysql-relay-bin
% p/ G. @& H) K: c8 ^log-slave-updates=on

#主主复制的主库设置（新增）
7 K2 i# b/ y! z7 f: G& p' l- O4 R#binlog-do-db=test
" H, l2 ]+ a! `; i' r# q) N: ^binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
* U; ?4 k2 q' I6 _( Sauto-increment-offset=2
auto-increment-increment=2


9 ~: X, m. i/ ?  F$ ~+ {/ ^添加后都重启mysql服务：
[root@centos153 ~]# service mysqld restart
  A: W) {' v3 l# l1 U" F9 D9 _

( ]8 Y9 }* u1 ^$ D7 \原从库(153)中创建用于连接的用户：


[root@centos153 ~]# grant replication slave on *.* to 'repl_user'@'192.168.1.152' identified by 'slave@152';

原从库(153)查看当前日志记录的位置：
$ D# c/ }/ V1 A5 i9 s+ i; i

) K" n$ P% P$ q
% [1 f+ t/ J! @. D0 I

• 
  
    y8 p# r# z: L# n7 N8 ?$ p* h' emysql> show master status \G;
  
  
• 
  
  1 y& l9 |# ^1 m( _$ O* g8 T. x*************************** 1. row ***************************
    v+ d$ _' ~7 M* I
  - E3 H1 B9 Z6 H* N% P# [
• 
  
  % O& N) M# w8 D  N& H9 m: ?, A  M2 u. n             File: mysql-bin.000005
  6 h) m+ A! c# k% S% k' Q
    c: r  }' j$ l% D% r% ?# g8 Q
• 
  
           Position: 120
  
  
• 
    W7 n' Q) S" F0 E0 X
  $ I+ z0 J& h, ~: ~; g: v7 l+ Y     Binlog_Do_DB:
  1 J( \" Q9 z' c" M# E
  $ }$ b7 A& k- q6 L6 E, J' [$ p- A+ Z
• 
  
    s4 j5 t* a, z. d* `* A( b Binlog_Ignore_DB: mysql,information_schema,performance_schema
  
  
• 
  / }  T# j) N0 C$ y4 F. }  ^
  Executed_Gtid_Set:
  
  
• 
  4 i  `" v8 M2 p& u
  7 r- {1 c7 {, ?4 o# w* s* Q1 row in set (0.00 sec)
  
  
  

) }" x! m) Q; S$ r. A
在主库(152)中连接到原从库(153)，之前的从库现在也将是主库：
/ G4 H# m, c: f* n$ z; X, ~: X
7 u1 y- C3 Z6 `4 d: Q: T& C, m4 V- P
: V1 l. B  u7 D. f' b+ M
9 K- @- K; g3 s! S

• 
  ' F7 |  p& s3 s: K5 R
  mysql>
  
  
• 
  
  CHANGE MASTER TO
  / C: E4 K7 ^0 ]( T: {
  1 t- ?3 v8 V! R9 U9 ?( J7 F
• 
  
  $ J! x( m6 U4 G4 _9 |4 F7 \       MASTER_HOST='192.168.1.153',
  , E# ^& F1 l" f! e6 c# n9 r1 x
  + C/ N( o* e& |5 A! e
• 
  * l; C5 J" d2 R; o
  5 l9 n2 X! m# n  j% w; t2 z+ c       MASTER_USER='repl_user',
  
  
• 
  
  , y5 ?9 N7 i% l$ x2 h! |7 `, d0 W       MASTER_PASSWORD='slave@152',
  ; J) N/ T8 m: d' d7 w
  $ A3 s% `. }( w
• 
  
  7 @* p+ ~) g* C" D       MASTER_LOG_FILE='mysql-bin.000005',
  
  4 y  L& C# D% K7 ?( D
• 
  , |" j& N' M6 e* T) `
         MASTER_LOG_POS=120;
  . Y0 ~9 [% |, S  s
  8 g. w/ M' J8 H( W5 C" Z
  / W& H; c$ k0 |2 m" s+ k

. l8 J. C$ D. V# |  @$ e7 l1 E( o
重启从库(152)mysql服务：
[root@centos152 ~]# service mysqld restart

/ g2 T- v3 d; G* W
2 Q5 h) ^* M3 j. V: p3 T
查看从库(152)的同步信息：


2 X: b1 U/ W. W2 R1 {

• 
  7 B3 @) C$ {6 }/ u
  * {9 n9 F  j8 s  V5 C+ {4 p) o  D  _6 Hmysql> show slave status \G;
  
    R* I& E9 r! m  c& l; o  u1 C
• 
  
  1 B! \6 W2 M+ K' l$ V& z" a*************************** 1. row ***************************
  
  1 Y3 x. j7 {+ f) n% V% {
• 
  
  ( [2 R/ T1 O8 J               Slave_IO_State: Waiting for master to send event
  5 F. w1 N/ x4 z+ h; K
  9 n4 r5 O* a, o2 ^% m
• 
  ; l5 L/ B! I4 O4 ~
                    Master_Host: 192.168.1.153
  
  
• 
  
                    Master_User: repl_user
  
  * A: K6 @, ?- g; l9 o
• 
  " h) I/ q- N! L; n% I6 r7 q
                    Master_Port: 3306
  
  
• 
  
                  Connect_Retry: 60
  * @1 V% S: C' w/ x
  
• 
  
  5 J2 H/ V) d3 k              Master_Log_File: mysql-bin.000005
  
  6 o& y8 M, |1 G4 J7 F/ e
• 
  
            Read_Master_Log_Pos: 120
  ( \' x( G2 S% O" ~: U% i
  : ]+ D% n) K7 {( w
• 
  & a  m: d6 ?6 C1 |9 y( q
                 Relay_Log_File: mysql-relay-bin.000003
  . F) \8 k+ `7 ~- F9 ]/ s
  
• 
  
  , c- i1 P1 }6 D- J5 b* B! }+ `: K                Relay_Log_Pos: 283
  $ m' S- @; {, `- Q. t* `" o
  . x3 w5 w' b$ @; I, f$ n7 ?+ H- m( q
• 
  ( [1 D, t" s/ Q" z# j/ v+ y
  7 |8 `* |" M% l! j        Relay_Master_Log_File: mysql-bin.000005
  
  
• 
  
               Slave_IO_Running: Yes
  
  
• 
  * c2 u" N/ ?; N, h
  6 K. p9 y8 X; _3 ?! D6 D            Slave_SQL_Running: Yes
  
  
• 
  
  ; l$ a* h3 w# Z              Replicate_Do_DB:
  
  ; K# E) E, E2 }: e2 x" R
• 
  
  6 b# P/ W- m6 c  [          Replicate_Ignore_DB: mysql,information_schema,performance_schema
  5 d& G7 L% p  k8 N6 t& g5 \  ~; }: O
  4 d* q  F: R- J# m- Z8 j
  ) A' _6 U8 R/ r/ P

" ?1 H% _! p" s% J2 k  a9 h


* f0 Y# U# u7 x3 ]- Y) m, `7 E' e现在已经配置好双主了！现在相互在两个数据库中都操作数据：
" S: Y9 P, i' x. z) z
  u& X8 z9 t& b# L' E' D0 O

• 
  # Z* p" L( B  ^9 o, Y
  #当前在原来的从库(153)操作数据。
  8 Y- W6 c& Q% q& m& X
  1 z6 t. f7 ~: x1 u
• 
  
  5 c, y) p: {4 G5 n. ^! Q
  
  
• 
  % l" N' }4 _! Q' @' \5 O7 g
  insert into tabdemo(value) values(2),(2),(2),(2),(2);
  - x! {) S/ w1 }7 t4 N
  ; h; Q$ V: O' U1 @3 |2 x
• 
  / k+ w% a+ P6 s* U' b
  ' M5 U1 P3 q3 F" n$ ?/ q6 }
  9 r( A6 ^- A7 ~7 i, J! K+ c
  / d& l8 X" T3 n) |
• 
  - w0 R/ R8 w6 ~/ W) e
  #数据同步正常了！
  / ?0 @; ~8 i1 t6 B5 r
  + _/ k# L' k! x
• 
  ' `2 M2 e6 T$ ]
  1 t. K8 S3 f+ J' X& P* g5 f; ]( Pselect * from tabdemo;
  
  
• 
  
  +----+-------+
  $ o8 U! f: C4 A4 K
  
• 
  4 G4 e4 C3 @/ P+ o
  ! @" n2 u& g2 E) Y7 n2 W| id | value |
    d& a: ~  r/ a/ c
  
• 
  ; ?: o8 B4 ?4 D1 c6 e. G
  +----+-------+
  
  $ S5 o4 f; ^2 B6 G( t% D. K$ X
• 
  0 x7 P+ [# U. }3 ?" c
  $ Y8 A# A# I+ X  b0 Q|  1 |     1 |
  
  
• 
  5 r( M9 Q$ Q& K! R+ g* c# z" u
  4 S( s- s+ y- p" P- B* {|  3 |     1 |
  + p8 H5 ^  J: X4 V  a7 n; J
  , k- X( _$ M# J/ m/ X2 u" W
• 
  
  |  5 |     1 |
  ) D8 V/ y9 a3 W! O$ N
  
• 
  " c* w6 y3 U. _; d! p" V
  |  7 |     1 |
  
  
• 
  
  + V" H( _5 o( D% b. e|  9 |     1 |
  9 V) T) s$ T' _6 c
  1 c" w  ^- w$ i* y! V4 K
• 
  5 d6 k; F3 _/ u) ]8 N- z9 y
  | 10 |     2 |
  
  ) [9 T9 }/ v& f' G) M0 z. V: s3 L
• 
  
  | 12 |     2 |
  ( j" N' k' t9 Q" t7 Y/ a/ f' o% c6 V
  
• 
  ) V- z( a3 ^* e3 a: N; p6 I( F
  | 14 |     2 |
  
  + H7 E1 p- Z- i" G! p$ C3 ?
• 
  
  | 16 |     2 |
  ; ?0 j8 b( _/ J
  
• 
  1 o' l$ r$ G- z
  | 18 |     2 |
  4 O' {7 m/ i) y& ?8 f, z" ?
  9 |9 j7 G0 T, ?1 k; f2 }
• 
  
  / A0 b* b, l, E2 c" H: k+----+-------+
  3 x9 G" e) \2 z8 n8 ^+ K3 r
  
• 
  
  ) \. u  \+ F- s  h
  
  
• 
  1 i  T- r/ q7 V9 w: p2 g
  % n% s( s  Z' u. A  T% g/ M) o在原来的主库(152)操作数据。
  " [. I# \+ q0 F! T
  
• 
  
  insert into tabdemo(value) values(3),(3),(3),(3),(3);
  
  
• 
  
  
  " n) E2 K; s+ X) ~* W
  
• 
  
  #也正常！
  , o+ o- [: i# p' {) R
  
• 
  , l& W. J! O4 z8 ?. z% K6 }
  select * from tabdemo;
  9 O- i/ \6 m5 I, C- W
  5 Z3 E3 s* C% p
• 
  
  8 s" u1 m/ H5 m; ^  a7 r- C, F+----+-------+
  
  
• 
  
  | id | value |
  
  
• 
  & A4 F0 k6 T0 _3 n# n* ?
  : T4 ^$ V) d! m: C4 _% f+----+-------+
  
  ; X& N( L- Q3 s4 p( A7 n
• 
  
  , J5 w& O( r. X( b0 f; x& Z|  1 |     1 |
  " X4 L* m! j; M1 p0 e# ^6 F/ \
  0 y8 |0 `3 m. g$ h5 Z) x6 x
• 
  
  9 g9 C4 A! H) E2 {+ R|  3 |     1 |
  
  0 I& {. x1 I: I2 E: T7 w" G
• 
  2 ^, r. u- y, j& e' O: {
  $ u6 X/ l8 M3 R4 g5 t8 K* R3 z; ^|  5 |     1 |
  5 m6 _- g& D& d; {
  * m( a9 @# B* h, s" U3 N& B
• 
  ( j1 a9 K3 v; l% ?1 A/ i6 i
  / o* m& q# W9 t, M6 @2 U4 A, ~# M0 S|  7 |     1 |
  
  
• 
  
  |  9 |     1 |
  ' r2 c" c: e8 D& _. j% `
  
• 
  
  | 10 |     2 |
  / [2 |0 i% k, ~4 I" W# @: R! ]
  0 ]6 ?& R# d: v( c- V! B, q
• 
  
  | 12 |     2 |
  
  & R' ]0 B) {& ~4 j
• 
  1 ~+ _! A: s( Q1 V
  ) @/ N, n: W1 e0 h6 l/ U| 14 |     2 |
  - M9 S$ Q. m( y# H* x' B
  : h7 A( }: E# J  }* y
• 
  ) ^2 L  a, a. a4 ]" p
  | 16 |     2 |
  - c5 a& C( A8 o9 L% ^& q
  ( l# A6 @/ k) B6 O
• 
    Y4 g* |- M6 ~5 e' B2 A
  8 J: M+ v' J- }- z9 P| 18 |     2 |
  
  # Y6 u0 l# A& U4 q
• 
  
  # V, R4 `& U) p9 R2 S' Q* ]| 19 |     3 |
  
  / X  W8 o  d0 F5 D. h' S6 C2 T' }
• 
  
  | 21 |     3 |
  
  ! X! i7 J1 t) G, N
• 
  
  | 23 |     3 |
  / p" q. ^/ ]. K9 L/ y8 x# [
  ( Q8 H. w/ `/ c
• 
  
  | 25 |     3 |
  
  $ L# M1 ?: N5 n" s% f
• 
  
  - i- N, `3 D9 ]5 M2 n' U| 27 |     3 |
  2 b. o* H* c( i; r
  
• 
  * l# [! E  d4 L( ~
  +----+-------+
  2 L0 e# N( T+ ~. r5 ], {
  + Z" V, d8 f( L! L# K9 A
• 
  8 i  K' @% G$ ^
  
  
  # y9 T6 y# v# x) b( @+ X; g
  

5 Z; {1 v! _; _6 }) b
至此，测试完毕！~注意参考：学一点 mysql 双机异地热备份—-mysql主从，主主备份原理及实践
, ?# _, N. Q% x" h5 n; I* d* c' |