mysql在rancher上完成大库的部署

1. 上两篇博客*（https://www.cnblogs.com/dhName/p/13179413.html，https://www.cnblogs.com/dhName/p/12936496.html）写了数据库的部署流程，正如中所阐述的，可以有这两种方法：

 第一种方法中，dockerfile需要把每一步的操作打包成要给镜像完成操作，有时候公司等其他情况限制，每一层的大小不能超过一定限制，因此此种方式很容易在构建镜像的时候失败。

第二种方法中，对sql语句执行导入操作，速度过慢，很容易在rancher平台部署的时候超过初始化的最大时间限制，造成服务启动失败。

 

因此，针对以上两种情况，采用了一个新的方式：

首先，根据如下命令，完成mysql的某个库的打包。（如果某个包的大小都大于构建镜像时候的一层的大小，那么干脆别部署了，找人先把这个限制去掉或者提高下）

第一步：数据库部署方法：
都是在本地数据库234上面执行：
导出数据库的压缩文件方法：
（1）先 mkdir /var/lib/mysql-files/imdb
然后chmod -R 777 /var/lib/mysql-files/imdb
（2）执行mysqldump --no-autocommit --opt -h172.19.57.234 -uroot -p123456 --tab /var/lib/mysql-files/imdb wisekg(数据库名)
（3）压缩
cd /var/lib/mysql-files/
tar zcvf wisekg.tar.gz ./imdb
其中wisekg为库名
若同时部署2个，需要对imdb进行更新名字；重复（1）-（3）步骤生成kb.tar.gz
最后将压缩文件放到ftp://ftp3.dc2.fzyun.io/wise/ai-nlp/mysql_server/中。

第二步：修改init.sh以及Dockerfile；
init.sh中主要修改不同数据库对应的/docker-entrypoint-initdb.d/imdb名字，以及数据库的名字；
Dockerfile中也是添加对应的数据库压缩文件。

 

对应的dockerfile和init.sh分别为：

dockerfile:（会自动执行init.sh脚本）

FROM founder/mysql:5.7

RUN apt-get update && apt-get update ; apt-get install -y parallel && chown -R mysql:mysql /docker-entrypoint-initdb.d

ADD init.sh /docker-entrypoint-initdb.d/
COPY kb.tar.gz /docker-entrypoint-initdb.d/
COPY FounderKB_real.tar.gz /docker-entrypoint-initdb.d/

ADD mysqld.cnf /etc/mysql/mysql.conf.d/

 

init.sh:

#!/bin/bash
whoami
tar zxvf /docker-entrypoint-initdb.d/kb.tar.gz -C /docker-entrypoint-initdb.d
mkdir /docker-entrypoint-initdb.d/imdbkb/split
cd /docker-entrypoint-initdb.d/imdbkb/split
for i in ../*.txt ; do echo $i ; split -d -a 6 -l 100000 -- $i `basename $i .txt`.; ls|grep `basename $i .txt`|xargs -n1 -i{} mv {} {}.txt; rm -fv $i;  done
for i in `ls *.*|sed 's/^[^.]+.//'|sort -u` ; do
  mkdir ../split-$i
  for j in $i ; do mv $j ../split-$i/$i ; done
done
cd /docker-entrypoint-initdb.d/imdbkb/
 time ( mysql -uroot -p"$MYSQL_ROOT_PASSWORD"  -e 'drop database if exists kb;create database kb;set global FOREIGN_KEY_CHECKS=0;'; (echo "SET FOREIGN_KEY_CHECKS=0;";cat ./*.sql) | mysql -uroot -p"$MYSQL_ROOT_PASSWORD" kb ; parallel -j32 'mysqlimport --use-threads=32 -uroot -p"$MYSQL_ROOT_PASSWORD" kb  /docker-entrypoint-initdb.d/imdbkb/{}/*.txt' ::: split-* )

whoami
tar zxvf /docker-entrypoint-initdb.d/FounderKB_real.tar.gz -C /docker-entrypoint-initdb.d
mkdir /docker-entrypoint-initdb.d/imdbFounderKB_real/split
cd /docker-entrypoint-initdb.d/imdbFounderKB_real/split
for i in ../*.txt ; do echo $i ; split -d -a 6 -l 100000 -- $i `basename $i .txt`.; ls|grep `basename $i .txt`|xargs -n1 -i{} mv {} {}.txt; rm -fv $i;  done
for i in `ls *.*|sed 's/^[^.]+.//'|sort -u` ; do
  mkdir ../split-$i
  for j in $i ; do mv $j ../split-$i/$i ; done
done
cd /docker-entrypoint-initdb.d/imdbFounderKB_real/
 time ( mysql -uroot -p"$MYSQL_ROOT_PASSWORD"  -e 'drop database if exists FounderKB_real;create database FounderKB_real;set global FOREIGN_KEY_CHECKS=0;'; (echo "SET FOREIGN_KEY_CHECKS=0;";cat ./*.sql) | mysql -uroot -p"$MYSQL_ROOT_PASSWORD" FounderKB_real ; parallel -j32 'mysqlimport --use-threads=32 -uroot -p"$MYSQL_ROOT_PASSWORD" FounderKB_real  /docker-entrypoint-initdb.d/imdbFounderKB_real/{}/*.txt' ::: split-* )

rm -r /docker-entrypoint-initdb.d/imdbkb/
rm -r /docker-entrypoint-initdb.d/imdbFounderKB_real/

 

mysqld.cnf:

#Copyright (c) 2014, 2016, 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        = /var/lib/mysql
#log-error    = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


innodb_buffer_pool_size = 4G
innodb_write_io_threads = 64
innodb_read_io_threads = 64

innodb_thread_concurrency=0

innodb_flush_method=O_DIRECT
#innodb_io_capacity=2000
#innodb_io_capacity_max=4000
max_allowed_packet=2G
skip-log-bin
sync_binlog=0
secure_file_priv=''

innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 1G
innodb_log_file_size = 3G

innodb_autoinc_lock_mode=2
innodb_doublewrite=0
innodb_old_blocks_pct=5
skip-innodb_adaptive_hash_index

secure_file_priv =