# DB Server, DashBoard
###### tags: `新人訓練`
## MySQL
### 安裝: docker engine

## 設定PWD, 實體資料存放目錄
PWD=/mnt/hdd/IP_Camera_Installation/mysql
## export PWD=/home/tpe-aa-03/AA/Ray/mysql
## echo $PWD

## 安裝docker engine
docker run -p 3306:3306 --name mysql -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql --restart=always  -e MYSQL_ROOT_PASSWORD=123456 -d mysql --default-authentication-plugin=mysql_native_password
## docker run -p 7306:3306 --name mysql_ray -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql --restart=always  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest --default-authentication-plugin=mysql_native_password

參數說明
- -p: 將container的3306 port對映到"主機(host)"的3306 port
- -name: 指定container名稱
- -e: 設定環境變數
MYSQL_ROOT_PASSWORD(root用戶的密碼)
- -v: Bind mount a volume(Bind mount 可存放在主機檔案系統中的任何地方)
- --restart: Restart policy to apply when a container exits (default "no")
- -d: 背景執行要安裝的MySQL image[(supported tags)](https://hub.docker.com/_/mysql?tab=description)
- --default-authentication-plugin[(選擇儲存密碼機制)](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin)

### 安裝: docker compose(20230531更新)
- docker-compose.yml

    version: '3.9'
    services:
      mysql:
        image: mysql:latest
        container_name: mysql
        volumes:
          - /mnt/hdd1/AA_services/mysql/conf.d:/etc/mysql/conf.d
          - /mnt/hdd1/AA_services/mysql/logs:/logs
          - /mnt/hdd1/AA_services/mysql/data:/var/lib/mysql
        restart: always
        environment:
          MYSQL_ROOT_PASSWORD: 123456
        command: --default-authentication-plugin=mysql_native_password
        ports:
          - "3306:3306"

### 操作
- 查詢MySQL版本

docker exec mysql bash -c "mysql -V"
## docker exec mysql_ray bash -c "mysql -V"

- Login MySQL

docker exec -it mysql bash
## docker exec -it mysql_ray bash
mysql -h localhost -u root --password=123456

- 建立user, table及測試資料

-- create new database user
CREATE USER 'ray'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'ray'@'%' IDENTIFIED BY '123456'; -- for remote user
-- grant privileges
GRANT ALL PRIVILEGES ON *.* TO 'ray'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'ray'@'%';
FLUSH PRIVILEGES;

-- log out
\q

-- log in as new user
mysql -u ray -p
show grants;

-- create a new database
CREATE DATABASE test;
SHOW DATABASES;

-- create table
CREATE TABLE test.mv(
  CustomerName VARCHAR(30),
  ContactName VARCHAR(30),
  Address VARCHAR(30),
  City VARCHAR(30),
  PostalCode VARCHAR(10),
  Country VARCHAR(30)
);

-- insert data
INSERT INTO test.mv (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

select * from test.mv;

### 備份與還原
- Backup

docker exec CONTAINER_NAME /usr/bin/mysqldump -u root --password=123456 DATABASE > backup.sql
## example
docker exec mysql_ray /usr/bin/mysqldump -u root --password=123456 test > /home/tpe-aa-03/AA/Ray/mysql/backup.sql

- Restore

cat SQL_File | docker exec -i CONTAINER_NAME /usr/bin/mysql -u root --password=123456 DATABASE_NAME
## example
cat backup.sql | docker exec -i mysql_ray /usr/bin/mysql -u root --password=123456 test

## Redis (Memory based)
### 安裝: docker engine

## 設定redis密碼
docker run --name redis-emi -p 6379:6379 -v /mnt/hdd1/redis/data:/data --restart=always -d redis --requirepass "emi2wsx"
## docker run --name redis -p 6379:6379 -v /home/tpe-aa-03/AA/Ray/redis/data:/data --restart=always -d redis --requirepass "1qaz@WSX"

### 操作
#### Python Client存取資料
[redis-py documentation](https://github.com/redis/redis-py)
- Step1: Install the Redis client library using pip

pip3 install redis

- Step2: Write your application code

import redis

redis = redis.Redis(
     host= 'localhost',
     port= '6379',
     password= '1qaz@WSX')

redis.set('England', 'London')
value1 = redis.get('England') 

redis.mset({'Ireland': 'Dublin', 'Scotland': 'Edinburgh'})
value2 = redis.mget(['Ireland', 'Scotland'])

print(value1)
print(value2)
print(redis.keys())

## Metabase (DashBoard)
### 安裝: docker engine

## 設定實體資料存放目錄
docker run -d -p 3000:3000 -v /mnt/hdd/Service_Log_Installation/metabase-data:/metabase-data --name metabase --restart=always metabase/metabase
## docker run -d -p 7300:3000 -v /home/tpe-aa-03/AA/Ray/metabase-data:/metabase-data --name metabase_ray --restart=always metabase/metabase

### 安裝: docker compose(20230530更新)
- docker-compose.yml

    version: '3.9'
    services:
      metabase:
        image: metabase/metabase:latest
        container_name: metabase
        volumes:
          - /mnt/hdd1/AA_services/metabase/metabase-data:/metabase-data
          - /mnt/hdd1/AA_services/metabase/metabase.db:/metabase.db
        ports:
          - 3000:3000
        restart: always

- 啟動&關閉指令:
- docker compose up -d
- docker compose down

### 備份移轉metabase(20230530更新)
1. 備份 metabase docker image
- [參考連結](https://www.metabase.com/docs/latest/installation-and-operation/backing-up-metabase-application-data#if-youre-running-the-metabase-docker-image)

    ## docker cp container_name:/metabase.db/metabase.db.mv.db ./
    docker cp metabase:/metabase.db/metabase.db.mv.db ./

2. 將 metabase.db.mv.db 搬移到對應的資料夾內

### 操作: 建立 Call_Log_Dashboard
- 進入 Dashboard
- http://10.109.6.10:7300/
- 建立 Data Source 來源
- 建立問題
- 產生圖表
- 變更圖表格式
- 建立及編輯資訊看板
- 設計儀表板 Call_Log_Dashboard
http://10.109.6.10:7300/dashboard/1
登入帳號:[e-mail]
登入密碼:1qaz@WSX
- sql檔
https://hackmd.io/HhxB4WIiSSu_yNXqWQVKFQ