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