一、背景
在企业中相信大家会遇到很多不同的测试服务器,在运维人员中有大量的部署工作要做,需要连接mysql数据库进行测试,做压力测试的,这里选择docker安装mysql5.7,简单便捷。
至于为什么选择mysql5.7,是因为mysql5.7版本稳定,其次安装相较于其他高版本(mysql8)实在是简单太多了,少了很多grant等初始化操作,比如access denied for user root......password no。看到这些连接错误提示信息实在头疼。
前提
二、安装 docker,docker-compose
国内镜像源
Docker中国区官方镜像
https://registry.docker-cn.com
网易
http://hub-mirror.c.163.com
ustc
https://docker.mirrors.ustc.edu.cn
通过daemon.json文件来修改
(1)在/etc/docker目录中添加daemon.json文件,内容如下:
{
"registry-mirrors": ["http://hub-mirror.c.163.com","https://registry.docker-cn.com","https://docker.mirrors.ustc.edu.cn"]
}
(2)重启docker服务:
systemctl restart docker
(3)执行:docker info,查看是否配置成功:
三、创建docker-compose 编排文件
# vim docker-compose.yml
version: '3.8'
services:
mysql:
container_name: mysql57
image: mysql:5.7.31
restart: always
ports:
- 3307:3306
privileged: true
volumes:
- $PWD/mysql57/log:/var/log/mysql
- $PWD/mysql57/conf/my.cnf:/etc/mysql/my.cnf
- $PWD/mysql57/data:/var/lib/mysql
environment:
MYSQL_ROOT_PASSWORD: "123456"
MYSQL_USER: 'haima'
MYSQL_PASS: '123456'
command: [
'--character-set-server=utf8mb4',
'--collation-server=utf8mb4_general_ci',
'--max_connections=3000'
]
networks:
- myweb
networks:
myweb:
driver: bridge
三.创建初始化脚本 init-mysql.sh
#!/bin/bash
mkdir -p $PWD/mysql57/{conf,data,log} #创建本地文件夹
#新建配置文件
tee $PWD/mysql57/conf/my.cnf<<-'EOF'
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
lower_case_table_names=1 #实现mysql不区分大小(开发需求,建议开启)
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
default-time_zone = '+8:00'
# 更改字符集 如果想Mysql在后续的操作中文不出现乱码,则需要修改配置文件内容
symbolic-links=0
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
EOF
四、编排文件以及安装脚本目录结构
[root@centos7 mysql]# chmod +x init-mysql.sh docker-compose.yml #加执行权限
[root@centos7 mysql]# ./init-mysql.sh
[root@centos7 mysql]# tree ./ #查看目结构
./
├── docker-compose.yml
├── init-mysql.sh
└── mysql57
├── conf
│ └── my.cnf
├── data
└── log
五、启动服务
docker-compose up -d
此时服务已经启动成功了.使用角本是不是很爽,嘿嘿...
登陆mysql
[root@centos7 mysql57]# docker exec -it mysql57 mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.00 sec)
mysql>
mysql 配置My.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /data/
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 100G
table_open_cache = 512
sort_buffer_size = 2M
net_buffer_length = 4K
read_buffer_size = 2M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""
innodb_data_home_dir = /data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/
innodb_buffer_pool_size = 512M
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
常用命令
docker ps -a #查看启动的服务
docker-compose up -d #后台启动服务
docker-compose -h #帮助命令
docker-compose down #停止并删除服务
docker-compose restart #重启服务
docker-compose stop #停止服务
docker-compose start #停止服务
docker-compose logs #停止日志