---
# System prepended metadata

title: 'DB Server, DashBoard'
tags: [新人訓練]

---

# DB Server, DashBoard
###### tags: `新人訓練`
## MySQL
### 安裝: docker engine
```shell=
## 設定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
    ```yaml=
    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版本
```shell=
docker exec mysql bash -c "mysql -V"
## docker exec mysql_ray bash -c "mysql -V"
```
- Login MySQL
```shell=
docker exec -it mysql bash
## docker exec -it mysql_ray bash
mysql -h localhost -u root --password=123456
```

- 建立user, table及測試資料
```sql=
-- 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;
```
![](https://i.imgur.com/f2BRVN9.png)
### 備份與還原
- Backup
```shell=
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
```shell=
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
```shell=
## 設定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
```python=
pip3 install redis
```
- Step2: Write your application code
```python=
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
```shell=
## 設定實體資料存放目錄
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
    ```yaml=
    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)
    ```shell=
    ## 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 來源
    ![](https://i.imgur.com/LO8rA65.png)
- 建立問題
    ![](https://i.imgur.com/nM9nxOj.png)
- 產生圖表
    ![](https://i.imgur.com/OpdiTN5.png)
- 變更圖表格式
    ![](https://i.imgur.com/u7DaAnF.png)
- 建立及編輯資訊看板
    ![](https://i.imgur.com/n47tG9c.png)
- 設計儀表板 Call_Log_Dashboard
    http://10.109.6.10:7300/dashboard/1
    登入帳號:Ray_Yu@compal.com
    登入密碼:1qaz@WSX
    ![](https://i.imgur.com/H9hrHqY.png)
- sql檔
https://hackmd.io/HhxB4WIiSSu_yNXqWQVKFQ






