dongnan
总版主
总版主
  • 粉丝52
  • 发帖数2165
  • 铜币13236枚
  • 威望5838点
  • 银元150个
  • 社区居民
  • 最爱沙发
  • 忠实会员
  • 原创写手
阅读:2182回复:1

如何设置 mysql 容器 sql_mode 模式?

楼主#
更多 发布于:2016-09-18 11:27
##### 内容陈旧失效,设置 mysql 容器 sql_mode 请直接参考 2楼 #####

目标
1. 解决 docker mysql镜像,无法设置 sql_mode 模式;
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" ;

2. 解决 docker mysql镜像,数据固化问题,数据保存在宿主机 /data/mysql 目录;

3. mysql 数据迁移, 利用 docker cp 命令拷贝原有数据,并启用新的容器 mysql-5.6;

环境
CentOS 7.2 amd64
Docker 1.10.3 on swarm
Mysql 5.6.x on Docker

步骤
1. 备份数据
#使用docker cp 命令复制容器内的数据;
docker cp mysql-pub-1:/var/lib/mysql /data/mysql

2. 创建容器
#创建新的Mysql 容器,并使用-v参数 指定 mysql 容器挂载目录;

docker -H tcp://ywwd.net:3375 run -tid --name mysql-pub-2 \
        -v /data/mysql:/var/lib/mysql -v /etc/localtime:/etc/localtime:ro \
        -e MYSQL_ROOT_PASSWORD=ywwd.net \
         hub-dev.ywwd.net/zongming/mysql
3. 修改配置
#使用docker exec命令 修改sql_mode
docker exec mysql sed -i 's/NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TA
BLES/NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION/' /etc/mysql/my.cnf

验证

mysql -h ywwd.net -uroot  -pywwd.net -e 'show variables like "sql_mode";'
Warning: Using a password on the command line interface can be insecure.
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

参考https://ywwd.net/read-906

#更多docker mysql 容器信息,待续;
dongnan
总版主
总版主
  • 粉丝52
  • 发帖数2165
  • 铜币13236枚
  • 威望5838点
  • 银元150个
  • 社区居民
  • 最爱沙发
  • 忠实会员
  • 原创写手
沙发#
发布于:2016-09-19 11:49
docker 创建 mysql 容器
更新:2016-10-06
1. 创建容器
docker run --name mysql-server -t \
       -e MYSQL_DATABASE="zabbix" \
       -e MYSQL_USER="zabbix" \
       -e MYSQL_PASSWORD="password" \
       -e MYSQL_ROOT_PASSWORD="password" \
       -e TZ=Asia/Chongqing \
       -v /etc/localtime:/etc/localtime:ro \
       -v /root/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf \
       -v /data/mysql:/var/lib/mysql \
       -d mysql:5.7 \
         --character-set-server=utf8 \
         --collation-server=utf8_general_ci \
         --sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2. mysql 容器配置文件
cat /root/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

max_allowed_packet = 10485760
max_connect_errors = 1000
max_connections = 1000
max_user_connections = 500
interactive_timeout = 300
wait_timeout = 180
# buffer_size 请根据实际情况调整,示例中系统物理内存为 4GB。
innodb_buffer_pool_size = 2048M


#END
游客

返回顶部