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

source .bash_profile
source ./hosts-env.sh

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

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

~/.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

~/.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