Linux 建立mySQL (Mariadb) 相關流程

//=====刪除指令=====

sudo systemctl disable mariadb

sudo pkill mariadbd

sudo rm -rf /etc/mysql

sudo rm -rf /var/lib/mysql

//======安裝指令==========

sudo apt-get install mariadb-server 在Ubuntu 安裝mariadb

sudo systemctl start mariadb 啟動mariadb

sudo systemctl stop mariadb

sudo systemctl status mariadb.service 查看maradb狀態

sudo apt install python3-pymysql 安裝pymysql 庫

sudo mysql -u root -p 進入創建數據庫，兩次enter 不用密碼

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password

USING PASSWORD("password"); //新建登入DB密碼

flush privileges;

(刷新權限)​​

指令sudo mysql_secure_installation 用於設定MySQL 或MariaDB 資料庫伺服器的安全性設定。它會引導你通過一系列的步驟，包括設定root 密碼、刪除測試資料庫、刪除不必要的使用者帳號、限制遠端root 登錄等等。這些步驟旨在幫助保護數據庫免受未經授權的訪問和潛在的安全風險。

MySQL 開啟遠端連線權限允許遠端裝置連接資料庫

查看目前MySQL 所監聽的連接埠：

# sudo netstat -tlnp | grep mariadb

# sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

如果要綁定所有網路界面，那就把bind-address 欄位值設為0.0.0.0 吧！如

果實要綁定多個網絡界面，也是設成0.0.0.0，再用防火牆去擋住其它不需要用到

的網路介面。

sudo systemctl restart mysql

# sudo netstat -tlnp | grep mariadbd

$ mysql -u admin -pPassword

建立新資料庫

MariaDB [(none)]>CREATE DATABASE foodsDB;

MariaDB [(none)]> CREATE USER 'order'@'localhost' IDENTIFIED BY 'Ff123456'; 為你的資料建立帳密

MariaDB [(none)]> DROP USER 'order'@'123.195.57.1​​11';刪除

MariaDB [(none)]> GRANT ALL PRIVILEGES ON foodsDB.* TO 'order'@'localhost';授權

MariaDB [(none)]> FLUSH PRIVILEGES; 生效

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| foodsDB |

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

MariaDB [(none)]> USE foodsDB;

Database changed

MariaDB [foodsDB]> CREATE TABLE food_items (

-> id INT AUTO_INCREMENT PRIMARY KEY,

-> category VARCHAR(255),

-> name VARCHAR(255),

-> image VARCHAR(255),

-> description TEXT,

-> price DECIMAL(10, 2)

-> );

Query OK, 0 rows affected (0.120 sec)

MariaDB [(none)]> \q 離開, ps. 反/

執行python

import json

import pymysql

# 連接到MySQL 資料庫

conn = pymysql.connect(

host='localhost',

user='order',

password='Ff123456',

database='foodsDB'

)

# 修改表字元集為utf8mb4

with conn.cursor() as cursor:

cursor.execute("ALTER TABLE food_items CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")

# 讀取foods.js 檔案中的數據

with open('foods.js', 'r') as f:

foods_data = json.load(f)

# file_content = f.read()

# print(file_content)

# 插入資料到MySQL 資料庫表格中

with conn.cursor() as cursor:

for category, items in foods_data.items():

for food in items:

sql = "INSERT INTO food_items (category, id, name, image, description, price) VALUES (%s, %s, %s, %s, %s, %s)"

cursor.execute(sql, (category, food['id'], food['name'], food['image'], food['description'], food['price']))

# 提交事務

conn.commit()

# 關閉資料庫連接

conn.close()