{"id":539,"date":"2018-12-18T09:27:13","date_gmt":"2018-12-18T09:27:13","guid":{"rendered":"http:\/\/www.asianux.org.vn\/?p=539"},"modified":"2018-12-18T09:27:14","modified_gmt":"2018-12-18T09:27:14","slug":"intro-mysql-replication","status":"publish","type":"post","link":"https:\/\/www.asianux.org.vn\/index.php\/2018\/12\/18\/intro-mysql-replication\/","title":{"rendered":"Intro MySQL replication"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\" id=\"1-gi\u1edbi-thi\u1ec7u\">1. Gi\u1edbi thi\u1ec7u<\/h1>\n\n\n\n<p>Right tool for right job. Tr\u01b0\u1edbc ti\u00ean ph\u1ea3i hi\u1ec3u l\u00e0 MySQL Replication kh\u00f4ng ph\u1ea3i l\u00e0 gi\u1ea3i ph\u00e1p gi\u1ea3i quy\u1ebft m\u1ecdi b\u00e0i to\u00e1n v\u1ec1 qu\u00e1 t\u1ea3i h\u1ec7 th\u1ed1ng c\u01a1 s\u1edf d\u1eef li\u1ec7u. \u0110\u1ec3 m\u1edf r\u1ed9ng m\u1ed9t h\u1ec7 th\u1ed1ng ta c\u00f3 hai ph\u01b0\u01a1ng ph\u00e1p m\u1edf r\u1ed9ng l\u00e0 scale up v\u00e0 scale out. B\u1eaft \u0111\u1ea7u v\u1edbi 1 m\u00e1y ch\u1ee7 th\u00ec hai ph\u01b0\u01a1ng ph\u00e1p tr\u00ean \u0111\u01b0\u1ee3c di\u1ec5n gi\u1ea3i nh\u01b0 sau:<\/p>\n\n\n\n<p>Scale up c\u00f3 ngh\u0129a l\u00e0 v\u1edbi m\u1ed9t m\u00e1y ch\u1ee7 ta l\u00e0m c\u00e1ch n\u00e0o \u0111\u00f3 \u0111\u1ec3 n\u00f3 c\u00f3 th\u1ec3 ph\u1ee5c v\u1ee5 nhi\u1ec1u h\u01a1n s\u1ed1 l\u01b0\u1ee3ng k\u1ebft n\u1ed1i, truy v\u1ea5n. Ngh\u0129a l\u00e0 gi\u00e1 tr\u1ecb 1\/(s\u1ed1 k\u1ebft n\u1ed1i ph\u1ee5c v\u1ee5) c\u00e0ng nh\u1ecf th\u00ec c\u00e0ng t\u1ed1t. \u0110\u1ec3 \u0111\u1ea1t \u0111\u01b0\u1ee3c m\u1ee5c \u0111\u00edch n\u00e0y th\u00ec c\u00f3 2 ph\u01b0\u01a1ng ph\u00e1p:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>T\u0103ng ph\u1ea7n c\u1ee9ng l\u00ean cho m\u00e1y ch\u1ee7. Ngh\u0129a l\u00e0 v\u1edbi CPU l\u00e0 4 core, RAM l\u00e0 8 GB ph\u1ee5c v\u1ee5 \u0111\u01b0\u1ee3c 500 truy v\u1ea5n th\u00ec gi\u1edd ta t\u0103ng CPU l\u00ean 24 core, RAM t\u0103ng l\u00ean 32GB -&gt; m\u00e1y ch\u1ee7 c\u00f3 th\u1ec3 ph\u1ee5c v\u1ee5 \u0111\u01b0\u1ee3c s\u1ed1 l\u01b0\u1ee3ng k\u1ebft n\u1ed1i truy v\u1ea5n nhi\u1ec1u h\u01a1n.<\/li><li>Optimize \u1ee9ng d\u1ee5ng, c\u00e2u truy v\u1ea5n. V\u00ed d\u1ee5 v\u1edbi c\u00e2u truy v\u1ea5n l\u1ea5y d\u1eef li\u1ec7u t\u1ed1n 5s \u0111\u1ec3 l\u1ea5y \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u, sau \u0111\u00f3 m\u1edbi tr\u1ea3 l\u1ea1i t\u00e0i nguy\u00ean cho h\u1ec7 th\u1ed1ng ph\u1ee5c v\u1ee5 c\u00e1c truy v\u1ea5n kh\u00e1c. M\u00e1y ch\u1ee7 c\u00f3 th\u1ec3 \u0111\u1ed3ng th\u1eddi ph\u1ee5c v\u1ee5 500 truy v\u1ea5n d\u1ea1ng nh\u01b0 v\u1eady th\u00ec n\u1ebfu ta t\u1ed1i \u01b0u \u0111\u1ec3 truy v\u1ea5n l\u1ea5y d\u1eef li\u1ec7u ch\u1ec9 t\u1ed1n 1s =&gt; M\u00e1y ch\u1ee7 c\u00f3 th\u1ec3 ph\u1ee5c v\u1ee5 \u0111\u1ed3ng th\u1eddi nhi\u1ec1u truy v\u1ea5n h\u01a1n<\/li><\/ul>\n\n\n\n<p>Scale out l\u00e0 gi\u1ea3i ph\u00e1p t\u0103ng s\u1ed1 l\u01b0\u1ee3ng server v\u00e0 d\u00f9ng c\u00e1c gi\u1ea3i ph\u00e1p load-balacer \u0111\u1ec3 ph\u00e2n ph\u1ed1i truy v\u1ea5n ra nhi\u1ec1u server. V\u00ed d\u1ee5 b\u1ea1n c\u00f3 1 server c\u00f3 kh\u1ea3 n\u0103ng ph\u1ee5c v\u1ee5 500 truy v\u1ea5n. N\u1ebfu ta d\u1ef1ng th\u00eam 5 server n\u1eefa c\u00f3 c\u1ea5u h\u00ecnh t\u01b0\u01a1ng t\u1ef1, \u0111\u1eb7t th\u00eam m\u1ed9t LB ph\u00eda tr\u01b0\u1edbc \u0111\u1ec3 ph\u00e2n ph\u1ed1i th\u00ec c\u00f3 kh\u1ea3 n\u0103ng h\u1ec7 th\u1ed1ng c\u00f3 th\u1ec3 ph\u1ee5c v\u1ee5 \u0111c 5&#215;500 truy v\u1ea5n \u0111\u1ed3ng th\u1eddi.<\/p>\n\n\n\n<p>MySQL Replication l\u00e0 m\u1ed9t gi\u1ea3i ph\u00e1p scale out (t\u0103ng s\u1ed1 l\u01b0\u1ee3ng instance MySQL) nh\u01b0ng kh\u00f4ng ph\u1ea3i b\u00e0i to\u00e1n n\u00e0o c\u0169ng d\u00f9ng \u0111\u01b0\u1ee3c. C\u00e1c b\u00e0i to\u00e1n m\u00e0 MySQL Replication s\u1ebd gi\u1ea3i quy\u1ebft t\u1ed1t:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Scale Read<\/li><li>Data Report<\/li><li>Real time backup<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"1-1-scale-read\">1.1 Scale Read<\/h2>\n\n\n\n<p>Scale Read th\u01b0\u1eddng g\u1eb7p \u1edf c\u00e1c \u1ee9ng d\u1ee5ng m\u00e0 s\u1ed1 truy v\u1ea5n \u0111\u1ecdc d\u1eef li\u1ec7u nhi\u1ec1u h\u01a1n ghi, t\u1ec9 l\u1ec7 read\/write c\u00f3 th\u1ec3&nbsp;<sup>80<\/sup>\u2044<sub>20<\/sub>&nbsp;ho\u1eb7c h\u01a1n. C\u00e1c \u1ee9ng d\u1ee5ng th\u01b0\u1eddng g\u1eb7p l\u00e0 b\u00e1o, trang tin t\u1ee9c.<\/p>\n\n\n\n<p>V\u1edbi scale read ta s\u1ebd ch\u1ec9 c\u00f3 m\u1ed9t Master instance ph\u1ee5c v\u1ee5 cho vi\u1ec7c \u0111\u1ecdc\/ghi d\u1eef li\u1ec7u. C\u00f3 th\u1ec3 c\u00f3 m\u1ed9t ho\u1eb7c nhi\u1ec1u Slave instance ch\u1ec9 ph\u1ee5c v\u1ee5 cho vi\u1ec7c \u0111\u1ecdc d\u1eef li\u1ec7u<\/p>\n\n\n\n<p>M\u1ed9t s\u1ed1 \u1ee9ng d\u1ee5ng write nhi\u1ec1u (th\u01b0\u01a1ng m\u1ea1i \u0111i\u1ec7n t\u1eed) c\u0169ng c\u00f3 s\u1eed d\u1ee5ng MySQL Replication \u0111\u1ec3 scale out h\u1ec7 th\u1ed1ng<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"1-2-data-report\">1.2 Data Report<\/h2>\n\n\n\n<p>M\u1ed9t s\u1ed1 h\u1ec7 th\u1ed1ng cho ph\u00e9p m\u1ed9t s\u1ed1 ng\u01b0\u1eddi (leader, manager, ng\u01b0\u1eddi l\u00e0m report, th\u1ed1ng k\u00ea, data) truy c\u1eadp v\u00e0o d\u1eef li\u1ec7u tr\u00ean production ph\u1ee5c v\u1ee5 cho c\u00f4ng vi\u1ec7c c\u1ee7a h\u1ecd. Vi\u1ec7c ch\u1ecdc th\u1eb3ng v\u00e0o data production s\u1ebd r\u1ea5t nguy hi\u1ec3m v\u00ec:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>V\u00f4 t\u00ecnh ch\u1ec9nh s\u1eeda l\u00e0m sai l\u1ec7nh d\u1eef li\u1ec7u (n\u1ebfu c\u00f3 quy\u1ec1n insert, update)<\/li><li>V\u00f4 t\u00ecnh th\u1ef1c thi c\u00e1c c\u00e2u truy v\u1ea5n t\u1ed1n nhi\u1ec1u t\u00e0i nguy\u00ean, th\u1eddi gian truy v\u1ea5n d\u00e0i l\u00e0m treo h\u1ec7 th\u1ed1ng<\/li><\/ul>\n\n\n\n<p>Vi\u1ec7c setup m\u1ed9t m\u00e1y ch\u1ee7 l\u00e0m data report (application c\u0169ng s\u1ebd kh\u00f4ng k\u1ebft n\u1ed1i t\u1edbi server n\u00e0y) l\u00e0m gi\u1ea3m thi\u1ec3u 2 r\u1ee7i ro tr\u00ean<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"1-3-real-time-backup\">1.3 Real time backup<\/h2>\n\n\n\n<p>V\u1edbi c\u01a1 s\u1edf d\u1eef li\u1ec7u l\u1edbn vi\u1ec7c backup kh\u00f4ng th\u1ec3 th\u1ef1c hi\u1ec7n th\u01b0\u1eddng xuy\u00ean \u0111\u01b0\u1ee3c (h\u00e0ng gi\u1edd, h\u00e0ng ph\u00fat). V\u1edbi c\u00e1c \u1ee9ng d\u1ee5ng giao d\u1ecbch t\u00e0i ch\u00ednh, thanh to\u00e1n, TMDT n\u1ebfu b\u1ecb m\u1ea5t d\u1eef li\u1ec7u 1 gi\u1edd, 1 ng\u00e0y th\u00ec thi\u1ec7t h\u1ea1i s\u1ebd r\u1ea5t l\u1edbn (m\u00e1y ch\u1ee7 ch\u00ednh t\u01b0 d\u01b0ng b\u1ecb h\u1ecfng). Real time backup l\u00e0 m\u1ed9t gi\u1ea3i ph\u00e1p b\u1ed5 sung cho offline backup, ch\u1ea1y \u0111\u1ed3ng th\u1eddi c\u1ea3 2 ph\u01b0\u01a1ng ph\u00e1p n\u00e0y \u0111\u1ec3 b\u1ea3o \u0111\u1ea3m s\u1ef1 an to\u00e0n cho d\u1eef li\u1ec7u.<\/p>\n\n\n\n<p>Tuy nhi\u00ean, vi\u1ec7c d\u00f9ng replicate \u0111\u1ec3 backup d\u1eef li\u1ec7u ch\u1ec9 \u0111\u1ea3m b\u1ea3o n\u1ebfu \u0111\u0129a c\u1ee9ng c\u1ee7a master b\u1ecb h\u1ecfng, trong tr\u01b0\u1eddng h\u1ee3p human error khi x\u00f3a nh\u1ea7m d\u1eef li\u1ec7u, h\u00e0nh \u0111\u1ed9ng x\u00f3a s\u1ebd \u0111\u01b0\u1ee3c replicate sang slave lu\u00f4n =&gt; v\u1eabn b\u1ecb m\u1ea5t d\u1eef li\u1ec7u.<\/p>\n\n\n\n<p>\u0110\u1ec3 tr\u00e1nh x\u1ea3y ra tr\u01b0\u1eddng h\u1ee3p tr\u00ean v\u00e0 gi\u1ea3m thi\u1ec3u r\u1ee7i ro m\u1ea5t d\u1eef li\u1ec7u, m\u00ecnh c\u00f3 gi\u1edbi thi\u1ec7u m\u1ed9t b\u00e0i kh\u00e1c&nbsp;<a href=\"https:\/\/xluffy.github.io\/post\/delayed-replication-in-mysql\/\" target=\"_blank\" rel=\"noopener\">delay-replication<\/a>.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"2-ho\u1ea1t-\u0111\u1ed9ng-nh\u01b0-th\u1ebf-n\u00e0o\">2. Ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o?<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"2-1-m\u1ed9t-s\u1ed1-m\u00f4-h\u00ecnh\">2.1 M\u1ed9t s\u1ed1 m\u00f4 h\u00ecnh<\/h2>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/i.imgur.com\/mevNr10.png\" alt=\"mysql-replication\" title=\"\"><\/figure>\n\n\n\n<p>V\u1edbi c\u1ea3 hai m\u00f4 h\u00ecnh ta lu\u00f4n ch\u1ec9 c\u00f3 1 Master database ph\u1ee5c v\u1ee5 cho Write d\u1eef li\u1ec7u, c\u00f3 th\u1ec3 c\u00f3 m\u1ed9t ho\u1eb7c nhi\u1ec1u Slave database. T\u00f9y t\u1eebng m\u00f4 h\u00ecnh ta c\u00f3 th\u1ec3 c\u1ea5u h\u00ecnh m\u1ed7i web node connect v\u00e0o m\u1ed9t Slave DB t\u01b0\u01a1ng \u1ee9ng ho\u1eb7c c\u00f3 th\u1ec3 d\u00f9ng m\u1ed9t LB \u0111\u1eb7t tr\u01b0\u1edbc c\u1ee5m Slave \u0111\u1ec3 LB t\u1ef1 \u0111\u1ed9ng ph\u00e2n ph\u1ed1i connection v\u00e0o t\u1eebng Slave DB theo thu\u1eadt to\u00e1n c\u1ee7a LB<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/i.imgur.com\/etkJXxd.png\" alt=\"mysql-replication-lb\" title=\"\"><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"2-2-c\u00e1ch-ho\u1ea1t-\u0111\u1ed9ng\">2.2 C\u00e1ch ho\u1ea1t \u0111\u1ed9ng<\/h2>\n\n\n\n<p>Tr\u00ean Master:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>C\u00e1c k\u1ebft n\u1ed1i t\u1eeb web app t\u1edbi Master DB s\u1ebd m\u1edf m\u1ed9t&nbsp;<code>Session_Thread<\/code>&nbsp;khi c\u00f3 nhu c\u1ea7u ghi d\u1eef li\u1ec7u.&nbsp;<code>Session_Thread<\/code>&nbsp;s\u1ebd ghi c\u00e1c statement SQL v\u00e0o m\u1ed9t file binlog (v\u00ed d\u1ee5 v\u1edbi format c\u1ee7a binlog l\u00e0 statement-based ho\u1eb7c mix). Binlog \u0111\u01b0\u1ee3c l\u01b0u tr\u1eef trong&nbsp;<code>data_dir<\/code>&nbsp;(c\u1ea5u h\u00ecnh my.cnf) v\u00e0 c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c c\u1ea5u h\u00ecnh c\u00e1c th\u00f4ng s\u1ed1 nh\u01b0 k\u00edch th\u01b0\u1edbc t\u1ed1i \u0111a bao nhi\u00eau, l\u01b0u l\u1ea1i tr\u00ean server bao nhi\u00eau ng\u00e0y.<\/li><li>Master DB s\u1ebd m\u1edf m\u1ed9t&nbsp;<code>Dump_Thread<\/code>&nbsp;v\u00e0 g\u1eedi binlog t\u1edbi cho&nbsp;<code>I\/O_Thread<\/code>&nbsp;m\u1ed7i khi&nbsp;<code>I\/O_Thread<\/code>&nbsp;t\u1eeb Slave DB y\u00eau c\u1ea7u d\u1eef li\u1ec7u<\/li><\/ul>\n\n\n\n<p>Tr\u00ean Slave:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Tr\u00ean m\u1ed7i Slave DB s\u1ebd m\u1edf m\u1ed9t&nbsp;<code>I\/O_Thread<\/code>&nbsp;k\u1ebft n\u1ed1i t\u1edbi Master DB th\u00f4ng qua network, giao th\u1ee9c TCP (v\u1edbi MySQL 5.5 replication ch\u1ec9 h\u1ed7 tr\u1ee3&nbsp;<code>Single_Thread<\/code>&nbsp;n\u00ean m\u1ed7i Slave DB s\u1ebd ch\u1ec9 m\u1edf duy nh\u1ea5t m\u1ed9t k\u1ebft n\u1ed1i t\u1edbi Master DB, c\u00e1c phi\u00ean b\u1ea3n sau 5.6, 5.7 h\u1ed7 tr\u1ee3 m\u1edf \u0111\u1ed3ng th\u1eddi nhi\u1ec1u k\u1ebft n\u1ed1i h\u01a1n) \u0111\u1ec3 y\u00eau c\u1ea7u binlog.<\/li><li>Sau khi&nbsp;<code>Dump_Thread<\/code>&nbsp;g\u1eedi binlog t\u1edbi&nbsp;<code>I\/O_Thead<\/code>,&nbsp;<code>I\/O_Thread<\/code>&nbsp;s\u1ebd c\u00f3 nhi\u1ec7m v\u1ee5 \u0111\u1ecdc binlog n\u00e0y v\u00e0 ghi v\u00e0o relaylog.<\/li><li>\u0110\u1ed3ng th\u1eddi tr\u00ean Slave s\u1ebd m\u1edf m\u1ed9t&nbsp;<code>SQL_Thread<\/code>,&nbsp;<code>SQL_Thread<\/code>&nbsp;c\u00f3 nhi\u1ec7m v\u1ee5 \u0111\u1ecdc c\u00e1c event t\u1eeb relaylog v\u00e0 apply c\u00e1c event \u0111\u00f3 v\u00e0o Slave =&gt; qu\u00e1 tr\u00ecnh replication ho\u00e0n th\u00e0nh.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/i.imgur.com\/RLAnddr.jpg\" alt=\"arch\" title=\"\"><\/figure>\n\n\n\n<p>V\u1ec1 logic m\u1ed7i Slave DB s\u1ebd ch\u1ec9 nh\u1eadn d\u1eef li\u1ec7u t\u1eeb Master DB, m\u1ecdi h\u00e0nh \u0111\u1ed9ng c\u1eadp nh\u1eadt d\u1eef li\u1ec7u&nbsp;<strong>B\u1eaeT BU\u1ed8C<\/strong>&nbsp;ph\u1ea3i \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n tr\u00ean Master. V\u1ec1 nguy\u00ean t\u1eafc n\u1ebfu ghi d\u1eef li\u1ec7u tr\u1ef1c ti\u1ebfp l\u00ean Slave DB =&gt; h\u1ecfng replication. Nh\u01b0ng th\u1ef1c ch\u1ea5t ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 ghi d\u1eef li\u1ec7u tr\u00ean Slave mi\u1ec5n sao khi Slave \u0111\u1ecdc binlog v\u00e0 apply kh\u00f4ng \u0111\u1ee5ng g\u00ec t\u1edbi nh\u1eefng tr\u01b0\u1eddng d\u1eef li\u1ec7u m\u00e0 ta m\u1edbi ghi v\u00e0o th\u00ec s\u1ebd kh\u00f4ng b\u1ecb l\u1ed7i (m\u1ee5c n\u00e0y s\u1ebd n\u00f3i th\u00eam \u1edf c\u00e1c ph\u1ea7n sau)<\/p>\n\n\n\n<p>V\u1edbi MySQL 5.5 th\u00ec m\u1ed7i slave s\u1ebd ch\u1ec9 c\u00f3 m\u1ed9t&nbsp;<code>slave_thread<\/code>&nbsp;connect t\u1edbi Master, tuy nhi\u00ean t\u1eeb phi\u00ean b\u1ea3n 5.6 ch\u00fang ta c\u00f3 th\u1ec3 c\u1ea5u h\u00ecnh nhi\u1ec1u&nbsp;<code>slave_thread<\/code>&nbsp;\u0111\u1ec3 vi\u1ec7c apply bin log t\u1edbi c\u00e1c slave nhanh h\u01a1n.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"3-h\u01b0\u1edbng-d\u1eabn-c\u00e0i-\u0111\u1eb7t-v\u00e0-c\u1ea5u-h\u00ecnh\">3. H\u01b0\u1edbng d\u1eabn c\u00e0i \u0111\u1eb7t v\u00e0 c\u1ea5u h\u00ecnh<\/h1>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"m\u00f4-h\u00ecnh\">M\u00f4 h\u00ecnh:<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>Master DB: 172.17.0.1<\/li><li>Slave DB: 172.17.0.2<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"tr\u00ean-master-db\">Tr\u00ean Master DB<\/h3>\n\n\n\n<p>C\u1ea5u h\u00ecnh my.cnf<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>event-scheduler = on\nbind-address = 172.17.0.1\nserver-id = 1\n\nlog-bin\nbinlog-format=row\nbinlog-do-db=dwh_prod\nbinlog-ignore-db=mysql\nbinlog-ignore-db=test\n\nsync_binlog=0\nexpire_logs_days=2\n<\/code><\/pre>\n\n\n\n<p>T\u1ea1o user replication<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'172.16.0.2' IDENTIFIED BY 'p@ssword';\nFLUSH PRIVILEGES;\n<\/code><\/pre>\n\n\n\n<p>T\u1ea1o schema, d\u1eef li\u1ec7u \u0111\u1ec3 test<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE SCHEMA dwh_prod CHARACTER SET utf8 COLLATE utf8_general_ci;\n\nCREATE TABLE tb1 (\n id INT,\n data VARCHAR(100)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n \nCREATE TABLE tb2 (\n id INT,\n data VARCHAR(100)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nSHOW TABLES;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"tr\u00ean-slave-db\">Tr\u00ean Slave DB<\/h3>\n\n\n\n<p>C\u1ea5u h\u00ecnh my.cnf<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>event_scheduler=off\nbind-address = 172.17.0.2\nserver-id=2\n\nlog-bin\nbinlog-format=row\nbinlog-do-db=dwh_prod\nbinlog-ignore-db=mysql\nbinlog-ignore-db=test\n\ntransaction-isolation=read-committed\nsync_binlog=0\nexpire_logs_days=2\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"t\u1ea1o-replication-v\u00e0-ki\u1ec3m-tra\">T\u1ea1o replication v\u00e0 ki\u1ec3m tra<\/h3>\n\n\n\n<p>Nguy\u00ean t\u1eafc khi t\u1ea1o replication l\u00e0 ph\u1ea3i LOCK t\u1ea5t c\u1ea3 c\u00e1c table tr\u00ean Master DB, \u0111\u1ec3 d\u1eef li\u1ec7u kh\u00f4ng thay \u0111\u1ed5i, sau \u0111\u00f3 x\u00e1c \u0111\u1ecbnh binlog v\u00e0 position, 2 th\u00f4ng s\u1ed1 d\u00f9ng \u0111\u1ec3 c\u1ea5u h\u00ecnh tr\u00ean Slave x\u00e1c \u0111\u1ecbnh \u0111o\u1ea1n d\u1eef li\u1ec7u b\u1eaft \u0111\u1ea7u \u0111\u1ed3ng b\u1ed9<\/p>\n\n\n\n<p>Tr\u00ean Master DB<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FLUSH TABLES WITH READ LOCK;\nSHOW MASTER STATUS;\n\n+----------------+----------+--------------+------------------+\n| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+----------------+----------+--------------+------------------+\n| m01-bin.000001 |      827 | dwh_prod     | mysql,test       |\n+----------------+----------+--------------+------------------+\n<\/code><\/pre>\n\n\n\n<p>Gi\u00e1 tr\u1ecb c\u1ea7n quan t\u00e2m l\u00e0<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>m01-bin.000001<\/li><li>827<\/li><\/ul>\n\n\n\n<p>Sau \u0111\u00f3 ta s\u1ebd dump d\u1eef li\u1ec7u t\u1eeb Master DB v\u00e0 \u0111\u1ea9y qua Slave DB (sau khi dump xong c\u00f3 th\u1ec3&nbsp;<code>UNLOCK TABLES;<\/code>&nbsp;\u0111\u1ec3 Master DB c\u00f3 th\u1ec3 ho\u1ea1t \u0111\u1ed9ng l\u1ea1i).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysqldump -uroot -p dwh_prod > dwh_prod_03072015.sql\nrsync -avz -P -e'ssh' dwh_prod_03072015.sql root@172.17.0.2:\/root\/\n<\/code><\/pre>\n\n\n\n<p>Tr\u00ean Slave<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -uroot -p dwh_prod &lt; \/root\/dwh_prod_03072015.sql\n\n> CHANGE MASTER TO MASTER_HOST='172.17.0.1',MASTER_USER='slave_user', MASTER_PASSWORD='p@ssword', MASTER_LOG_FILE='m01-bin.000001', MASTER_LOG_POS=827;\n> START SLAVE\n> SHOW SLAVE STATUS\\G\n<\/code><\/pre>\n\n\n\n<p>M\u1ed9t s\u1ed1 th\u00f4ng tin \u0111\u00e3 \u0111\u01b0\u1ee3c l\u01b0\u1ee3c b\u1ecf cho d\u1ec5 nh\u00ecn<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>*************************** 1. row ***************************\n               Slave_IO_State: Waiting for master to send event\n                  Master_Host: 172.0.0.1\n                  Master_User: slave_user\n              Master_Log_File: m01-bin.000001\n          Read_Master_Log_Pos: 827\n               Relay_Log_File: m02-relay-bin.000002\n                Relay_Log_Pos: 251\n        Relay_Master_Log_File: m01-bin.000001\n                   Last_Errno: 0\n                   Last_Error: \n                 Skip_Counter: 0\n          Exec_Master_Log_Pos: 827\n              Relay_Log_Space: 405\n        Seconds_Behind_Master: 0\n                Last_IO_Errno: 0\n                Last_IO_Error: \n               Last_SQL_Errno: 0\n               Last_SQL_Error: \n             Master_Server_Id: 1\n<\/code><\/pre>\n\n\n\n<p>C\u00e1c th\u00f4ng s\u1ed1 c\u1ea7n quan t\u00e2m l\u00e0<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>Last_Error: 0<\/code><\/li><li><code>Last_SQL_Error<\/code><\/li><li><code>Seconds_Behind_Master: 0<\/code><\/li><\/ul>\n\n\n\n<p>Hai th\u00f4ng s\u1ed1 \u0111\u1ea7u ti\u00ean l\u00e0 l\u1ed7i khi Slave DB th\u1ef1c thi c\u00e1c event \u0111\u1ecdc t\u1eeb relay log. Th\u00f4ng s\u1ed1&nbsp;<code>Seconds_Behind_Master<\/code>&nbsp;cho ta bi\u1ebft d\u1eef li\u1ec7u c\u1ee7a Slave DB \u0111ang b\u1ecb&nbsp;<strong>tr\u1ec5<\/strong>&nbsp;(delay, lag) bao nhi\u00eau gi\u00e2y so v\u1edbi Master DB. C\u00e1c ph\u1ea7n sau ta s\u1ebd n\u00f3i k\u1ef9 h\u01a1n v\u1ec1 replication lag n\u00e0y.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"4-v\u1eadn-h\u00e0nh-h\u1ec7-th\u1ed1ng-mysql-replicatione\">4. V\u1eadn h\u00e0nh h\u1ec7 th\u1ed1ng MySQL Replicatione<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"4-1-test-logic-replication\">4.1 Test logic replication<\/h2>\n\n\n\n<p>\u1ede tr\u1ea1ng th\u00e1i b\u00ecnh th\u01b0\u1eddng d\u1eef li\u1ec7u tr\u00ean Slave DB \u0111\u00e3 \u0111\u1ed3ng b\u1ed9 v\u1edbi Master DB. Ki\u1ec3m tra<\/p>\n\n\n\n<p>Tr\u00ean Master<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> USE dwh_prod\nmysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n+--------------------+\n<\/code><\/pre>\n\n\n\n<p>Tr\u00ean Slave<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> USE dwh_prod\nmysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n+--------------------+\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e -p 'SHOW SLAVE STATUS\\G' | grep -i 'error\\|seconds'\n                   Last_Error: \n        Seconds_Behind_Master: 0\n                Last_IO_Error: \n               Last_SQL_Error:\n<\/code><\/pre>\n\n\n\n<p>M\u1ecdi th\u1ee9 \u0111\u1ec1u \u1ed5n, kh\u00f4ng l\u1ed7i v\u00e0 kh\u00f4ng c\u00f3 Lag.<\/p>\n\n\n\n<p>Gi\u1edd gi\u1ea3 s\u1eed ta s\u1ebd t\u1ea1o m\u1ed9t table v\u1edbi t\u00ean l\u00e0 tb00 tr\u00ean Slave v\u00e0 ki\u1ec3m tra xem c\u00f3 \u0111\u00fang l\u00e0 khi ghi d\u1eef li\u1ec7u l\u00ean Slave DB th\u00ec replication c\u00f3 b\u1ecb h\u1ecfng hay kh\u00f4ng.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> CREATE TABLE tb00 (\n id INT,\n data VARCHAR(100)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nmysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb00               |\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n+--------------------+\n5 rows in set (0.00 sec)\n<\/code><\/pre>\n\n\n\n<p>Ki\u1ec3m tra c\u00e1c table tr\u00ean Master DB<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n+--------------------+\n<\/code><\/pre>\n\n\n\n<p>V\u00e0 ki\u1ec3m tra l\u1ea1i tr\u1ea1ng th\u00e1i c\u1ee7a replication<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e 'SHOW SLAVE STATUS\\G' | grep -i 'error\\|seconds'                                                                                                   \n            Last_Error: \n      Seconds_Behind_Master: 0\n            Last_IO_Error: \n            Last_SQL_Error:\n<\/code><\/pre>\n\n\n\n<p>=&gt; Nh\u01b0 ta th\u1ea5y r\u00f5 r\u00e0ng l\u00e0 d\u1eef li\u1ec7u tr\u00ean Slave v\u00e0 Master \u0111\u00e3 kh\u00e1c nhau (Slave c\u00f3 tb00 nh\u01b0ng Master th\u00ec kh\u00f4ng) nh\u01b0ng tr\u1ea1ng th\u00e1i c\u1ee7a replication v\u1eabn ho\u00e0n to\u00e0n \u1ed5n.<\/p>\n\n\n\n<p>Gi\u1edd ch\u00fang ta s\u1ebd th\u1eed th\u00eam m\u1ed9t tr\u01b0\u1eddng h\u1ee3p n\u1eefa l\u00e0 tr\u00ean Master ta s\u1ebd t\u1ea1o m\u1ed9t table t\u00ean l\u00e0 tb6 \u0111\u1ec3 ki\u1ec3m tra xem chuy\u1ec7n g\u00ec s\u1ebd x\u1ea3y ra<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> CREATE TABLE tb6 (\n id INT,\n data VARCHAR(100)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\nmysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n| tb6                |\n+--------------------+\n<\/code><\/pre>\n\n\n\n<p>Ki\u1ec3m tra c\u00e1c table tr\u00ean Slave DB<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> SHOW TABLES;\n+--------------------+\n| Tables_in_dwh_prod |\n+--------------------+\n| tb00               |\n| tb1                |\n| tb2                |\n| tb3                |\n| tb4                |\n| tb6                |\n+--------------------+\n<\/code><\/pre>\n\n\n\n<p>=&gt; b\u1ea3ng tb6 \u0111\u00e3 \u0111\u01b0\u1ee3c \u0111\u1ed3ng b\u1ed9 t\u1eeb Master qua, ki\u1ec3m tra tr\u1ea1ng th\u00e1i replication<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e 'SHOW SLAVE STATUS\\G' | grep -i 'error\\|seconds'\n                   Last_Error: \n        Seconds_Behind_Master: 0\n                Last_IO_Error: \n               Last_SQL_Error:\n<\/code><\/pre>\n\n\n\n<p>=&gt; M\u1ecdi th\u1ee9 v\u1eabn \u1ed5n, ngh\u0129a l\u00e0 d\u00f9 ta c\u00f3 ghi d\u1eef li\u1ec7u v\u00e0o Slave, nh\u01b0ng n\u1ebfu Master th\u1ef1c thi c\u00e1c c\u00e2u truy v\u1ea5n kh\u00f4ng \u0111\u1ee5ng g\u00ec t\u1edbi d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c ghi m\u1edbi \u1edf Slave th\u00ec tr\u1ea1ng th\u00e1i c\u1ee7a replication v\u1eabn \u1ed5n.<\/p>\n\n\n\n<p>Gi\u1edd ta s\u1ebd th\u1ef1c hi\u1ec7n th\u00eam m\u1ed9t th\u1eed nghi\u1ec7m n\u1eefa l\u00e0 tr\u00ean Master ta t\u1ea1o m\u1ed9t table t\u00ean l\u00e0 tb00, tr\u00f9ng v\u1edbi table \u0111\u00e3 t\u1ea1o l\u00fac tr\u01b0\u1edbc \u1edf Slave ph\u00eda tr\u00ean v\u00e0 ki\u1ec3m tra l\u1ea1i tr\u1ea1ng th\u00e1i c\u1ee7a replication<\/p>\n\n\n\n<p>Ki\u1ec3m tra tr\u1ea1ng th\u00e1i replication tr\u00ean Slave<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql -e 'SHOW SLAVE STATUS\\G' | grep -i 'error\\|seconds'\n              Last_Error: Error 'Table 'tb00' already exists' on query. Default database: 'dwh_prod'. Query: 'CREATE TABLE tb00 (\n   Seconds_Behind_Master: NULL\n           Last_IO_Error:\n          Last_SQL_Error: Error 'Table 'tb00' already exists' on query. Default database: 'dwh_prod'. Query: 'CREATE TABLE tb00 (\n<\/code><\/pre>\n\n\n\n<p>=&gt; nh\u01b0 ta th\u1ea5y h\u1ec7 th\u1ed1ng b\u00e1o l\u1ed7i do tr\u00ean Slave kh\u00f4ng th\u1ec3 th\u1ef1c thi h\u00e0nh \u0111\u1ed9ng t\u1ea1o table tb00 t\u1eeb Master \u0111\u1ea9y xu\u1ed1ng (do table n\u00e0y \u0111\u00e3 t\u1ed3n t\u1ea1i tr\u01b0\u1edbc \u0111\u00f3)<\/p>\n\n\n\n<p>K\u1ebft Lu\u1eadn: Vi\u1ec7c ghi d\u1eef li\u1ec7u v\u00e0o Slave l\u00e0 c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n \u0111\u01b0\u1ee3c, nh\u01b0ng s\u1ebd g\u00e2y ra r\u1ee7i ro h\u1ecfng replication \u1edf m\u1ed9t l\u00fac n\u00e0o \u0111\u00f3. Nh\u1ea5t l\u00e0 c\u00e1c c\u00e2u truy v\u1ea5n d\u1ea1ng SELECT \u2026 UPDATE. T\u1ed1t nh\u1ea5t l\u00e0 n\u00ean tr\u00e1nh ghi d\u1eef li\u1ec7u v\u00e0o Slave<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"4-2-replication-lag\">4.2 Replication Lag<\/h2>\n\n\n\n<p><code>Replication Lag<\/code>&nbsp;l\u00e0 \u0111\u1ed9 tr\u1ec5 d\u1eef li\u1ec7u c\u1ee7a Slave so v\u1edbi Master. Khi tri\u1ec3n khai m\u1ed9t h\u1ec7 th\u1ed1ng MySQL Replication th\u00ec Lag l\u00e0 v\u1ea5n \u0111\u1ec1 ch\u1eafc ch\u1eafn g\u1eb7p ph\u1ea3i. Ta ch\u1ec9 c\u00f3 th\u1ec3 gi\u1ea3m thi\u1ec3u \u0111\u1ed9 tr\u1ec5 d\u1eef li\u1ec7u trong m\u1ee9c ch\u1ea5p nh\u1eadn \u0111\u01b0\u1ee3c ch\u1ee9 kh\u00f4ng th\u1ec3 kh\u00f4ng c\u00f3 lag. L\u00ed do l\u00e0 vi\u1ec7c \u0111\u1ed3ng b\u1ed9 d\u1eef li\u1ec7u l\u00e0 Asynchronous, ngh\u0129a l\u00e0 c\u00e1c Slave server kh\u00f4ng c\u1ea7n th\u00f4ng b\u00e1o cho Master bi\u1ebft khi transaction th\u1ef1c hi\u1ec7n tr\u00ean Slave th\u00e0nh c\u00f4ng -&gt; \u0111i\u1ec1u n\u00e0y gi\u00fap gi\u1eef nguy\u00ean hi\u1ec7u su\u1ea5t (kh\u00e1c v\u1edbi c\u01a1 ch\u1ebf \u0111\u1ed3ng b\u1ed9 synchronous, m\u1ed9t transaction \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 th\u00e0nh c\u00f4ng khi n\u00f3 committed tr\u00ean master server v\u00e0 master server nh\u1eadn \u0111\u01b0\u1ee3c m\u1ed9t th\u00f4ng b\u00e1o t\u1eeb slave server l\u00e0 transaction n\u00e0y \u0111\u00e3 \u0111\u01b0\u1ee3c write v\u00e0 committed. Qu\u00e1 tr\u00ecnh n\u00e0y \u0111\u1ea3m b\u1ea3o t\u00ednh th\u1ed1ng nh\u1ea5t gi\u1eefa master v\u00e0 slave server nh\u01b0ng \u0111\u1ed3ng th\u1eddi n\u00f3 l\u00e0m gi\u1ea3m hi\u1ec7u su\u1ea5t \u0111i m\u1ed9t n\u1eefa do c\u00e1c v\u1ea5n \u0111\u1ec1 v\u1ec1 network, bandwidth, location.)<\/p>\n\n\n\n<p>V\u1ea5n \u0111\u1ec1 c\u1ee7a&nbsp;<code>replication lag<\/code>&nbsp;\u1ea3nh h\u01b0\u1edfng t\u1edbi c\u00e1c truy v\u1ea5n v\u1eeba write d\u1eef li\u1ec7u xu\u1ed1ng l\u00e0 read d\u1eef li\u1ec7u l\u00ean li\u1ec1n. V\u00ed d\u1ee5<\/p>\n\n\n\n<p>M\u1ed9t trang th\u01b0\u01a1ng m\u1ea1i \u0111i\u1ec7n t\u1eed c\u00f3 t\u00ednh n\u0103ng add v\u00e0o gi\u1ecf h\u00e0ng m\u1ed9t s\u1ea3n ph\u1ea9m. Sau khi s\u1ea3n ph\u1ea9m \u0111\u01b0\u1ee3c add v\u00e0o gi\u1ecf h\u00e0ng s\u1ebd tr\u1eeb s\u1ed1 l\u01b0\u1ee3ng trong t\u1ed3n kho. 2 user th\u1ef1c hi\u1ec7n mua s\u1ea3n ph\u1ea9m \u0111\u00f3 (s\u1ea3n ph\u1ea9m \u0111\u00f3 c\u00f3 s\u1ed1 l\u01b0\u1ee3ng t\u1ed3n kho l\u00e0 1). C\u1ea3 2 \u0111\u1ec1u th\u1ea5y s\u1ea3n ph\u1ea9m \u0111\u00f3 tr\u00ean website hi\u1ec3n th\u1ecb tr\u1ea1ng th\u00e1i C\u00d2N H\u00c0NG. Khi m\u1ed9t user mua s\u1ea3n ph\u1ea9m \u0111\u00f3 v\u00e0 thanh to\u00e1n th\u00e0nh c\u00f4ng. Do \u0111\u1ed9 tr\u1ec5 d\u1eef li\u1ec7u (v\u00ed d\u1ee5 5s) n\u00ean d\u1eef li\u1ec7u ch\u01b0a \u0111c c\u1eadp nh\u1eadt t\u1ed3n kho xu\u1ed1ng Slave l\u00e0 s\u1ea3n ph\u1ea9m \u0111\u00e3 h\u1ebft h\u00e0ng. Khi user \u0111\u00f3 add gi\u1ecf h\u00e0ng v\u00e0 thanh to\u00e1n th\u00ec l\u00fac n\u00e0y d\u1eef li\u1ec7u m\u1edbi c\u1eadp nh\u1eadt v\u00e0 tr\u1ea3 v\u1ec1 m\u00e3 l\u1ed7i l\u00e0 thanh to\u00e1n kh\u00f4ng th\u00e0nh c\u00f4ng do s\u1ed1 l\u01b0\u1ee3ng t\u1ed3n kho kh\u00f4ng \u0111\u00e1p \u1ee9ng =&gt; \u1ea3nh h\u01b0\u1edfng t\u1edbi tr\u1ea3i nghi\u1ec7m c\u1ee7a user tr\u00ean h\u1ec7 th\u1ed1ng.<\/p>\n\n\n\n<p>Th\u01b0\u1eddng v\u1edbi nh\u1eefng tr\u01b0\u1eddng h\u1ee3p n\u00e0y (truy v\u1ea5n write xong l\u00e0 read li\u1ec1n) th\u00ec n\u00ean s\u1eed d\u1ee5ng c\u1ea5u h\u00ecnh truy v\u1ea5n tr\u00ean Master (\u0111\u00e2y l\u00e0 l\u00ed do Master c\u00f3 th\u1ec3 v\u1eeba write v\u1eeba read ch\u1ee9 kh\u00f4ng nh\u1ea5t thi\u1ebft l\u00e0 ch\u1ec9 c\u00f3 write)<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"4-3-lb-mysql-v\u00e0-healthchk\">4.3 Lb mysql v\u00e0 healthchk<\/h2>\n\n\n\n<p>Nh\u01b0 1 trong 2 m\u00f4 h\u00ecnh ph\u00eda tr\u00ean th\u00ec v\u1edbi m\u00f4 h\u00ecnh th\u1ee9 2 ta c\u00f3 th\u1ec3 d\u00f9ng haproxy l\u00e0m lb cho c\u00e1c MySQL Instance.<\/p>\n\n\n\n<p>V\u1edbi m\u00f4 h\u00ecnh 1 nh\u01b0\u1ee3c \u0111i\u1ec3m l\u00e0 n\u1ebfu MySQL instance b\u1ecb delay qu\u00e1 l\u00e2u, server qu\u00e1 t\u1ea3i ho\u1eb7c r\u1ee7i ro nh\u1ea5t l\u00e0 instace \u0111\u00f3 b\u1ecb down th\u00ec ta kh\u00f4ng c\u00f3 c\u00e1ch n\u00e0o check ho\u1eb7c remove instance \u0111\u00f3 ra \u0111\u01b0\u1ee3c.<\/p>\n\n\n\n<p>V\u1edbi m\u00f4 h\u00ecnh 2 nh\u01b0\u1ee3c \u0111i\u1ec3m l\u00e0 ta m\u1ea5t th\u00eam 1 layer (haproxy) n\u1eefa m\u1edbi c\u00f3 th\u1ec3 k\u1ebft n\u1ed1i t\u1edbi MySQL (t\u1ed1n th\u1eddi gian, x\u1eed l\u00ed nhi\u1ec1u l\u1edbp) nh\u01b0ng l\u1ee3i \u0111i\u1ec3m l\u00e0 c\u00f3 th\u1ec3 c\u1ea5u h\u00ecnh healthchk, ho\u1eb7c remove instance theo m\u1ed9t s\u1ed1 \u0111i\u1ec1u ki\u1ec7n.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"5-m\u1ed9t-s\u1ed1-l\u01b0u-\u00fd\">5. M\u1ed9t s\u1ed1 l\u01b0u \u00fd<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"5-1-v\u1ea5n-\u0111\u1ec1-v\u1ec1-server-ph\u1ea7n-c\u1ee9ng\">5.1 V\u1ea5n \u0111\u1ec1 v\u1ec1 server, ph\u1ea7n c\u1ee9ng<\/h2>\n\n\n\n<p>C\u00e1c v\u1ea5n \u0111\u1ec1 v\u1ec1 CPU, RAM, \u0111\u0129a c\u1ee9ng (k\u00edch th\u01b0\u1edbc, lo\u1ea1i \u0111\u0129a c\u1ee9ng, SSD hay HDD, t\u1ed1c \u0111\u1ed9 \u0111\u1ecdc ghi c\u1ee7a \u0111\u0129a c\u1ee9ng)<\/p>\n\n\n\n<p>V\u1edbi m\u1ed9t h\u1ec7 th\u1ed1ng DB c\u00e1c th\u00f4ng s\u1ed1 ph\u1ea7n c\u1ee9ng N\u00caN quan t\u00e2m l\u00e0<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>CPU: C\u00e0ng nhi\u1ec1u core c\u00e0ng t\u1ed1t, t\u1ed1c \u0111\u1ed9 c\u00e0ng nhanh c\u00e0ng t\u1ed1t<\/li><li>RAM: RAM c\u00e0ng nhi\u1ec1u c\u00e0ng t\u1ed1t<\/li><\/ul>\n\n\n\n<p>V\u1edbi \u0111\u0129a c\u1ee9ng<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>N\u00ean s\u1eed d\u1ee5ng RAID 5, 6, 10<\/li><li>N\u00ean s\u1eed d\u1ee5ng SSD (Enterprise th\u00ec c\u00e0ng t\u1ed1t) IOPS c\u00e0ng cao c\u00e0ng t\u1ed1t<\/li><li>\u0110\u0129a c\u1ee9ng n\u00ean c\u00f3 dung l\u01b0\u1ee3ng \u00edt nh\u1ea5t l\u00e0 x2 l\u1ea7n dung l\u01b0\u1ee3ng c\u1ee7a CSDL (s\u1ebd c\u1ea7n thi\u1ebft trong tr\u01b0\u1eddng h\u1ee3p dump, backup d\u1eef li\u1ec7u \u0111\u1ec3 fix replication)<\/li><\/ul>\n\n\n\n<p>Kh\u00e1c v\u1edbi c\u00e1c \u1ee9ng d\u1ee5ng kh\u00e1c nh\u01b0 web, static (th\u01b0\u1eddng CPU kh\u00f4ng c\u1ea7n nhi\u1ec1u core, \u0111\u0129a c\u1ee9ng kh\u00f4ng c\u1ea7n nhi\u1ec1u v\u00e0 nhanh), m\u00e1y ch\u1ee7 CSDL s\u1ebd c\u1ea7n nhi\u1ec1u c\u00e1c th\u00f4ng s\u1ed1 tr\u00ean<\/p>\n\n\n\n<p>V\u1edbi AWS khi ch\u1ecdn Instance c\u0169ng n\u00ean ch\u00fa \u00fd c\u00e1c \u0111i\u1ec3m tr\u00ean<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"5-2-c\u00e1c-v\u1ea5n-\u0111\u1ec1-v\u1ec1-k\u00edch-th\u01b0\u1edbc-d\u1eef-li\u1ec7u\">5.2 C\u00e1c v\u1ea5n \u0111\u1ec1 v\u1ec1 k\u00edch th\u01b0\u1edbc d\u1eef li\u1ec7u<\/h2>\n\n\n\n<p>V\u1ea5n \u0111\u1ec1 k\u00edch th\u01b0\u1edbc d\u1eef li\u1ec7u \u1ea3nh h\u01b0\u1edfng kh\u00e1 nhi\u1ec1u \u0111\u1ebfn v\u1eadn h\u00e0nh m\u1ed9t h\u1ec7 th\u1ed1ng MySQL Replication. D\u1eef li\u1ec7u l\u1edbn th\u00ec qu\u00e1 tr\u00ecnh replication \u0111\u1ea7u ti\u00ean ho\u1eb7c khi h\u1ecfng replication s\u1ebd r\u1ea5t l\u00e2u =&gt; Slave kh\u00f4ng th\u1ec3 s\u1eed d\u1ee5ng \u0111\u01b0\u1ee3c trong th\u1eddi gian replication, \u0111\u1ebfn khi&nbsp;<code>Second_Behind_Master<\/code>&nbsp;= 0 th\u00ec m\u1edbi c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng \u0111\u01b0\u1ee3c.<\/p>\n\n\n\n<p>Ngo\u00e0i ra c\u00e1c y\u1ebfu t\u1ed1 v\u1ec1 \u1ed5 \u0111\u0129a c\u1ee9ng (SSD, t\u1ed1c \u0111\u1ed9 \u0111\u1ecdc ghi) c\u0169ng \u1ea3nh h\u01b0\u1edfng nhi\u1ec1u \u0111\u1ebfn vi\u1ec7c import ho\u1eb7c apply c\u00e1c binlog t\u1eeb Master<\/p>\n\n\n\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t m\u00f4 t\u1ea3 th\u1ef1c t\u1ebf:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>D\u1eef li\u1ec7u th\u00f4 \/var\/lib\/mysql c\u00f3 k\u00edch th\u01b0\u1edbc 80-100GB<\/li><li>D\u1eef li\u1ec7u dump ra ch\u01b0a n\u00e9n 18-30GB<\/li><li>D\u1eef li\u1ec7u n\u00e9n b\u1eb1ng chu\u1ea9n tgz ~ 2-3GB<\/li><li>M\u00e1y ch\u1ee7 24 core, 32GB RAM, SSD Plextor M6 PRO (4&#215;256, RAID 10)<\/li><li>Th\u1eddi gian dump d\u1eef li\u1ec7u l\u00e0 1h-1h30<\/li><li>Th\u1eddi gian sync b\u1ea3n dump qua c\u00e1c server (local, port 1Gb) ~ 1h<\/li><li>Th\u1eddi gian import d\u1eef li\u1ec7u ~1.5h<\/li><li>Th\u1eddi gian&nbsp;<code>Second_Behind_Master<\/code>&nbsp;sau khi import xong ~ 3600s<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"6-failover\">6. Failover<\/h2>\n\n\n\n<p>M\u1ed9t v\u1ea5n \u0111\u1ec1 kh\u00e1c ngo\u00e0i chuy\u1ec7n scale \u0111\u00f3 l\u00e0&nbsp;<strong>n\u1ebfu master db ch\u1ebft th\u00ec chuy\u1ec7n g\u00ec x\u1ea3y ra?<\/strong>. C\u00f3 m\u1ed9t s\u1ed1 mindset m\u00e0 b\u1ea1n b\u1eaft bu\u1ed9c ph\u1ea3i hi\u1ec3u khi ch\u1ecdn gi\u1ea3i ph\u00e1p replication master-slave \u0111\u00f3 l\u00e0:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Qu\u00e1 tr\u00ecnh promote 1 Slave thay th\u1ebf Master l\u00e0 th\u1ee7 c\u00f4ng, kh\u00f4ng th\u1ec3 t\u1ef1 \u0111\u1ed9ng switch sang slave m\u00e0 h\u1ec7 th\u1ed1ng kh\u00f4ng c\u00f3 v\u1ea5n \u0111\u1ec1 g\u00ec.<\/li><li>V\u1eabn s\u1ebd c\u00f3 downtime n\u1ebfu master db ch\u1ebft, tuy nhi\u00ean vi\u1ec7c d\u00f9ng slave \u0111\u1ea3m b\u1ea3o th\u1eddi gian downtime t\u1ed1i thi\u1ec3u nh\u1ea5t c\u00f3 th\u1ec3.<\/li><\/ul>\n\n\n\n<p>Quay tr\u1edf l\u1ea1i m\u00f4 h\u00ecnh 1 master v\u00e0 2 slave (g\u1ecdi l\u1ea7n l\u01b0\u1ee3t l\u00e0 S1 v\u00e0 S2), ta c\u1ea7n tr\u1ea3 l\u1eddi l\u00e0 n\u1ebfu master ch\u1ebft th\u00ec chuy\u1ec7n g\u00ec x\u1ea3y ra v\u1edbi h\u1ec7 th\u1ed1ng v\u00e0 c\u00e1ch promote m\u1ed9t slave l\u00ean thay th\u1ebf master l\u00e0 g\u00ec?<\/p>\n\n\n\n<p>M\u1eb7c \u0111\u1ecbnh, Slave v\u1eabn s\u1ebd c\u00f3 binlog, v\u00e0 binlog n\u00e0y l\u00e0 c\u1ee7a ri\u00eang slave ch\u1ee9 kh\u00f4ng gi\u1ed1ng v\u1edbi binlog c\u1ee7a master (binlog c\u1ee7a master khi \u0111\u1ea9y qua slave s\u1ebd th\u00e0nh relay-log), c\u00f3 ngh\u0129a l\u00e0 n\u1ebfu S1 \u0111\u1ea9y l\u00ean l\u00e0m master th\u00ec S2 s\u1ebd kh\u00f4ng c\u00f2n \u0111\u1ed3ng b\u1ed9 v\u1edbi S1 n\u1eefa v\u00e0 ta s\u1ebd c\u1ea7n build l\u1ea1i S2.<\/p>\n\n\n\n<p>\u0110\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y, mysql khuy\u1ebfn c\u00e1o ch\u00fang ta b\u1eadt&nbsp;<code>--skip-log-slave-updates<\/code>&nbsp;tr\u00ean Slave, chuy\u1ec7n n\u00e0y \u0111\u1ea3m b\u1ea3o:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Slave v\u1eabn s\u1ebd c\u00f3 binlog nh\u01b0ng v\u1edbi c\u00e1c h\u00e0nh vi apply relay-log (update d\u1eef li\u1ec7u nh\u01b0 master) th\u00ec slave s\u1ebd kh\u00f4ng ghi ra binlog.<\/li><li>Khi master ch\u1ebft, ta c\u00f3 nhu c\u1ea7u promote S1 l\u00ean l\u00e0m master, ta s\u1ebd c\u1ea7n reset master c\u1ee7a S2 tr\u1ecf v\u1ec1 S1, tuy nhi\u00ean nh\u01b0 \u1edf tr\u00ean ta s\u1ebd c\u1ea7n ch\u1ec9 \u0111\u1ecbnh file binlog v\u00e0 position c\u1ee7a file log, v\u00e0 do S1 sau khi \u0111c \u0111\u1ed5i th\u00e0nh master th\u00ec m\u1edbi b\u1eaft \u0111\u1ea7u sinh ra binlog, n\u00ean tr\u00ean S2 ta ch\u1ec9 c\u1ea7n tr\u1ecf v\u1ec1 file binlog v\u00e0 position \u0111\u1ea7u ti\u00ean c\u1ee7a S2 l\u00e0 \u0111\u1ee7. =&gt; chuy\u1ec7n n\u00e0y \u0111\u1ea3m b\u1ea3o r\u1eb1ng S2 s\u1ebd \u0111\u1ed3ng b\u1ed9 d\u1eef li\u1ec7u v\u1edbi S1.<\/li><\/ul>\n\n\n\n<p>Sau khi vi\u1ec7c promote ho\u00e0n th\u00e0nh, ta c\u00f3 th\u1ec3 c\u1eadp nh\u1eadt l\u1ea1i \u1edf ph\u00eda client \u0111\u1ecba ch\u1ec9 c\u1ee7 a S1 v\u00e0 ho\u00e0n th\u00e0nh vi\u1ec7c b\u1ea3o tr\u00ec h\u1ec7 th\u1ed1ng. Tuy nhi\u00ean \u0111\u1ec3 \u00fd l\u00e0 qu\u00e1 tr\u00ecnh tr\u00ean l\u00e0 th\u1ee7 c\u00f4ng v\u00e0 ta v\u1eabn c\u00f3 downtime trong qu\u00e1 tr\u00ecnh promote.<\/p>\n\n\n\n<p>Tuy nhi\u00ean, \u0111i\u1ec1u tr\u00ean ch\u1ec9 \u0111\u00fang ch\u1ec9 \u0111\u00fang khi slave sync v\u1edbi master tr\u01b0\u1edbc khi master ch\u1ebft v\u1edbi&nbsp;<code>second_behind_master = 0<\/code>.<\/p>\n\n\n\n<figure class=\"wp-block-embed\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication-solutions-switch.html\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"7-semi-synchronous\">7. Semi-synchronous<\/h2>\n\n\n\n<p>C\u00f3 m\u1ed9t v\u1ea5n \u0111\u1ec1 v\u1edbi asynchronous \u0111\u00f3 l\u00e0 n\u1ebfu b\u1ea1n c\u00f3 nhu c\u1ea7u \u0111\u1ecdc ngay d\u1eef li\u1ec7u v\u1eeba ghi xu\u1ed1ng th\u00ec c\u00f3 th\u1ec3 d\u1eef li\u1ec7u s\u1ebd sai, do slave ch\u01b0a k\u1ecbp apply d\u1eef li\u1ec7u t\u1eeb master (lag d\u1eef li\u1ec7u), c\u00f3 2 c\u00e1ch gi\u1ea3i quy\u1ebft t\u1ea1m:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>V\u1edbi tr\u01b0\u1eddng h\u1ee3p v\u1eeba ghi v\u00e0 \u0111\u1ecdc li\u1ec1n d\u1eef li\u1ec7u, ta n\u00ean d\u00f9ng \u1edf master.<\/li><li>D\u00f9ng c\u01a1 ch\u1ebf semi-synchronous \u0111\u1ec3 gi\u1ea3m lag d\u1eef li\u1ec7u.<\/li><\/ul>\n\n\n\n<p>Semi-synchronous l\u00e0 m\u1ed9t ki\u1ec3u lai gi\u1eefa asynchronous v\u00e0 synchronous. B\u00ecnh th\u01b0\u1eddng n\u1ebfu x\u00e0i synchronous th\u00ec c\u00e0ng nhi\u1ec1u slave th\u00ec c\u00e0ng gi\u1ea3m t\u1ed1c \u0111\u1ed9 ghi d\u1eef li\u1ec7u, do slave ph\u1ea3i committed v\u00e0 tr\u1ea3 l\u1eddi ng\u01b0\u1ee3c v\u1ec1 master. V\u1edbi semi-synchronous th\u00ec master coi nh\u01b0 ghi th\u00e0nh c\u00f4ng l\u00e0 khi c\u00f3 t\u1ed1i thi\u1ec3u m\u1ed9t slave&nbsp;<strong>\u0111\u00e3 nh\u1eadn<\/strong>&nbsp;v\u00e0&nbsp;<strong>ghi ra relay log<\/strong>&nbsp;event m\u00e0 master g\u1eedi qua. \u0110i\u1ec3m kh\u00e1c bi\u1ec7t l\u00e0 kh\u00f4ng c\u1ea7n t\u1ea5t c\u1ea3 c\u00e1c slave g\u1eedi t\u00edn hi\u1ec7u ng\u01b0\u1ee3c l\u1ea1i master, v\u00e0 event c\u0169ng kh\u00f4ng b\u1eaft bu\u1ed9c ph\u1ea3i \u0111\u01b0\u1ee3c execute v\u00e0 commited tr\u00ean slave, ch\u1ec9 c\u1ea7n \u0111\u1ea3m b\u1ea3o l\u00e0 \u0111\u00e3 nh\u1eadn v\u00e0 ghi ra relay log l\u00e0 \u0111\u1ee7.<\/p>\n\n\n\n<p>Nh\u01b0 m\u00f4 t\u1ea3 \u1edf tr\u00ean th\u00ec slave v\u1eabn c\u00f3 th\u1ec3 kh\u00f4ng c\u00f3 d\u1eef li\u1ec7u n\u1ebfu relay log b\u1ecb t\u00e1c \u0111\u1ed9ng v\u1edbi con ng\u01b0\u1eddi, ho\u1eb7c server b\u1ecb h\u1ecfng ngay khi ch\u01b0a k\u1ecbp apply relay log, tuy nhi\u00ean nh\u1edd vi\u1ec7c \u0111\u1ea3m b\u1ea3o binlog event \u0111\u00e3 \u0111c nh\u1eadn v\u1edbi slave v\u00e0 ghi xu\u1ed1ng \u0111\u0129a \u0111\u00e3 l\u00e0m gi\u1ea3m th\u1eddi gian delay v\u00e0 v\u1ea5n \u0111\u1ec1 v\u1ec1 data race condition c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c h\u1ea1n ch\u1ebf ph\u1ea7n n\u00e0o.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. Gi\u1edbi thi\u1ec7u Right tool for right job. Tr\u01b0\u1edbc ti\u00ean ph\u1ea3i hi\u1ec3u l\u00e0 MySQL Replication kh\u00f4ng ph\u1ea3i l\u00e0 gi\u1ea3i ph\u00e1p gi\u1ea3i quy\u1ebft m\u1ecdi b\u00e0i to\u00e1n v\u1ec1 qu\u00e1 t\u1ea3i h\u1ec7 th\u1ed1ng c\u01a1 s\u1edf d\u1eef li\u1ec7u. \u0110\u1ec3&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-539","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts\/539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/comments?post=539"}],"version-history":[{"count":1,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts\/539\/revisions"}],"predecessor-version":[{"id":540,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/posts\/539\/revisions\/540"}],"wp:attachment":[{"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/media?parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/categories?post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.asianux.org.vn\/index.php\/wp-json\/wp\/v2\/tags?post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}