Docker部署Mysql8

1、下载镜像

#拉取镜像
docker pull mysql:8.3.0
#查看docker已拉取镜像
docker images

2、提前创建目录

mkdir -p /usr/local/mysql/log
mkdir -p /usr/local/mysql/date
mkdir -p /usr/local/mysql/conf

3、编辑配置文件  

vim /home/mysql/conf/my.cnf

[client]
#设置客户端默认字符集utf8mb4
default-character-set=utf8mb4
[mysql]
#设置服务器默认字符集为utf8mb4
default-character-set=utf8mb4
[mysqld]
#配置服务器的服务号，具备日后需要集群做准备
server-id = 1
#开启MySQL数据库的二进制日志，用于记录用户对数据库的操作SQL语句，具备日后需要集群做准备
log-bin=mysql-bin
#设置清理超过30天的日志，以免日志堆积造过多成服务器内存爆满。2592000秒等于30天的秒数
binlog_expire_logs_seconds = 2592000
#解决MySQL8.0版本GROUP BY问题
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#允许最大的连接数
max_connections=1000
# 禁用符号链接以防止各种安全风险
symbolic-links=0
# 设置东八区时区
default-time_zone = '+8:00'

4、运行MySQL8.3.0服务

docker run \
-p 3306:3306 \
--restart=always \
--name mysql \
--privileged=true \
-v /usr/local/mysql/log:/var/log/mysql \
-v /usr/local/mysql/date:/var/lib/mysql \
-v /usr/local/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-e MYSQL_ROOT_PASSWORD=ebank@123 \
-d mysql:8.3.0 

-p：表示声明端口，宿主机与容器内端口一一对应；
--restart=always：表示服务器开机自动启动服务；
--name：表示声明服务的名称；
--privileged=true：表示使container内的root拥有真正的root权限。
-v：表示将宿主机的文件映射到容器内，分别映射日志文件、数据文件、配置文件；
-e：表示配置root密码；
-d：表示通过mysql8.0镜像运行服务。

5、进入mysql实例

docker exec -it mysql /bin/bash

6、登录mysql客户端

mysql -u root -p

7、创建一个账号-admin，用来进行远程访问

CREATE USER 'admin'@'%' IDENTIFIED BY 'ebank@123';

8、配置用户密码为mysql_native_password 模式，即可远程连接：

ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'ebank@123';

9、刷新权限

FLUSH PRIVILEGES;

10、对mysql进行操作

#创建数据库 uyun
create database uyun;

#切换到uyun数据库
use uyun;
 
#创建表结构
CREATE TABLE department (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE emp (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    project VARCHAR(100),
    dept_id INT,
    birth_date DATE,
    salary DECIMAL(10, 2),
    address VARCHAR(200),
    hire_date DATE,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

 
#在sys_mytest中添加几条数据(建议在Navicat工具中操作)
-- 插入部门数据
INSERT INTO department (dept_name, location) VALUES 
('R&D', 'Headquarters, 3rd Floor'),
('Marketing', 'Shanghai Branch, 2nd Floor'),
('Finance', 'Headquarters, 1st Floor'),
('HR', 'Guangzhou Branch, 1st Floor'),
('Sales', 'Shenzhen Branch, 3rd Floor');

-- 插入员工数据
INSERT INTO emp (emp_name, project, dept_id, birth_date, salary, address, hire_date) VALUES
('John Smith', 'E-commerce Platform', 1, '1990-05-15', 15000.00, '123 Zhongguancun Street, Haidian District', '2018-03-10'),
('Emily Johnson', 'Mobile App Optimization', 1, '1988-11-22', 18000.00, '88 Jianguo Road, Chaoyang District', '2016-07-15'),
('Michael Brown', 'Marketing Campaign', 2, '1992-03-08', 12000.00, 'Zhangjiang High-Tech Park, Pudong', '2019-05-20'),
('Sarah Davis', 'Annual Financial Audit', 3, '1985-07-30', 20000.00, 'Finance Street, Xicheng District', '2015-09-12'),
('Robert Wilson', 'Employee Training Program', 4, '1993-09-14', 11000.00, 'Zhujiang New Town, Tianhe District', '2020-02-18'),
('Jennifer Lee', 'North China Sales', 5, '1991-12-05', 14000.00, 'Technology Park, Nanshan District', '2019-08-22'),
('David Taylor', 'Data Platform Development', 1, '1989-06-25', 22000.00, 'Xi'erqi, Haidian District', '2017-04-05'),
('Jessica Martinez', 'Brand Promotion', 2, '1994-02-18', 10000.00, 'Nanjing West Road, Jingan District', '2021-01-10'),
('Thomas Anderson', 'Compensation System Reform', 4, '1987-10-11', 16000.00, 'East Wind Middle Road, Yuexiu District', '2018-11-15'),
('Lisa Jackson', 'South China Sales', 5, '1995-04-30', 13000.00, 'Huaqiang North, Futian District', '2020-07-01');