mysql | 利用docker快速迁移mysql数据

一、项目背景

  最近在进行系统的复刻迁移，如，有个demo环境，其中包含mysql、bpm、nginx等，mysql使用的是源码安装。目前最繁琐的步骤就是迁移mysql，mysql数据库级别在20GB左右，说大不大、说小不小，从demo中把mysql导出sql脚本后，再导入新环境中，经常由于莫名的原因报错。
  由于我的mysql是使用脚本安装的，首先我想在新环境中也使用脚本安装同版本、同类型的mysql。我曾尝试过小数据量的，直接把data目录进行打包解压在新环境中，发现是可行的，但是数据量在10GB左右的，就不能正常使用了，会有各种启动的报错。
  由于之前也使用过docker进行数据库的安装，所以这次就想借助docker看看是否可以。结果很是让人欣慰，当然，这也是一种办法，或许还有更好的办法。

二、操作步骤

2.1 打包demo环境中data目录

cd  $mysql_home
tar -zcvf data.tar.gz  data

2.2 新环境中创建挂载目录

2.2.1目录说明

  此处我们需要挂载3个目录。其中两个是创建的，另一个是demo环境中的data.tar.gz文件解压缩的，记得data移动过后要进行授权。

#解压后执行
chown -R mysql.mysql mysql
chmod 777 -R data

mysql
|-- conf
|   |-- conf.d
|   |-- my.cnf                     #自己添加的
|   `-- my.cnf.fallback
|-- data                           #解压过来的
|   |-- auto.cnf
|   |-- ca-key.pem
|   |-- ca.pem
|   |-- client-cert.pem
|   |-- client-key.pem
|   |-- ecology
|   |-- ib_buffer_pool
|   |-- ibdata1
|   |-- ib_logfile0
|   |-- ib_logfile1
|   |-- ib_logfile2
|   |-- ib_logfile3
|   |-- mysql
|   |-- mysql-bin.index
|   |-- performance_schema
|   |-- private_key.pem
|   |-- public_key.pem
|   |-- server-cert.pem
|   |-- server-key.pem
|   `-- sys
`-- mysql-files                    #空文件夹即可

2.2.2my.cnf配置

# 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]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /usr/local/mysql
port = 3306
secure-file-priv= ''
default_authentication_plugin= mysql_native_password
character-set-server=utf8mb4

server-id = 1
expire_logs_days = 7
binlog_format=ROW

max_connections = 5000
max_connect_errors = 10
table_open_cache = 4096
event_scheduler = ON
skip-name-resolve
lower_case_table_names = 1
group_concat_max_len = 102400

max_allowed_packet = 100M
binlog_cache_size = 32M
max_heap_table_size = 256M
read_rnd_buffer_size = 64M
sort_buffer_size = 256M
join_buffer_size = 512M
thread_cache_size = 300
log_bin_trust_function_creators=1
key_buffer_size = 256M
read_buffer_size = 32M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 512M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

######READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = READ-COMMITTED
tmp_table_size = 512M
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 15
long_query_time = 5

####### InnoDB
innodb_buffer_pool_size = 512M
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
#innodb_force_recovery=1

[mysqldump]
quick
max_allowed_packet = 102400M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 16M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 65535
log-error=/var/log/mysql_error.log

# Custom config should go here
!includedir /etc/mysql/conf.d/

2.3 使用docker启动mysql

  可以通过 docker run 命令或者docker-compose创建 mysql 容器

2.3.1 通过docker-compose

2.3.1.1 dockerfiles

# 添加基础镜像
FROM  mysql:5.7.28
# 作者信息
MAINTAINER HB@stringedu
# 设置时区
RUN  ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime \
     && echo 'Asia/Shanghai' >/etc/timezone \
     && rm /etc/mysql/my.cnf \
     && mkdir /usr/local/mysql

# 添加配置文件
ADD conf/my.cnf  /etc/mysql/
# 开放端口
EXPOSE 3306

2.3.1.2 docker-compose.yml

version: '3.6'
services:
  mysql:
    restart: always
    build:
      context: ./mysql
      dockerfile: Dockerfile
    image: string/mysql:5.7.28
    container_name: mysql
    hostname: mysql
    network_mode: "host"
    environment:
      MYSQL_ROOT_PASSWORD: '        '
      TZ: Asia/Shanghai
    volumes:
      - /usr/local/docker/mysql/data:/usr/local/mysql
      - /usr/local/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf
      - /usr/local/docker/mysql/mysql-files:/var/lib/mysql-files

2.3.2 通过docker run

docker run \
-d \
--restart=always \
--network=host \
--hostname=localhost \
--name mysql \
-v /usr/local/docker/mysql/conf:/etc/mysql  \
-v /usr/local/docker/mysql/data:/usr/local/mysql \
-v /usr/local/docker/mysql/mysql-files:/var/lib/mysql-files \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=string@1307 \
mysql:5.7.28 \
--lower_case_table_names=1 

#注意，mysql的数据目录在容器中的默认位置是 /var/lib/mysql/ ，我映射 /usr/local/mysql 是由于在my.cnf指定了目录为 /usr/local/mysql

三、查看效果

docker logs -f -t --tail 500 mysql