Giuseppe maxia
Download
1 / 42

Advanced MySQL replication techniques - PowerPoint PPT Presentation


  • 177 Views
  • Updated On :

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Advanced MySQL replication techniques' - ivan


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 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

...



Failover basics l.jpg
Failover basics

Master replacement

inform each slave about the new master




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;







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


ad