Dockeryum安装Mysql8、⽤户管理、权限管理、配置详解、常见问题解决⼀、docker安装
1、查看可⽤的 MySQL 版本
2、拉取 MySQL 镜像
这⾥我们拉取官⽅的最新版本的镜像：
$ docker pull mysql:8.0.26
3、查看本地镜像
使⽤以下命令来查看是否已安装了 mysql：
$ docker images
4、创建挂载⽬录
mkdir -p /usr/local/mysql/conf
mkdir -p /usr/local/mysql/data
mkdir -p /usr/local/mysql/logs
5、创建myf
vim /usr/local/mysql/conf/myf
把下⾯配置复制到⽂件中:
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# sql/doc/mysql/en/server-system-variables.html
[client]
default-character-set = utf8mb4
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
# 字符集
character_set_server=utf8
collation-server=utf8_general_ci
# 是否对sql语句⼤⼩写敏感，1表⽰不敏感
lower_case_table_names = 1
# 最⼤连接数
max_connections = 1000
# Innodb缓存池⼤⼩
innodb_buffer_pool_size = 4G
# 表⽂件描述符的缓存⼤⼩
table_open_cache_instances=1
table_open_cache=2000
table_definition_cache=2000
!includedir /etc/mysql/conf.d/
6、创建启动容器
docker run --restart=always --name mysql8.0 --privileged=true \
-d -p 3306:3306 \
-v /usr/local/mysql/conf/myf:/etc/mysql/myf \
-v /usr/local/mysql/logs:/logs \
-v /usr/local/mysql/data/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 mysql:8.0.26
参数解释：
-
-restart=always -> 开机启动容器，容器异常⾃动重启
--name -> 指定容器名称
-d -> 以守护进程的⽅式启动容器
-p -> 映射宿主机端⼝
-v -> 映射配置⽂件、⽇志、数据
-e -> 写⼊配置root密码
7、查看是否启动正常
$ docker ps
使⽤Navicat连接：
⼆、yum 安装
1、(如果服务器接通外⽹)直接⽤wget下载mysql源rpm安装包
cd ~
wget sql//arch.rpm
如果没有安装wget，则安装: yum -y install wget
2、（如果服务器没有接通外⽹）下载mysql源rpm安装包
上传到服务器
3、安装rmp包
yum arch.rpm
4、查看是否安装成功
[root@localhost ~]# yum repolist enabled | grep "mysql.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community 212
!
mysql-tools-community/x86_64 MySQL Tools Community 132
!mysql80-community/x86_64 MySQL 8.0 Community Server 283
5、安装mysql
yum -y install mysql-community-server
6、（可选）修改配置：
vim /etc/myf
把下⾯配置复制到⽂件中:
# For advice on how to change settings please see
# sql/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# sql/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# Custom config should go here
# 字符集
character_set_server=utf8
collation-server=utf8_general_ci
# 是否对sql语句⼤⼩写敏感，1表⽰不敏感
lower_case_table_names = 1
#最⼤连接数
max_connections = 1000
# Innodb缓存池⼤⼩
innodb_buffer_pool_size = 4G
# 表⽂件描述符的缓存⼤⼩
table_open_cache_instances=1
table_open_cache=2000
table_definition_cache=2000
7、启动mysql
service mysqld.service restart
8、查看启动状态
[root@localhost etc]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 四 2021-08-26 16:04:16 CST; 1 day 1h ago
Docs: man:mysqld(8)
sql/doc/refman/en/using-systemd.html
Main PID: 8704 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─8704 /usr/sbin/mysqld
8⽉ 26 16:04:05 localhost.localdomain systemd[1]: Starting
8⽉ 26 16:04:16 localhost.localdomain systemd[1]: Started MySQL Server.
9、配置开机启动
systemctl enable mysqld
systemctl daemon-reload
10、查看初始化密码
[root@localhost etc]# cat /var/log/mysqld.log | grep password
2021-08-26T08:04:10.256089Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Gg2P(&gB<>Z)
11、输⼊命令进⼊mysql
mysql -uroot -p 回车
输⼊初始密码密码（粘贴即可）
12、修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '设置密码';
13、创建远程连接root⽤户、授权
mysql> use mysql;
mysql> create user 'root'@'%' identified by '123456';
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
mysql> flush privileges;
mysql> select user,host from user;
mysql> show grants for root@'%';
14、使⽤Navicat连接
三、⽤户管理
1、新增⽤户
格式：create user '⽤户名'@localhost identified by '密码'; ——localhost表⽰只允许本地连接，'%' 代表允许所有远程连接，ip表⽰只允许指定ip连接；例⼦：create user 'root'@'%' identified by '123456';
# 使⽤password关键字密码需要符合加密⽅式
create user 'root'@'%' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
2、删除⽤户
格式：drop user '⽤户名'@'localhost';
例⼦：drop user 'root'@'%';
3、查看⽤户
# 切换到mysql：
use mysql;
# 执⾏查看语句：
select User,Host,Password from user;
4、更改⽤户密码:
格式：mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '设置新密码';
例⼦：mysql>ALTER USER 'root'@'%' IDENTIFIED BY '123456';
# 使⽤password关键字密码需要符合加密⽅式
mysql>ALTER USER 'root'@'%' password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
格式：mysql>set password for ⽤户名@localhost =password('新密码');
例⼦: mysql>set password for root@'%'=password('123456');
四、权限管理
通常情况下，数据库会根据不同的环境进⾏隔离，有针对性地进⾏权限控制：
'root'@'%' : dev环境开放所有权限，sit，uat回收所有权限
'root'@'192.168.252.%' : 该⽹段开放所有权限
'root'@'IP' : 测试⼈员的IP开放所有权限
1、查看⽤户权限：
格式：show grants for '⽤户名'@'localhost';
例⼦：show grants for 'root'@'%';
2、⽤户授予权限：
格式：grant 权限列表 on 数据库名.数据表名 to '⽤户表名'@'主机' with grant option;
例⼦：grant all privileges on . to 'root'@'%' with grant option;
# 指定密码
grant all privileges on . to 'root'@'%' identified by '123456' with grant option;
# 使⽤password关键字密码需要符合加密⽅式
grant all privileges on . to 'root'@'%' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' with grant option;
3、刷新权限：
授权以后⼀定要刷新权限：
flush privileges;
4、权限回收
格式：revoke 权限列表 on 数据库名.数据库表名 from ⽤户名@主机;
例⼦：revoke all privileges on pcloud_ust2. from 'root'@'%';
5、授权、回收具体权限：select,create,delete,grant
赋予：grant select,create,delete on . to 'root'@'localhost' with grant option;
回收：revoke select,create,delete,grant option on . from 'root'@'localhost';
五、Mysql8.0配置myf详解
1、关键配置
1. 配置⽂件的位置
MySQL配置⽂件
/
etc/myf 或者 /etc/myf.d/serverf
2. ⼏个关键的⽂件：
.pid⽂件，记录了进程id
.sock⽂件，是内部通信使⽤的socket接⼝，⽐3306快
.log⽂件，⽇志⽂件
f或.conf⽂件，配置⽂件
安装⽬录：basedir
数据⽬录：datadir
2、/etc/myf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
# Mysql服务的唯⼀编号每个mysql服务Id需唯⼀
server-id = 1
# 服务端⼝号默认3306
port = 3306
# mysql安装根⽬录
basedir = /usr/local/mysql
# mysql数据⽂件所在位置
datadir = /usr/local/mysql/data
# pid
pid-file = /usr/local/mysql/mysql.pid
# 设置socke⽂件所在⽬录
socket = /tmp/mysql.sock
# 设置临时⽬录
tmpdir = /tmp
# ⽤户
user = root
# 允许访问的IP⽹段
bind-address = 0.0.0.0
# 跳过密码登录
#skip-grant-tables
# 主要⽤于MyISAM存储引擎，如果多台服务器连接⼀个数据库则建议注释下⾯内容
skip-external-locking
# 只能⽤IP地址检查客户端的登录，不⽤主机名
skip_name_resolve = 1
# 事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED
# 数据库默认字符集，主流字符集⽀持⼀些特殊表情符号（特殊表情符占⽤4个字节）
character-set-server = utf8mb4
# 数据库字符集对应⼀些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
# 设置client连接mysql时的字符集，防⽌乱码
init_connect='SET NAMES utf8mb4'
# 是否对sql语句⼤⼩写敏感，1表⽰不敏感
lower_case_table_names = 1
# TIMESTAMP如果没有显⽰声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
# MySQL连接闲置超过⼀定时间后(单位：秒)将会被强⾏关闭
#MySQL默认的wait_timeout 值为8个⼩时, interactive_timeout参数需要同时配置才能⽣效
interactive_timeout = 1800
wait_timeout = 1800
#数据库错误⽇志⽂件
log_error = error.log
3、其他的配置详解
1. 禁⽤mysql的缓存查询结果集功能
# 写⼊频繁的数据库，不要开查询缓存
# 后期根据业务情况测试决定是否开启
# ⼤部分情况下关闭下⾯两项
# ⾸先要把Query_cache和该表相关的语句全部置为失效，然后在写⼊更新。
# 那么如果Query_cache⾮常⼤，该表的查询结构⼜⽐较多，查询语句失效也慢，⼀个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。# 所以在数据库写⼊量或是更新量也⽐较⼤的系统，该参数不适合分配过⼤。⽽且在⾼并发，写⼊量⼤的系统，建议把该功能禁掉。
query_cache_size = 0
query_cache_type = 0
# 指定单个查询能够使⽤的缓冲区⼤⼩，缺省为1M
query_cache_limit = 1M
# 默认是4KB，设置值⼤对⼤数据查询有好处，但如果你的查询都是⼩数据查询，就容易造成内存碎⽚和浪费
# 说明：禁掉查询缓存的⽅法就是直接注释掉查询缓存的配置，如#query_cache_size=1M, 这样就可以了
query_cache_min_res_unit = 4KB
2. 其他需要开的缓存：读缓存、线程缓存、排序缓存
# connection级参数。太⼤将导致在连接数增⾼时，内存不⾜。
sort_buffer_size = 2M
# ⽹络传输中⼀次消息传输量的最⼤值。系统默认值为1MB，最⼤值是1GB，必须设置1024的倍数。
max_allowed_packet = 32M
# 和sort_buffer_size⼀样，该参数对应的分配内存也是每个连接独享
join_buffer_size = 2M
# 默认⼤⼩是 32M。GROUP BY 多不多的问题
tmp_table_size = 256M
# 索引的缓冲区⼤⼩，对于内存在4GB左右的服务器来说，该参数可设置为256MB或384MB。
max_heap_table_size = 256M
mysql下载安装后怎么用key_buffer_size = 2048M
# 进⾏排序查询时，MySql会⾸先扫描⼀遍该缓冲，以避免磁盘搜索
read_buffer_size = 1M
read_rnd_buffer_size = 16M
# 批量插⼊数据缓存⼤⼩，可以有效提⾼插⼊效率，默认为8M
bulk_insert_buffer_size = 64M
# Innodb缓存，只需要⽤Innodb的话则可以设置它⾼达 70-80% 的可⽤内存。
innodb_buffer_pool_size = 2048M
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论