docker 安装 mysql5.7与mariadb:10

1.安装mysql5.7 docker镜像

拉取官方mysql5.7镜像 

docker pull mysql:5.7
mkdir -p /data/mysql/data /data/mysql/logs /data/mysql/conf
touch /data/mysql/conf/my.cnf
docker run --restart=always -p 13306:3306 --name mysql -v /data/mysql/conf:/etc/mysql/conf.d -v /data/mysql/logs:/logs -v /data/mysql/data:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro -e MYSQL_ROOT_PASSWORD=111111 -d mysql:5.7
docker start mysql

-d: 后台运行容器

-p 将容器的端口映射到本机的端口

-v 将主机目录挂载到容器的目录

-e 设置参数

2.安装mariadb:10 docker镜像,并且创建testdb数据库授权外网访问

docker pull mariadb:10.0   
docker run --name mariadb -p 13306:3306 -e MYSQL_ROOT_PASSWORD=nihaoma -v /etc/localtime:/etc/localtime:ro -d d1bde56970c6
docker exec -it mariadb bash
mysql -u root -pnihaoma
mysql
> CREATE DATABASE IF NOT EXISTS testdb COLLATE = 'utf8_general_ci' CHARACTER SET = 'utf8';
> GRANT ALL ON testdb.* TO 'tvb9ring'@'%' IDENTIFIED BY 'tvb9ring';
> GRANT ALL ON testdb.* TO 'tvb9ring'@'localhost' IDENTIFIED BY 'tvb9ring';
> FLUSH PRIVILEGES;
exit
exit

-- 创建数据库用户
create user 'test'@'%' identified by '123qqq...A';
-- 授权所有库只读权限
grant select on *.* to 'test'@'%' identified by '123qqq...A';

3.安装指定版本跟生产库一致5.7.32-log

select VERSION()

docker pull mysql:5.7.32 

docker run --restart=always -p 13306:3306 --name mysql-dev -v /data/mysql/conf:/etc/mysql/conf.d -v /data/mysql/logs:/logs -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123qqq...A -d mysql:5.7.32

vi /data/mysql/conf/my.cnf       #自动映射到docker内部配置文件

[mysqld]
character-set-server=utf8
user=mysql
basedir=/var/lib/mysql
datadir=/var/lib/mysql
log_warnings
log-output=file
general_log=0
slow-query-log=1
long_query_time=1
 
 
###InnodbSetting
innodb_buffer_pool_size=16384M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
innodb_stats_on_metadata=OFF
#innodb_thread_concurrency=16
innodb_flush_method=O_DIRECT
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size=1024M
max_binlog_cache_size=4096M
binlog_stmt_cache_size=1M
table_open_cache=4096
binlog_cache_size=4M
expire_logs_days=30
relay_log_purge=0
sort_buffer_size=2M

 
lower_case_table_names=1   # 其中 0：区分大小写，1：不区分大小写
innodb_print_all_deadlocks
skip_external_locking
skip_name_resolve
#read_only=1
 
max_allowed_packet=32M
max_connections=1000
max_connect_errors=10000
#interactive_timeout=600
#wait_timeout=600
#event_scheduler=on
 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
innodb_lru_scan_depth=256

解决mysql的配置ONLY_FULL_GROUP_BY引起的错误

在 [mysqld] 下面添加代码：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

lower_case_table_names参数详解：

lower_case_table_names = 1
其中 0：区分大小写，1：不区分大小写

default-time_zone = '+8:00'

# vim /etc/my.cnf ##在[mysqld]区域中加上

default-time_zone = '+8:00'

# /etc/init.d/mysqld restart ##重启mysql使新时区生效

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
default-time-zone='+08:00'

mysqldump -uroot -p123qqq...A -E -R cloud >cloud.sql  #备份数据库

 把文件中的所有的utf8mb4_0900_ai_ci替换为utf8_general_ci
以及utf8mb4替换为utf8

sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' cloud.sql

sed -i 's/utf8mb4/utf8/g' cloud.sql

mysql -uroot -p123qqq...A cloud <cloud.sql   

centos定时crontab执行任务，docker中的mysql数据备份(本地备份、远程备份)

chmod +x /home/mysql.sh

#!/bin/bash

docker exec -it mysql-dev mysqldump -uroot -p123qqq...A -R -E cloud | gzip >/home/mysqldump/cloud_$(date +%Y%m%d_%H%M%S).sql.gz            #解压缩命令  gzip -d cloud_20211211_110221.sql.gz
find /home/mysqldump/ -name "*.sql.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
scp /home/mysqldump/* root@192.168.0.31:/backup/mysqldump/

crontab -e

6 6 * * * /home/mysqldump.sh &>/dev/null