基于bin-log&position搭建主从架构MySQL

基于bin-log&position搭建主从架构MySQL

目录
一、MySQL主从搭建
二、主库
2.1、确定主库的binlog是否开启
2.2、骚气的命令
2.3、记录主库的mastN 3 | 3 Uer状态
三、从库1 @ K w G l d w
3.1、从库和主库保持同步
3.2、开启主从同步
3.3、从库L U b ):如何断开主从
3.4、主库:如何断开主从
四、中断处理
4.1、Slave_IO_Running异常
4.2、Slave_Sql_Running异常
五、流程
六、可能会遇到的问题p L 2 ; 6 t }
6.1、问题一:
6.2、问题二:
6.3、问题三:
6.4、问题四:
6.5、问题五:
6.6、问题六:
6.7、问题七: Z h
一、MySQL主p $ R l v J +从搭建#
搭建主从架构u @ 5 8 d } 2MySQL常用的有两种实现方式:

基于binlog的fileName + po& n ; ] @stioI B ^ H En模式完成主从同步。
基于gtid完成主从同步F p B N G o搭建S ] *
本篇就介绍如何使x ( A M M _ U Y g用第一种方式完成MySQL主7 ! m =从环境的搭建。

基于fileName和position去实现主从复制,所谓的fileName就是bin-log的name,position指的是{ P 4 y Q islave需要从master的binlog的哪个位置开始同步数据。

这种模式同步数据方式麻烦的地方就是需要我们自己通过如下的命令去查找应该从哪个bin-logO & B k _ # V %的哪个position去开始同步。

二、主库#
2.1、确定主库的biR 9 Z W ) o . _ ynlog是否开启#
命令:show v) R g #ariables like 'bin-log'

原因:了解MySQL中常见的三个日志:

单机MySQL的9 P . I ! g ! e 0undolog日志中记录着如何将现有的数据恢复成被修改前的旧数据。
单机MySQL的red3 $ (olog. 中记录事物日志。
主从模式的MySQL通过bin-log日# M ! 6 #志同步数据。
2.2、骚气的命令#
Copy
grf x E C j 0 8 X 5ant reple o z z g K G N Pication slave on . to MySQLsync@"127.0.0.1" identified by 3 L 8 F `"MySQLsync123";
这条命令是在干什么呢?

捋一下e m T思路:我们做主从同步,在主库这边我们其实会单独创建` g M T Q P一个账号用于实现主从同步。w y ~下面的命令其实就会帮我们创建出username=mysqlsyn0 - gc password=mysqlsync123的d b y = G { # I q账户专门用户主从= 3 q + 5同步使用。

执行完上面的命令后,执行如下的[ J %命令查看上面的grant执行结果:

Copy2 ] Q b d Q y P
select user, host from mysql.user like '%mysqlsync%'

2.3、记录主库的mast1 N { D ) ` ver状态#
注意主库的查看主库当前是第几个binlog,已经数据的positiG & V = ] `on。

因为一会从库就是根e L : m [ 2据这两个信息知道自己该从主库的第几个binlG d 1 O c w T Fog的什么positon开始同步。

三、从库#
3.1、从库和主库保m j j $ / o R `持同步#
从库执行p F [ 1 Kchange语句,和主库保持同步

Copy
CHANGE MASTER TO

MASTER_HOST='10.157.23.158',
MA^ e [ zSTER_USER='mysqlsync',
MASTER_PASSWORD='mysqlsync123',
MASTER_PORT=8882,
MASTER_LOG_FILE='mysq} : n , Z L Dl-biG z 0 T 1 |n.000008',
MASTER_LOG_POS=1013;

CHw P Z x +ANGE MASTER TO
MASTER_HOST = '${new_master_ip}',
MASTER_USER = '${user}',
MASTER_PASSWORD = '${password}',
MASTER_PORT = ${new_masy l O 4 cter_port},
master_auto_position = 1;

CH0 | r ?ANGE MASTER TO
MASTER_HOST = '10.157.23.123',
MASTER_USER = 'mysqlsync% * w Q 1 h',
MASTER_PASSWORD = 'mysqlsync123',
MASTER_PORT = 8882,
master_auto_position = 1;
3.2D q T 0 Y C P、开启主从同步#
Copy
start sla{ c Sve
show slaveB d J y G ? 1 sta? t & $ . 5tus G

当我们可以看到 io线程和sql线程的状态都是yes时,说明此刻主从同步已经搭建完成了。

3.3、从库:如何断开主从#
Copy
stop slave io_thread
stop slave sql_thread
3.4、主库:如何断开G ; H ] K Q E主从#
把用于进行主从同步的账号删除就好了

Copy
drop user ${user}@${slave_ip}

四、中断处理#
中断处理部分说的是,一开始我们搭建主从很可能并不是一番风顺的,就比如上面的Slave_IO_Running和Slave_SQL_Running很可能处于NO的状态。下面介绍一下常见的解决方式。

4._ h * @ I e1、Slave_IO_Running异常#
Slave_IO_Running:no/connecting

这说明从库连接不上主库,或者是一直处于正在连接的状态。

可能是主库没有对从库进行授权,如果已经授权了那么重启一下salve。

另一种原因就是master和slave的mysqld相关配置文件中,配置了相同server_id。

还有可能你在执行change mh S 0aster命令时,输入的主库相关的信息本来就是错误的。

4.2、e ) ; Q * g ;Slave_Sql_Running异常#
Slave_Sql_Running:no

一般这种情况是bin-log中的sql出问题了。

第一种情况:可能我们配置了slave只能读,但是却有写请求打过来了,导致slave不能继续往下执行。

第二种解决思路:让slave跳过有问题的这个事件,但是还是得把事X = Z件的原因查明白,不然不推$ | K v ] 3 :荐直接跳过这个事件。

Copy
sto: Z S 9 ~p slave;
set global sql_slave_skip_counter=1;
start slave;
第三种思路:我们提前配置好错误号机制,当slaveU + + 6在同步的过程中,碰到我们配置的错误号采取自动跳过的机会而不再去默认的终止同步数据。

Copy

一般我们可以像下面这样,在my.cnf中^ N S T = ) u Z的[MySQLd]的启动参数中添加如下内容

--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skP A z W ( .ip-errors=ddl_exist_errors

通过如下语句查看当前MySQL配置的变量

MySQL> show variables li, S v K B Z 6 = dke 'slave_skip%';

通过如下命令可以查看到出现的errornk ? k Q 2 F o 3o

sh# S = + $ qow slave status; # 观察Last_Errno

常见的errorno

1007:数据库已存在,创建数据W M +库失败
1008:数据库不存在,删除数据库失败
1050:数据表已存在,创建数据表失败
1051:数据表不存在,删除数据表失败
1054:字段不存在,或程序文件跟数据库有冲突
1060:字段重复,导x 5 D K致无法插入
1061:重复键名
1068:定义了多个主键
1094:位置线程ID
1146:数据表缺失,请恢复数据库
1053:复制过程中主服务器宕机
1062:主键冲突 Duplicate entryH 0 ( U i '%s' for key %d
第四种思路:手动给slave调整fileNa + C + ] $ame和position的位置(如何o D / V - D 9允许放弃之前的一部分数据,而从当前最新的数据开始同步)

Copy

停掉slave

slave stop

U ` @ r $ g 9 Q入maste? $ { z ; x Pr

停止master的写操作

查看master中当前bin-log和position

show master status;

切换回slave从新根据最新的position和bin-log进行同步

进入master,开启master的写操作

五、流程#
通过fileName和position完成定位,从库会向主库发送_ } $ G 2命令,BINLOG_DUMP,命令中包含有positon和fileN] d Oame, 主库获J F 5 , %取到这些信息之后,指定name到指定position往f D ^ ` O从库发送/ + X D u + dbin-log

六、可能会遇到的问题#
6.1、问题一:#
change master时报错了

报错说:ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MJ 0 l O 5 z b }ASTc S , W m [ER_AUTO_POSITION is active.

原因是我之前使用过gtid进行同步数据,当时将2 X i S z bmaster_auto_position设置成了1,再想使用0 Z u J手动指定position的主从同步方式需要得像下面这样,change回去。

Copy
CHANGE MASTER TO

    MASTER_AUTOL ] x g * I 9_POSITION=0;

6.2、问题二4 b T:#
如果我随便写了个position再搭建主从时,会发生什么?

下面的 MASTER_P 6 WLOG_POS = 1003 就是我随便写的一个position,o ) , 1 r然后你可以看到两个现象

Slave_IO_Ru{ F * F W S ; V PnniY i vng : No
Last_IO_Error^ C | x = ^ 位置报了个严重的错误
Copy
mG @ :ysql> CHANG7 x k H f 9E MASTER TO

->        M, W l % d H YASTER_HOST='10.157.23.xxx',
->         MASTER_USER='mysqlsync',
-[ x - p X X b&! 7 , @ K 4 R b zgt;        MASTER_PASSWORD=; f 8 & [ g X 9 Q'mysqlsync1p m ^ c r ) _ L H23J i y m r s D',
->        MASTER_PO/ ) o d O { *RT=8882,
->        MASTERE y F_LOG_FILE='mysqs c } ) { _ n U -l-bin.000008',] O ; @ 0 - g
->       MASTER_LOG_P* 6 M I 0OS=1003;

Query OK, 0 rows affected, 2 waz [ @ J ] ] z a Mrnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave state f 0 n kusG;
1. row **

           Slave_IO_StateF T f T ( W +:
Master_Host: 10.157.23.158
Mastes & I } Y d  2 $r_User: mysqlsync
Master_Port:] 6 I K 888O D 2 E f ] = B A2
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1003
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Run{ K i Q _ d pning: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_TableF ~ Y @ o z ` t g:
Replicate_Wilds - t P t V b_Do_Table:

Replicate_Wild_Ignore_Table: mysql.%,test.%

               Last_Errno: 0
Last_Error:
Skip_Counte= m ~ q C ( yr: 0
Exec_Master_Log_Pos: 1003
Relay_Log_Space: 521
Until_Condition: None
Until_Log_File:
U T c . w I q m entil_Log_Pos: 0
Master_SSL_Allowed: No
MastZ H Oer_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cer? 5 a V 7 At: No

            Last_IO_E9 1 Y h j e F &rrno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading2 8 q s y + 7 data from binary log: 'log event entry exceed& j  Jed max_allowed_packet; Incr[ f i y  h , c pease max_allowed_packet on master; the first event 'mysql-bin.000008' at 1003, the last event read from './mysql-bin.000008' at 123, the last byte read from './myl 9 Jsql-bin.000008' at 1022U V y u J D * p +.'
Last_SQL_Errno: 0
Last_SQL_Error:

Replicate_Ignore_Server_Ids:

         Master_Server_Id: 2787871625
Master_UUID: a5f1d6b2-8f9au N = u C p y `-11ea-8138-b8599f2ef058
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slas 9 * (ve_SQL_Running_State: Slave ha* G Q es read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 200529 10:22:46
Last_SQ? 9 ?L_Error_TimestamA E c h k / J / =p:
Masterb ]  w L a_SSL_Crl:
Master_SSL_Crlpata g m r + Q d mh:
Retrieved_Gtid_d 4 + 7 {Set:
Executed_Gtid_Set: 00c755a6-7a07-15 F O +1ea-8701-b8599f2ef058:33P { . k Y-222,

40efcb1b-7a1f-11ea-84ac-b8599f229b38:1-20,
7e2dcb21-7d3b-11ea-aa0c-b8599f2ef058:1-18,
9e6m 6 *027f2-7ae9-11ea-ac13-b8599f2ef058:1409-7176,
a5f1d6b2-8. B 9 G J k ( `f9a-11ea-8138-b8599f2ef058:6-9:12-13:15,
e90fdd54-7e04-11ea-8b23-b8599f2ef058:1-11

            Auto_Position: 0
Replicate_S c O e x qRes k Xwrite_DB:
Channel_Name:
Master_TLS_Version:

1 row in set (0.00 sec)
6.3、问题三:#
假设我们有这样的场景:

场景:现在主库有7条数据,从库有5条数据,搭建主从时如何让从库从第六条开始同步?

这种情况仅仅是我们在做这种小实验,为啥这样说呢?如果是T C C _为线上的业务搭建搭建主从MySQL的话,大概率我们会清空主库然后再做同步。如果数据很重要P _ d M #,我们会e } @ Q f C t _ /对主库中的数据进行一次全量拷贝到从库(拷贝var包)。再做主从同步。

在线上的环境中,主从的数据是会强一致的,d o ^ v . T从库I H E H L只会接受业务方的读流量,也许网络环境很恶劣从库同步的速度X C ` E D F明显比主库写入到速度低,但是只要从库没有说跳过了某个binlog而少同步了某条记录,我们都可以认为它们是正常的主从同步。不会出现主从中断的情况。

线上的环境中什么情况下会出现主从中断呢?比如说,从库同步数据时,从库同步binlog时丢了一条数据,这时业务上突然来了条update语句,要更新数据,然l C $ / k . x i后从库美滋滋的回放在主库% 6 X N `dump过来的binlog时发现,竟然自己没有需要更新的p _ + X 0 h 这条记录_ j #,就会报错,这时为了] D D f o 6 s K业务止损,我们要在第一时间下线从库,然后去分析哪里出现问题了。

针对这个实验我们这样去b9 x n i 2 [ % Yinlog中查看第5,6条数据的position,然后在从库中使用相应的position完成主从数据的同步。

进入主库,通过下面的命令查看binlog

Copy
mysqlbinlog --no-defaults -vv --base64-output=decode-rows ../var/mysql-bin.000008 | less

P I ?到了指定的binlog和指定的end_l+ & Jog_pos

比如从库中没有第10,11条数据,我们就能通过end_log_pos = postion = 1013完成定位。

Copy
CH^ - 2 b + fANGE MASTER TO

MASTER_HOST='10.157.23.158',
MASTER_USER='mysqlsync',
MASTER_PASSw 8 nWORD='mysqlsync123',
MASTER_PORT=8882,
MASTER_LOG_FILE='mysql-bi4 2 7 | 1 R Qn.000008',
MASTER_LOG_POS? : Y ? d D=1013;

开启同步,并查看状态

Copy
start slave;
show slave statt X , xusG;
再去查看& * , p ( O j从库就能发现,从你指定的pZ ) P C .osition开始往后和主库的数据保持同步的。

6.4、问题四:#
问:主从接流量的情况是怎样的?业务的C% ^ .RUD请求是如何被主从平) m & t . i ,分消费的?

答:默认这种架构下是读写分离,也就是说,仅读流量会打到从库中

问:那如果我们在从库所在的机器上本地登陆,然后手动执行删除的操作能E n #成功吗?

答:是H Q % T } v y We f . M 1 . E n,可以执行成功。

问:我可以简单粗暴的限制从库仅读吗?

答:可以的,像下面这样

Copy

mysql> show variables like '%read_only%';
Variable_name Value
innodb_read_only OFF
read_only OFF
super_read_only OFF
transaction_read_only OFF
tx_read_only OFF

5 rowsl - 6 r * $ H 0 z in set (0.00 sec)

set global re2 E * l w G E Oad_only=0; #关闭只读,可以读写
set global read_onK O e }ly=1; #开始只读模式
6.5、问题五:#
假设主库中有1~7 共7条数据,从库中有1~5五条数据。也就是说,主库从库中前五条数据一样,但是主库比从库多了两条新数据。

这时我们搭建主从同步时搞一搞事情,重复这个动作:在从库断开同步,D } L ~ I | O 然后查到主库第一个binlog中的数据的记录,确定我们要查找的position,再重新构建主从环境。观察一下从库这边2 / * _ |数据的同步情况,以及会出现什么问题?从库这边的数据会成为double吗?

答:数据不会double的

6.6、问题六:#
假设从库执行changemaster时,主库MASTER_HOST填错了:

在查看slave 状态时,我们可以看到Last_W ^ l s y P ~ O lIO_Error列有报错提示:error connecting to master

Copy
mysql> CHANGE M$ b x L F Z a I @ASTER TO

->     MASTER_HOST='10.157.23.158',
->     MASTER_US1 A 0 ; . l b O 7ER='mysqlsync'~ , . ( J * W,
-&gk R _ gt;     MASTER_PASSWORD='mysqlsync1[ 9 H D * G p2l X l A D3',
->     MASTER_PORT=8882,
->     MASTER_LOG_FILE='mysql-bin.000008',
->     MASTER_LF , 9 H e + w j ~OG_POS=1013;

Query OK, 0 rows affectS q V Q ] 3 4 w `ed, 2 warnings (0.01 sec)

mysql> s+ H 5 5tart slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;
1. row **+ ^ I

           Slave_IO_State: ConnectiM ; P M S / 1 # Rng to master
Master_Host: 10.157.2k B h3.123
MasterE 8 ] { o & [_User: mysqlsync
Master_Port: 8882
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1003
Relay_Log_File: relay-log.000001
ReC z % [ * $ Elay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: ConnX U y ( Q L Q 9 {ecting
Slave_SQL_Running: Yes
Replicate_Dc W Bo_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:

Replicate_Wi& # old_Ignore_J ` # 3 o v ? ,Table: my1 1 a l | i ~ 5sql.%,test.%

               Last_Errno: 0
Last_Error:
Sk/  ] % s Y m =ip_Counter: 0
Exec_Master_Log_Pos: 1003
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_LogA . ) S . N_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behi/ o @ 0 ^ nnd_Master: NULL

Master_SSL_Verify_Server_Cert: No

            Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'mysqlsync@10.15L Q B 9 N C J7.23.123F % r p:8882' - retryL c v y f c B-time: 60  retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:

Replicate_Ignore_Server_Id? / r P * I ` j As:

         Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULLX X g K { @ 6 C
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Ra 0 a E $ Aetry_Count: 86400
MasteN B  Kr_Bind:
La] H T ~ t A w I 3st_IO_Error_Timestamp: 200529 10:13:34
Last_SQL_Error_TimestK u ) u 0 B _amp:
Master_E p T L X k g *SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 00c755a6-7a07-11ea-8701-b8599f2ef058:33-222,

40efc0 ^ F k c 6b1b-7a1f-11ea-84ac-b8599f229b38:1-20,
7e2dcb219 v Q Q O 2 ] l-7d3b-11ea-aa0c-b8599f2ef05_ c p n ; | K u8g J ^ I 5 4 b L c:1-18,
9e6027f2-7ae9-118 l $ea-ac13-b8599f2ef058:1409-7176,
a5f1d6b2-8f9a-11ea-8138-b8599f2ef058:6-9:12-13; 7 u N:15,
e90fdda A + q X ~54-7e04-11ea-8b23-b8599f2ef058:1-11

            Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Nan g p 9 S Ume:
Master_TLS_Versio: G z 3 Nn:

6.7、问题七:#
假设这种场景:假设主从现在的数据& C t c . t是一致的,然后你在d ? 9 g m o从库所在的机器上本地登陆,然b & o I T l f后手g ! I O K动删除一条,再从主库写入数据,那从库还能同步成功吗?

答:从库依然会同步成功,但是其实这时候已经算是事故了,主从数据不一致,万一业务{ Z Z Y k U J 4打来一条sql刚好使用你删的数据,那就会报错。

如果觉得对你有帮助欢迎关注我,后面还会分享通过gtid搭建主从mysql以及其他相关的知识点

作者:赐我白} C U u N f ~ W日梦

出处:https://www.cnblogs.com/ZhuChangwu/p/12990062.html