docker 创建mysql

南洲牧云
167 阅读7分钟

1. 创建 mysql 容器

docker run -d --name mysql8 \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 mysql:8.2 \
--bind-address=0.0.0.0

2. 创建目录和安装mysql客户端

mkdir -p /data/docker/mysql/conf 
mkdir -p /data/docker/mysql/data
mkdir -p /data/docker/mysql/log
yum install -y mysql

3. 复制文件

docker cp mysql8:/var/lib/mysql /data/docker/mysql/data
docker cp mysql8:/etc/my.cnf /data/docker/mysql/conf/
docker cp mysql8:/var/log/mysqld.log /data/docker/mysql/log

4. 修改配置

在my.cnf文件中，[mysqld] 章节下，添加以下配置

/data/docker/mysql/conf/my.cnf

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_allowed_packet=2G
bind-address=0.0.0.0

5. 删除容器

docker rm -f mysql8

6. 创建容器

docker run -d --name mysql8 \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306  \
-v /etc/localtime:/etc/localtime:ro \
-v /data/docker/mysql/conf/my.cnf:/etc/my.cnf \
-v /data/docker/mysql/data/mysql:/var/lib/mysql \
-v /data/docker/mysql/log/mysqld.log:/var/log/mysqld.log \
mysql:5.7

7. 创建数据库和表

远程连接后，创建数据库和表

create database db_test;
USE `test-spring`;
DROP TABLE  if exists t_user;
CREATE TABLE t_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) DEFAULT NULL UNIQUE,
    email VARCHAR(100) DEFAULT NULL UNIQUE,
    mobile VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

8. 创建测试数据

# 插入记录
INSERT INTO t_user (username, email, mobile, password) VALUES
    ('user1', 'user1@example.com', '13800138001', 'password1'),
    ('user2', 'user2@example.com', '13900139002', 'password2'),
    ('user3', 'user3@example.com', '13600136003', 'password3'),
    ('user4', 'user4@example.com', '13700137004', 'password4'),
    ('user5', 'user5@example.com', '13500135005', 'password5'),
    ('user6', 'user6@example.com', '13400134006', 'password6'),
    ('user7', 'user7@example.com', '13300133007', 'password7'),
    ('user8', 'user8@example.com', '13200132008', 'password8'),
    ('user9', 'user9@example.com', '13100131009', 'password9'),
    ('user10', 'user10@example.com', '13000130010', 'password10'),
    ('user11', 'user11@example.com', '13000130011', 'password11'),
    ('user12', 'user12@example.com', '13000130012', 'password12'),
    ('user13', 'user13@example.com', '13000130013', 'password13'),
    ('user14', 'user14@example.com', '13000130014', 'password14'),
    ('user15', 'user15@example.com', '13000130015', 'password15'),
    ('user16', 'user16@example.com', '13000130016', 'password16'),
    ('user17', 'user17@example.com', '13000130017', 'password17'),
    ('user18', 'user18@example.com', '13000130018', 'password18'),
    ('user19', 'user19@example.com', '13000130019', 'password19'),
    ('user20', 'user20@example.com', '13000130020', 'password20'),
    ('user21', 'user21@example.com', '13000130021', 'password21'),
    ('user22', 'user22@example.com', '13000130022', 'password22'),
    ('user23', 'user23@example.com', '13000130023', 'password23'),
    ('user24', 'user24@example.com', '13000130024', 'password24'),
    ('user25', 'user25@example.com', '13000130025', 'password25'),
    ('user26', 'user26@example.com', '13000130026', 'password26'),
    ('user27', 'user27@example.com', '13000130027', 'password27'),
    ('user28', 'user28@example.com', '13000130028', 'password28'),
    ('user29', 'user29@example.com', '13000130029', 'password29'),
    ('user30', 'user30@example.com', '13000130030', 'password30'),
    ('user31', 'user31@example.com', '13000130031', 'password31'),
    ('user32', 'user32@example.com', '13000130032', 'password32'),
    ('user33', 'user33@example.com', '13000130033', 'password33'),
    ('user34', 'user34@example.com', '13000130034', 'password34'),
    ('user35', 'user35@example.com', '13000130035', 'password35');

9. 终极shell脚本

sh init-db-docker.sh

init-db-docker.sh 内容 ：

#!/bin/bash

echo "安装mysql 客户端..."
yum install -y mysql


echo "创建文件夹..."
mkdir -p /data/docker/mysql/conf
mkdir -p /data/docker/mysql/data
mkdir -p /data/docker/mysql/log
mkdir -p /data/docker/mysql/sql

echo "创建临时容器..."
docker run -d --name mysql8 \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306 mysql:8.2

echo "复制临时容器文件..."
docker cp mysql8:/var/lib/mysql /data/docker/mysql/data
docker cp mysql8:/etc/my.cnf /data/docker/mysql/conf/
docker cp mysql8:/var/log/mysqld.log /data/docker/mysql/log

echo "修改配置..."
sed -i "s/\[mysqld\]/[mysqld] \ncharacter-set-server=utf8 \ncollation-server=utf8_general_ci \nmax_allowed_packet=2G \nbind-address=0.0.0.0/" /data/docker/mysql/conf/my.cnf

echo "创建init.sql..."
cat > /data/docker/mysql/sql/init.sql <<EOF
# 创建数据库和表
create database test_spring;
USE test_spring;
DROP TABLE  if exists t_user;
CREATE TABLE t_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) DEFAULT NULL UNIQUE,
    email VARCHAR(100) DEFAULT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入记录
INSERT INTO t_user (username, email, phone, password) VALUES
    ('user1', 'user1@example.com', '13800138001', 'password1'),
    ('user2', 'user2@example.com', '13900139002', 'password2'),
    ('user3', 'user3@example.com', '13600136003', 'password3'),
    ('user4', 'user4@example.com', '13700137004', 'password4'),
    ('user5', 'user5@example.com', '13500135005', 'password5'),
    ('user6', 'user6@example.com', '13400134006', 'password6'),
    ('user7', 'user7@example.com', '13300133007', 'password7'),
    ('user8', 'user8@example.com', '13200132008', 'password8'),
    ('user9', 'user9@example.com', '13100131009', 'password9'),
    ('user10', 'user10@example.com', '13000130010', 'password10'),
    ('user11', 'user11@example.com', '13000130011', 'password11'),
    ('user12', 'user12@example.com', '13000130012', 'password12'),
    ('user13', 'user13@example.com', '13000130013', 'password13'),
    ('user14', 'user14@example.com', '13000130014', 'password14'),
    ('user15', 'user15@example.com', '13000130015', 'password15'),
    ('user16', 'user16@example.com', '13000130016', 'password16'),
    ('user17', 'user17@example.com', '13000130017', 'password17'),
    ('user18', 'user18@example.com', '13000130018', 'password18'),
    ('user19', 'user19@example.com', '13000130019', 'password19'),
    ('user20', 'user20@example.com', '13000130020', 'password20'),
    ('user21', 'user21@example.com', '13000130021', 'password21'),
    ('user22', 'user22@example.com', '13000130022', 'password22'),
    ('user23', 'user23@example.com', '13000130023', 'password23'),
    ('user24', 'user24@example.com', '13000130024', 'password24'),
    ('user25', 'user25@example.com', '13000130025', 'password25'),
    ('user26', 'user26@example.com', '13000130026', 'password26'),
    ('user27', 'user27@example.com', '13000130027', 'password27'),
    ('user28', 'user28@example.com', '13000130028', 'password28'),
    ('user29', 'user29@example.com', '13000130029', 'password29'),
    ('user30', 'user30@example.com', '13000130030', 'password30'),
    ('user31', 'user31@example.com', '13000130031', 'password31'),
    ('user32', 'user32@example.com', '13000130032', 'password32'),
    ('user33', 'user33@example.com', '13000130033', 'password33'),
    ('user34', 'user34@example.com', '13000130034', 'password34'),
    ('user35', 'user35@example.com', '13000130035', 'password35');


EOF

echo "删除临时容器..."
docker rm -f mysql8 

echo "创建mysql容器..."
docker run -d --name mysql8 \
--restart=always \
-e MYSQL_ROOT_PASSWORD=root \
-p 3306:3306  \
-v /etc/localtime:/etc/localtime:ro \
-v /data/docker/mysql/conf/my.cnf:/etc/my.cnf \
-v /data/docker/mysql/data/mysql:/var/lib/mysql \
-v /data/docker/mysql/log/mysqld.log:/var/log/mysqld.log \
mysql:8.2

sleep 10

echo "复制init.sql到容器里..."
docker cp /data/docker/mysql/sql/init.sql mysql8:/tmp/

echo "执行初始化sql..."

docker exec -i mysql8 bash -c "mysql -uroot -proot < /tmp/init.sql > /tmp/initSql.log" 
echo "执行完毕."
avatar
文章
阅读
粉丝