catalogue
2.2docker specifies -- lower case table names = 1 (invalid in specific cases):
1, Environment:
centos7,docker 17.12.1-ce,mysql 8.0.21
The mysql command of docker installation:
docker run --name mysql --restart=always \ -v /home/mysql/conf/my.cnf:/etc/mysql/my.cnf \ -v /home/mysql/data:/var/lib/mysql \ -p 3317:3306 \ -e MYSQL_ROOT_PASSWORD="root" \ -e TZ=Asia/Shanghai \ -d mysql:8.0
[root@shamee shamee]# docker run --name mysql --restart=always -e MYSQL_ROOT_PASSWORD="root" -d mysql:8.0 222f3969bdb4a3c58da50707a73866c48cfd26f4c7b2feed32b56ae7de09b036 [root@shamee shamee]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 222f3969bdb4 mysql:8.0 "docker-entrypoint.s..." 2 seconds ago Up 1 second 3306/tcp, 33060/tcp mysql [root@shamee shamee]# docker exec -it mysql bash root@222f3969bdb4:/# mysql -uroot -proot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.21 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> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | +------------------------+-------+ 2 rows in set (0.01 sec) mysql>
You can see that mysql is started successfully and can connect normally at the same time. (this is a local virtual machine test, so docker run does not have an external host directory, which is obviously a bad habit.). Try navicat,
Nice, docker, install mysql8 0 one minute. Isn't there an afternoon to play games? Then beat a chicken and scare it.
But the next day, the server reported an error:
Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'biz.XXL_JOB_QRTZ' doesn't exist
Obviously, the table name does not exist because the table name is capitalized. Therefore, immediately check the case sensitive configuration of mysql:
mysql> show variables like '%case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 0 | +------------------------+-------+ 2 rows in set (0.01 sec) mysql>
Sure enough, it's case sensitive. So modify my cnf:
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL lower_case_table_names=1 # Custom config should go here !includedir /etc/mysql/conf.d/
Restart mysql and find that mysql is not only not started successfully, but constantly restarted. View log:
[root@iZuf68t6hada0ayijajs45Z ~]# docker logs -f -t --tail 100f mysql
2020-09-02T08:47:27.840720958Z 2020-09-02 16:47:27+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.21-1debian10 started.
2020-09-02T08:47:27.897290758Z 2020-09-02 16:47:27+08:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-09-02T08:47:27.901725465Z 2020-09-02 16:47:27+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.21-1debian10 started.
2020-09-02T08:47:27.960172822Z mysqld: [Warning] Skipping '!includedir /etc/mysql/conf.d/' directive as maximum include recursion level was reached in file /etc/mysql/conf.d/my.cnf at line 30.
2020-09-02T08:47:28.197969455Z 2020-09-02T08:47:28.190817Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 1
2020-09-02T08:47:28.199468695Z 2020-09-02T08:47:28.199360Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-09-02T08:47:28.432824695Z 2020-09-02T08:47:28.429540Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-09-02T08:47:28.434242966Z 2020-09-02T08:47:28.434080Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2020-09-02T08:47:28.434477428Z 2020-09-02T08:47:28.434339Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-09-02T08:47:28.434584287Z 2020-09-02T08:47:28.434551Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-09-02T08:47:28.954820885Z 2020-09-02T08:47:28.949232Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL.
Obviously, the eighth line of the error message indicates that the lower is set_ case_ table_ Names = 1 is inconsistent with the default dictionary value of 0, and then rejected my setting.
Check the Mysql official website, https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names There is a paragraph stating:
In most cases, this requires configuring lower in the MySQL options file before starting the MySQL server for the first time_ case_ table_ names.
2, Modify case
2.1 modify my CNF (invalid):
According to the official website, mysql8 0 once you do not give the corresponding value during initialization, it defaults to lower_case_table_names=0. Follow up if you want to be in my Modify this item in CNF, mysql8 0 does not allow inconsistency between the two (version 5.7 is OK, so version 5.7 can still directly modify my.cnf). Therefore, it is not allowed to modify this configuration afterwards.
2.2docker specifies -- lower case table names = 1 (invalid in specific cases):
docker starts mysql to modify the configuration:
docker run --name mysql --restart=always \ -v /home/mysql/conf/my.cnf:/etc/mysql/my.cnf \ -v /home/mysql/data:/var/lib/mysql \ -p 3317:3306 \ -e MYSQL_ROOT_PASSWORD="root" \ -e TZ=Asia/Shanghai \ -d mysql:8.0 --lower-case-table-names=1
Many online statements are configured when docker starts mysql -- lower case table names. However, I tried many times and failed. The reason is that my mysql has been initialized, / var/lib/mysql is not the default initialization database, and there are traces of manual modification.
2.3 solutions
Restart mysql. If it is the first installation, it is OK to directly add -- lower case table names = 1 after docker.
If it is not the first installation, you need to uninstall the original mysql, - v /home/mysql/data:/var/lib/mysql, and specify a new and clean data warehouse here. Then -- lower case table names = 1.
For example:
original:
docker run --name mysql --restart=always \ -v /home/mysql/conf/my.cnf:/etc/mysql/my.cnf \ -v /home/mysql/data:/var/lib/mysql \ -p 3317:3306 \ -e MYSQL_ROOT_PASSWORD="root" \ -e TZ=Asia/Shanghai \ -d mysql:8.0
Modification:
docker run --name mysql --restart=always \ -v /home/mysql/conf/my.cnf:/etc/mysql/my.cnf \ -v /home/mysql/data2:/var/lib/mysql \ -p 3317:3306 \ -e MYSQL_ROOT_PASSWORD="root" \ -e TZ=Asia/Shanghai \ -d mysql:8.0 --lower-case-table-names=1
Where / home/mysql/data2 is the newly created directory, but in this way, it is best to back up the data to prevent loss.
3, Reference link
Reference link: https://segmentfault.com/a/1190000021523570
#SQL statement for creating database select version(); CREATE DATABASE `imapoc` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE USER imapoc IDENTIFIED BY 'imapoc2022L!@#'; grant all privileges on imapoc.* to 'imapoc'@'%'; alter user 'imapoc'@'%' identified with mysql_native_password by 'imapoc2022L!@#'; FLUSH PRIVILEGES;