antony@notes:~/data-platform$ cat "MySQL-CDC-Change-Data-Capture.md"

MySQL_CDC (Change Data Capture)

2023-08-24· data-platform ·MySQL

MySQL_CDC (Change Data Capture)

[TOC]

在 Alpine Linux 安裝 MySQL 本尊

$ sudo apk update

$ sudo apk upgrade

$ sudo apk add mysql mysql-client

修改 bash script: mysql_install_db


## 確認 mysql_install_db 的位置
$ which mysql_install_db
/usr/bin/mysql_install_db

$ echo $PATH
/home/bigred/bin:/home/bigred/dt/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

前面的權限是777
$ ls -al /usr/bin/mysql_install_db
lrwxrwxrwx 1 root root 18 Jun  1 20:45 /usr/bin/mysql_install_db -> mariadb-install-db

$ which mariadb-install-db
/usr/bin/mariadb-install-db

$ ls -al /usr/bin/mariadb-install-db
-rwxr-xr-x 1 root root 22030 May 21 23:35 /usr/bin/mariadb-install-db


$ sudo nano /usr/bin/mysql_install_db
…
if false
# if test -n "$user“     
then
  if test -z "$srcdir" -a "$in_rpm" -eq 0
…

第 492 行

注意/usr/bin/mysql_install_db它是軟連結，不能亂改它的名字

在 Alpine Linux 創建一般使用者帳號

$ sudo adduser -s /bin/bash -h /home/rbean -D rbean

$ echo -e "rbean\nrbean\n" | sudo passwd rbean

$ exit

建立 MySQL 分身

## 登入使用帳號 rbean

$ mkdir /home/rbean/{lib,my.cnf.d,mysqld}

$ mkdir ~/lib/mysqla

$ tree .
.
├── lib
│   └── mysqla
├── my.cnf.d
└── mysqld

$ cp /etc/my.cnf.d/mariadb-server.cnf ~/my.cnf.d/mariadb-servera.cnf

編輯MySQL 分身設定檔

:::success

$ nano ~/my.cnf.d/mariadb-servera.cnf
......
[mysqld]
skip-networking=0
user=rbean
pid-file=/home/rbean/mysqld/mysqlda.pid
socket=/home/rbean/mysqld/mysqlda.sock
port=3311
datadir=/home/rbean/lib/mysqla
character-set-client-handshake=FALSE
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server=utf8mb4

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
......

skip-networking=0 ，允許使用者透過 Internet Domain Socket 登入，其實就是遠端連線
user=rbean ，指定 mysql 的使用者 rbean
pid-file=/home/rbean/mysqld/mysqlda.pid ，避免重複啟用 mysql
socket=/home/rbean/mysqld/mysqlda.sock ，設定分身的 Unix Domain Socket 的位置
port=3311 ，本尊 port 號：3306 ，分身 port 號：3311 ，其他分身 3312 ，依此類推
datadir=/home/rbean/lib/mysqla ， mysql 運作儲存的目錄
character-set-client-handshake=FALSE ，不使用系統預設編碼 utf8mb4_general_ci
collation-server=utf8mb4_unicode_ci ，使用指定的編碼 utf8mb4_unicode_ci :::

建立 MySQL 分身

命令

$ mysql_install_db --user=rbean --datadir=/home/rbean/lib/mysqla/

命令結果：

Installing MariaDB/MySQL system tables in '/home/rbean/lib/mysqla/' ...
OK
...以下省略

啟用 MySQL 分身

命令：

$ mysqld_safe --defaults-file=/home/rbean/my.cnf.d/mariadb-servera.cnf &

命令結果：

[1] 8267
rbean@alp:~$ 220602 10:34:54 mysqld_safe Logging to '/home/rbean/lib/mysqla/alp.err'.
220602 10:34:54 mysqld_safe Starting mariadbd daemon with databases from /home/rbean/lib/mysqla

如果爆錯可以檢查

## 成功的話，mysqld 目錄下會有 mysqla.pid 和 mysqlda.sock 兩個檔案
$ tree -L 2
.
├── lib
│   └── mysqla
├── my.cnf.d
│   └── mariadb-servera.cnf
└── mysqld
    ├── mysqlda.pid    
    └── mysqlda.sock

使用 MySQL 分身

$ mysql -S ~/mysqld/mysqlda.sock

MariaDB [(none)]> grant all on *.* to 'root'@'localhost' identified by 'rbean' with grant option;

MariaDB [(none)]> flush privileges;

MariaDB [(none)]> exit

設定 MySQL 分身安全性

$ mysql_secure_installation -S ~/mysqld/mysqlda.sock

出現以下問題：
    1、Enter current password for root (enter for none):
   （輸入密碼）
    2、Switch to unix_socket authentication [Y/n]
   （ n，不切換到 unix_socket 身份驗證）
    3、Change the root password? [Y/n]
   （ n，不更換 root 密碼）
    4、Remove anonymous users? [Y/n]
   （ Y，移除匿名登入）
    5、Disallow root login remotely? [Y/n]
   （ Y，移除遠端 root 登入權限）
    6、Remove test database and access to it? [Y/n]
   （ Y，移除測試資料庫及帳號）
    7、Reload privilege tables now? [Y/n]
   （ Y，重新載入權限表）

檢查 MySQL 分身套件版本與服務運行狀態

命令：

$ mysqladmin version -p -S ~/mysqld/mysqlda.sock

命令結果：

Enter password: rbean
mysqladmin  Ver 9.1 Distrib 10.6.8-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.6.8-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /home/rbean/mysqld/mysqlda.sock
Uptime:                 43 min 13 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 21  Open tables: 14  Queries per second avg: 0.007

登入 MySQL 分身

$ mysql -u root -prbean -h 127.0.0.1 -P 3311

注意 -P ，的 P 要大寫

查看當前使用者
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

查看所有 Database
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

檢視 MySQL 分身相關使用資訊

MariaDB [(none)]> select user,host,select_priv,insert_priv from mysql.user;
+-------------+-----------+-------------+-------------+
| User        | Host      | Select_priv | Insert_priv |
+-------------+-----------+-------------+-------------+
| mariadb.sys | localhost | N           | N           |
| root        | localhost | Y           | Y           |
| rbean       | localhost | Y           | Y           |
+-------------+-----------+-------------+-------------+
3 rows in set (0.001 sec)

What’s transaction?

transaction
- 是資料庫執行過程中的一個「邏輯單位」
- 一個交易：多個對資料庫操作的行為
- 一個交易有兩種可能的結局：
  - 全部執行成功 - > commit
  - 全部不執行 - > rollback
Transaction ACID
- 原子性（Atomicity）：整個交易（transaction）若有其中一個操作沒有完成，就會回滾（Rollback）到交易開始前的狀態，如同從未執行過。
- 一致性（Consistency）：資料庫從一個有效狀態，經過交易後變成另一個有效狀態。
- 隔離性（Isolation）：確保所有交易在進行時，是互不干擾的。
- 持續性（Durability）：交易處理結束後，對數據的修改就是永久的，即便系統故障也不會丟失。

Transaction 實作

MariaDB [(none)]> create database bank;

MariaDB [(none)]> use bank;

MariaDB [bank]> create table if not exists account(
ID int auto_increment not null,
customer_name varchar(200) not null,
current_money decimal(7,2) not null,
primary key(ID)
);

MariaDB [bank]> show create table account;
+---------+--------------------------------------------------------+
| Table   | Create Table                                           |
+---------+--------------------------------------------------------+
| account | CREATE TABLE `account` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_money` decimal(7,2) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------+--------------------------------------------------------+
1 row in set (0.000 sec)

新增規則：constraint check_current_money check(current_money >= 1)

MariaDB [bank]> alter table account add constraint check_current_money check(current_money >= 1);

MariaDB [bank]> show create table account;
+---------+--------------------------------------------------------+
| Table   | Create Table                                           |
+---------+--------------------------------------------------------+
| account | CREATE TABLE `account` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_money` decimal(7,2) NOT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `check_current_money` CHECK (`current_money` >= 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------+--------------------------------------------------------+
1 row in set (0.001 sec)

測試新增資料是否符合預期

MariaDB [bank]> insert into account(customer_name, current_money) values ('Mike', 1500),('Amber', 1);
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0


MariaDB [bank]> update account set current_money = current_money - 1500 where customer_name = 'Mike';
ERROR 4025 (23000): CONSTRAINT `check_current_money` failed for `bank`.`account`

MariaDB [bank]> insert into account(customer_name, current_money) values ('Amber2', 0.55);
ERROR 4025 (23000): CONSTRAINT `check_current_money` failed for `bank`.`account`

Transaction 實作2

$ nano tranfer.sql
DELIMITER //

CREATE PROCEDURE transfer(IN name_a VARCHAR(200), IN name_b VARCHAR(200), IN total DECIMAL(7,2))
    BEGIN
        DECLARE exit handler for sqlexception BEGIN ROLLBACK; END;
        DECLARE exit handler for sqlwarning BEGIN ROLLBACK; END;
        START TRANSACTION;
            update account set current_money = current_money + total
                where customer_name = name_b;
            update account set current_money = current_money - total
                where customer_name = name_a;
        COMMIT;
    END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE transfer_test(IN name_a VARCHAR(200), IN name_b VARCHAR(200), IN total DECIMAL(7,2))
    BEGIN
        START TRANSACTION;
            update account set current_money = current_money + total
                where customer_name = name_b;
            update account set current_money = current_money - total
                where customer_name = name_a;
        COMMIT;
    END //

DELIMITER ;

WHAT’S BINLOG

Binary log : 存放資料庫更改的事件，例如創建資料表或修改資料表的資料。 DML (Data Manipulation Language): INSERT / UPDATE / DELETE DDL (Data Definition Language): CREATE / ALTER / DROP

兩個重要用途

資料庫備援（高可用性）Replication
某個時間的資料還原 Certain data recovery operations

BINLOG TYPE

Statement-based logging (SBL)
- 基於 SQL 語句紀錄
- 資料量小
Row-based logging (RBL)
- 基於資料行紀錄，紀錄每個值的變動
- 資料量大
Mixed-format logging
- 混合模式
- 會對每一個 SQL 語句判斷，做合適的紀錄方式 (SBL or RBL)
- 資料量適中

啟用 BINLOG

:::success 檢查 BINLOG 設定

MariaDB [(none)]> show variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| bind_address                            |                      |
| ...                                     |                      |
| binlog_format                           | MIXED                |
| ...                                     |                      |
| log_bin                                 | OFF                  |
+-----------------------------------------+----------------------+
30 rows in set (0.001 sec)

MariaDB [(none)]> exit

:::

:::success

停用 MySQL 分身

命令：

$ mysqladmin -S /home/rbean/mysqld/mysqlda.sock shutdown

命令結果：

[1]+ Done mysqld_safe --defaults-file=/home/rbean/my.cnf.d/mariadb-servera.cnf

:::

:::success

修改 MySQL server 設定檔，啟用 Binlog

$ nano /home/rbean/my.cnf.d/mariadb-servera.cnf

新增以下 6 行命令

……

skip-bind-address
server_id=1
log_bin=/home/rbean/lib/mysqla/bin-log
log_bin_index=/home/rbean/lib/mysqla/mysql-bin.index
binlog_format=row
expire_logs_days=0

……

skip-bind-address ， mariadb 預設會把服務綁到127.0.0.1 上。所以我們需要設定不綁定。
server_id = 0001 ，指定一個叢集內的 MySQL 伺服器 ID，如果做資料庫叢集那麼必須全域性唯一，一般來說不推薦指定server_id 等於 1。
log_bin = /home/rbean/lib/mysqla/bin-log，開啟 Binlog 並寫明存放日誌的位置與檔案名稱以bin-log 開頭。
log_bin_index = /var/lib/mysql/mysql-bin.index ，指定索引檔案的位置。
binlog_format = ROW ，設定方面提到過的三種 Binlog 的日誌模式。
expire_logs_days = 0 ，Default 0 , 不自動刪除；刪除超出這個變數保留期之前的全部日誌被刪除

再次啟動 MySQL 分身

$ mysqld_safe --defaults-file=/home/rbean/my.cnf.d/mariadb-servera.cnf &

:::spoiler 完整的 config 檔， 注意！新增的內容不可加在 [client] 底下

[mysqld]
skip-networking=0

user=rbean
pid-file=/home/rbean/mysqld/mysqlda.pid
socket=/home/rbean/mysqld/mysqlda.sock
port=3311
datadir=/home/rbean/lib/mysqla
character-set-client-handshake=FALSE
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server=utf8mb4

skip-bind-address
server_id=1
log_bin=/home/rbean/lib/mysqla/bin-log
log_bin_index=/home/rbean/lib/mysqla/mysql-bin.index
binlog_format=row
expire_logs_days=0

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

:::

查看 BINLOG

$ mysql -uroot -prbean -h 127.0.0.1 -P 3311 -e "show variables like '%bin%';"
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| bind_address                            |                      |
| ...                                     |                      |
| binlog_format                           | MIXED                |
| ...                                     |                      |
| log_bin                                 | ON                  |
+-----------------------------------------+----------------------+

檢查目前儲存的 Binlog 檔案以及 Postion

$  mysql -uroot -prbean -h 127.0.0.1 -P 3311 -e "show master status;"

Change Data Capture (Master-Slave Replication)

Master-Slave Replication（主從複製）

What’s 主從複製：藉由將 Master 執行的交易複製到多台 Slave 達到主從資料一致性與高可用性
Why 主從複製：因為在這個框架下，主從資料是一致性的，所以 Slave 可以當備用系統或唯獨資料庫

CDC Master-Slave 流程圖

主從的時間同步很重要！！！ 哦吼

Master-Slave 機制共會啟動三個 Thread

Master - 產生 binlog 紀錄
Slave - I/O thread 作用：向 Binlog dump thread 抓取資料，放到 Relay log
Slave - SQL thread 作用：向 Relay log 讀取資料，寫入 Slave 資料庫

master master ??? 連結

練習

題目：啟動第二個 MySQL Instance，指定 port number 為 3312，相關的目錄和檔案名稱請由 a 改為 b

答案：


# 建分身 b 的目錄
$ mkdir ~/lib/mysqlb

# 拷貝設定檔
$ cp /etc/my.cnf.d/mariadb-server.cnf ~/my.cnf.d/mariadb-serverb.cnf

# 編輯設定檔
$ nano ~/my.cnf.d/mariadb-serverb.cnf
[mysqld]
skip-networking=0
user=rbean
pid-file=/home/rbean/mysqld/mysqldb.pid
socket=/home/rbean/mysqld/mysqldb.sock
port=3312
datadir=/home/rbean/lib/mysqlb
character-set-client-handshake=FALSE
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server=utf8mb4

skip-bind-address
server_id = 2
relay-log = /home/rbean/lib/mysqlb/slave-relay-bin
read_only = 1
relay-log-recovery = 1

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

# 建立 MySQL 分身 b
$ mysql_install_db --user=rbean --datadir=/home/rbean/lib/mysqlb/

# 啟用 MySQL 分身 b
$ mysqld_safe --defaults-file=/home/rbean/my.cnf.d/mariadb-serverb.cnf &

# 使用 MySQL 分身 b
$ mysql -S ~/mysqld/mysqldb.sock

# 建 root 帳號
MariaDB [(none)]> grant all on *.* to 'root'@'localhost' identified by 'rbean' with grant option;

# 刷新權限
MariaDB [(none)]> flush privileges;

# 離開 mysql
MariaDB [(none)]> exit

# 設定 MySQL 分身安全性
$ mysql_secure_installation -S ~/mysqld/mysqlda.sock

## 檢查
$ mysqladmin version -p -S ~/mysqld/mysqlda.sock
$ mysqladmin version -p -S ~/mysqld/mysqldb.sock

tags: `MySQL`

了解 Btrfs Filesystem 運作 Oracle MySQL NDB 實作

MySQL_CDC (Change Data Capture)

MySQL_CDC (Change Data Capture)

在 Alpine Linux 安裝 MySQL 本尊

修改 bash script: mysql_install_db

在 Alpine Linux 創建一般使用者帳號

建立 MySQL 分身

編輯MySQL 分身設定檔

建立 MySQL 分身

啟用 MySQL 分身

使用 MySQL 分身

設定 MySQL 分身安全性

檢查 MySQL 分身套件版本與服務運行狀態

登入 MySQL 分身

檢視 MySQL 分身相關使用資訊

What’s transaction?

Transaction 實作

Transaction 實作2

WHAT’S BINLOG

啟用 BINLOG

查看 BINLOG

Change Data Capture (Master-Slave Replication)

Master-Slave Replication（主從複製）

CDC Master-Slave 流程圖

練習

tags: MySQL

tags: `MySQL`