docker mysql5.7安装(阿里云)

安装步骤

1. 拉取mysql5.7官方镜像

docker pull mysql/mysql-server:5.7

2. 启动容器

docker run --name=mysql \
-v /root/mysql/data:/var/lib/mysql \
-e LANG=en_US.UTF-8 \
-p 3306:3306 \
-d mysql/mysql-server:5.7

-v /root/mysql/data:/var/lib/mysql 挂载数据目录到宿主机

3. 修改初始密码

查看安装密码命令

docker logs mysql

执行命令或进入容器后执行

docker exec -it mysql mysqladmin -uroot -p password

或

docker exec -it mysql bash
mysqladmin -uroot -p password

4. 拷贝my.cnf到容器

docker cp my.cnf mysql:/etc/my.cnf

my.cnf内容

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld]
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

port = 3306
character-set-server = utf8
lower_case_table_names = 1
max_connections = 3000
default-time-zone = '+08:00'

最后5行为追加部分

4. 重启容器

docker restart mysql

远程授权

• 授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

注：
ALL PRIVILEGES 表示所有权限，可以改成：SELECT，INSERT，UPDATE，DELETE。
. 表示所有数据库下的资源，可改成dbname.*。
root是用户，可改为其他用户。
root123是密码，可改为任意密码，可以和MySQL服务器中的用户密码不同。
FLUSH PRIVILEGES使授权生效。

• 撤销

REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
FLUSH PRIVILEGES;

密码无效处理

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;

mysql字符集设置

1. 查看字符集

show variables like '%char%';

2. 修改字符集

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;

查看字符集

show create database db_name;
alter database `db_name` default character set utf8;

show create table tb_name;
alter table `tb_name` default character set utf8;

show full columns from tb_name;
alter table `tb_name` change `col_name` ` col_name ` varchar( 45 ) character set utf8 not null

备份与恢复

docker exec -t mysql mysqldump -uroot -proot single > /home/single.db

注意事项

mysql时区问题：default-time-zone = ‘+08:00’
系统字符集问题：进入系统后执行 export LANG=en_US.UTF-8，
永久生效启动时加：-e LANG=en_US.UTF-8