跳转至

Mysql容器设置sql_mode模式

2016-09-18 by dongnan

目标

为 mysql容器，设置正确的 sql_mode 模式。

环境

  • 镜像版本，Mysql 5.6.x
  • 运行时，Docker 1.10.3
  • 宿主机，CentOS 7.2 amd64

创建容器

示例为 zabbix 监控使用的 mysql 数据库。

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 /tmp/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"

注意选项

  • --sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 设置 sql_mode模式。
  • -v /tmp/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf 根据实际情调整况配置文件。

验证sql_mode

# 命令行执行
mysql -uroot -p -e 'show variables like "sql_mode";'

# 返回结果
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

扩展：mysql配置文件

示例为 zabbix 监控使用的 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
# buffer_size 请根据实际情况调整，示例中系统物理内存为 4GB。
innodb_buffer_pool_size = 2048M

参考

Mysql 设置sql_mode

回到页面顶部