本机 node + 容器中的 mysql

小公鸡卡哇伊呀~

已于 2022-12-09 17:11:08 修改

阅读量464

点赞数

文章标签： mysql node.js docker

于 2022-08-17 22:18:30 首次发布

本文链接：https://blog.csdn.net/ftell/article/details/126394801

版权

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 -A

Database 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