文章目录
- 一、docker部署MySQL
- 1.1.拉取镜像
- 1.2.查看镜像
- 1.4.启动容器
- 1.3. 创建挂载目录
- 1.5.复制容器配置文件到挂载目录
- 1.6.销毁容器
- 1.7.挂载方式启动容器
- 1.4. 自定义my.cnf文件
- 1.6.mycnf配置参数参考
- 二、部署中遇到的问题及解决方案
- 2.1.启动MYSQL服务时出现无法创建/var/log/error.log
- 2.2.Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
- 2.3.mysql登录报错:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
- 2.4.Warning: World-writable config file '/etc/mysql/my.cnf' is ignored
- 总结
# 前言
- 容器间的网络服务和外部机器是不能直接通信的
- 外部机器和宿主机是可以直接通信的
- 宿主机和容器可以直接通信
- 当容器中的网络服务需要被外部机器访问时,可以将容器中提供服务的端口映射到宿主机端口上。外部机器访问宿主机的该端口,从而间接访问容器里的服务
- 以上描述的就是端口映射
一、docker部署MySQL
1.1.拉取镜像
docker pull mysql
1.2.查看镜像
docker images
1.4.启动容器
docker run \
--restart=always \
--privileged=true \
-p 3306:3306 \
--name mysql \
-e MYSQL_ROOT_PASSWORD=123456a \
-d mysql:latest \
--default_authentication_plugin=mysql_native_password \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci
1.3. 创建挂载目录
mkdir -p /opt/docker/mysql/{conf,logs,data}
1.5.复制容器配置文件到挂载目录
docker cp mysql: /etc/mysql/my.cnf /opt/docker/mysql/conf/
1.6.销毁容器
docker rm -f 容器ID
1.7.挂载方式启动容器
docker run \
--privileged=true \
--restart=always \
-v /opt/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /opt/docker/mysql/data:/var/lib/mysql \
-v /opt/docker/mysql/logs:/var/log/mysql \
--privileged=true \
-p 3307:3306 \
--name mysql \
-e MYSQL_ROOT_PASSWORD=123456a \
-d mysql:latest \
--default_authentication_plugin=mysql_native_password \
--character-set-server=utf8 \
--collation-server=utf8_general_ci
1.4. 自定义my.cnf文件
[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
!includedir /etc/mysql/conf.d/
log-error=/var/log/mysql/error.log
#log=/var/log/mysql/mysql.log
slow_query_log =ON
slow_query_log_file=/var/log/mysql/slowquery.log
long_query_time=3
log-queries-not-using-indexes=/var/log/mysql/nouseindex.log
lower_case_table_names=1
default_authentication_plugin=mysql_native_password
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
参数解析
- -p:端口映射 宿主机端口:容器内端口
- –restart-always: docker服务启动时,自动启动容器,并且当容器停止时,尝试重启容器
–restart具体参数值详细信息:
no - 容器退出时,不重启容器;
on-failure - 只有在非0状态退出时才从新启动容器;
always - 无论退出状态是如何,都重启容器; - -name 指定容器名称
- -e: 指定环境变量,容器中可以使用该环境变量
- -v:为容器挂载目录,比如 /usr/docker/data:/data 前者为数宿主机目录后者为容器内目录
- -d:后台运行容器,并返回容器ID
- MYSQL_ROOT_PASSWORD:设置root密码为123456a
- –character-set-server:设置数据库编码为uft8
- –collation-serverr:设置默认排序规则为utf8_unicode_ci
- -default_authentication_plugin=mysql_native_password 设置mysql8的密码加密方式为mysql_native_password
1.6.mycnf配置参数参考
最完整的
[mysqld]
####: for global
user =mysql
federated
basedir =/usr/local/mysql
datadir =/data/mysql/data
server_id = 1250
port =3306
mysqlx_port = 33070
character_set_server =utf8mb4
collation_server=utf8mb4_bin
log_timestamps =system
default_time_zone ='+8:00'
socket =/data/mysql/mysql.sock
read_only = 0
super_read_only = 0
skip_name_resolve =on
lower_case_table_names =1
#secure_file_priv =/data/mysql/data
open_files_limit =65536
max_connections =2000
thread_cache_size =256
table_open_cache =4096 # 2000
table_definition_cache =4096
table_open_cache_instances =64
wait_timeout =28800
interactive_timeout =28800
event_scheduler = 1
max_allowed_packet =1024M
init_connect ='SET AUTOCOMMIT=1'
transaction_isolation =READ-COMMITTED
lock_wait_timeout=3600
default_authentication_plugin=mysql_native_password
###: cache
key_buffer_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
####: for binlog
binlog_format =row
log_bin =/data/mysql/logs/mysql-bin
binlog_rows_query_log_events =off
log_slave_updates =on
#expire_logs_days =7
binlog_expire_logs_seconds=604800
binlog_cache_size =1G
max_binlog_size =1G
binlog_checksum =none
sync_binlog =1
slave-preserve-commit-order =ON
log_bin_trust_function_creators=on
####:for error-log
log_error =/data/mysql/logs/mysql.err
general_log =off
general_log_file =/data/mysql/logs/general.log
####: for slow query log
slow_query_log =on
slow_query_log_file =/data/mysql/logs/slowquery.log
long_query_time =1.000000
log_queries_not_using_indexes =0
####: for gtid
#gtid_executed_compression_period =1000
gtid_mode =on
enforce_gtid_consistency =on
####: for replication
relay-log-index = /data/mysql/logs/relay-bin.index
relay-log-info-file = /data/mysql/logs/relay-log.info
relay-log = /data/mysql/logs/relay-bin
log-slave-updates =1
skip_slave_start =1
master_info_repository =table
relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =2
####: for performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
performance_schema_digests_size =100000
####: for innodb
innodb_data_file_path =ibdata1:1024M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool
innodb_log_files_in_group =3
innodb_log_file_size =256M # 50331648(48M)
innodb_max_undo_log_size =256M
innodb_undo_log_truncate =1
innodb_undo_tablespaces =2
innodb_file_per_table =on
innodb_open_files =65535
innodb_thread_concurrency =0 # 0
innodb_read_io_threads =2
innodb_write_io_threads =2
innodb_purge_threads =1
innodb_page_cleaners =1
innodb_print_all_deadlocks =off
innodb_lock_wait_timeout =20
innodb_autoinc_lock_mode =2
innodb_io_capacity =500
innodb_io_capacity_max =1000
#########################
innodb_flush_method =O_DIRECT
innodb_log_buffer_size =128M
innodb_flush_log_at_trx_commit =1
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 2
#innodb_numa_interleave=on
#####################################
#######################
[client]
socket=/data/mysql/mysql.sock
2.1.启动MYSQL服务时出现无法创建/var/log/error.log
原因:
容器中启动MySQL 服务的是 mysql 用户。而我们宿主机中是没有mysql 这个用户存在的。没有办法创建error.log
解决方案:
所以,MySQL容器启动MySQL 服务是用的mysql 用户。因此,我们可以在启动容器的时候,直接在容器中去运行一个创建error.log 的命令即可。
mkdir /var/log/error.log
chmod 700 /var/log/error.log
2.2.Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
原因:
启动mysql报如下错误,那是因为MYSQL新特性secure_file_priv对读写文件的影响
解决方案:
linux下:修改my.cnf 在[mysqld]内加入secure_file_priv=/var/lib/mysql
还不行的话就需要指定 /var/lib/mysql-files的外部目录 加上这个:-v /home/mysql/mysql-files:/var/lib/mysql-files/
2.3.mysql登录报错:ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
原因:
错误1045(28000):用户’root’@‘localhost’(使用密码:YES)拒绝访问
首先解析此英文:ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES);
解析的地方有两处:1.Access denied(拒绝访问);2.using password:NO/YES
一、出现access denied的原因有如下可能:
1)mysql的服务器停止:
若MySQL已经没有启动,重启MySQL服务器:systemctl restart mysqld
2)用户的端口号或者IP导致:
若用户的端口号与IP(3306/3307)不一致,打开my.ini文件进行编辑。全部编辑替换为: port=X
3)mysql的配置文件错误----my.ini等文件:
my.ini文件误输入无效内容,不知道到何处。复制替换该文件;有人已经对my.ini文件进行解释以及注释:
4)root用户的密码错误(后面解决)
二、出现 using password的原因如下
不输入密码:
错误的密码:
解决方案:
- vim /etc/my.cnf文件;
在[mysqld]后添加skip-grant-tables(登录时跳过权限检查) - 重启docker服务
docker restart mysql8.0
- 进入docker容器
docker exec -it mysql8.0 /bin/bash
- 修改密码
mysql -uroot -p #此时是没有密码的,直接再按一下enter健即可
use mysql
set password for ‘root’@‘localhost’=password(‘123456a’); #设置root密码为123456a
flush privileges; #刷新权限
# 再次设置远程访问和mysql_native_password 加密方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
alter user 'root'@'%' identified by 'root';
flush privileges; #刷新权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
flush privileges; #刷新权限
exit;#退出
- 再把my.ini的skip-grant-tables删除或者注释掉,重启docker容器
docker restart mysql8.0
原因:
配置文件’/etc/mysql/my.cnf’ 的权限设置成 “777” 了
解决方案:
- 改为原来的权限
chmod 644 /etc/my.cnf
ls -l /etc/my.cnf
- 重启docker容器
docker restart mysql8.0
以上就是博主在公司使用docker部署mysql8.0.16的全部过程,如果有什么问题,请在下方评论,一起进步