TIDB双集群主从复制shell脚本部署(适用于统信uos和麒麟系统)

前提，已安装tidb和cdc
cdc可在tidb的topo.yaml配置文件配置
tidb的离线安装部署可移步我另一个文章查看
IP1可填tidb1数据库的ip，IP2可填tidb2数据库的ip
两个数据库的ip需要互相ping通
适用于部分表同步和全部表同步
输入1可为全部表同步
输入2可为部分表同步以及就是某个时间点同步
自用，shell脚本仅供参考

shell脚本如下：

#!/bin/bash

# 定义颜色变量
RED="\e[31m"
GREEN="\e[32m"
RESET="\e[0m"

# 定义函数来验证IP地址
validate_ip() {
    local ip=$1
    if [[ $ip =~ ^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
        IFS='.' read -ra octets <<< "$ip"
        for octet in "${octets[@]}"; do
            if (( octet > 255 )); then
                echo -e "${RED}IP format is incorrect, please re-enter! (IP格式不正确，请重新输入!${RESET})"
                return 1
            fi
        done
        return 0
    else
        echo -e "${RED}IP format is incorrect, please re-enter! (IP格式不正确，请重新输入!${RESET})"
        return 1
    fi
}

# 获取并验证IP1
while true; do
    read -p "Please enter server IP1 (请输入中央服务器IP1): " IP1
    if validate_ip "$IP1"; then
        echo -e "${GREEN}Verify the server IP1 address again (请再次核实中央服务器IP1): $IP1${RESET}"
        read -p "[Confirm] Please enter 1, [Modify] Please enter r: (【确认】请输入1，【修改】请输入r: )" confirm1
        if [[ $confirm1 == "1" ]]; then
            break
        elif [[ $confirm1 == "r" ]]; then
            echo "retype (重新输入)"
            continue
        else
            echo "Invalid input, please try again."
        fi
    fi
done

# 获取并验证IP2
while true; do
    read -p "Please enter server IP2 (请输入节点服务器IP2): " IP2
    if validate_ip "$IP2"; then
        echo -e "${GREEN}Verify the server IP2 address again (请再次核实节点服务器IP2): $IP2${RESET}"
        read -p "[Confirm] Please enter 1, [Modify] Please enter r: (【确认】请输入1，【修改】请输入r: )" confirm2
        if [[ $confirm2 == "1" ]]; then
            break
        elif [[ $confirm2 == "r" ]]; then
            echo "retype (重新输入)"
            continue
        else
            echo "Invalid input, please try again."
        fi
    fi
done

# 输出最终确认的IP地址
echo "Final IP1: $IP1"
echo "Final IP2: $IP2" 

#log_file="/var/log/path_info.log"

# 提取路径
#akyPath=$(grep -oP '文件夹已成功存储到\K.*' "$log_file")
akyPath=/usr/local/ankeyin
#此处很重要，用来删除tidb数据库中原先所有的表或者你所需的部分表之后才可以导入到tidb2数据库
cat > $akyPath/tidb/delete.sql <<EOF
DROP TABLE IF EXISTS plat_dapertment;
DROP TABLE IF EXISTS plat_role;
DROP TABLE IF EXISTS plat_user;
DROP TABLE IF EXISTS plat_user_face;
DROP TABLE IF EXISTS plat_user_authentication;
DROP TABLE IF EXISTS biz_authdev;
DROP TABLE IF EXISTS biz_authdev_face;
DROP TABLE IF EXISTS biz_printdev;
DROP TABLE IF EXISTS plat_domain;

EOF

cat > $akyPath/tidb/ticdc.toml <<EOF
case-sensitive = false

[filter]
rules = [
  "!printing.biz_device_fault",
  "!printing.biz_device_fault_day",
  "!printing.biz_device_fault_record",
  "!printing.biz_warning",
  "!printing.gat_recordday",
  "!printing.gat_recordreal",
  "!printing.plat_sys_log",
  "printing.biz_cost_billing",
  "printing.biz_quota_scheme",
  "printing.plat_dapertment",
  "printing.plat_role",
  "printing.plat_setting",
  "printing.plat_user",
  "printing.plat_user_authentication",
  "printing.plat_user_face",
  "printing.process_rules",
  "printing.process_rules_verify"
]
[mounter]
worker-num = 8

EOF
#部分表进行同步
execute_additional_commands() {
		mysql -h "$IP1" -uroot -P 4000 -p"jq@aky#6512" -e "SET GLOBAL tidb_gc_enable=FALSE;"
		mysql -h "$IP1" -uroot -P 4000 -p"jq@aky#6512" -e "SELECT @@global.tidb_gc_enable;"
		cd $akyPath/tidb
		#导出部分表
		tiup dumpling -u root -p jq@aky#6512 -h "$IP1" -P 4000 -r 200000 -F 256MiB -t 8 -B printing -T printing.plat_dapertment,printing.plat_role,printing.plat_user,printing.plat_user_face,printing.plat_user_authentication,printing.biz_authdev,printing.biz_authdev_face,printing.biz_printdev,printing.plat_domain -o $akyPath/tidb

		find . -type f -name '*-schema-create.sql' -delete

		# 定义要合并的文件
		delete_file="delete.sql"
		schema_files="*-schema.sql"
		other_sql_files="*.sql"

		# 定义输出文件名
		output_file="merged.sql"

		# 清空输出文件
		> "$output_file"

		# 合并 delete.sql
		if [[ -f "$delete_file" ]]; then
			cat "$delete_file" >> "$output_file"
			echo "-- End of $delete_file" >> "$output_file"
		fi

		# 合并所有 -schema.sql 文件
		for file in $schema_files; do
			if [[ -f "$file" ]]; then
				cat "$file" >> "$output_file"
				echo "-- End of $file" >> "$output_file"
			fi
		done

		# 合并剩余的所有 SQL 文件（排除 delete.sql 和 -schema.sql）
		for file in $other_sql_files; do
			if [[ -f "$file" && "$file" != "$delete_file" && "$file" != *-schema.sql ]]; then
				cat "$file" >> "$output_file"
				echo "-- End of $file" >> "$output_file"
			fi
		done

		echo "所有文件已合并到 $output_file"
		find . -maxdepth 1 -name '*.sql' ! -name 'merged.sql' -delete
        #导入到tidb2数据库里
		mysql -h "$IP2"  -uroot -P 4000 -p"jq@aky#6512" -D printing  < $akyPath/tidb/merged.sql
		# 提取Pos的值
		pos_value=$(awk '/Pos:/ {print $2}' $akyPath/tidb/metadata)

		echo "Pos值为: $pos_value"
		tiup ctl:v8.1.0 cdc changefeed create --pd=http://$IP1:2379 --sink-uri="mysql://root:jq%40aky%236512@$IP2:4000?worker-count=16&max-txn-row=5000" --changefeed-id="replication-task-${node_id}" --sort-engine="unified"  --config $akyPath/tidb/ticdc.toml --start-ts=$pos_value
		mysql -h "$IP1" -uroot -P 4000 -p"jq@aky#6512" -e "SET GLOBAL tidb_gc_enable=TRUE;"
		mysql -h "$IP1" -uroot -P 4000 -p"jq@aky#6512" -e "SELECT @@global.tidb_gc_enable;"
		
}

while true; do
    echo "请输入数字（1、2 或 3）：1 代表第一节点服务器，2 代表其他服务器，3 代表退出操作。"
    
    read -p "请输入数字（1 或 2 或 3）: " choice

    if [[ "$choice" =~ ^[123]$ ]]; then
        read -p "您输入的是 $choice，确认吗？(y/n): " confirm
        if [[ "$confirm" == "y" ]]; then
            if [ "$choice" -eq 1 ]; then
                echo "您选择了第一节点服务器。"
                # 在这里执行与第一节点相关的操作
				tiup ctl:v8.1.0 cdc changefeed create --pd=http://$IP1:2379 --sink-uri="mysql://root:jq%40aky%236512@$IP2:4000?worker-count=16&max-txn-row=5000" --changefeed-id="replication-task-1" --sort-engine="unified"  --config $akyPath/tidb/ticdc.toml
				break
            elif [ "$choice" -eq 2 ]; then
                while true; do
                    read -p "请输入第几个节点服务器: " node_id
                    read -p "您输入的节点服务器是 $node_id，确认吗？(y/n): " node_confirm
                    if [[ "$node_confirm" == "y" ]]; then
                        echo "执行操作 --changefeed-id=\"replication-task-${node_id}\""
                        execute_additional_commands
                        exit 0
                    else
                        echo "请重新输入节点服务器。"
                    fi
                done

            elif [ "$choice" -eq 3 ]; then
                echo "退出操作。"
                break
            fi
        else
            echo "请重新输入。"
        fi
    else
        echo "无效输入，请重新输入。"
    fi
done