1 使 mysql 在容器中运行

抓取 mysql image：

docker pull mysql

然后启动容器：

docker container run -d -p 3306:3306 --name mysql-container -e MYSQL_RANDOM_ROOT_PASSWORD=yes mysql

1.1 获取 root 密码

使用上述命令， mysql 为root 用户随机生成密码，因此，当容器运行之后，使用命令：
C:\Users\yanchun>docker logs mysql-container 查看 log 行中的密码：
可以看到密码所在行：
2022-08-17 11:20:20+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: IwlJ4bdTufRa2dR16crD/Zq3z+2HO6RW

2 新建 node 工程

安装 package：
npm install express mysql2 nodemon

2.1 package.json

package.json 中增加行 "serve": "nodemon app.js"：

  "name": "mysql-node","version": "1.0.0","description": "","main": "index.js","scripts": {"serve": "nodemon app.js","test": "echo \"Error: no test specified\" && exit 1"},"keywords": [],"author": "","license": "ISC","dependencies": {"express": "^4.18.1","mysql2": "^2.3.3","nodemon": "^2.0.19"}
}

2.2 创建连接

以下代码首次运行，数据库名称未知，所以 database: "nodemysql" 省略,
浏览器输入 http://localhost:3000/createdb 创建数据库后，database: "nodemysql" 这一行再加回去。
(或者不按上面的步骤使，直接手动创建数据库也可以。)

// create connection
const db = mysql.createConnection({host: "localhost",user: "root",password: "IwlJ4bdTufRa2dR16crD/Zq3z+2HO6RW",database: "nodemysql",   // 首次运行时，database 名称未知，此行省略
});

2.3 完整的 app.js 如下

包含了创建数据库，创建表，插入记录，更新记录，删除记录，全部 CURD 操作

const express = require("express");const mysql = require("mysql2");// create connection
const db = mysql.createConnection({host: "localhost",user: "root",password: "IwlJ4bdTufRa2dR16crD/Zq3z+2HO6RW",database: "nodemysql",
});// connect
db.connect((err) => {if (err) throw err;console.log("MySQL connection established...");
});const app = express();// create database
app.get("/createdb", (req, res) => {let sql = "CREATE DATABASE nodemysql";db.query(sql, (err, result) => {if (err) throw err;console.log(result);res.send("Database created...");});
});// create table
app.get("/createpoststable", (req, res) => {let sql ="CREATE TABLE posts(id int AUTO_INCREMENT, title VARCHAR(255), body VARCHAR(255), PRIMARY KEY (id))";db.query(sql, (err, result) => {if (err) throw err;console.log(result);res.send("Posts table created...");});
});// Insert post 1
app.get("/addpost1", (req, res) => {let post = { title: "Post One", body: "This is post number 1" };let sql = "INSERT INTO posts SET?";let query = db.query(sql, post, (err, result) => {if (err) throw err;console.log(result);res.send("Post 1 added...");});
});// Insert post 2
app.get("/addpost2", (req, res) => {let post = { title: "Post TWo", body: "This is post number 2" };let sql = "INSERT INTO posts SET?";let query = db.query(sql, post, (err, result) => {if (err) throw err;console.log(result);res.send("Post 2 added...");});
});// Select posts
app.get("/getposts", (req, res) => {let sql = "SELECT * FROM posts";let query = db.query(sql, (err, results) => {if (err) throw err;console.log(results);res.send(results);});
});// Select single post
app.get("/getpost/:id", (req, res) => {let sql = `SELECT * FROM posts WHERE id=${req.params.id}`;let query = db.query(sql, (err, result) => {if (err) throw err;console.log(result);res.send(result);});
});// update post
app.get("/updatepost/:id", (req, res) => {let newTitle = "Updated Title";let sql = `UPDATE posts SET title='${newTitle}' WHERE id=${req.params.id}`;let query = db.query(sql, (err, result) => {if (err) throw err;console.log(result);res.send("Post updated...");});
});// delete post
app.get("/deletepost/:id", (req, res) => {let sql = `DELETE FROM posts WHERE id=${req.params.id}`;let query = db.query(sql, (err, result) => {if (err) throw err;console.log(result);res.send("Post deleted...");});
});app.listen("3000", () => {console.log("Server started on http://localhost:3000");
});

2.4 启动 node app：

node run serve

浏览器 http://localhost:3000 可以对数据库进行各种操作，例如，获取 posts 表的全部记录：

3 手动与容器中的 mysql 交互

Docker - How to take a look at the Tables inside MySQL volume?

使用命令以下命令，-p 后面的一长串是随机生成的密码，来源于前面的 1.1

C:\Users\yanchun>docker exec -it mysql-container mysql -uroot -pIwlJ4bdTufRa2dR16crD/Zq3z+2HO6RW

原文中的命令是这样的：
docker exec -it mysql_container_name mysql -uroot -p
但接下来的数次输入密码都出错，不知道原因，故实际使用的命令将密码直接合并到命令行。

mysql> USE nodemysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select * from posts;
+----+----------+-----------------------+
| id | title    | body                  |
+----+----------+-----------------------+
|  1 | Post One | This is post number 1 |
+----+----------+-----------------------+
1 row in set (0.01 sec)mysql>

4 容器中的 mysql 备份

Backup MySQL Database That Running on Docker Container
https://dev.to/lanandra/backup-mysql-database-that-running-on-docker-container-1k8h

docker exec [mysql_container_name] /usr/bin/mysqldump -u [mysql_username] --password=[mysql_password] [database_name] > [destination_path]
实际运行命令：

C:\Users\yanchun>docker exec mysql-container  /usr/bin/mysqldump -u root --password=IwlJ4bdTufRa2dR16crD/Zq3z+2HO6RW nodemysql > d:\sqlbackup.sql

sqlbackup.sql 文件内容，因为数据库数据一般要持久化保存，所以要使用 volume, 但是当前容器只是简单启动，没有使用卷，所以估计还有别的备份数据的方法。

-- MySQL dump 10.13  Distrib 8.0.30, for Linux (x86_64)
--
-- Host: localhost    Database: nodemysql
-- ------------------------------------------------------
-- Server version	8.0.30/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Table structure for table `posts`
--DROP TABLE IF EXISTS `posts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `posts` (`id` int NOT NULL AUTO_INCREMENT,`title` varchar(255) DEFAULT NULL,`body` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `posts`
--LOCK TABLES `posts` WRITE;
/*!40000 ALTER TABLE `posts` DISABLE KEYS */;
INSERT INTO `posts` VALUES (1,'Post One','This is post number 1');
/*!40000 ALTER TABLE `posts` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2022-08-17 13:28:42

Stack overflow 上的另一种备份方法，但是应该不是容器中的mysql 备份方法：
Node.js: Backup MySQL database
https://stackoverflow.com/questions/30921435/node-js-backup-mysql-database

油鹳视频： Using MySQL With Node.js

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！