Docker部署mysql

部署Docker教程：https://bj.cddone.com/doc/14/

使用云原生方案部署mysql数据库方便数据管理迁移备份

部署

1、创建mysql工作目录

mkdir -p /home/docker/mysql && cd /home/docker/mysql
php
52 Bytes
© CDDONE
mkdir -p /home/docker/mysql && cd /home/docker/mysql

2、创建容器yaml

vim docker-compose.yaml
php
23 Bytes
© CDDONE
vim docker-compose.yaml

• 参数说明：
• 设置mysql镜像版本
• 设置容器名称
• 设置容器自启动
• 创建一个库
• 映射端口
• 映射目录

version: '3.1'
services:
  mysql:
    image: mysql
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: '123456'
      MYSQL_DATABASE: www_wordpress
    ports:
      - 3306:3306
    volumes:
      - ./db:/var/lib/mysql
php
255 Bytes
© CDDONE
version: '3.1'
services:
  mysql:
    image: mysql
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: '123456'
      MYSQL_DATABASE: www_wordpress
    ports:
      - 3306:3306
    volumes:
      - ./db:/var/lib/mysql

3、启动容器

docker-compose up -d
php
20 Bytes
© CDDONE
docker-compose up -d

4、查看容器状态

docker ps
php
9 Bytes
© CDDONE
docker ps

CONTAINER ID   IMAGE                      COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
76992769b36c   mysql                      "docker-entrypoint.s…"   9 hours ago      Up 9 hours      0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
php
321 Bytes
© CDDONE
CONTAINER ID   IMAGE                      COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
76992769b36c   mysql                      "docker-entrypoint.s…"   9 hours ago      Up 9 hours      0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

5、链接测试

mysql -h127.0.0.1 -uroot -p
php
27 Bytes
© CDDONE
mysql -h127.0.0.1 -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7874
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| www_dh             |
| www_lol            |
+--------------------+
6 rows in set (0.00 sec)
mysql>
php
719 Bytes
© CDDONE
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7874
Server version: 8.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| www_dh             |
| www_lol            |
+--------------------+
6 rows in set (0.00 sec)

mysql>

6、mysql数据库管理

mysql -h127.0.0.1 -P3333 -uroot -p          #登录数据库
select user,host FROM mysql.user;          #查看用户及权限
CREATE USER 'user'@'%' IDENTIFIED BY 123456;        #创建用户
SHOW GRANTS FOR 'user'@'%';            #查看用户的权限
create database wordpress_bk;            #创建wordpress的数据库
drop database wordpress_bk;            #删除wordpress的数据库
GRANT ALL PRIVILEGES ON wordpress_dh.* TO 'user'@'%';      #授予用户库权限
FLUSH PRIVILEGES;              #刷新权限
REVOKE ALL PRIVILEGES ON wordpress_ai.* FROM 'user'@'localhost';  #删除用户对数据库的权限
mysqldump -uroot -p wordpress_chat > /root/chat.sql      #库文件备份导出
mysql -uroot -p wordpress_chat < /root/chat.sql        #库文件备份导入
php
636 Bytes
© CDDONE
mysql -h127.0.0.1 -P3333 -uroot -p					#登录数据库
select user,host FROM mysql.user;					#查看用户及权限
CREATE USER 'user'@'%' IDENTIFIED BY 123456;				#创建用户
SHOW GRANTS FOR 'user'@'%';						#查看用户的权限
create database wordpress_bk;						#创建wordpress的数据库
drop database wordpress_bk;						#删除wordpress的数据库
GRANT ALL PRIVILEGES ON wordpress_dh.* TO 'user'@'%';			#授予用户库权限
FLUSH PRIVILEGES;							#刷新权限
REVOKE ALL PRIVILEGES ON wordpress_ai.* FROM 'user'@'localhost';	#删除用户对数据库的权限
mysqldump -uroot -p wordpress_chat > /root/chat.sql			#库文件备份导出
mysql -uroot -p wordpress_chat < /root/chat.sql				#库文件备份导入

8、容器管理

• 停止mysql容器、删除容器、删除容器网络、清理本地数据

docker stop mysql && docker rm mysql && docker network rm mysql_default && rm -rf /home/docker/mysql/data/*
php
107 Bytes
© CDDONE
docker stop mysql && docker rm mysql && docker network rm mysql_default && rm -rf /home/docker/mysql/data/*

• 重新创建mysql容器

cd /home/docker/mysql/ && docker-compose up -d
php
46 Bytes
© CDDONE
cd /home/docker/mysql/ && docker-compose up -d