1.新建docker-compose.yml
version: '3'
services:
mysql:
restart: always
image: mysql:5.7.18
container_name: mysql-5.7.18
privileged: true
volumes:
- /Users/mysql/data:/var/lib/mysql
- /Users/mysql/conf/my.cnf:/etc/my.cnf
- /Users/mysql/source:/docker-entrypoint-initdb.d
environment:
- "MYSQL_ROOT_PASSWORD=root"
- "TZ=Asia/Shanghai"
ports:
- 3306:3306
2.新建目录
mkdir -p /opt/mysql/{data,conf,source}
3.新建my.cnf
[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
character-set-client-handshake=FALSE
collation-server=utf8_unicode_ci
init_connect='SET NAMES utf8'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
4.实操命令
[root@localhost Users]# mkdir -p /Users/mysql/{data,conf,source} # 创建挂载需要的目录
[root@localhost Users]# cd mysql/
[root@localhost mysql]# ll
total 0
drwxr-xr-x. 2 root root 6 Apr 3 04:37 conf
drwxr-xr-x. 2 root root 6 Apr 3 04:37 data
drwxr-xr-x. 2 root root 6 Apr 3 04:37 source
[root@localhost mysql]# vim docker-compose.yml
[root@localhost mysql]# cd conf/
[root@localhost conf]# ll
total 0
[root@localhost conf]# vim my.cnf
[root@localhost conf]# ll
total 4
-rw-r--r--. 1 root root 247 Apr 3 04:53 my.cnf
[root@localhost conf]# ss -tunlp |grep 3306
[root@localhost conf]# cd ..
[root@localhost mysql]# ll
total 4
drwxr-xr-x. 2 root root 20 Apr 3 04:53 conf
drwxr-xr-x. 2 root root 6 Apr 3 04:37 data
-rw-r--r--. 1 root root 377 Apr 3 04:51 docker-compose.yml
drwxr-xr-x. 2 root root 6 Apr 3 04:37 source
[root@localhost mysql]# docker-compose up -d
[+] Running 12/12
✔ mysql 11 layers [⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿] 0B/0B Pulled 216.7s
✔ 9f0706ba7422 Pull complete 122.7s
✔ 2290e155d2d0 Pull complete 122.8s
✔ 547981b8269f Pull complete 123.1s
✔ 2c9d42ed2f48 Pull complete 123.2s
✔ 55e3122f1297 Pull complete 125.4s
✔ abc10bd84060 Pull complete 125.4s
✔ c0a5ce64f2b0 Pull complete 125.5s
✔ c4595eab8e90 Pull complete 211.3s
✔ 098988cead35 Pull complete 211.3s
✔ 300ca5fa5eea Pull complete 211.4s
✔ 43fdc4e3e690 Pull complete 211.5s
[+] Running 1/1
✔ Container mysql Started 1.5s
[root@localhost mysql]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 5.7.18 44a8e1a5c0b2 5 years ago 407 MB
[root@localhost mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
43e97fa6b64f mysql:5.7.18 "docker-entrypoint..." 29 seconds ago Up 27 seconds 0.0.0.0:3306->3306/tcp mysql
[root@localhost mysql]# docker exec -it 43e97fa6b64f /bin/bash # 进入容器
root@43e97fa6b64f:/# mysql -uroot -p #连接mysql默认端口3306可以不写
Enter password: #输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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 |
+--------------------+
4 rows in set (0.01 sec)
mysql> exit;
Bye
root@43e97fa6b64f:/# exit;
exit
[root@localhost mysql]#
5.Mysql命令
连接进入MySQL：./mysql -P 端口-u用户名 -p密码
查看数据库：mysql> show databases;
创建数据库：mysql> create database zhyw default character set utf8mb4 collate utf8mb4_bin;
进入数据库：mysql> use zhyw;
查看数据库表：mysql> show tables;
查看表结构：mysql> describe 表名;
eg：describe t_report_tjfx;
修改数据库表名：alter table 旧表名 rename 新表名;
eg：alter table t_report_tjfx rename t_report_tjfx_20230417;
运行sql文件到MySQL数据库里 source /sql文件存放目录/.sql文件
eg：mysql> source /nfsbak/20230417/t_report_tjfx.sql
备份数据库：从数据库导出数据：
备份库：./mysqldump -h [链接ip] -P [端口P大写] -u [用户名] -p [数据库密码] [数据库名] >/备份存放目录/备份文件名.sql
eg：mysql>./mysqldump -P 3307 -u root -p zhyw> /nfsbak/20230418/zhyw-20230418.sql
数据库表备份：
备份表：./mysqldump -P 3307 -u root -p 库名 表名 > /备份存放目录/备份文件名.sql
eg：./mysqldump -P 3307 -u root -p zhyw t_report_tjfx > /nfsbak/20230417/t_report_tjfx-20230417.sql
