使用Docker在生产环境部署MySQL 5.7.34、配置防火墙、个性化MySQL配置文件【测试成功】

[root@MySQL-WQ conf]# docker -v
Docker version 19.03.12, build 48a66213fe

root@MySQL-WQ ~]# docker pull mysql:5.7.34
Error response from daemon: 
Get https://registry-1.docker.io/v2/: dial tcp: lookup registry-1.docker.io on [::1]:53: read udp [::1]:56229->[::1]:53: read: connection refused

yum-config-manager     --add-repo     http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

# 宿主机创建数据存放目录映射到容器
mkdir -p usr/local/docker_data/mysql/data


# 宿主机创建配置文件目录映射到容器 
mkdir -p usr/local/docker_data/mysql/conf #(需要在此目录下创建"conf.d"、"mysql.conf.d"两个目录)
mkdir -p usr/local/docker_data/mysql/conf/conf.d # (建议在此目录创建my.cnf文件并进行相关MySQL配置)
mkdir -p usr/local/docker_data/mysql/conf/mysql.conf.d


# 宿主机创建日志目录映射到容器
mkdir -p usr/local/docker_data/mysql/logs




#  CentOS 7 不建议用这个命令
docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d  -v usr/local/docker_data/mysql/data:/var/lib/mysql -v usr/local/docker_data/mysql/conf:/etc/mysql/ -v usr/local/docker_data/mysql/logs:/var/log/mysql mysql:5.7


# 经测试上面这行命令在 CentOS 7 下目录挂载失败。
# 在上面这行命令的基础上增加了--privileged=true参数，让容器拥有真正的root权限
docker run --privileged=true --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d  -v /usr/local/docker_data/mysql/data:/var/lib/mysql -v /usr/local/docker_data/mysql/conf:/etc/mysql/ -v /usr/local/docker_data/mysql/logs:/var/log/mysql mysql:5.7

docker exec -it mysql5.7 bash
mysql -u root -p
# 创建用户并开启远程登录
CREATE USER '你的账号'@'%'  IDENTIFIED BY '你的密码';




# 创建数据库并设置字符集
CREATE DATABASE `库名` CHARACTER SET 'utf8mb4';




# 给账号授权数据库
GRANT ALL PRIVILEGES ON `库名`.* TO '你的账号'@'%';




# 刷新权限
FLUSH PRIVILEGES;

[root@MySQL-WQ ~]# mysql -uroot -P13306 -p --socket=/usr/local/Yinling/mysql/mysql.sock
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

mysql -uroot -P13306 -p --host=127.0.0.1 --protocol=TCP

成功连接命令如下：
mysql -uroot -h10.18.x.x  -P13306 -pyl123456 --socket=/usr/local/Yinling/mysql/mysql.sock   --protocol=TCP

查看防火墙状态，并列出防火墙条目：
systemctl status firewalld
firewall-cmd --list-all

firewall-cmd --zone=public --add-port=13306/tcp --permanent
firewall-cmd --zone=public --add-source=192.168.207.22 --permanent
firewall-cmd --reload

[mysqld]
server_id = 1
port = 3306
character-set-server = utf8
collation-server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
lower_case_table_names = 1
#lower_case_file_system = 1
transaction-isolation = READ-COMMITTED
default-time_zone = '+8:00'
log_timestamps = SYSTEM


log_bin=mysql-bin
expire_logs_days=30
binlog_format=ROW
###############################
innodb_buffer_pool_size = 8G
innodb_log_file_size=128M
innodb_log_files_in_group=4
innodb_log_buffer_size=16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb-file-per-table=1
auto_increment_increment=1
auto_increment_offset=1
connect_timeout=10
group_concat_max_len=1024
innodb_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_write_io_threads=12
interactive_timeout=28800
lock_wait_timeout=31536000
long_query_time=10.000000
low_priority_updates=OFF
max_allowed_packet=500M
max_connect_errors=999999999
max_connections=1600
max_length_for_sort_data=1024
max_prepared_stmt_count=16382
max_user_connections=0
net_read_timeout=30
net_retry_count=10
net_write_timeout=60
ngram_token_size=2
open_files_limit=102400
performance_schema=OFF
query_alloc_block_size=8192
query_cache_limit=1048576
query_cache_size=1024M
query_cache_type=OFF
query_cache_wlock_invalidate=OFF
query_prealloc_size=8192
slow_launch_time=2
table_definition_cache=768
table_open_cache=512
table_open_cache_instances=16
thread_cache_size=512
tmp_table_size=1073741824
wait_timeout=2147483
interactive_timeout=3153600
explicit_defaults_for_timestamp = true
log-bin-trust-function-creators = 1

mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

log_bin=mysql-bin
expire_logs_days=30
binlog_format=ROW

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | ON                             |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

docker restart  mysql5.7