MySQL使用Docker部署
1.拉取docker镜像
>> sudo docker pull mysql:8
2.后台启动mysql容器
>> sudo docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8
3.连接mysql数据库
# 1.-p 后直接输入密码，则不可加空格，并在此输入密码，密码会显示出来
>> sudo docker exec -it mysql mysql -uroot -p123456
# 2.-p后按回车，命令行窗口就会自动跳到下一行
>> sudo docker exec -it mysql mysql -u root -p
4.创建数据库
mysql> create database webdb;
Query OK, 1 row affected (0.01 sec)
5.开启插件
5.1 开启插件mysql_native_password
# 1.进入docker容器
>> sudo docker exec -it mysql bash
# 2.将容器中的配置文件拷贝出来
>> sudo docker cp mysql:/etc/my.cnf ./my.cnf
Successfully copied 2.56kB to /home/mirror/my.cnf
# 3.编辑my.cnf文件
>> vim ./my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
mysql_native_password=ON
host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
# 4.拷贝回去
>> sudo docker cp ./my.cnf mysql:/etc/my.cnf
# 5.重启容器
>> sudo docker restart mysql
81089995e329
6.添加用户，并给予数据库权限
# 1.创建用户并开启登录权限
mysql> CREATE USER 'mirror'@'%' IDENTIFIED WITH mysql_native_password BY 'cjy';
Query OK, 0 rows affected (0.01 sec)
# 2.将webdb数据库所有权限开放给用户mirror
mysql> GRANT ALL PRIVILEGES ON webdb.* TO 'mirror'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
# 3.刷新生效
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
# 4.给用户mirror数据库webdb访问权限
mysql> grant all on webdb.user to 'mirror'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 5.刷新权限，使之生效
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
后续需要删除用户，可以使用如下命令：
mysql> DROP USER IF EXISTS 'mirror'@'%';
Query OK, 0 rows affected (0.01 sec)
如果需要给用户所有数据库权限：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mirror'@'%' WITH GRANT OPTION;
用户登录权限修改语句：
# 1.修改用户登录权限
mysql> UPDATE mysql.user SET Host = 'localhost' WHERE User = 'mirror' AND Host = '%';
mysql> FLUSH PRIVILEGES;
# 2.查看用户登录权限
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mirror | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
7.使用Navicat工具创建表
7.1 登录MySQL数据库
"文件" -> "新建" -> "MySQL..."->填写相关配置：
连接名：local
主机：localhost
端口：3306
用户名：mirror
密码：cjy
点击"测试连接" -> "确定"即可。
7.2 创建表
- (a) 创建表：左键单击"webdb" -> 右键单击"表" -> "新建表" -> 使用"添加字段" 、"插入字段"和"删除字段"来添加或删除表字段，得到表结果。
- (b) 导出对应sql语句：右键单击创建表名 -> 左键单击"转储SQL文件" -> 选择"仅结构"或"结构和数据"就可以得到对应sql文件。
/*
Navicat Premium Data Transfer
Source Server : DockerMirror
Source Server Type : MySQL
Source Server Version : 80404
Source Host : localhost:3306
Source Schema : webdb
Target Server Type : MySQL
Target Server Version : 80404
File Encoding : 65001
Date: 07/03/2025 11:44:11
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户密码',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;