Giuseppe maxia l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 42

Advanced MySQL replication techniques PowerPoint PPT Presentation


  • 140 Views
  • Uploaded on
  • Presentation posted in: General

Giuseppe Maxia. Advanced MySQL replication techniques. About me. http:// datacharmer .org. Agenda. Replication basics Circular replication Automatic failover Failover with circular replication. Replication goals. Data redundancy Load balancing Backup points Base for failover.

Download Presentation

Advanced MySQL replication techniques

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Giuseppe maxia l.jpg

Giuseppe Maxia

Advanced MySQL replication techniques


About me l.jpg

About me

http://datacharmer.org


Agenda l.jpg

Agenda

Replication basics

Circular replication

Automatic failover

Failover with circular replication


Replication goals l.jpg

Replication goals

Data redundancy

Load balancing

Backup points

Base for failover


Replication basics l.jpg

Replication basics

One single point of data insertion and changes (MASTER)

more points of data read (SLAVES)

Different IDs for each server

Binary log

Replication slave user

Replication threads (IO and SQL)


Replication basics6 l.jpg

Replication basics

read

(I/O)

read

(I/O)

binary log

relay log

relay log

run (SQL)

run (SQL)


Circular replication l.jpg

Circular Replication

More points of data insertion and changes (MASTER)

Each slave is also a master

Insertion conflicts!


Circular replication8 l.jpg

Circular Replication


Circular replication9 l.jpg

Circular Replication

# CONFLICT

CREATE TABLE x (

id int(11) NOT NULL AUTO_INCREMENT,

c char(10) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=MyISAM


Circular replication10 l.jpg

Circular Replication

# CONFLICT

#(assume broken connection between nodes)

[node A] insert into x (c) values

('aaa'), ('bbb'), ('ccc');

[node B] insert into x (c) values ('xxx'), ('yyy'), ('zzz');


Circular replication11 l.jpg

Circular Replication

# CONFLICT

#(when connection is resumed)

Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database:

'test'. Query: 'insert into x (c) values ('aaa')'


Circular replication12 l.jpg

Circular Replication

[node A] select * from x;

+----+------+

| id | c |

+----+------+

| 1 | aaa |

| 2 | bbb |

| 3 | ccc |

+----+------+

[node B] select * from x;

+----+------+

| id | c |

+----+------+

| 1 | xxx |

| 2 | yyy |

| 3 | zzz |

+----+------+


Circular replication13 l.jpg

Circular Replication

Circular Replication

solving auto-increment conflicts


Circular replication14 l.jpg

Circular Replication

# SOLVING AUTO-INCREMENT CONFLICTS

# in both nodes

STOP SLAVE;

RESET MASTER;

RESET SLAVE;

TRUNCATE x;


Circular replication15 l.jpg

Circular Replication

# SOLVING AUTO-INCREMENT CONFLICTS

[NodeA]

set auto_increment_increment = 10;

set auto_increment_offset = 1;

[NodeB]

set auto_increment_increment = 10;

set auto_increment_offset = 2;


Circular replication16 l.jpg

Circular Replication

# SOLVING AUTO-INCREMENT CONFLICTS

# (Slaves still stopped)

[node A] insert into x (c) values

('aaa'), ('bbb'), ('ccc');

[node B] insert into x (c) values ('xxx'), ('yyy'), ('zzz');


Circular replication17 l.jpg

Circular Replication

[node A] select * from x;

+----+------+

| id | c |

+----+------+

| 1 | aaa |

| 11 | bbb |

| 21 | ccc |

+----+------+

[node B] select * from x;

+----+------+

| id | c |

+----+------+

| 2 | xxx |

| 12 | yyy |

| 22 | zzz |

+----+------+


Circular replication18 l.jpg

Circular Replication

# SOLVING AUTO-INCREMENT CONFLICTS

# (resume replicastion)

[node A] SLAVE START;

[node B] SLAVE START;


Circular replication19 l.jpg

Circular Replication

# SOLVING AUTO-INCREMENT CONFLICTS

# (resume replication)

[node A] SHOW SLAVE STATUS\G

[node B] SHOW SLAVE STATUS\G

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...


More circular replication l.jpg

More Circular Replication


Failover basics l.jpg

Failover basics

Master replacement

inform each slave about the new master


Failover scenario 1 l.jpg

Failover scenario (1)


Failover scenario 2a l.jpg

Failover scenario (2a)


Failover scenario 2b l.jpg

Failover scenario (2b)

# in the master

CREATE TABLE who (

server_id int

) ENGINE=MyIsam


Failover scenario 2c l.jpg

Failover scenario (2c)

# in each slave

CREATE TABLE master_who (

server_id int

) ENGINE=Federated CONNECTION='mysql://user:[email protected]:3306/replica/who';


Failover scenario 2d l.jpg

Failover scenario (2d)

# in each slave (requires MySQL 5.1)

create event check_master_conn

on schedule every 30 second

enable

do call check_master();


Failover scenario 2e l.jpg

Failover scenario (2e)

create procedure check_master()

begin

declare master_dead boolean

default false;

declare curx cursor for

select server_id

from replica.master_who;

declare continue handler

for SQLSTATE 'HY000'

set master_dead = true;

...


Failover scenario 2f l.jpg

Failover scenario (2f)

procedure check_master()

...

open curx;

# a failure to open the cursor

# occurs here

# setting the master_dead variable

# to true


Failover scenario 2g l.jpg

Failover scenario (2g)

procedure check_master()

...

if (master_dead) then

stop slave;

change master to

master_host='172.16.1.40',

master_log_file='binlog_name',

master_log_pos=0;

start slave;

alter event check_master_conn disable;

end if;


Failover scenario 3 l.jpg

Failover scenario (3)


Failover scenario 4 l.jpg

Failover scenario (4)


Circular failover 1 l.jpg

Circular Failover (1)


Circular failover 2 l.jpg

Circular Failover (2)


Circular failover 3 l.jpg

Circular Failover (3)


Try it l.jpg

Try it !

The Replication playground

http://sourceforge.net/projects/my-repl-play


Failover example 1 l.jpg

Failover example (1)

# replication playground

$ ./start_all.sh

$ ./check_slaves.sh

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO_Running: Yes

Slave_SQL_Running: Yes


Failover example 2 l.jpg

Failover example (2)

# replication playground

$ ./use.sh my.nodeC.cnf replica

nodeC> show slave status \G

...

Master_Host: 127.0.0.1

Master_User: nodeCuser

Master_Port: 10011

...


Failover example 3 l.jpg

Failover example (3)

# replication playground

nodeC> select * from check_master_log;

+---------------------+---------------+

| ts | master_status |

+---------------------+---------------+

| 2006-10-29 10:39:26 | master OK |

| 2006-10-29 10:39:56 | master OK |

+---------------------+---------------+


Failover example 4 l.jpg

Failover example (4)

# replication playground

$ ./stop_all.sh A

nodeA shutdown


Failover example 5 l.jpg

Failover example (5)

# replication playground

nodeC> select * from check_master_log;

+---------------------+----------------+

| ts | master_status |

+---------------------+----------------+

| 2006-10-29 10:39:26 | master OK |

| 2006-10-29 10:39:56 | master OK |

| 2006-10-29 10:40:26 | master is dead |

+---------------------+----------------+


Failover example 6 l.jpg

Failover example (6)

# replication playground

$ ./use.sh my.nodeC.cnf replica

nodeC> show slave status \G

...

Master_Host: 127.0.0.1

Master_User: nodeCuser

Master_Port: 10021

...


Thanks l.jpg

THANKS

Any questions?

http://datacharmer.org


  • Login