master-slave replication in MySQL

487 views

Looking for some help configuring 5.0.45 master-slave replication. Here's my scenario...

We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic.

I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for the duration of the 5.5 hour dump. Is this true?

If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need?

 mysqldump --single-transaction --master-data --all-databases

posted Aug 28, 2013 by Sanketi Garg

Share this question

Facebook Share Button

Twitter Share Button

LinkedIn Share Button

Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity

commented Aug 28, 2013 by Deepak Dasgupta

I neglected to mention these systems are both CentOS linux systems. Unfortunately, the 5.5 hour dumps are already done during the least busy times.

Regarding snapshots, how long are snapshot locks held? These are ext4 filesystems. Assuming the lock is not held for long, what's the recommended way to do snapshots on ext4?

commented Aug 28, 2013 by anonymous

1 Answer

If u have LVM's then lock is held only for the duration of taking snapshot, which would be few min, if there is very less activity on the db.

answer Aug 28, 2013 by Deepak Dasgupta

Similar Questions

0 votes

SLAVE aware of binary log file switch in mysql?

Given a MASTER and a SLAVE.

When launching the SLAVE, it knows about the binary log file used by the MASTER and the position in that log file.

Say the binary log file (on the master) has reached its maximum size, so that it has to switch to a "+1" binary log file: does he inform the SLAVE of that switch so that the SLAVE updates its information about the MASTER status?

I'm readind the documentation http://dev.mysql.com/doc/refman/5.1/en/binary-log.html and dont see what
is happening while slaving.

+1 vote

Implementation of parallel-replication in MySQL

We have replication set-up, where we cater to huge amounts of data. Since quite some time, we have been facing issues wherein the slave lags behind master quite a lot.

So, yesterday we were able to setup parallel replication, by incorporating the following changes ::

a) To begin with, partitioned some tables into dedicated databases.

b) Set up the "slave-parallel-workers" parameter.

The above seems to work functionally fine, but we have one doubt/query about the scalability of this solution.

First, I will jot down the flow as far as I understand (please correct if wrong) ::

"Even in parallel-replication scenario, the master writes all the binlog (combined for all databases) in just one file, which then gets passed onto the slave as single-file itself. Thereafter, all the replication commands (combined for all databases) are written sequentially onto one slave-relay file.

Thereafter, as per the documentation, the slave-SQL-Thread acts as the manager, handing over commands to worker-threads depending upon the databases on which the commands run."

So far, so good. However, what would happen if the slave-relay file contains the following ::

db1-statement-1 (short-running)
db2-statement-1 (very, very long-running)
db2-statement-2 (short-running)
db1-statement-2 (short-running)
db1-statement-3 (short-running)

We will be grateful if someone could please clarify, as to how the above statements will be managed among the Manager and the Worker-Threads (let's say there is just one worker-thread-per-db) ?

In particular, does the Manager thread creates internal slave-relay-log-files, one for per database-statements?

+3 votes

MySQL statement based replication with LOAD DATA LOCAL INFILE statement

I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'.

According to manual -
https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html -
LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'.

I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Master is using MySQL 5.0 and slave is using MySQL 5.5

0 votes

Multiple MySQL Date Replication

I have been asked to set up multiple database replication which I have done before for simple cases however there are some nuances with this instance that add some complexity and I'd like to hear your collective expertise on this proposed scenario:-

1) Single master database
2) n (probably 3 to start with) number of slave databases
3) All but 5 tables (123 tables in total) are to be replicated from the master to all the slaves
4) 3 tables from the slaves are to be replicated back to the master

It is mainly item 4) that concerns me - the primary ID's are almost certain to collide unless I seed the auto increment ID to partition the IDs into separate ranges or does MySQL handle this issue?
There are some foreign keys on one of the 3 slave to master tables but they are pointing at some extremely static tables that are very unlikely to change. Is the above a feasible implementation...?

0 votes

Master not creating new binary log in MySql

We currently have a problem with a master slave setup running mysql 5.0.

This is one of our legacy servers which are in the planning to be upgraded, however in order for this to be done the replication needs to be up and running.

The problem we have currently however is that the binary logs on the master was moved to a seperate partition due to disc space restrictions.

A new binlog file called mysql-bin.1 was created and everything seemed to work fine.

However, the moment the file reached the file size of 100Mb, it does not go on to create a new binlog file called mysql-bin.2 and the replication fails stating that it is unable to read the binary log file.

Thus far we have done a flush logs and reset master , but the same problem occurs, where it creates mysql-bin.1 and the moment it reaches it's max size and suppose to create a new file, it stops and
does not create the new one.

I really hope this makes sense, and that someone can perhaps point us in the correct direction.

...