MySQL环境搭建

MySQL版本：8.0.20

docker搭建MySQL8

docker-compose.yml

直接使用下面的docker-compose.yml，用docker-compose up -d启动

version: '3.4'
services:
  mysql:
    image: mysql:8.0.20
    container_name: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: jTc7Y9AFzMRp083Ubw5s
      TZ: Asia/Shanghai
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
    network_mode: host
    volumes:
      - ./data:/var/lib/mysql

此时应该已经能够正常连接，但如果需要修改配置文件，我们还需要将配置文件从容器中复制出来（不能一开始就映射，因为配置文件不会自动生成）。

1	docker cp mysql:/etc/mysql /usr/mysql/config

需要修改配置文件则修改/usr/mysql/config/my.conf文件

最后修改docker-compose.yml，添加配置文件映射，如下：

version: '3.4'
services:
  mysql:
    ...
    volumes:
      - ./data:/var/lib/mysql
      - ./config:/etc/mysql

映射完配置文件后需要重启镜像，使用docker-compose up重启容器。

慢查询日志配置

参考my.conf配置说明，修改my.conf，添加如下配置：

1
2
3

slow_query_log=1
long_query_time=2
slow_query_log_file=mysql-slow.log

使用sleep()函数，如

1	select sleep(2);

查看慢查询日志文件

主从复制配置

配置主库

参考my.conf配置说明，修改my.conf，添加如下配置：

server-id=1
log-bin=mysql-bin
binlog_format=ROW
binlog-do-db=chirp
expire_logs_days=7

修改配置后重启数据库

配置从库

修改my.conf，添加如下配置：

1 2	server-id=2 relay-log=mysql-relay

修改配置后重启数据库

创建账户并授权

当前也可以直接使用root账户密码

1
2
3

CREATE USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY 'slave123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;

查看主库状态

记录下File和Position的值

在从库节点上设置主库节点参数

CHANGE MASTER TO 
MASTER_HOST='10.86.52.74',
MASTER_PORT=3306,
MASTER_USER='slave1',
MASTER_PASSWORD='slave123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=837;

启动主从复制

启动主从复制：

1	start slave;

查看状态：

Slave_IO_Running和Slave_SQL_Running的状态都为YES则表示同步成功。

my.conf常见配置说明

注意：修改配置后需要重启

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
# 设置3306端口
port=3306
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
# 数据库数据存放目录
datadir         = /var/lib/mysql
# 禁用DNS主机名查找
skip-host-cache
# 禁用主机名缓存
skip-name-resolve
# 最大连接数
max_connections = 400
# 单次网络传输的最大值传输量，系统默认值 为1MB，最大值是1GB，必须设置1024的倍数
max_allowed_packet = 64M
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 服务器w唯一id
server-id=1
# 开启二进制日志功能，默认不开启
log-bin=mysql-bin
# binlog格式
binlog_format=ROW
# 设置需要同步的库(可配置多个)
# binlog-do-db=
# binlog-do-db=
# 设置不需要同步的库(可配置多个)
# binlog-ignore-db=mysql
# 设置binlog自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除
expire_logs_days=7
# 开启慢查询日志，默认不开启
slow_query_log=1
# 慢查询阈值，单位为妙
long_query_time=1
# 配置慢查询文件名
slow_query_log_file=mysql-slow.log
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/