#!/bin/bash
REGION_NAME=${1}
TRAINER_TAG=${2}

source .bash_profile
source ./hosts-env.sh

./create-cluster-v751.sh ${REGION_NAME}
sleep 5;

# User and Roles in Users Management Lab

mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot << EOF
DROP USER IF EXISTS 'jack'@'${HOST_CM_PRIVATE_IP}';
CREATE USER 'jack'@'${HOST_CM_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
CREATE ROLE r_manager, r_staff;
ALTER USER 'jack'@'${HOST_CM_PRIVATE_IP}' IDENTIFIED BY 'tidb';
EOF

mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot << EOF
DROP ROLE r_staff;
DROP ROLE r_manager;
DROP USER 'jack'@'${HOST_CM_PRIVATE_IP}';
EOF

mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot << EOF
USE test;
CREATE TABLE emp(id INT, name VARCHAR(20));
INSERT INTO emp VALUES(1,'tom');
INSERT INTO emp VALUES(2,'jack');
CREATE USER 'jack'@'${HOST_CM_PRIVATE_IP}' IDENTIFIED BY 'pingcap';
CREATE ROLE r_mgr, r_emp;
GRANT SELECT ON test.emp TO r_emp;
GRANT INSERT, UPDATE, DELETE ON test.* TO r_mgr;
GRANT r_emp TO r_mgr, 'jack'@'${HOST_CM_PRIVATE_IP}';
EOF

mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot << EOF
USE test;
CREATE TABLE dept(id INT, dname VARCHAR(20));
INSERT INTO dept VALUES(1, 'dev');
INSERT INTO dept VALUES(2, 'sales');
GRANT SELECT ON test.dept TO 'jack'@'${HOST_CM_PRIVATE_IP}';
EOF

mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -ujack -ppingcap << EOF
USE test;
SET ROLE ALL;
DELETE FROM emp WHERE id=1;
EOF

# Dataset Imported and Exported

~/.tiup/bin/tiup install dumpling:v7.5.1
unzip -u stage/tidb-admin-dataset-v2.zip -d stage/

mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 << 'EOF'
DROP DATABASE IF EXISTS emp;
EOF

~/.tiup/bin/tiup tidb-lightning:v7.5.1 -config solution-lightning-sql.toml
rm -rf /tmp/tidb_lightning_checkpoint.pb

~/.tiup/bin/tiup dumpling:v7.5.1 -uroot -P4000 -h ${HOST_DB1_PRIVATE_IP} --filetype sql -t 8 -o /tmp/dep -r 200000 -F 256MiB -T emp.dep

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_PD3_PRIVATE_IP} << 'EOFX'
echo "Operating on `hostname`"
unzip -u stage/tidb-admin-dataset-v2.zip -d stage/
sudo service mysqld start
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << 'EOF'
DROP USER IF EXISTS exp@'%';
CREATE USER exp@'%' IDENTIFIED BY 'q1w2e3R4_';
GRANT ALL PRIVILEGES on *.* to exp@'%';
SOURCE ./stage/TiDB-Administration-exercise-data/exercise-dumpling/emp-schema-create.sql
EOF
exit
EOFX

~/.tiup/bin/tiup dumpling:v7.5.1 -uexp -P 3306 -h ${HOST_PD3_PRIVATE_IP} -pq1w2e3R4_ --filetype sql -t 8 -o /tmp/empmysql -r 200000 -F 256MiB -B emp

~/.tiup/bin/tiup install br:v7.5.1
unzip -u stage/tidb-admin-dataset-v2.zip -d stage/

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem unzip -u stage/tidb-admin-dataset-v2.zip -d stage/

mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 << 'EOF'
DROP DATABASE IF EXISTS emp;
CREATE USER ltask@'%' IDENTIFIED BY 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO ltask@'%';
EOF

~/.tiup/bin/tiup tidb-lightning:v7.5.1 -config solution-lightning-init.toml > nohup.out
rm -rf /tmp/tidb_lightning_checkpoint.pb

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_KV1_PRIVATE_IP} mkdir /tmp/backup
ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_KV2_PRIVATE_IP} mkdir /tmp/backup
ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_KV3_PRIVATE_IP} mkdir /tmp/backup

# Backup and Restore

~/.tiup/bin/tiup br:v7.5.1 backup full --pd "${HOST_PD1_PRIVATE_IP}:2379" --storage "local:///tmp/backup" --ratelimit 128 --log-file backupfull.log

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_KV1_PRIVATE_IP} find /tmp/backup/

ls /tmp/backup/

~/.tiup/bin/tiup br:v7.5.1 backup db --db emp --pd "${HOST_PD1_PRIVATE_IP}:2379" --storage "s3://pe-class-${REGION_NAME}/${TRAINER_TAG}/user1/emp" --s3.region ${REGION_CODE} --send-credentials-to-tikv=false --ratelimit 128 --log-file backupdb.log

mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 << 'EOF'
DROP DATABASE emp;
EOF

~/.tiup/bin/tiup br:v7.5.1 restore db --pd "${HOST_PD1_PRIVATE_IP}:2379" --db "emp" --storage "s3://pe-class-${REGION_NAME}/${TRAINER_TAG}/user1/emp" --s3.region ${REGION_CODE} --send-credentials-to-tikv=false --log-file restoredb.log

# sync-diff

wget https://download.pingcap.org/tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz
tar xvf tidb-community-toolkit-v7.5.1-linux-amd64.tar.gz

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_PD3_PRIVATE_IP} << 'EOFX'
echo "Operating on `hostname`"
sudo service mysqld start
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << 'EOF'
DROP USER IF EXISTS ins@'%';
CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
GRANT ALL PRIVILEGES on *.* to ins@'%';
CREATE DATABASE test1;
USE test1;
CREATE TABLE T1(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32));
CREATE TABLE T2(id INT(11), name VARCHAR(32));
CREATE TABLE T3(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32));
CREATE TABLE T4(id INT(11), name VARCHAR(32));
INSERT INTO T1 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T2 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T3 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T4 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
EOF
exit
EOFX

mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000 << 'EOF'
DROP DATABASE IF EXISTS ins;
CREATE USER ins@'%' IDENTIFIED BY 'q1w2e3R4_';
GRANT ALL PRIVILEGES on *.* to ins@'%';
CREATE DATABASE test1;
USE test1;
CREATE TABLE T1(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32));
CREATE TABLE T2(id INT(11), name VARCHAR(32));
CREATE TABLE T3(id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(32));
CREATE TABLE T4(id INT(11), name VARCHAR(32));
INSERT INTO T1 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T2 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T3 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
INSERT INTO T4 VALUES (1,'Tom'), (2,'Jack'), (3,'Frank'), (4,'Tony');
EOF

./tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector --config=./solution-sync-diff-config.toml

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_PD3_PRIVATE_IP} << 'EOFX'
echo "Operating on `hostname`"
sudo service mysqld start
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << 'EOF'
USE test1;
INSERT INTO T1 SELECT 5, 'Candy';
INSERT INTO T2 SELECT 5, 'Candy';
INSERT INTO T3 SELECT 5, 'Candy';
INSERT INTO T4 SELECT 5, 'Candy';
DELETE FROM T1 WHERE id=4;
DELETE FROM T2 WHERE id=4;
DELETE FROM T3 WHERE id=4;
DELETE FROM T4 WHERE id=4;
UPDATE T1 SET NAME='Andy' WHERE id=1;
UPDATE T2 SET NAME='Andy' WHERE id=1;
UPDATE T3 SET NAME='Andy' WHERE id=1;
UPDATE T4 SET NAME='Andy' WHERE id=1;
EOF
exit
EOFX

./tidb-community-toolkit-v7.5.1-linux-amd64/sync_diff_inspector --config=./solution-sync-diff-config.toml

# Catch up DM Practices
~/.tiup/bin/tiup install dm dmctl:v7.5.1
~/.tiup/bin/tiup dm deploy dm-test v7.5.1 ./solution-dm-topology-six-nodes.yaml --yes
~/.tiup/bin/tiup dm start dm-test

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_PD1_PRIVATE_IP} << 'EOFX'
echo "Operating on `hostname`"
sudo service mysqld start
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << 'EOF'
DROP USER IF EXISTS dm_user@'%';
CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
SET @@global.show_compatibility_56=ON;
EOF
exit
EOFX

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -i ~/.ssh/pe-class-key-${REGION_NAME}.pem ${HOST_PD2_PRIVATE_IP} << 'EOFX'
echo "Operating on `hostname`"
sudo service mysqld start
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << 'EOF'
DROP USER IF EXISTS dm_user@'%';
CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
SET @@global.show_compatibility_56=ON;
EOF
exit
EOFX

cd ./stage/
unzip tidb-admin-dataset-v2.zip 1>/dev/null 2>&1
cd -
mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD1_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3306db.sql
mysql -udm_user -pq1w2e3R4_ -h ${HOST_PD2_PRIVATE_IP} -P3306 < stage/TiDB-Administration-exercise-data-v2/exercise-dm/3307db.sql 

mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot << 'EOF'
DROP USER IF EXISTS dm_user@'%';
CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
DROP DATABASE IF EXISTS user_north;
CREATE DATABASE user_north;
CREATE TABLE user_north.information(id INT PRIMARY KEY, info VARCHAR(64));
CREATE TABLE user_north.trace(id INT PRIMARY KEY, content VARCHAR(64));
DROP DATABASE IF EXISTS user_east;
CREATE DATABASE user_east;
CREATE TABLE user_east.information(id INT PRIMARY KEY, info VARCHAR(64));
CREATE TABLE user_east.trace(id INT PRIMARY KEY, content VARCHAR(64));
DROP DATABASE IF EXISTS store;
CREATE DATABASE store;
CREATE TABLE store.store_bj(id INT PRIMARY KEY, pname VARCHAR(64));
CREATE TABLE store.store_tj(id INT PRIMARY KEY, pname VARCHAR(64));
CREATE TABLE store.store_sh(id INT PRIMARY KEY, pname VARCHAR(64));
CREATE TABLE store.store_suzhou(id INT PRIMARY KEY, pname VARCHAR(64));
DROP DATABASE IF EXISTS salesdb;
CREATE DATABASE salesdb;
CREATE TABLE salesdb.sales(id INT PRIMARY KEY, pname varchar(20), cnt int);
DROP DATABASE IF EXISTS log;
CREATE DATABASE log;
CREATE TABLE log.messages(id INT PRIMARY KEY, msg VARCHAR(64));
EOF

X=`~/.tiup/bin/tiup dmctl:v7.5.1 --encrypt 'q1w2e3R4_'`

cat << EOF > mysql-source-conf1.yaml
source-id: "mysql-replica-01"
from:
  host: "${HOST_PD1_PRIVATE_IP}"
  port: 3306
  user: "dm_user"
  password: "${X}"
EOF

cat << EOF > mysql-source-conf2.yaml
source-id: "mysql-replica-02"
from:
  host: "${HOST_PD2_PRIVATE_IP}"
  port: 3306
  user: "dm_user"
  password: "${X}"
EOF

~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml
~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source create mysql-source-conf2.yaml

~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-01
~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 get-config source mysql-replica-02

~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 operate-source show

cat << EOF > dm-task.yaml
name: "dm-task"
task-mode: all    # The task mode is set to full (only migrates full data). 
ignore-checking-items: ["auto_increment_ID"] # Ignorable checking auto_increment_ID.

target-database:  # set the host, port, user and password of the downstream database TiDB.
  host: "${HOST_DB1_PRIVATE_IP}"
  port: 4000
  user: "dm_user"
  password: "${X}"

mysql-instances:  # configure data sources that need to be migrated for the data migration task.
  - source-id: "mysql-replica-01"
    route-rules: ["instance-1-user-schema-rule","salesdb-schema-route-rule"]
    filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"]
    block-allow-list: "log-ignored"
    mydumper-config-name: "global"
  - source-id: "mysql-replica-02"
    route-rules: ["instance-2-user-schema-rule", "instance-2-store-schema-rule","salesdb-schema-route-rule"]
    filter-rules: ["trace-table-filter-rule", "user-table-filter-rule" , "store-table-filter-rule"]
    block-allow-list: "log-ignored"
    mydumper-config-name: "global"

routes:
# Rule 1: Migrate all tables in the user database in upstream MySQL instance with port 3306 to user_north database in the downstream TiDB instance. Migrate all tables in user database in upstream MySQL instance with port 3307 to user_east database in the downstream TiDB instance.
  instance-1-user-schema-rule: # The first of the routing mapping rule.
    schema-pattern: "user"
    target-schema: "user_north"
  instance-2-user-schema-rule:
    schema-pattern: "user"
    target-schema: "user_east"
  
# Rule 2: Migrate all tables in store database in upstream MySQL instance to store database in the downstream TiDB instance, except the table store.store_sz in upstream MySQL instances with port 3307 to the table store.store_suzhou in the downstream TiDB instance.
  instance-2-store-schema-rule:
    schema-pattern: "store"
    table-pattern: "store_sz"
    target-schema: "store"
    target-table: "store_suzhou"
  
# Rule 3: Migrate the table salesdb.sales which is sharded schemas in upstream MySQL instances with port 3306 and 3307 in the two upstream MySQL instances to the salesdb.sales tables in the downstream TiDB instance.
  salesdb-schema-route-rule:
    schema-pattern: "salesdb"
    target-schema: "salesdb"

# Rule 4: Any delete DML event on user.*, any drop table, truncate table DDL event on table user.trace and any delete, truncate table, drop table DDL event on store database in any upstream MySQL instance will not be replicated to the downstream TiDB instance.
filters:
  trace-table-filter-rule:
    schema-pattern: "user"
    table-pattern: "trace"
    events: ["truncate table", "DROP TABLE", "delete"]
    action: Ignore
  user-table-filter-rule:
    schema-pattern: "user"
    events: ["drop database"]
    action: Ignore
  store-table-filter-rule:
    schema-pattern: "store"
    events: ["drop database", "truncate table", "DROP TABLE", "delete"]
    action: Ignore

# Rule 5: The log databases in any upstream MySQL instance will not be replicated to the downstream TiDB instance.
block-allow-list:
  log-ignored:
    ignore-dbs: ["log"]
mydumpers:
  global:
    threads: 4
    chunk-filesize: 64
EOF

~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 check-task ./dm-task.yaml
~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 start-task ./dm-task.yaml
~/.tiup/bin/tiup dmctl:v7.5.1 --master-addr=${HOST_PD1_PRIVATE_IP}:8261 query-status ./dm-task.yaml

sleep 15;

~/.tiup/bin/tiup dm destroy dm-test