Mysql主从同步配置

Mysql主从同步配置

主从同步介绍

Mysql主从同步是指当主数据库(Master)节点数据发生更改后,能够快速将这种改变同步到其他从数据库(slave)节点中。由于Mysq的同步功能是Mysql内部基于事件通知机制,具有更高性能,更高可靠性。非常适合负载较大的网站,下面这个图片是Mysql官方提供的应用场景。

image1

主从同步是如何工作的?

从总体来说,有3个步骤:

  1. master将改变记录到二进制日志(binary log)中,并且通知slave开始开始拷贝;
  2. slave将master的二进制日志拷贝到它的中继日志(relay log);
  3. slave重做中继日志中的事件,将改变反映它自己的数据。

下图描述了复制的过程:

image2

主从同步需要一个主数据库和至少一个从数据库,下面就如何配置做一个说明。

主数据库(master)配置

主数据库配置较为简单,流程如下:

  1. 在配置文件[mysqld]下,开启Mysql二进制日志(log-bin = mysql-bin)
  2. 在配置文件[mysqld]下,设置服务器ID,一般情况下master设置为1,且所有服务器ID不可重复 (server-id= 1)

image3

  1. 添加用于同步的用户(可选,一般都是添加专用用户)

grant replication slave on *.* to slaveuser@’%’ identified by ‘123456’ ;

image4

完成上面配置后,就完成了master节点配置,通过执行下面命令查看master状态:

show master status ;

image5

如果看到这个,说明主库已经开始正确产生日志了。

从数据库(slave)配置

从数据库配置流程如下:

  1. 在配置文件[mysqld]下,设置服务器ID,一般情况下从服务区都设置2~2^32之间的数值;注意在整个主从同步服务器中,不能存在服务器ID相同的配置。

image6

  1. 设置master信息。

master_host,master_port,master_user,master_password这些信息是从库链接主库需要使用的信息,在此不再赘述。

master_log_file,master_log_pos表示你需要从那个日志文件以及位置开始同步,当前最新信息可以通过在主库执行show master status命令查询。

change master to master_host = ‘192.168.0.159’, master_port = 3306, master_user = ‘slaveuser’, master_password = ‘123456’,master_log_file = ‘mysql-bin.000011’,master_log_pos = 651;

image7

开始同步,并检查是否正确同步

Start slave ;

Show slave status\G;

image8

当我们开到Slave_IO_Running和Slave_SQL_Running均为YES是说明一切都正常了。

如果存在任何错误,错误信息会现在Last_IO_Error或者Last_SQL_Error,我们需要更具这儿提供的错误信息进行手工处理,如果是IO_Error,系统会定时自动重试,如果是SQL_Error,则系统会终止日志回放,需要人工处理错误后,重新开始同步。

image9

以上就是主从所有配置,我们现在在主库进行任何更改,从库都会自动同步。

注意事项

一般情况下我们只在主库进行写入,在从库读取信息,如果不小心直接更改了从库,可能会导致执行主库同步过来的数据时报错,导致同步被中断。当程序出错时,需要查看当前状态和错误信息,然后根据错误信手工处理错误,继续开始同步。下面是几个常用的sql语句:

SHOW SLAVE STATUS\G; //查看同步状态重要看错误信息,手工处理错误信息,然后重新开始同步

STOP SLAVE;  //停止同步

START SLAVE;  //开始同步

 

dasha

95条评论

UsenoCodo 发布于下午9:43 - 2021年6月14日

comprar cialis online

UsenoCodo 发布于下午5:54 - 2021年6月13日

dapoxetine

Desiree 发布于下午12:21 - 2021年5月26日

Magnificent beat ! I would like to apprentice whilst you amend your site, how can i subscribe for a weblog website?
The account aided me a applicable deal. I were tiny bit acquainted of this your broadcast provided vibrant transparent concept

my web site – home love138 download

Alisia 发布于上午5:54 - 2021年5月26日

I was able to find good info from your blog articles.

Here is my webpage aaa1188 agent

Damien 发布于上午2:22 - 2021年5月26日

Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to more added agreeable from you! However, how can we communicate?

my webpage – mega88 test id

Leonida 发布于上午12:33 - 2021年5月26日

Great beat ! I would like to apprentice while you amend your site, how can i subscribe for a blog site?
The account aided me a applicable deal. I had been tiny bit familiar of this your broadcast provided bright transparent concept

Visit my site … download slot game epicwin (https://918kiss-m.com)

Ursula 发布于下午11:20 - 2021年5月25日

Hello, I do believe your web site could possibly be having browser compatibility issues.
When I look at your website in Safari, it looks fine
however when opening in I.E., it’s got some overlapping issues.
I merely wanted to give you a quick heads up!
Besides that, fantastic blog!

Here is my page: apk xe88 (https://918kiss-m.com/blog/winning-strategy-for-online-gambling)

Alysa 发布于下午11:18 - 2021年5月25日

I am not sure where you’re getting your information, however good topic.
I must spend some time finding out more or figuring out more.
Thank you for excellent information I was looking for this info for my mission.

my web-site – rollex11 download

Mirta 发布于下午8:40 - 2021年5月25日

Wow, amazing blog layout! How long have you been blogging
for? you made blogging look easy. The overall
look of your site is great, let alone the content!

Here is my website – 918Kiss Plus company

Nathan 发布于下午8:13 - 2021年5月25日

Thanks in support of sharing such a good thought, post is fastidious,
thats why i have read it entirely

Feel free to visit my homepage; id test ace333

Lieselotte 发布于下午12:35 - 2021年5月25日

I am really impressed with your writing skills and also with the layout on your weblog.
Is this a paid theme or did you customize it yourself? Either way keep up the nice quality writing, it’s rare to see a nice blog like this one today.

Feel free to surf to my web page slot game 918kiss 2 (xe88-app.com)

Brandon 发布于下午12:31 - 2021年5月25日

Useful information. Lucky me I discovered your web site unintentionally, and I am stunned why
this twist of fate did not happened in advance! I
bookmarked it.

my website ntc33 android download

Annmarie 发布于上午6:37 - 2021年5月25日

I was able to find good advice from your content.

Have a look at my site live22 download link

Kraig 发布于上午3:35 - 2021年5月23日

It’s hard to find educated people about this subject, but you seem like you know what you’re talking about!
Thanks

my page jackpot sky777

Dillon 发布于下午11:18 - 2021年5月22日

Hey! Do you know if they make any plugins to protect against hackers?
I’m kinda paranoid about losing everything I’ve worked hard on. Any recommendations?

Feel free to surf to my site; calibet ios (https://918kiss-m.com)

Valentin 发布于下午9:04 - 2021年5月22日

Greate pieces. Keep posting such kind of info on your site.

Im really impressed by your site.
Hey there, You have done an excellent job. I’ll definitely digg it and in my
opinion suggest to my friends. I’m confident they’ll be
benefited from this website.

Feel free to visit my blog: slot greatwall99 (Rosaria)

Kristi 发布于下午6:30 - 2021年5月22日

Howdy, i read your blog occasionally and i own a similar one and i was just curious if you get a lot of spam remarks?
If so how do you reduce it, any plugin or anything you can advise?
I get so much lately it’s driving me mad so any support is very much appreciated.

my page ok388 demo id

Edison 发布于上午5:19 - 2021年5月22日

Hi, yes this post is actually fastidious and I
have learned lot of things from it about blogging.
thanks.

Also visit my web-site – wm casino ios

Dick 发布于上午1:17 - 2021年5月22日

Great beat ! I wish to apprentice even as you amend your web site, how can i subscribe for a weblog
website? The account aided me a applicable deal.
I have been a little bit familiar of this your broadcast provided shiny clear concept

Visit my web page; jackpot wukong333

Armand 发布于下午11:51 - 2021年5月21日

This text is priceless. When can I find out more?

My web site :: game lpe88 download

Fredric 发布于下午10:44 - 2021年5月21日

It is not my first time to pay a quick visit this site, i am visiting this
web page dailly and obtain good data from here everyday.

Look into my webpage: club suncity website

Amanda 发布于下午1:56 - 2021年5月21日

An outstanding share! I have just forwarded this onto a
coworker who had been doing a little research on this.

And he actually ordered me breakfast because
I discovered it for him… lol. So let me reword this….
Thank YOU for the meal!! But yeah, thanx for spending time to talk about this issue here on your web page.

Also visit my page; i sky1388

Therese 发布于下午10:54 - 2021年5月20日

Hey just wanted to give you a quick heads up.
The words in your post seem to be running off the screen in Internet explorer.
I’m not sure if this is a formatting issue or something to do with internet browser compatibility but I thought I’d
post to let you know. The layout look great though!
Hope you get the issue resolved soon. Cheers

Take a look at my web site :: download game 3win8

Jonathon 发布于下午6:35 - 2021年5月20日

Hi there! Someone in my Facebook group shared this website with us so I came to check it out.
I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers!

Terrific blog and outstanding style and design.

My website … home 918kaya download

Simon 发布于下午4:22 - 2021年5月20日

This is my first time visit at here and i am truly happy to read everthing at single place.

my web page; xe88hack (pussy888sg.com)

Felisha 发布于上午11:19 - 2021年5月20日

That is a really good tip especially to those new to the blogosphere.
Brief but very accurate information… Many thanks for
sharing this one. A must read article!

My web blog Download Game King855

Esperanza 发布于上午10:42 - 2021年5月20日

I used to be able to find good information from your blog articles.

Also visit my web page … agent Pussy888

Elizabet 发布于上午7:01 - 2021年5月20日

I read this paragraph fully on the topic of the resemblance of most up-to-date and earlier
technologies, it’s amazing article.

Feel free to visit my web-site joker128 mobile

Katia 发布于上午4:17 - 2021年5月20日

Have you ever thought about publishing an e-book or guest authoring
on other websites? I have a blog centered on the same ideas you discuss and would love to
have you share some stories/information. I
know my subscribers would value your work. If you are even remotely interested,
feel free to shoot me an e mail.

My page; lionking888 download ios

Jerald 发布于下午12:51 - 2021年5月19日

Very good information. Lucky me I discovered your website by chance (stumbleupon).
I have book-marked it for later!

My blog :: playboy2apk

Jacqueline 发布于下午4:01 - 2021年4月20日

Good replies in return of this question with genuine arguments and explaining
all about that.

Also visit my web page – http://dysonvacuumdc24.com/index.php?action=profile;u=6513

Zoila 发布于上午1:47 - 2021年4月20日

I don’t even understand how I finished up here, however I
assumed this submit was good. I do not know who you’re however certainly you’re going to a well-known blogger
if you are not already 😉 Cheers!

Take a look at my blog post litdevelopments.com

Jamika 发布于上午2:01 - 2021年4月10日

Saya tidak bisa menahan diri dari berkomentar. Baik ditulis!

Here is my web-site … Joker123 download apk joker123 android

Mia 发布于上午12:39 - 2021年4月10日

Helpful info. Fortunate me I found your website accidentally, and I am surprised why this accident
didn’t took place earlier! I bookmarked it.

Here is my page … Monika

Jonnie 发布于下午10:17 - 2021年4月9日

What’s Taking place i am new to this, I stumbled upon this I’ve discovered
It absolutely useful and it has helped me out loads. I hope to contribute & help
different users like its aided me. Good job.

Here is my blog post; Colette

Noemi 发布于下午9:48 - 2021年4月9日

Nice blog! Is your theme custom made or did you download it from somewhere?
A design like yours with a few simple adjustements would really make my blog shine.
Please let me know where you got your design. Thanks a lot

my blog :: Jaunita

Darlene 发布于下午6:49 - 2021年4月9日

I am sure this article has touched all the internet visitors, its really really pleasant article on building up new blog.

my web blog informasi poker online

Chun 发布于下午6:12 - 2021年4月9日

Excellent web site. A lot of helpful information here.
I’m sending it to some pals ans also sharing in delicious.

And certainly, thanks to your effort!

Feel free to visit my web site – Teena

Evelyne 发布于下午3:22 - 2021年4月9日

What’s up to every , for the reason that I am genuinely eager of reading
this weblog’s post to be updated daily. It includes fastidious information.

Look into my web blog; Michelle

Colette 发布于上午11:56 - 2021年4月9日

Hello very nice site!! Guy .. Excellent .. Wonderful .. I’ll bookmark your
blog and take the feeds additionally? I’m satisfied to search out a lot
of helpful info right here in the put up, we need work out extra techniques
on this regard, thank you for sharing. . . . . .

Also visit my page Irish

Lilla 发布于上午11:39 - 2021年4月9日

Greetings! I’ve been reading your web site for some time now and finally
got the bravery to go ahead and give you a shout out from Dallas
Tx! Just wanted to mention keep up the excellent work!

My web blog: Victoria

Hilton 发布于上午11:37 - 2021年4月9日

Good way of describing, and pleasant paragraph
to get data on the topic of my presentation topic,
which i am going to convey in institution of higher education.

Feel free to visit my website – Chandra

Brooke 发布于上午10:10 - 2021年4月9日

Its like you read my mind! You seem to know so
much about this, like you wrote the e-book in it or something.
I feel that you can do with some percent to pressure
the message house a little bit, however other than that, that is great blog.
A fantastic read. I’ll definitely be back.

my web site :: Ervin

Katherine 发布于上午7:44 - 2021年4月9日

Great ѕite. Plenty of helpful info here. I am sending it to several pals ans additionally shaгing in delicious.
Αnd certaіnly, thank yoս too your sweat!

my web-site; Holllywoߋd star trivia; drakonas.wip.lt,

Lillie 发布于上午6:57 - 2021年4月9日

If sme one needs expert vіew regarding bⅼogging tһen i
recommend һim/her to vizit this website, Keep up the good work.

Aⅼso visit my web site … celebrity biography documentaries

发表评论