版权声明：本文为博主原创文章，转载请注明出处：https://twocups.cn/index.php/2021/07/02/44/
gtid主从不一致情况分类
- 从库gtid多于主库（一般是从库开启了写操作）
- 从库gtid少于主库（一般是发生了宕机）
- 从库上没有全部的主库已经删除的gtid
gtid主从不一致情况扫描脚本逻辑
我写了一个 gtid 主从不一致扫描脚本，运行在集群管控节点上，每天扫描一下集群内的 gtid 异常情况。企业实际环境中不是很推荐像我一样用 shell 脚本去写，因为太依赖机器环境了。最好使用 Go 之类的语言去写，而且 Go 本身也有很多封装好的库，写起来会更方便。并且，用 Go 写之后还可以和公司内部的一些巡检或监视平台配合。
我说一下脚本的扫描逻辑。首先，在集群管控节点上搜索集群中所有的 mysql 实例 id，以及主从库的 ip 地址和端口，然后对每个实例运行以下扫描逻辑。
1. 前置检查
- 检查gtid模式是否开启
- 检查数据库类型
首先，我们要检查gtid是否开启，如果没有开启就直接报错，并结束这个实例的扫描。
2. 采集gtid
- 采集从库gtid和uuid
- 停顿
- 采集主库gtid和uuid
采集从库 gtid 一定要在主库前面，从而保证主库的gtid比从库更新，否则可能会出现从库gtid跑在主库前面的奇怪情况。因为我们集群比较大，所以向主从库请求gtid会有一定程度的延迟，还是会有可能引发从库gtid比主库更新的情况，所以我还是在中间停顿了0.1s，保证了主库gtid一定比从库的更新。
3. 查找多的gtid
- 采集从库比主库多的gtid
- 第一次采集主库比从库多的gtid
- 停顿
- 第二次采集主库比从库多的gtid
- 根据两次采集的结果，计算出主库比从库多的gtid中异常的那部分
- 分析主库比从库多的gtid
- 分析从库比主库多的gtid
看到这个逻辑，一定会想到一个问题：为什么采集从库比主库多的 gtid 只需要采集一次，而采集主库比从库多的 gtid 要采集两次呢？
从库比主库多的 gtid 只需要采集一次是因为，从库的 gtid 本身就不应该比主库多，从库的 gtid 应该和主库是一样的，或者因为延迟，主库的 gtid 比主库稍微慢一些。如果从库的 gtid 比主库多，那么多出来的那部分一定是有问题的 gtid。
但是主库 gtid 比从库多是正常的，因为我们是先采集从库再采集主库，这就导致主库的 gtid 比从库的更新。在我们采集两者的间隔，主库也可能在源源不断地产生新的 gtid，所以主库的 gtid 会比从库更多。那我们可以先采集主库再采集从库吗？那更不行，否则你就会看到从库的 gtid 跑到主库前面去，这更离谱。
所以，我们需要采集两次主库比从库多出的 gtid，并且在两次采集中加一个时间间隔，目的是区分出所有的主库正在增长的 gtid。这里我们用到了 mysql 提供的一个关于 gtid 的函数。
select gtid_subtract('A', 'B')
这个函数的作用是求出 gtid 集合 A 与 gtid 集合 B 的差值，具体来说，是求出 A 集合中有、而 B 集合中没有的 gtid。换言之，这个函数可以在 A 集合中去掉 A 和 B 集合相同的 gtid。
好消息是，这个函数非常方便。如果没有这个函数，我们就需要去比较 gtid 的字符串了，非常麻烦。
坏消息是，关于 gtid 的函数，mysql 就提供了这么一个。我们能用的也只有它。
我们通过连续使用两次这个函数，就可以得到两次采集的主库比从库多的 gtid 结果中重合的那一部分，这就是主库 gtid 比从库多的 gtid 中异常的那部分。
这里其实存在一个问题，那就是两次采集重合的部分，不一定是异常的部分，还可能是正在正常增长的 gtid。所以我们采集两次的时间间隔可以长一些，我自己设置的是0.5s。如果两次采集的部分中仍然出现了正常增长的 gtid，那么说明主从库存在延迟大的问题。检测主从库延迟是否过大，也正是我们设置时间间隔的第二个原因。
4. 查找主库中已经删除但从库中未出现过的gtid
- 采集从库接收到但未执行的 gtid + 从库已执行的 gtid（两者是有重合的）
- 分离出从库收到但未执行的 gtid
- 采集从库已删除的 gtid
- 采集主库已删除的 gtid
- 从主库已删除的 gtid 中依次过滤掉从库中出现过的 gtid
我没找到单独采集从库接收到但未执行的 gtid 的方法，我只能从 mysql 指令“show slave status”里面的参数“Retrieved_Gtid_Set”里面得知该数据。但这个参数又不能单独采集，所以我只能连它和“Executed_Gtid_Set”一起采集了。但是它们俩的 gtid 一般来说都是有重合的，不能直接使用函数 gtid_subtract。
我又观察到它们集合中的 gtid 之间除了逗号还多有一个空格，并且“Retrieved_Gtid_Set”的值和“Executed_Gtid_Set”的值两段数据之间没有逗号只有空格。那我就可以通过处理字符串的方法，将从库接收到但未执行的 gtid 分离出来。
All in all, everyone is different and are suited to different environments. Production and mining was great experience and something that has launched my career. I would definitely recommend that all engineers get this exposure a an early stage in their careers. However, consulting is very exciting and you get broader exposure to the industry as a whole. Some people start off in consulting and never leave. It may be stressful, but if you are a very driven individual who values professionalism and detail, I would recommend it to you! Todd Edeline
Zo herkenbaar. Mijn dochter zit op het Speciaal onderwijs. Maar ik vraag me steeds vaker af wat er speciaal aan is. Natuurlijk zit ze in een klein klasje, heeft ze miet voor iedere les een andere leraar en er is veel aandacht voor haar. Maar dat schoolse presteren blijft. En de manier van lesgeven ook. Het is duidelijk dat Nikki een beelddenker is en talige informatie lastig verwerkt. Intelligentie is er voldoende. Maar dat past niet in het de manier van lesgeven. En de juf doet echt haar best. Maar passend onderwijs kan ik het niet noemen helaas. Roscoe Stockebrand
You’re truly a just right webmaster. This website loading pace is incredible.
It kind of feels that you are doing any distinctive trick.
Moreover, the contents are masterwork. you have done a great process in this matter!
Similar here: bezpieczne zakupy and also here: Sklep internetowy
Hey there! Do you know if they make any plugins to assist with SEO?
I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very
good success. If you know of any please share. Kudos!
You can read similar art here: Ecommerce
Hello! Do you know if they make any plugins to assist with Search Engine
Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not
seeing very good gains. If you know of any please share.
Thanks! You can read similar art here: Ecommerce
Howdy! Do you know if they make any plugins to assist with
Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m
not seeing very good gains. If you know of any please share.
Kudos! You can read similar article here: Ecommerce
I initially learned about this product on the mothering.com forums and put the purchase off for awhile. Recently I happened to go to a boutique store and my daughter, 2.5, jumped right on the tricycle and started moving it right away. Anything my daughter loves, I love, so I ordered it right away and I am confident it will last much longer than any other balance bike. Joshua Panos
Hi there! Do you know if they make any plugins to help with SEO?
I’m trying to get my website to rank for some targeted keywords but I’m not seeing very good success.
If you know of any please share. Cheers! I saw similar text here: Auto Approve List
Si on ecoute france info ou france inter ce matin, le petrole coule a flot et y en a jamais eu autant. Cela montre le niveau lamentable des medias et la stupidite crasse dans laquelle sont maintenu nos concitoyens. Pas etonnant qu ils adoubent des politiques suicidaires. La connerie est un cercle vicieux. Earl Forget
For many casinos are a associated with glitz and glamour that is any apart from their normal lives. High stakes, Gambling, finest Slot Machines, Roulette Tables and more are the normal features of an internet casino. Many people go using a casino because they like the glamour and also the gambling, however, there are lots of who love november 23 at Slot Machines but hate the bang and din of the on line casinos. For such people the casino can be brought home by appealing to the Mu Mu World Skill Stop Slot Machine. This machine is a genuine casino product which has been refurbished which allows you to be purchased for any home use. Brendon Mansouri
More and more websites are competing on the World Wide Web to give helpful article posts about wedding dresses. Well, this site is probably one of the good ones everyone could visit. I got the idea of how to find the right tuxedo for my wedding after I read this post. I got what I need not only for my attire but also to my groomsmen and best man. Every one of us at our wedding looked awesome. I will not hesitate to recommend your site to my other friends who may need tips regarding tuxedos for any occasions. Rufus Hogstrum
Salvia esse nihil, flexitarian Truffaut synth art party deep v chillwave. Seitan High Life reprehenderit consectetur cupidatat kogi. Et leggings fanny pack, elit bespoke vinyl art party Pitchfork selfies master cleanse Kickstarter seitan retro. Drinking vinegar stumptown yr pop-up artisan sunt. Deep v cliche lomo biodiesel Neutra selfies. Deshawn Modisette
Wow, amazing blog structure! How long have you been running a blog for?
you made blogging look easy. The total glance of
your web site is excellent, let alone the content!
You can see similar here najlepszy sklep
avis achat viagra ligne|propecia viagra online|viagra suppliers canadian|generico de viagra preco|viagra shop in tanzania|buying viagra online safety|viagra rezeptfrei lander|viagra pills lowest price|costo viagra ricetta|cost of viagra in canada|achat viagra en ligne canada|viagra legal australia|viagra 100 mg generico|viagra vorzeitiger samenerguss|viagra brazil generic|only here india viagra|can you buy viagra shops uk|viagra pfizer werbung|only here buy now viagra|pilule de viagra le ru|cheap viagra with out|spyware buy viagra dose|viagra tunisie prix|purchase viagra cheap|viagra pour homme prix maroc|viagra salesman movie|wat doet viagra prices|viagra kaufen sho|something better than viagra|free viagra sample pack in uk|viagra soft cheap uk pharmacy|viagra cialis levitra buy|nome generico do viagra|generic viagra as effective|review online viagra sites|viagra preise apothek|viagra with food|viagra online zoll|viagra canadian store|viagra super active best value|viagra how to order|viagra fur frauen bestellen|generic drugs like viagra|prix viagra sans ordonnance|probepackung viagra levitra|viagra spray for sale|deutsche viagra bestellen|acheter viagra rapidemen|professional viagra buy|only today order viagra canada|viagra e vasodilatatori|australian cost of viagra|follow link buying viagra|viagra online billig bestellen|vente viagra sans ordonnanc|viagra cialis vendit|viagra genrico mexicano|es buena la viagra generica|order viagra no prescripion|click here buy viagra now|viagra online prescription uk|viagra prescription online|1 buy viagra|viagra sale shipped canada|viagra kaufen europa|viagra legal buy line|acheter viagra indien|viagra zonder|comprar pastillas viagra|top viagra store online|pfizer viagra from canada|viagra in contrassegno|costo del canada viagra|viagra online wholesale|verordnung fr viagra|viagra le generique|generic uk viagra|viagra daily medication|viagra gold generique 800m|forum viagra pour fille|viagra cheap buy generic|viagra bestellen erfahrungen|viagra kopen niet online|viagra price war|comprare viagra yaho|the crisis viagra for free|herbe viagra|viagra originale on line|viagra sildenafil mastercard|viagra al miglior prezz|i want to buy pfizer viagra|viagra online irrational|where can i get free viagra|can one buy viagra online|generique viagra super active|only for you viagra soft|achat viagra ligne quebec|cheap viagra women uk|viagra pro and cons|by real viagra|viagra full stomach|viagra 16 anni|viagra where generic i order|buy viagra online canada pills|wow cheap viagra online uk|buying viagra in bullk|only now use viagra|buy viagra in finland|try it discount viagra no rx|cheap viagra pills us|viagra cialis manila|when generic viagra will be|precio viagra costa rica|where i can get viagra tablets|viagra sales in usa|receta de viagra|achat viagra cialis en lign|viagra find edinburgh|with generic canada viagra|viagra brevetto 2013|prix vrai viagra|faux viagra au quebec|viagra online acquisto|viagra online next day uk|buy viagra in cagayan de oro|viagra price ksa|prezzo di prezzo di viagra|cheap viagra with p|approved viagra store|buy viagra online canada free|cheap viagra uk for sale|viagra pill for sale uk|viagra preis 100mg generica|venta de viagra en barcelona|pills 100 mg viagra|viagra uk best price|viagra shop in singapore|viagra in stores vancouver|a viagra get prescription|viagra tablette|click now viagra cheap price|viagra precios farmacia|buy viagra online au|need prescription viagra|viagra wirkung levitra|cost of a 100mg viagra tablet|pill viagra|viagra oral jelly 100mg|viagra plus no prescription|viagra billig online kaufen|buy viagra buy pills online|cheap herbal viagra uk|viagra with fast shipping|safest place order viagra|viagra rezeptfrei erfahrung|buy viagra in cairns|low cost alternative to viagra|coreg viagra|viagra buy propecia|viagra uso istruzion|how can i buy some viagra|vente en ligne de viagra|venta cialis viagra|we like it china viagra|viagra simbabwe|best price for 50mg viagra|visit our site viagra shop|espaa madrid viagra|viagra gnstig ohne rezept|discount viagra soft online|achetez le canada viagra|viagra plus no prrescription|expextations of viagra|viagra online legally|pfizer viagra price canada|only today generic viagra cost|look here buy viagra in europe|in co denver viagra|i use it cheap buy viagra|effetto viagra sulle donn|viagra rebate|viagra cialis free offer|viagra cialis argentin|only here buy internet viagra|viagra 10 pills cheap|viagra taken evry day is ok|discount viagra sale online|viagra samples from pharmacy|comprar viagras|viagra comprar madrid|pfizer viagra pharmacy|generic viagra pills tadalafil|u s pharmacy viagra online|adresse pour acheter viagra|viagra venta online|cheap viagra or sale|viagra 50 ou 100 m|generic propecia free viagra|viagra canad entrega|cheap viagra uk|mexican pharmacy viagra|r1000eady tabs viagra|viagra y drogas|look here viagra paypal|men viagra|pfizer viagra otc|pfizer viagra price increase|generic viagra at walmart|prix viagra gnrique le ru|viagra deutschland import|viagra nose bleed|cheap viagra professional uk|viagra shopper|viagra generic 2012 drugs|efectos colaterales del viagra|ordering viagra online illegal|cost of viagra 10mg|viagra argentina precio|places to get viagra|site achat viagra serieu|viagra richtig verwenden|viagra counterfeit|viagra commander|viagra dealers in japanese|has viagra come down in price| discount viagra europe|viagra red face|is viagra safe to use|safety buying viagra online|cialis mas viagra|generic viagra us|viagra price in rupees|comprar viagra temuco|viagra with pay paypal|webmd viagra|viagra online scams uk|viagra price cialis price|cost viagra lloyds|where do you buy liquid viagra|sale online viagra ie|cialis viagra levitra kaufen|compare prices cheapest viagra|how to use viagra bill|kosten viagra pille|viagra buy can i online safely|viagra kaufen in tschechien|generic soft viagra sildenafil|pacco campione viagra libero|viagra original ohne rezept|quebec pharmacy viagra Jarred Bachmeyer
Suscipit fuga quod unde molestias accusantium praesentium officia asperiores, ipsa itaque. Doloribus ipsum neque nihil magni labore. Voluptates at, temporibus quam eos iure ullam lorem ipsum dolor sit amet, consectetur adipisicing elit. Magnam modi laudantium, autem, quasi eligendi possimus suscipit fuga quod unde molestias accusantium praesentium officia asperiores, ipsa itaque. Doloribus ipsum neque nihil magni labore. Voluptates at, temporibus quam eos iure ullam lorem ipsum dolor sit amet, consectetur adipisicing elit. Eaque repellendus dolores nemo deleniti quaerat dicta delectus odit a perspiciatis similique necessitatibus, reiciendis possimus fugiat dolorem! Diego Amelang
Wow, amazing blog layout!
How long have you ever been blogging for? you make running a blog look easy.
The overall look of your web site is fantastic, let alone the content material!
You can see similar here prev
next and that was
wrote by Joey02.
Wow, amazing weblog layout!
How lengthy have you been running a blog for?
you made running a blog glance easy. The total glance of your site
is excellent, let alone the content! You can read similar here prev next and it’s was wrote by Chia96.
Wow, fantastic weblog format!
How long have you been blogging for? you make running a
blog glance easy. The total glance of your site is excellent,
as smartly as the content! You can see similar here prev
next and that was
wrote by Jeffery66.
Wow, awesome weblog structure!
How lengthy have you ever been running a blog for?
you make running a blog look easy. The overall glance of
your web site is wonderful, let alone the content! I read similar here
prev next
and those was wrote by Mohammed84.