Docker-Compose實現(xiàn)Mysql主從

Docker-Compose實現(xiàn)Mysql主從

我從cnaaa.com購買了云服務(wù)器。

1. 簡介

通過使用docker-compose搭建一個主從數(shù)據(jù)庫，本示例為了解耦 將兩個server拆分到了兩個compose文件中，當(dāng)然也可以放到一個compose文件中

演示mysql版本：5.7.16

2. 部署流程

master節(jié)點：

  1. 安裝mysql-server

  2. 修改配置

  3. 創(chuàng)建用于同步的賬號并授權(quán)

  4. 檢查相關(guān)配置

slave節(jié)點：

  1. 安裝mysql-server

  2. 修改配置

  3. 選擇主節(jié)點

  4. 檢查相關(guān)配置并驗證同步功能

3. master節(jié)點

3.1 安裝mysql

  1. 創(chuàng)建mysql文件夾并進(jìn)入文件夾（文件夾名稱mysql）

  2. 創(chuàng)建docker-compose文件內(nèi)容如下

    <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n34" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;"># docker-compose.yml
    version: '3'
    services:
    mysql:
    restart: "no"
    image: mysql:5.7.16
    container_name: mysql-master
    volumes:

    • ./datadir:/var/lib/mysql
    • ./conf/mysql:/etc/mysql
      environment:
    • "MYSQL_ROOT_PASSWORD=123456"
    • "TZ=Asia/Shanghai"
      ports:
    • 3306:3306
      networks:
    • mysql-net
      networks:
      mysql-net:
      driver: bridge</pre>

    注意：因為要把配置文件掛在到服務(wù)中去，所以要先把容器中的配置文件copy到宿主機(jī)上

    1. 先啟動一個用于copy文件的容器

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n40" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ docker run --name mysql-temp -e MYSQL_ROOT_PASSWORD=root --rm -d mysql:5.7.16</pre>

    2. 將mysql-temp容器中的配置文件copy出來，現(xiàn)在conf文件夾中就是mysql自帶的所有配置文件

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n44" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ docker cp mysql-temp:/etc/mysql conf</pre>

      [圖片上傳失敗...(image-6861e-1664289286258)]

    3. 因為當(dāng)前conf目錄中的my.cnf還是個link，所以直接使用當(dāng)前目錄中的備份文件作為主要的配置文件

      <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n49" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">$ mv my.cnf.fallback my.cnf</pre>

  3. 修改配置文件my.cnf

    在文件的最下方加入配置信息

    <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n54" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">[mysqld]
    log-bin=mysql-bin # 開啟 binlog
    server-id=1 # 當(dāng)前server在cluster中的id，必須保證在cluster中唯一

    只保留7天的二進(jìn)制日志，以防磁盤被日志占滿(可選)

    expire-logs-days = 7

    不備份的數(shù)據(jù)庫 （可選）

    binlog-ignore-db=information_schema
    binlog-ignore-db=performation_schema
    binlog-ignore-db=sys</pre>

  4. 啟動mysql服務(wù)，通過輸出內(nèi)容得知真實的網(wǎng)絡(luò)名稱為mysql_mysql-net，也就是當(dāng)前所在文件夾的名稱拼接了文件中指定的網(wǎng)絡(luò)名稱

    [圖片上傳失敗...(image-4f2637-1664289286259)]

  5. 服務(wù)啟動完畢后，創(chuàng)建用于同步的用戶并授權(quán)

    創(chuàng)建的用戶名稱為slave密碼為123456

    <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n62" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">CREATE USER 'slave' @'%' IDENTIFIED BY '123456';
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'slave' @'%';

    刷新權(quán)限

    FLUSH PRIVILEGES;</pre>

  6. 查看master狀態(tài)信息

    <pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n66" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit;">SHOW MASTER STATUS;

    查看Mater數(shù)據(jù)有哪些slave

    select * from information_schema.processlist as p where p.command = 'Binlog Dump'; </pre>

    [圖片上傳失敗...(image-bc9842-1664289286259)]

4. slave節(jié)點

安裝步驟同master相同，只把需要修改的展示一下，當(dāng)前的目錄結(jié)構(gòu)如下

[圖片上傳失敗...(image-f521bb-1664289286262)]

docker-compose.yaml 主要修改了網(wǎng)絡(luò)相關(guān)的信息和container_name（網(wǎng)絡(luò)名稱上面有解釋）

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n73" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">version: '3'
services:
mysql:
restart: "no"
image: mysql:5.7.16
container_name: mysql-slave
volumes:

  • ./datadir:/var/lib/mysql
  • ./conf:/etc/mysql
    environment:
  • "MYSQL_ROOT_PASSWORD=123456"
  • "TZ=Asia/Shanghai"
    ports:
  • 3307:3306
    networks:
  • mysql_mysql-net
    networks:
    mysql_mysql-net:
    external: true # 來自外部</pre>

my.cnf添加的內(nèi)容如下：

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n76" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">[mysqld]
server-id=2
relay_log=relay-log

開啟只讀 意味著當(dāng)前的數(shù)據(jù)庫用作讀，當(dāng)然這也只會影響到非root的用戶，如果使用root用戶操作本庫是不會有影響的

read_only=ON</pre>

設(shè)置完成后啟動salve server，連接slave并關(guān)聯(lián)master節(jié)點

  • MASTER_HOST：直接使用container_name

  • MASTER_LOG_FILE/MASTER_LOG_POS：直接使用安裝master步驟中的最后一步的值，其實就是指定同步的bin-log文件名稱和Offset

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n84" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;</pre>

管理完成后 啟動salve

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n87" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">START SLAVE;</pre>

最后查看slave status

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n90" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">SHOW SLAVE STATUS;</pre>

[圖片上傳失敗...(image-343d70-1664289286261)]

5. 驗證

在master上創(chuàng)建test數(shù)據(jù)庫并創(chuàng)建user表，刷新查看salve庫，出現(xiàn)了對應(yīng)的庫表

[圖片上傳失敗...(image-6e4fa-1664289286261)]

經(jīng)驗證數(shù)據(jù)同步也沒有問題。

6. 可能遇到的問題

SHOW SLAVE STATUS時發(fā)現(xiàn)slave_io_running=No salve_sql_running=No，可能的原因有很多，可以查看如下的字段中輸出的內(nèi)容

[圖片上傳失敗...(image-43d64b-1664289286261)]

可能的原因：

  1. 主從網(wǎng)絡(luò)不通

  2. 兩臺節(jié)點的server-id重復(fù)，直接修改對應(yīng)的id即可

  3. 數(shù)據(jù)庫的uuid相同（可能是因為數(shù)據(jù)庫文件是直接copy過來的導(dǎo)致的），在對應(yīng)的庫下生成不同于master的uuid到auto.cnf中即可

  4. sql執(zhí)行失敗，可能是slave剛添加進(jìn)來，也沒有master庫的數(shù)據(jù)庫instance，導(dǎo)致操作對應(yīng)的庫時slave這邊根本沒有對應(yīng)的instance或者table又或是記錄，引發(fā)的報錯。這個只能具體問題具體解決了

  5. master和slave的MASTER_LOG_FILE/MASTER_LOG_POS值設(shè)置的有問題，在slave節(jié)點上STOP SLAVE;然后重新連接下master即可

7. 同步部分?jǐn)?shù)據(jù)庫實例或表

在master節(jié)點上添加配置【可選】（如果只希望從庫讀取到部分實例）

在my.cnf文件中加入如下配置

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n115" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">#需要同步的數(shù)據(jù)庫名 有多個庫添加多行即可
binlog-do-db=test
binlog-do-db=test1

排除的數(shù)據(jù)庫

binlog-ignore-db=sys</pre>

salve端：在my.cnf文件中加入如下配置，這樣的話salve只會讀取配置的db或table，master對其他db的操作也不會影響slave

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n118" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">#如果salve庫名稱與master庫名相同，使用本配置
replicate-do-db=test

如果master庫名[test]與salve庫名[test001]不同，使用以下配置[需要做映射]

replicate-rewrite-db = test -> test001

如果不是要全部同步[默認(rèn)全部同步]，則指定需要同步的表

replicate-wild-do-table=test.user

replicate-wild-do-table=test.role</pre>

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容