進入root後開啟mysql服務，並查看狀態

systemctl start mysqld
systemctl status mysqld

開啟服務後，透過以下指令進入mysql

mysql -u root -p

接著輸入當時在mysql設定的密碼。
顯示用戶

SELECT host, user FROM mysql.user 

生成新用戶並提供mysql連接權限(因為從後端沒辦法直接用root登入)

CREATE USER 'intern2'@'localhost' IDENTIFIED BY '0000';
//GRANT ALL PRIVILEGES ON *.* TO 'intern2'@'localhost' IDENTIFIED BY '0000';
GRANT ALL PRIVILEGES ON *.* TO 'intern2'@'localhost' 
FLUSH PRIVILEGES;

若出現'Client does not support authentication protocol requested by server; consider upgrading MySQL client'的錯誤訊息，可參考[此篇文章](https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server)。
Execute the following query in MYSQL Workbench

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then run this query to refresh privileges: `flush privileges;`

Windows安裝MySQL，請參考[此文章](https://www.geeksforgeeks.org/how-to-install-mysql-in-windows/)。

遇到"No module named MySQL."請打

pip install mysql-connector
或
pip3 install mysql-connector-python

查看密碼規則

SHOW VARIABLES LIKE 'validate_password%';

暫時修改

SET GLOBAL validate_password.policy=LOW;
SET GLOBAL validate_password.length=4;
SET GLOBAL validate_password.special_char_count=0;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.mixed_case_count=0; 

測試是否連接mysql

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host='localhost',       # 主機名稱
        user='intern2',         # 用戶名
        password='0000'         # 用戶密碼
    )

    if connection.is_connected():
        print("Connected to MySQL database")

except Error as e:
    print(f"Error: {e}")

finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

隨機生數據

import mysql.connector
import random
conn = mysql.connector.connect(
        host = "127.0.0.1",
        user = "oraclelee",
        password = "0000",
        database = "user"
        )

cursor = conn.cursor()
for i in range(1000):
    name =f'User{i}'
    age = random.randint(1,100)
    # Here are the input sentence
    query = f"INSERT INTO users(name , age) VALUES('{name}' , {age})"
    cursor.execute(query)

conn.commit()

cursor.close()
conn.close()
print("success")

切.txt

import csv
import re
# 定义一个函数来处理行数据并提取所需列
def process_line(line):
    # 使用正则表达式将行分割为字段，支持空格或制表符
    fields = re.split(r'\s+', line.strip())
    # 提取第 2 到第 11 列
    return fields[1:11]

for i in range(1990,2024):
    input_file = f'/home/oraclelee/Desktop/中職全壘打/{i}一軍例行賽全壘打.txt'
    output_file = f'/var/lib/mysql-files/home_run/{i}data.csv'


    with open(input_file, 'r', encoding='utf-8') as infile, open(output_file, 'w', encoding='utf-8', newline='') as outfile:
        reader = infile.readlines()
        writer = csv.writer(outfile)
        # 忽略第一行
        reader = reader[1:]
        for line in reader:
            #跳過空白行
            if line.strip():
                processed_row = process_line(line)
                writer.writerow(processed_row)

    print(f"Processed data saved to {output_file}")

.csv

import mysql.connector
import os

# 数据库连接配置
conn = mysql.connector.connect(
    host="127.0.0.1",
    user="oraclelee",
    password="0000",
    database="homerun"
)

cursor = conn.cursor()

query = f"ALTER TABLE game_data AUTO_INCREMENT = 1"
cursor.execute(query)
# 批量导入数据
for i in range(1990, 2024):
    file_path = f"/var/lib/mysql-files/home_run/{i}data.csv"

    if os.path.exists(file_path):
        # 构建完整的 SQL 查询
        query = f"""
        LOAD DATA INFILE '{file_path}'
        INTO TABLE game_data
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 ROWS
        (year, session, innings, date, venue, batter, batter_team, pitcher, pitcher_team, RBI)
        """

        try:
            # 执行查询
            cursor.execute(query)
            print(f"Data from {file_path} has been successfully loaded.")
        except mysql.connector.Error as err:
            print(f"Error loading data from {file_path}: {err}")
    else:
        print(f"File {file_path} does not exist.")

# 提交更改
conn.commit()

# 关闭连接
cursor.close()
conn.close()
print("Success")

### 連接MySQL的 python server + log API
使用 `python <fileName>.py`執行以下檔案

from flask import Flask, request, jsonify
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

# MySQL database configuration
db_config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'remote_host_ip', # localhost
    'database': 'your_db' # logdb
}

def create_connection():
    try:
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            print("Connected to MySQL database")
            return connection
    except Error as e:
        print("Error while connecting to MySQL", e)
    return None

@app.route('/log', methods=['POST'])
def log():
    data = request.get_json()

    # Basic input validation
    required_fields = ['HOST_NAME', 'HOST_IP', 'SYSTEM_TYPE', 'LEVEL', 'PROCESS_NAME', 'CONTENT', 'LOG_TIME']
    for field in required_fields:
        if field not in data:
            return jsonify({'status': 'error', 'message': f'Missing field: {field}'}), 400

    try:
        connection = create_connection()
        if connection:
            cursor = connection.cursor()
            insert_query = """
            INSERT INTO log (HOST_NAME, HOST_IP, SYSTEM_TYPE, LEVEL, PROCESS_NAME, CONTENT, LOG_TIME)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            cursor.execute(insert_query, (
                data['HOST_NAME'], data['HOST_IP'], data['SYSTEM_TYPE'], data['LEVEL'],
                data['PROCESS_NAME'], data['CONTENT'], data['LOG_TIME']
            ))
            connection.commit()
            cursor.close()
            connection.close()
            return jsonify({'status': 'success', 'message': 'Log entry added successfully'}), 201
        else:
            return jsonify({'status': 'error', 'message': 'Database connection failed'}), 500
    except Error as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

### 資料庫
建立新DB跟table

-- Create the log database
CREATE DATABASE logdb;

-- Switch to the log database
USE logdb;

-- Create the log table
CREATE TABLE log_data (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    HOST_NAME VARCHAR(16),
    HOST_IP VARCHAR(15),
    SYSTEM_TYPE VARCHAR(20),
    LEVEL VARCHAR(4),
    PROCESS_NAME VARCHAR(64),
    CONTENT VARCHAR(512),
    LOG_TIME DATETIME,
    TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

寫入資料

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

登入、進DB、列table的資料

sudo mysql -u root -p 

use <dbName>

select * from <tableName>

### 試打API
**記得先 `unset http_proxy`** 才可以從本機 curl
Example:

curl -X POST http://localhost:5000/log -H "Content-Type: application/json" -d '{
    "HOST_NAME": "example_host",
    "HOST_IP": "192.168.1.1",
    "SYSTEM_TYPE": "LINUX",
    "LEVEL": "WARN",
    "PROCESS_NAME": "proc",
    "LOG_TIME": "2024-07-23",
    "CONTENT": "HELLO!"
}'

### 透過公司內網建立server連線

#### 內網通訊
若要用另一台電腦建立公司內網間的通訊，只能用port 22才不會被防火牆擋掉，且要暫時關閉proxy.

連接埠傳送的地方進行以下設定，主機IP是本機的IPv4位置，主機port需要是22 (SSH連線預設port)，客體IP是虛擬機的inet，客體連接port需跟server指定的運行端口一致。


在PuTTY可以透過telnet將自己的主機連接至虛擬機進行操作，但要注意主機port不能重複設定22，且VM傳送規則的主機連接埠設定需跟PuTTY中的Port設定一致。設定完成後請先重新啟動VM.



查看是否成功建立連接，可以在開啟server後，從powershell下`curl -v <主機IP>:<主機port>` 指令，如果有成功連接會顯示”200 OK”的status，server端也會顯示200如下圖。

請注意，如果是直接從瀏覽器開啟會失敗(如下圖)，因為瀏覽器本身可能會阻擋未知IP或Port.

#### 結束server
若在terminal運行，可以直接透過ctrl+c結束；若在背景執行可透過ps命令找到腳本進程，`ps -ef`會列出目前正在運行的所有程序，`ps -ef | grep <process name>`可以列出特定名稱的運行程序，透過`kill <PID>`或者`pkill <process name>`可以停止該腳本運行。例如透過`ps -ef`可得知下圖newapp.py的PID是65468.

https://github.com/EdwardLeeee/logger