Mac 下 利用 docker 構建 MySQL 叢集

alalala發表於2019-10-21
MacDockerMySql

下載映象

MySql映象版本 ------ 8.0

docker pull mysql:8.0

新建掛載目錄以及掛載的配置檔案

用於主機跟容器之間資料的互通

mkdir /tmp/mysql_data/master
mkdir /tmp/mysql_data/slave_cnf
mkdir /tmp/mysql_data/master
mkdir /tmp/mysql_data/slave_cnf

編輯主節點的配置檔案

~ touch /tmp/mysql_data/master_cnf/master.cnf
~ vim /tmp/mysql_data/master_cnf/master.cnf
[mysqld]
server_id=1
skip-host-cache
skip-name-resolve

編輯從節點的配置檔案

~ touch /tmp/mysql_data/slave_cnf/slave.cnf
~ vim /tmp/mysql_data/slave_cnf/slave.cnf
[mysqld]
server_id=2
skip-host-cache
skip-name-resolve

兩個檔案主要內容的差異是 server_id 值的不同，必須設定，否則在配置從庫時，會報錯：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

報錯內容提示：slave 節點I/O執行緒停止執行，原因是 master 和 slave 節點的 server_id 衝突。

構建兩個 MySql 容器

master

master 節點執行在主機的 3307 埠

~  docker run -d -e MYSQL_ROOT_PASSWORD=123456 --name master --restart always -v /tmp/mysql_data/master:/var/lib/mysql -v /tmp/mysql_data/master_cnf:/etc/mysql/conf.d -p 3307:3306 mysql:8.0
ba987e79856a14cadb2fd195ca9d8912e81a4f51ae30e5c762aba664aa5e4c97
  1. -v /tmp/mysql_data/master:/var/lib/mysql 將主機的 /tmp/mysql_data/master 目錄掛載到 master 容器中 /var/lib/mysql目錄上。
  2. -v /tmp/mysql_data/master_cnf:/etc/mysql/conf.d 將主機中的 /tmp/mysql_data/master_cnf 路徑下的 mysql 配置檔案掛載到 /etc/mysql/conf.d 目錄中。

slave

master 節點執行在主機的 3308 埠

~  docker run -d -e MYSQL_ROOT_PASSWORD=123456 --name slave --restart always -v /tmp/mysql_data/slave:/var/lib/mysql -v /tmp/mysql_data/slave_cnf:/etc/mysql/conf.d -p 3308:3306 mysql:8.0
3d7227e0db499f8d7e615ee6bea77e623e2f3b2bc45347d832282bc4da2aca40
  1. -v /tmp/mysql_data/slave:/var/lib/mysql 將主機的 /tmp/mysql_data/slave 目錄掛載到 slave 容器中 /var/lib/mysql目錄上。
  2. -v /tmp/mysql_data/slave_cnf:/etc/mysql/conf.d 將主機中的 /tmp/mysql_data/slave_cnf 路徑下的 mysql 配置檔案掛載到 /etc/mysql/conf.d 目錄中。

檢視是否容器狀態

~  docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
3d7227e0db49        mysql:8.0           "docker-entrypoint.s…"   15 minutes ago      Up 15 minutes       33060/tcp, 0.0.0.0:3308->3306/tcp   slave
ba987e79856a        mysql:8.0           "docker-entrypoint.s…"   16 minutes ago      Up 16 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   master

運轉正常

  • master 節點的 CONTAINER ID 為 ba987e79856a
  • slave 節點的 CONTAINER ID 為 3d7227e0db49

Master 節點

檢驗是否配置成功

進入容器內部：

docker exec -it ba98 /bin/bash

檢視 MySql 配置：

/# ls /etc/mysql/conf.d/
master.cnf
/# cat /etc/mysql/conf.d/master.cnf
[mysqld]
server_id=1
skip-host-cache
skip-name-resolve

master 配置檔案掛載成功。

登入並配置 master 節點

 ~  mysql -uroot -h127.0.0.1 -P 3307 -p

建立使用者

  1. 建立使用者，identified with 'mysql_native_password'表示使用該使用者的密碼加密方式。
  2. 賦予使用者相應的許可權；
  3. 重新整理 MySql 系統許可權。
mysql> create user 'reader'@'%' identified with 'mysql_native_password' by 'reader';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'reader'@'%'
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

檢視 master 節點的狀態

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      818 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave 節點

配置 Slave 節點

mysql> CHANGE MASTER TO MASTER_HOST='192.168.25.228',Master_Port=3307,MASTER_USER='reader',MASTER_PASSWORD='reader',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=0;
Query OK, 0 rows affected, 1 warning (0.04 sec)
  • MASTER_HOST：主庫的 IP 地址，透過 ifconfig 檢視
  • MASTER_USER：指定的使用者名稱：此處為 reader
  • MASTER_PASSWORD：指定使用者名稱的密碼
  • MASTER_LOG_FILE：從庫需要從主庫上備份的檔名稱
  • MASTER_LOG_POS：從庫開始備份的位置，此時設定為0，也就是從初始位置開始備份

開啟主從同步

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

檢視從庫狀態

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.25.228
                  Master_User: reader
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1239
               Relay_Log_File: 8634c5ebfdf9-relay-bin.000003
                Relay_Log_Pos: 319
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1239
              Relay_Log_Space: 1826
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 64845198-f3eb-11e9-9969-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified

可見 ：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave 節點運轉正常。

測試

在 Master 節點上執行

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> create table test (Id int primary key auto_increment, Name varchar(18))
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(18) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Master 節點上測試資料寫入成功。

在 Slave 節點上檢測是否同步成功

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(18) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Slave 節點資料同步成功。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章