使用 Docker 搭建 mysql 数据库

准备

• 更新 yum 源
  yum update
• 关闭 SELinux
  sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
• 关闭防火墙
  systemctl stop firewalld.service
systemctl disable firewalld.service
• 安装运维监控系统 Open-Falcon
  yum install git telnet net-tools tree nmap sysstat lrzsz dos2unix tcpdump ntpdate vim -y

配置 Docker

• 安装 Docker
  yum -y install https://mirrors.aliyun.com/docker-ce/linux/centos/7/x86_64/stable/Packages/docker-ce-18.03.1.ce-1.el7.centos.x86_64.rpm
• 设置开机自动启动 Docker
  systemctl enable docker
• 启动 Docker
  systemctl start docker
• 查看 Docker 状态
  docker ps
• 安装 docker-compose (容器编排的工具)
  curl -o /usr/local/bin/docker-compose https://dataline-hz-test.oss-cn-hangzhou.aliyuncs.com/docker-compose
• 添加 docker-compose 执行权限
  chmod +x /usr/local/bin/docker-compose

更新 Docker 业务镜像及容器

• 在 /opt/mysql/ 文件夹下创建 docker-compose.yml 配置文件
  cd /opt/
mkdir mysql
cd mysql/
vim docker-compose.yml

version: '2'
services:
  mysql_compose:
    image: mysql:5.7
    container_name: mysql-5.7
    ports:
      - 26597:3306
    restart: unless-stopped
    volumes:
      - ./data:/usr/local/mysql
      - ./conf/mysql.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
      - /etc/localtime:/etc/localtime
    environment:
      MYSQL_ROOT_PASSWORD: "123456"

• 在 /opt/mysql/conf/ 文件夹下创建 mysql.cnf 文件
  cd /opt/mysql/
mkdir conf
cd conf/
vim mysql.cnf

[mysql]
default-character-set = utf8mb4
prompt = "\U [\d]> "
no-auto-rehash
socket = /tmp/mysql.sock
max_allowed_packet = 32M
[mysqld]
port = 3306
user = mysql
server-id = 100
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
character_set_server = utf8mb4
collation-server = utf8mb4_unicode_ci
default_storage_engine = InnoDB
autocommit = 1
explicit_defaults_for_timestamp = 0
log_timestamps = system
max_allowed_packet = 32M
lower_case_table_names = 0
#sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
open_files_limit = 65000
##connection##
back_log = 600
interactive_timeout = 28800
wait_timeout = 28800
lock_wait_timeout = 1800
skip_name_resolve = 1
max_connections = 1024
max_user_connections = 256
max_connect_errors = 1000000
##table cache performance settings##
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
##session memory settings##
read_buffer_size = 4M
read_rnd_buffer_size = 8M
sort_buffer_size = 16M
tmp_table_size = 16M
join_buffer_size = 32M
thread_cache_size = 64
##log settings##
log_error = error.log
log_error_verbosity = 2
sync_binlog = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_rows_query_log_events = 1
max_binlog_size = 512M
expire_logs_days = 3
slow_query_log = 1
long_query_time = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
# innodb settings #
transaction_isolation = REPEATABLE-READ
innodb_page_size = 16384
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 15
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_max_dirty_pages_pct = 80
innodb_flush_method = O_DIRECT
#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 0
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
lower_case_table_names=1
innodb_purge_threads = 2
innodb_large_prefix = 1
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 16M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_per_table = 1
innodb_stats_persistent = 1
innodb_stats_persistent_sample_pages = 32
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 16
innodb_purge_rseg_truncate_frequency = 128
# perforamnce_schema settings
performance-schema-instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096
[mysqldump]
single-transaction
socket = /tmp/mysql.sock
max_allowed_packet = 512M
default-character-set = utf8mb4

• 启动并运行所有的容器
  docker-compose up -d

在安全组放开 26597 端口

修改密码

• 查看容器列表
  docker container ls
• 进入容器内部
  docker exec -it [CONTAINER ID] bash
• 连接 mysql 数据库
  mysql -u root -p
• 切换数据库
  use mysql
• 修改密码
  update user set authentication_string=PASSWORD("123456") where User='root';
• 重新启动容器
  docker-compose up -d

Docker 常用命令

• 启动 systemctl start docker
• 重启 systemctl restart docker
• 关闭 systemctl stop docker
• 查看容器列表 docker container ls
• 重新启动容器 docker-compose up
• 启动并运行所有的容器 docker-compose up -d
  注: 默认情况下, docker-compose up 启动的容器都在前台, 控制台将会同时打印所有容器的输出信息, 可以很方便进行调试. 当通过 Ctrl+C 停止命令时, 所有容器将会停止. 如果希望在后台启动并运行所有的容器, 使用 docker-compose up -d