Comments on: How does MySQL Replication work?

By: Susanta Tewari

Susanta Tewari — Sat, 30 Nov 2019 12:33:50 +0000

In reply to Aurimas Mikalauskas. I have a requirement where I need the master to send bin logs to the slave without slave connecting first. Is this possible?

By: Mohammad Musleh

Mohammad Musleh — Tue, 15 Jan 2019 22:34:38 +0000

We have master-slave replication in place utilizing GTID approach; the use case we have is to configure this replication such that some records that exist in some tables on the master, which meet specific criteria do not get replicated to the salve; is this possible?
Thanks

By: All In One Script

All In One Script — Wed, 23 Aug 2017 06:26:09 +0000

Good Job Aurimas. I, Like It ..

By: John Saran

John Saran — Wed, 23 Aug 2017 06:23:56 +0000

Thank you for explain replication

By: Martin Streicher

Martin Streicher — Fri, 30 Jun 2017 17:37:56 +0000

I have a database table with a BINARY(16) column that has a UNIQUE index. It seems to take forever to insert into this table when replication among the clusters is enabled. Any ideas?

By: Shailesh

Shailesh — Tue, 19 Jan 2016 09:16:24 +0000

I was dropped 1 table on production server yesterday. but that time I have backup till last day so that I was restored but for yesterdays data i have to refer bin-log file .
My table used now() function for insertion of date-time column, so my question is all expertise is how to restore that now() function in current date with yesterdays date & time.
I am trying to insert the exact value of column datetime when that
data was created.
for Additionally binlog file has statement like this :
#160115 10:15:14
INSERT INTO TABLEA ( id, col2,col3, datetime) values
(1,value2,valu3,now());
#160115 10:15:24
INSERT INTO TABLEA ( id, col2,col3, datetime) values
(1,value2,valu3,now());
#160115 10:25:10
INSERT INTO TABLEA ( id, col2,col3, datetime) values
(1,value2,valu3,now());
I am having just starting my Administration work. so please help me.
Thanks in advance .

By: Afox

Afox — Thu, 14 Jan 2016 04:09:57 +0000

Hello,

is it possible to configure the replication only as “backup” solution with no reads on the slave?

Thanks in advance for your answer!

Afox

By: MySQL

MySQL — Tue, 03 Nov 2015 08:56:10 +0000

Hi,Aurimas. In your article, you said that 「This process called IO thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. That’s all」.

But from MySQL offical documentation,it said that(https://dev.mysql.com/doc/refman/5.5/en/master-thread-states.html)

8.14.5 Replication Master Thread States

● Sending binlog event to slave

Binary logs consist of events, where an event is usually an update plus some other information. The thread has read an event from the binary log and is now sending it to the slave.

● Master has sent all binlog to slave; waiting for binlog to be updated

The thread has read all outstanding updates from the binary logs and sent them to the slave. The thread is now idle, waiting for new events to appear in the binary log resulting from new updates occurring on the master.

So, my question is that how the binary logs transferred to the slave?

By: Aurimas Mikalauskas

Aurimas Mikalauskas — Thu, 13 Aug 2015 13:22:10 +0000

Jove,

sorry for a slow response. Essentially, it is possible, but the key is to use –master-data when exporting the data with mysqldump. And for that you have to make a consistent backup which means either using –lock-all-tables (which I think is used by default if you’re using –master-data) or –single-transaction if all your tables are transactional.

Consider using mydumper which will make both export and import faster, assuming you have enough CPU cores and IO capacity.

Aurimas

By: Jove D.

Jove D. — Thu, 30 Jul 2015 01:50:45 +0000

Hi,

I have question on replication of DB. Is it possible to perform replication using the old DB dump? Is it going to re-sync on Master?

The reason I ask because we have 1 MySQL DB (around 160GB size and this DB is updating every seconds) and performing DB Dump is fast but the problem is the restoration, it took around 15hrs to finish.

Is it possible?

Thanks in advance. 🙂

By: Manu

Manu — Mon, 20 Jul 2015 08:51:36 +0000

Thank you Aurimas.

We already have a geographically separated setup but we are facing issues because of network latency.
I just wanted to make sure about certain other things. Your answer helped a lot, so its not incomplete in that way.

Kind regards,
Manu

By: Aurimas Mikalauskas

Aurimas Mikalauskas — Mon, 15 Jun 2015 07:59:16 +0000

Hi, Manu

Thanks for the question. I don’t think it’s very complete though as it depends really on what you’re trying to achieve with geo separated servers. Also, depending on the amount of write workload, WAN may become a bottle-neck for the replication.

While replication does work just fine over distance, it will not be synchronous normally. And it’s definitely not going to work both ways unless you write to different schemas or at the very least use offset for auto increment (doesn’t help in all cases btw).

I know my answer is incomplete, but so is the question 🙂 It’s not an easy design decision to make. A lot of variables to consider. And also a lot of alternative approaches depending on what the goals are.

Hope that helps.

By: Manu Swami

Manu Swami — Fri, 12 Jun 2015 07:14:52 +0000

Hello Aurimas,

Thanks for this article.

I have one query related to mysql replication. Is it recommended to have a MySQL replication when both the master and slave are geographically separated. And that too when the replication is both way.

Kind regards,
Manu

By: Aurimas Mikalauskas

Aurimas Mikalauskas — Thu, 30 Apr 2015 10:16:55 +0000

Viji,

I don’t think I fully understand your question. But. Binary logs are normally not overwritten – when the master server starts writing binary logs, it will write to binlog.000001, then binlog.000002 and so on.

However, if you have expire_logs_days not set to 0, then binary logs that are more than X days old will be automatically removed upon log rotation. So if your server is now writing binlog.001000, expire_logs_days=30 and binlog.000500 was closed 31 days ago, then all logs prior to binlog.000500 will be removed once server starts writing binlog.001001.

Hope that answers your question. Basically, binary logs are not written in a circular fashion like transaction logs are.

By: Viji

Viji — Wed, 29 Apr 2015 09:00:34 +0000

Thanks for your nice article. I have a doubt. Whether the bin log file every time clear and update the new data or it will have the data from the scratch ie. when we start replication

By: Gaurav

Gaurav — Wed, 05 Nov 2014 06:03:30 +0000

Awesome article .
Just a small doubt , does replication also exists for lmdb ? since i am using py-lmdb as an interface to lmdb , do i need to write protocols and binlog methods , connection methods .

Thanks

By: Aurimas Mikalauskas

Aurimas Mikalauskas — Tue, 28 Oct 2014 13:53:26 +0000

Vadiraj - thanks for the question. If you mean active-active master-master setup, chances for the conflicts are great, unless you running something like Galera which takes entirelly different approach. Active-active master-master is otherwise something we do not recommend and if you mean my article about MMM back in the day, it talks about active-passive setup, not active-active.

By: vadiraj

vadiraj — Tue, 28 Oct 2014 10:05:33 +0000

Sir, Please help me to understand.
I have one question related to master-master replication concept….might be I didn’t understand the concept correctly.
Both mysql systems having bin log,relay log and logically how it is not going to get conflict replication/data in M1M2 based on the concept which i read from your article.
Thank you very much for your help.
Vadiraj

By: a

Fri, 19 Sep 2014 12:01:43 +0000

In few words, you explained to me what I was lookig for.
Thanks!

By: Tanuj

Tanuj — Thu, 28 Aug 2014 14:40:48 +0000

Thank you for writing this brief overview.