- 安装mysql
docker search mysql
docker pull mysql
-- 简单方式
docker run --name mysql8.0 -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root12340 mysql:8.0.12 \
--character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
-- 复杂方式
docker run \
--privileged=true \
--restart=always \
-v /usr/local/docker_data/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/docker_data/mysql/data:/var/lib/mysql \
-v /usr/local/docker_data/mysql/logs:/var/log/mysql \
--privileged=true \
-p 3306:3306 \
--name mysql \
-e MYSQL_ROOT_PASSWORD=root12340 \
-d mysql:latest \
--default_authentication_plugin=mysql_native_password \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci
docker run \
--privileged=true \
--restart=always \
-v /usr/local/docker_data/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /usr/local/docker_data/mysql/data:/var/lib/mysql \
-v /usr/local/docker_data/mysql/logs:/var/log/mysql \
--privileged=true \
-p 3306:3306 \
--name mysql \
-e MYSQL_ROOT_PASSWORD=root12340 \
-d mysql:8.0.2 \
--default_authentication_plugin=mysql_native_password \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci
mysql5.7
docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root1230 -d -v /usr/local/docker_data/mysql/data:/var/lib/mysql -v /usr/local/docker_data/mysql/conf:/etc/mysql/ -v /usr/local/docker_data/mysql/logs:/var/log/mysql ccr.ccs.tencentyun.com/yubang/mysql:5.7.20
docker run --name mysql5.7 -p 3307:3307 -e MYSQL_ROOT_PASSWORD=root1230 -d -v /usr/local/docker_data/mysql/data:/var/lib/mysql -v /usr/local/docker_data/mysql/conf:/etc/mysql/ -v /usr/local/docker_data/mysql/logs:/var/log/mysql mysql:5.7.2
- 进入mysql
docker ps|grep mysql #查看container_id
docker exec -it ${container_id} mysql -uroot -p Caad1230
Enter password:
- 设置mysql
CREATE USER 'root'@'%' IDENTIFIED BY 'root123450';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.18.5.13' IDENTIFIED BY 'root12340' WITH GRANT OPTION;
- Navicat12.1 无法连接
由于新版本的MySQL8.0修改了默认的密码加密方式,旧版的Navicat连接报错,新的我发在群里
1.进入mysql 容器 docker exec -it mysql /bin/bash
2.登录mysql mysql -u root -p
3.ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root12340';
4.GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root12340' WITH GRANT OPTION;
4.FLUSH PRIVILEGES;
5.exit 重启mysql 容器
重启MySQL服务器:systemctl restart mysqld
新方式
如果没有用户进行创建,有就跳过
select user,host from user;
# 第一先刷新
FLUSH PRIVILEGES;
# create user 'root'@'%' identified by 'root123450';
# 修改远程访问
grant all on *.* to 'root'@'%';
flush privileges;
# 修改加密规则
ALTER USER 'root'@'%' IDENTIFIED BY 'root12340' PASSWORD EXPIRE NEVER;
# 修改密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root12340';
FLUSH PRIVILEGES;
如果不能密码登录,可以在my.cnf添加 skip-grant-tables
如果不能修改文件,可以将挂载的文件修改后,cp 到/etc/my.cnf,在重启容器
如果还是不行,指定ip
# 给指定用户权限
GRANT ALL PRIVILEGES ON . TO 'root'@'172.18.5.13' IDENTIFIED BY 'root';
grant all on *.* to 'root'@'172.18.5.13';
ALTER USER 'root'@'172.18.5.13' IDENTIFIED BY 'root12340' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'172.18.5.13' IDENTIFIED WITH mysql_native_password BY 'root12340';
#创建用户
CREATE USER 'logstash'@'172.18.5.13' IDENTIFIED BY 'logstash12340';
GRANT ALL PRIVILEGES ON *.* TO 'logstash'@'%';
ALTER USER 'logstash'@'%' IDENTIFIED BY 'logstash12340' PASSWORD EXPIRE NEVER;
ALTER user 'logstash'@'%' IDENTIFIED WITH mysql_native_password BY 'logstash12340';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.18.5.13';
ALTER USER 'root'@'172.18.5.13' IDENTIFIED BY 'root12340' PASSWORD EXPIRE NEVER;
ALTER user 'root'@'172.18.5.13' IDENTIFIED WITH mysql_native_password BY 'root12340';
FLUSH PRIVILEGES;
# 腾讯云开发提供
grant all on *.* to root@'172.18.5.13' WITH GRANT OPTION;
create user 'root'@'172.18.5.13' identified by 'root12340';
grant all on *.* to 'root'@'172.18.5.13';
alter user 'root'@'172.18.5.13' identified with mysql_native_password by 'root12340';
FLUSH PRIVILEGES;
- 字符集
character_set_client=utf8mb4 #客户端发来SQL的编码
character_set_connection=utf8mb4 #服务端用于解析客户端连接SQL的编码
character_set_results=utf8mb4 #服务端发送给客户端SQL结果的编码
我们以字符集utf8mb4为例,常用的排序规则有:utf8mb4_general_ci、utf8mb4_bin、utf8mb4_unicode_ci。
3.1 utf8mb4_general_ci
ci即case insensitive,不区分大小写。没有实现Unicode排序规则,在遇到某些特殊语言或者字符集,排序结果可能不一致,但是,在绝大多数情况下,这些特殊字符的顺序并不需要那么精确。另外,在比较和排序的时候速度更快。
3.2 utf8mb4_bin
将字符串每个字符用二进制数据编译存储,区分大小写,而且可以存二进制的内容。
3.3 utf8mb4_unicode_ci
不区分大小写,基于标准的Unicode来排序和比较,能够在各种语言之间精确排序,在特殊情况下,Unicode排序规则为了能够处理特殊字符的情况,实现了略微复杂的排序算法,所以兼容度比较高,但是性能不高。
- 其它
MySQL中的utf8(utf8mb3)是只占3个字节的简版,实际上utf8mb4才是我们通常意义上的utf8,每个字符占用4个字节
7.性能优化
innodb_buffer_pool_size = 16G
innodb_log_buffer_size = 100M
innodb_log_file_size = 1G
innodb_flush_method = O_DIRECT #避免双缓冲技术
max_allowed_packet = 16M #最大允许的数据包大小,16M,默认1024*1024*4
thread_cache_size = 12
innodb_autoextend_increment = 128M
sync_binlog = 0 #事务已提交,而无需同步到磁盘。
innodb_flush_log_at_trx_commit = 0 #0意味着刷新到磁盘,但不同步(在提交时不执行实际IO)
性能监控工具:zabbix监控MySQL(windows和linux环境)
> https://cloud.tencent.com/info/717250f6fa05582ccc9ca56eb19ad847.html
> https://github.com/itnihao/zabbix-book
8.修改密碼
1.docker exec -it mysql /bin/bash
cd /etc/mysql
vi my.cnf
#在最后添加:
skip-grant-tables
#在启mysql时不启动grant-tables
2.docker restart mysql
3.进入容器
4.mysql -u root -p
yaml
version: '3.3'
services:
mysql:
container_name: mysql
image: 'mysql:8.0.12'
restart: always
ports:
- "3306:3306"
volumes:
- /var/data/mysql:/var/lib/mysql
- /etc/mysql:/etc/mysql
- ./mysql-files:/var/lib/mysql-files
environment:
MYSQL_ROOT_PASSWORD: root12340
MYSQL_DATABASE: datawell_dev
MYSQL_USER: root
MYSQL_PASSWORD: root12340
character-set-server: utf8mb4
collation-server: utf8mb4_unicode_ci