docker linux

MySQL安装

Docker方式安装

mysql-5.7

准备

# 拉取镜像
docker pull mysql:5.7.23
# 查看镜像列表
docker images
# 删除镜像
docker rmi 2a0961b7de03

# 查看实例
docker ps -a
# 删除实例
docker rm f4e3f21814a7 f0a528cb0483 96a75f03d048
# 删除正在运行的实例
docker ps -a
docker stop 63ba22e8bedb
docker rm 63ba22e8bedb

安装

指定配置文件和数据文件

# 映射本地路径
mkdir -p /docker/mysql_3306
docker run --name mysql5.7 -p 3306:3306 \
-v /docker/mysql_3306/log:/var/log/mysql \
-v /docker/mysql_3306/data:/var/lib/mysql \
-v /docker/mysql_3306/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD="yyyy" \
-d mysql:5.7.23

修改默认字符编码

修改配置文件后重启

cd /docker/mysql_3306/conf/
# docker cp mysql5.7:/etc/mysql/conf.d/mysql.cnf ./
vi mysql.cnf
docker restart mysql5.7

配置文件内容(设置为utf-8字符编码)

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
character-set-server=utf8
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
collation-server=utf8_general_ci
skip-character-set-client-handshake

查看mysql字符编码：

docker exec -it mysql5.7 bash
mysql -uroot -p
# 输入密码显示mysql字符编码
mysql> show variables like'char%';

授权登录

修改密码并授权登录

use mysql;
SET PASSWORD FOR root=PASSWORD('yyyy');
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'yyyy' WITH GRANT OPTION; 
GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY 'yyyy' WITH GRANT OPTION;
FLUSH PRIVILEGES;

创建新库和用户

create database aiot default character set utf8mb4 collate utf8mb4_unicode_ci;
CREATE USER aiot IDENTIFIED BY "aiot123";
set password for aiot@'%' = password('aiot123'); 
GRANT ALL PRIVILEGES ON aiot.* TO aiot@'%' IDENTIFIED BY 'aiot123' WITH GRANT OPTION; 
FLUSH PRIVILEGES;
SHOW GRANTS FOR aiot@'%';

mysql-8.0

准备

# 拉取镜像
docker pull mysql:8.0.20
# 查看镜像列表
docker images

安装

指定配置文件和数据文件

# 映射本地路径
mkdir -p /docker/mysql_3307
docker run --name mysql8.0 -p 3307:3306 \
-v /docker/mysql_3307/log:/var/log/mysql \
-v /docker/mysql_3307/data:/var/lib/mysql \
-v /docker/mysql_3307/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD="yyyy" \
-d mysql:8.0.20

修改默认字符编码

修改配置文件后重启

cd /docker/mysql_3307/conf/
# docker cp mysql8.0:/etc/mysql/conf.d/mysql.cnf ./
vi mysql.cnf
docker restart mysql8.0

配置文件内容(设置为utf-8字符编码)

[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
character-set-server=utf8
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
collation-server=utf8_general_ci
skip-character-set-client-handshake

查看mysql字符编码：

docker exec -it mysql8.0 bash
mysql -uroot -p
# 输入密码显示mysql字符编码
mysql> show variables like'char%';

授权登录

修改密码并授权登录

use mysql;
update user set authentication_string='' where  user = 'root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yyyy';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yyyy';
grant all privileges on *.* to 'root'@'localhost' with grant option;
grant all privileges on *.* to 'root'@'%' with grant option;
FLUSH PRIVILEGES;

Linux解压安装

下载离线安装包

下载安装包 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
下载地址：https://downloads.mysql.com/archives/community/

环境准备

下载libaio: http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm

rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm 
whereis libaio.so.1
libaio.so: /usr/lib64/libaio.so.1

安装

准备

# 解压安装包 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-5.7.24-linux-glibc2.12-x86_64 mysql
## 添加启动用户
groupadd mysql
useradd mysql -g mysql -M -s /sbin/nologin
## 创建mysql相关目录
mkdir -p /data/mysql_3306 /data/mysql_3306/data /data/mysql_3306/log /data/mysql_3306/log-bin
## 创建并配置mysql相关参数(具体内容参考下面的配置文件)
vi /etc/my.cnf

## 修改软件及数据目录相关权限
chown -R mysql:mysql /data/mysql_3306
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64

初始化与启动

# 采用新的初始化命令
## 打印相关参数
cd /usr/local/mysql
bin/mysqld --verbose --help
bin/mysqld --initialize --user=mysql
# 修改启动文件并安装 mysqld_3306 服务
cp -af /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld_3306
vi /etc/init.d/mysqld_3306
## 修改两处变量
basedir=/usr/local/mysql
datadir=/data/mysql_3306/data

# 修改执行权限, 设置开机启动
chmod 755 /etc/init.d/mysqld_3306
chkconfig --add mysqld_3306
chkconfig --level 345 mysqld_3306 on

# 启动mysql
service mysqld_3306 start

重置密码

# 重置密码
vi /etc/my.cnf
## 临时跳过权限校验(添加 skip-grant-tables)
[mysqld]
skip-grant-tables

## 重启mysql
service mysqld_3306 restart

## 通过mysql客户端连接
/usr/local/mysql/bin/mysql -S /data/mysql_3306/data/mysql.sock

# 删除 skip-grant-tables 后重启
service mysqld_3306 restart

修改密码:

-- 确认 mysql_native_password
use mysql;
flush privileges;
select plugin from user where user = 'root';
update user set plugin='mysql_native_password';

-- 修改root登录密码
update user set authentication_string=PASSWORD('123456') where user='root';
flush privileges;
grant all privileges on *.* to root@'%' identified by '123456';
flush privileges;

-- 创建 hive 用户
create user hive identified by '123456';
grant all privileges on *.* to hive@'%' identified by '123456';
flush privileges;

配置文件: /etc/my.cnf

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
user = mysql
character-set-server=utf8
default-storage-engine = InnoDB
socket = /data/mysql_3306/data/mysql.sock
pid-file = /data/mysql_3306/mysql.pid
port = 3306
key_buffer_size = 64M
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
datadir = /data/mysql_3306/data/
long_query_time = 1
log-bin = /data/mysql_3306/log-bin/mysql-bin
expire-logs-days = 14
sync-binlog = 1
server-id = 1
max_binlog_size = 100M
relay-log = /data/mysql_3306/log-bin/relay-bin
slave-net-timeout = 60
tmp_table_size = 16M
max_heap_table_size = 16M
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_lock_wait_timeout = 50
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 64M
log-error = /data/mysql_3306/log/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
long_query_time = 1
slow-query-log-file = /data/mysql_3306/log/mysql-slow.log

遇到异常ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.:

-- 修改当前密码
ALTER USER USER() IDENTIFIED BY '123456';

其他

停止+删除

for id in `docker ps -a | grep mysql|awk '{print $1}'`; do \
  echo "clean [`docker ps -a | grep $id`]"; \
docker stop $id && docker rm $id; \
done

修改默认时区

show variables like '%time_zone%';
set GLOBAL time_zone = '+8:00';
set time_zone = '+8:00';
flush privileges;
select now();

如果觉得文章对你有用，请随意赞赏

docker mysql linux

MySQL安装

http://burna.cn/archives/安装mysql

作者

飞飞幻想

发布于

2021-11-23

更新于

2024-04-09

许可协议

CC BY 4.0

MySQL安装

Docker方式安装

mysql-5.7

准备

安装

修改默认字符编码

授权登录

创建新库和用户

mysql-8.0

准备

安装

修改默认字符编码

授权登录

Linux解压安装

下载离线安装包

环境准备

安装

其他

作者

发布于

更新于

许可协议

评论