{"id":2227,"date":"2011-11-05T16:31:32","date_gmt":"2011-11-05T16:31:32","guid":{"rendered":"https:\/\/supportex.net\/?p=2227"},"modified":"2019-05-10T17:50:23","modified_gmt":"2019-05-10T15:50:23","slug":"mysql-chained-replication-m1s1s2","status":"publish","type":"post","link":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/","title":{"rendered":"MySQL: chained replication M1>S1>S2"},"content":{"rendered":"

In most case simple master-slave replication provides enough level of high-avalability (about 99.9%), read ops balancing and allows non-blocking backups (if you use MyISAM tables since InnoDB tables can be backed up without blocking). But sometimes you may want to setup chained replication with two slaves: first slave replicates from the master, and the second slave replicates from the first one. Often such technique is used for smooth MySQL server migration. In this this case first slave is switched to master mode, and second slave remains the slave. Hereby we will refer master as M1<\/strong>, first slave as S1<\/strong>, and the second one as S2<\/strong>, so S1 replicates from M1, and S2 replicates from S1.<\/p>\n

M1<\/h4>\n

Enable binary logging:<\/p>\n

server-id=1\nlog-bin=mysql-bin\nbinlog_do_db=sakila<\/pre>\n
Provide privileges for slave:<\/p>\n
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'slave1.example.com' IDENTIFIED BY 'password';\n<\/pre>\n
FLUSH PRIVILEGES;<\/pre>\n
Dump database:<\/p>\n
mysqldump -uroot --opt --master-data -p sakila > \/tmp\/sakila.sql<\/code><\/p>\n
M1 is ready. Going to S1.<\/p>\n
S1<\/h4>\n
server-id=2\nlog-bin=mysql-bin\nreplicate-do-db=sakila\nlog-slave-updates\nskip-slave-start<\/pre>\n
Missing log-slave-updates<\/em> parameter is the most common mistake while setting up chained replication. So make sure you have it.
\nGranting privileges:<\/p>\n
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'slave2.example.com' IDENTIFIED BY 'password';\nFLUSH PRIVILEGES;<\/pre>\n
Drop replicated database sakila if exists:<\/p>\n
DROP DATABASE sakila;<\/code><\/p>\n
Create empty database:<\/p>\n
CREATE DATABASE sakila;<\/code><\/p>\n
Reset old slave:<\/p>\n
RESET SLAVE;<\/code><\/p>\n
Import dump from M1<\/strong> to S1<\/strong>:<\/p>\n
mysql -uroot -p sakila < \/tmp\/sakila.sql<\/code><\/p>\n
Obtaining M1<\/strong> binary log coordinates. Thet can be found at the bginning of dump file:<\/p>\n
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=107;<\/code><\/p>\n
Providing information about replication user:<\/p>\n
CHANGE MASTER TO MASTER_HOST='master1.example.com', MASTER_USER='replica_user', MASTER_PASSWORD='password';<\/code><\/p>\n
Checking slave status:<\/p>\n
SHOW SLAVE STATUSG<\/code><\/p>\n
Let’s continue with S2.<\/p>\n
S2<\/h4>\n
Adding replication parameters to S2<\/strong>:<\/p>\n
server-id=3\nlog-bin=mysql-bin\nbinlog_do_db=sakila\nreplicate-do-db=sakila<\/pre>\n
Drop database sakila if exists:<\/p>\n
DROP DATABASE sakila;<\/code><\/p>\n
Create empty database:<\/p>\n
CREATE DATABASE sakila;<\/code><\/p>\n
Reset old slave:<\/p>\n
RESET SLAVE;<\/code><\/p>\n
Import dump from M1<\/strong> to S2<\/strong>:<\/p>\n
mysql -uroot -p sakila < \/tmp\/sakila.sql<\/code><\/p>\n
At this moment we need to obtain S1<\/strong> binary log coordinates in order to start replicating from it.<\/p>\n
Run ‘SHOW MASTER STATUS’ on S1<\/strong> to get them:<\/p>\n
mysql> SHOW MASTER STATUS;\n+------------------+-----------+--------------+------------------+\n| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |\n+------------------+-----------+--------------+------------------+\n| mysql-bin.000033 | 743915402 |              |                  |\n+------------------+-----------+--------------+------------------+\n1 row in set (0.00 sec) \nmysql>\n<\/pre>\n
Provide master information to S2<\/strong>:<\/p>\n
CHANGE MASTER TO MASTER_HOST='slave1.example.com', MASTER_USER='replica_user', MASTER_PASSWORD='password';<\/code><\/p>\n
Providing S1<\/strong> binary log infromation to S2<\/strong>:<\/p>\n
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000033', MASTER_LOG_POS=743915402;<\/code><\/p>\n
Starting replication on S2<\/strong>:<\/p>\n
START SLAVE;\nSHOW SLAVE STATUSG;<\/pre>\n
mysql> SHOW SLAVE STATUSG;\n*************************** 1. row ***************************\nSlave_IO_State: Waiting for master to send event\nMaster_Host: slave1.example.com\nMaster_User: replica_user\nMaster_Port: 3306\nConnect_Retry: 60\nMaster_Log_File: mysql-bin.000033\nRead_Master_Log_Pos: 747231154\nRelay_Log_File: relay-bin.000097\nRelay_Log_Pos: 747231299\nRelay_Master_Log_File: mysql-bin.000033\nSlave_IO_Running: Yes\nSlave_SQL_Running: Yes\nReplicate_Do_DB: sakila\nReplicate_Ignore_DB:\nReplicate_Do_Table:\nReplicate_Ignore_Table:\nReplicate_Wild_Do_Table:\nReplicate_Wild_Ignore_Table:\nLast_Errno: 0\nLast_Error:\nSkip_Counter: 0\nExec_Master_Log_Pos: 747231154\nRelay_Log_Space: 747231494\nUntil_Condition: None\nUntil_Log_File:\nUntil_Log_Pos: 0\nMaster_SSL_Allowed: No\nMaster_SSL_CA_File:\nMaster_SSL_CA_Path:\nMaster_SSL_Cert:\nMaster_SSL_Cipher:\nMaster_SSL_Key:\nSeconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\nLast_IO_Errno: 0\nLast_IO_Error:\nLast_SQL_Errno: 0\nLast_SQL_Error:<\/pre>\n
Now we can start slave threads on S1<\/strong> as well:<\/p>\n
START SLAVE;\nSHOW SLAVE STATUSG;<\/pre>\n
What to monitor with replication<\/h4>\n
The most important variables are Slave_IO_Running<\/strong>, Slave_SQL_Running<\/strong> and Seconds_Behind_Master<\/strong>. Two first variables should be equal to ‘Yes’, and Seconds_Behind_Master should be 0 (not NULL!) or near to it. These variables indicate that both replication threads are running and slave is up to date with master.
\nIf all variables are correct on both slaves, you can start using your replication.<\/p>\n
Future reading<\/h3>\n
MySQL Recipes: Promoting a Slave to Master or Changing Masters <\/a><\/p>\n
Switching Masters During Failover<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In most case simple master-slave replication provides enough level of high-avalability (about 99.9%), read ops balancing and allows non-blocking backups (if you use MyISAM tables since InnoDB tables can be backed up without blocking). But sometimes you may want to setup chained replication with two slaves: first slave replicates from the master, and the second… <\/p>\n
Read <\/a><\/div>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[278],"tags":[199],"class_list":["post-2227","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql-en"],"yoast_head":"\nMySQL: chained replication M1>S1>S2 – Supportex.NET blog<\/title>\n<meta name=\"description\" content=\"How to MySQL chained replication\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL: chained replication M1>S1>S2 – Supportex.NET blog\" \/>\n<meta property=\"og:description\" content=\"How to MySQL chained replication\" \/>\n<meta property=\"og:url\" content=\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\" \/>\n<meta property=\"og:site_name\" content=\"Supportex.NET blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-11-05T16:31:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-10T15:50:23+00:00\" \/>\n<meta name=\"author\" content=\"Oleksii Tykhonov\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oleksii Tykhonov\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\"},\"author\":{\"name\":\"Oleksii Tykhonov\",\"@id\":\"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251\"},\"headline\":\"MySQL: chained replication M1>S1>S2\",\"datePublished\":\"2011-11-05T16:31:32+00:00\",\"dateModified\":\"2019-05-10T15:50:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\"},\"wordCount\":367,\"keywords\":[\"mysql\"],\"articleSection\":[\"mysql\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\",\"url\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\",\"name\":\"MySQL: chained replication M1>S1>S2 – Supportex.NET blog\",\"isPartOf\":{\"@id\":\"https:\/\/supportex.net\/blog\/en\/#website\"},\"datePublished\":\"2011-11-05T16:31:32+00:00\",\"dateModified\":\"2019-05-10T15:50:23+00:00\",\"author\":{\"@id\":\"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251\"},\"description\":\"How to MySQL chained replication\",\"breadcrumb\":{\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/supportex.net\/blog\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL: chained replication M1>S1>S2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/supportex.net\/blog\/en\/#website\",\"url\":\"https:\/\/supportex.net\/blog\/en\/\",\"name\":\"Supportex.NET blog\",\"description\":\"Server and network management company\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/supportex.net\/blog\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251\",\"name\":\"Oleksii Tykhonov\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4fd5f58002717075c88963469b9babef?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4fd5f58002717075c88963469b9babef?s=96&d=mm&r=g\",\"caption\":\"Oleksii Tykhonov\"},\"url\":\"https:\/\/supportex.net\/blog\/author\/oleksiitykhonov\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL: chained replication M1>S1>S2 – Supportex.NET blog","description":"How to MySQL chained replication","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/","og_locale":"en_US","og_type":"article","og_title":"MySQL: chained replication M1>S1>S2 – Supportex.NET blog","og_description":"How to MySQL chained replication","og_url":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/","og_site_name":"Supportex.NET blog","article_published_time":"2011-11-05T16:31:32+00:00","article_modified_time":"2019-05-10T15:50:23+00:00","author":"Oleksii Tykhonov","twitter_misc":{"Written by":"Oleksii Tykhonov","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#article","isPartOf":{"@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/"},"author":{"name":"Oleksii Tykhonov","@id":"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251"},"headline":"MySQL: chained replication M1>S1>S2","datePublished":"2011-11-05T16:31:32+00:00","dateModified":"2019-05-10T15:50:23+00:00","mainEntityOfPage":{"@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/"},"wordCount":367,"keywords":["mysql"],"articleSection":["mysql"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/","url":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/","name":"MySQL: chained replication M1>S1>S2 – Supportex.NET blog","isPartOf":{"@id":"https:\/\/supportex.net\/blog\/en\/#website"},"datePublished":"2011-11-05T16:31:32+00:00","dateModified":"2019-05-10T15:50:23+00:00","author":{"@id":"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251"},"description":"How to MySQL chained replication","breadcrumb":{"@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/supportex.net\/blog\/2011\/11\/mysql-chained-replication-m1s1s2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/supportex.net\/blog\/en\/"},{"@type":"ListItem","position":2,"name":"MySQL: chained replication M1>S1>S2"}]},{"@type":"WebSite","@id":"https:\/\/supportex.net\/blog\/en\/#website","url":"https:\/\/supportex.net\/blog\/en\/","name":"Supportex.NET blog","description":"Server and network management company","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/supportex.net\/blog\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/0690c26a0266603129fc15eae6243251","name":"Oleksii Tykhonov","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/supportex.net\/blog\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4fd5f58002717075c88963469b9babef?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4fd5f58002717075c88963469b9babef?s=96&d=mm&r=g","caption":"Oleksii Tykhonov"},"url":"https:\/\/supportex.net\/blog\/author\/oleksiitykhonov\/"}]}},"_links":{"self":[{"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/posts\/2227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/comments?post=2227"}],"version-history":[{"count":3,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/posts\/2227\/revisions"}],"predecessor-version":[{"id":3172,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/posts\/2227\/revisions\/3172"}],"wp:attachment":[{"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/media?parent=2227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/categories?post=2227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/supportex.net\/blog\/wp-json\/wp\/v2\/tags?post=2227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}