这里汇总我遇到的所有关于MySQL的一些环境问题。
背景
之前在这篇文章中(记一次在测试环境云服务器上搭建运行在Docker中的MySQL)记录了在Docker环境中搭建MySQL的服务。现在各条线测试环境的MySQL都是在该环境中运行。所以会遇到一些“类运维”相关的问题，这里做个记录，算是学习笔记了。
设置MySQL不区分表名的大小写
数据好不容易迁移完了，然后有一个应用起来之后，web页面提示数据库表不存在。一看，发现这个库的表名中有大写，然后把错误提示中的SQL语句放到客户端执行下，发现迁移前的数据库可以正常执行，迁移后的docker版的MySQL就提示表不存在。好吧，应该要配置docker版的MySQL不区分表名大小写。需要在/etc/mysql/mysql.conf.d/mysqld.cnf中添加lower_case_table_names=1。处理过程如下：
- 先将docker容器中的文件复制到宿主机。
- 然后添加
lower_case_table_names=1 - 随后将文件复制到容器中。
- 最后重启容器（如果防火墙不关闭的话，容器会启动失败，如果防火墙开启的话，即使端口打开也会访问不到，这个问题的原因后面再研究）
命令行存档如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[root@Server-i-jwvdl9av3u steve]# docker cp 1a0495313ae1:/etc/mysql/mysql.conf.d/mysqld.cnf .
[root@Server-i-jwvdl9av3u steve]# vim mysqld.cnf
[root@Server-i-jwvdl9av3u steve]# cat 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, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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
lower_case_table_names=1
[root@Server-i-jwvdl9av3u steve]# docker cp mysqld.cnf 1a0495313ae1:/etc/mysql/mysql.conf.d/mysqld.cnf
[root@Server-i-jwvdl9av3u steve]# docker exec -it 1a0495313ae1 /bin/bash
root@1a0495313ae1:/# cat /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
# ---------------same with above---------------
# blah blah blah...
# ---------------same with above---------------
lower_case_table_names=1
[root@Server-i-jwvdl9av3u steve]# docker stop 1a0495313ae1
1a0495313ae1
[root@Server-i-jwvdl9av3u steve]# service firewalld start
Redirecting to /bin/systemctl start firewalld.service
[root@Server-i-jwvdl9av3u steve]# docker start 1a0495313ae1
1a0495313ae1
[root@Server-i-jwvdl9av3u steve]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
如此费事的原因是MySQL的镜像中不包含vim等文本编辑功能。可以安装，也可以通过上面的docker cp命令。思路来自stackoverflow
修改时区
MySQL的官方image，启动后是UTC，和中国差了8个小时。可以先通过图形化客户端通过如下指令排查。
1
2
3
select sysdate();
SELECT NOW();
SHOW VARIABLES LIKE "%time_zone%";
修改时区有两种方式：修改MySQL的配置文件；或者直接通过终端命令修改。貌似通过终端修改的话，重启也可以不丢失（通过docker重启后配置丢了）。另注修改完成后，服务端直接生效，但是客户端需要重启才能生效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@Server-i-jwvdl9av3u ~]# docker exec -it 1a0495313ae1 /bin/bash
root@1a0495313ae1:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 93400
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET GLOBAL time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
方式二：修改配置文件
参考设置MySQL不区分表名的大小写板块中的内容，在/etc/mysql/mysql.conf.d/mysqld.cnf文件最下方增加一行**default-time_zone = ‘+8:00’**即可。
MySQL连接数过多
测试环境的应用启动报错了，原因是连接数过多，于是我登录终端想去改下配置。可惜😅
1
2
3
4
[root@Server-i-jwvdl9av3u ~]# docker exec -it fff8182066ff /bin/bash
root@fff8182066ff:/# mysql -u root -p
Enter password:
ERROR 1040 (HY000): Too many connections
一劳永逸的方式是直接改配置文件，参考设置MySQL不区分表名的大小写板块中的内容，在/etc/mysql/mysql.conf.d/mysqld.cnf文件最下方增加一行**max_connections=1000**即可。
重启后通过**show variables like ‘max_connections’;来确认修改是否生效。很遗憾，我上述的方式在我的机器上没有生效，只能用临时方案，在任意连接的客户端上执行set global max_connections = 1000;**后面再研究下原因。
MySQL禁止foreign host的连接
现象是程序无法连接到MySQL，但是我没有看到具体的错误信息。不过从MySQL Workbench上也表现出连接不上的现象。通过telnet可以看出，端口是通的。注：telnet只能看端口是否通，不能正确反应外部的客户端是否能连接。所以还是要用客户端进行可用性的判断。
1
2
3
4
5
6
7
Steve@ZBMAC-C657521M ~ % telnet 11.51.193.15 22001
Trying 11.51.193.15...
Connected to 11.51.193.15.
Escape character is '^]'.
J
5.7.31+g{Dm% (:pi)sdd
mmysql_native_passwordConnection closed by foreign host.
登录mysql的控制台，通过下面的路径将root的以密码123456的登录授权给任意ip。若想将任意ip替换为指定ip，则将%替换为192.168.0.1（指定ip）即可。
1
2
3
4
5
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
坑：我在配置的时候，不知为何密码只能用数字。别说英文符号了，连英文字母都不行，我也不知道为啥🤷♂️，后面再研究吧。
密码只能设置数字的问题，可以尝试用这个来解决ALTER USER 'root'@'%' IDENTIFIED BY 'my-secret-pw-xixi';。具体原因后面一并研究。
权限追加
通过GRANT SELECT,INSERT,UPDATE ON i_xixi.* TO 'admin'@'%';可以给admin用户赋予i_xixi库中所有表的查，插，更新的权限。
通过终端连接远端的数据库
mysql -h 主机名(url/ip) -u 用户名 -P 端口 -p密码
更新日志
- 2020年10月26日：初稿。
- 2020年10月：追加
MySQL连接数过多；修改修改时区；迁移设置MySQL不区分表名的大小写 - 2020年11月：追加
MySQL禁止foreign host的连接 - 2020年12月：追加
权限追加
