1. 远程连接问题
MySql 8.0版本修改了默认的加密规则,用Navicat连接会报错。
解决方法:加密方式改为mysql_native_password
即可
$ mysql -u root -p
--输入密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| seckill |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
--查询当前数据库相关信息
mysql> select host,user,authentication_string,plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
--将root用户设置为所有地址可登录,原来是localhost表示只用本机可登录
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
--将用户root密码设置为永不过期
mysql> alter user 'root'@'%' identified by '12345678' password expire never;
Query OK, 0 rows affected (0.01 sec)
--将root用户密码加密方式改为mysql_native_password ,上面查到root用户密码的加密方式为caching_sha2_password
mysql> alter user 'root'@'%' identified with mysql_native_password by '12345678';
Query OK, 0 rows affected (0.00 sec)
--刷新权限,在别的机器上即可登录
mysql> flush privileges;
2. 数据库区分大小写
- 是否区分大小写是通过
lower_case_table_names=1
参数来设置lower_case_table_names = 1 表名存储在磁盘是小写的,但是比较的时候是不区分大小写
lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的 - 通过
show Variables like ‘%table_names’
来查看默认的值
虚拟机
修改MySql配置文件 vi /etc/my.cnf ,在[mysqld]
配置中增加 lower_case_table_names=1
-- 修改配置文件
cd /etc/mysql/
vi my.cnf
-- 添加一行配置
[mysqld]
datadir=/home/data
log-error=/home/logs/error.log
lower_case_table_names=1
docker容器
-
以上方式不适用容器技术,修改配置文件后restart失败
-
需要在创建容器的时候就指定
lower_case_table_names
的属性
命令如下:docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d -v /home/mysql/:/var/lib/mysql mysql --lower_case_table_names=1
lower_case_table_names=1要加在镜像名后面,镜像名前面是参数,后面是mysql配置
-
进入数据库查看是否成功
show global variables like '%lower_case%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 1 | +------------------------+-------+ 2 rows in set (0.00 sec)
3. only_full_group_by
错误一般发生在mysql 5.7以及 5.7以上的版本中,其原因是mysql的默认 sql_mode=“ONLY_FULL_GROUP_BY” 这个配置严格执行了 ‘SQL92标准’
进入mysql服务
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
4. 快速开始
docker run -d --name xxx --privileged -p 3306:3306 -e MYSQL_ROOT_PASSWORD=xxx xxx镜像 \
--lower_case_table_names=1 \
--sql_model=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION \
--default-authentication-plugin=mysql_native_password \
--max_connections=300 \
--group_concat_max_len=102400 \
--log_bin_trust_function_creators=1
lower_case_table_names
:忽略大小写sql_mode
l:only_full_group_bydefault-authentication-plugin
:加密方式max_connections
:最大连接group_concat_max_len
:最大拼接长度log_bin_trust_function_creators
:二进制日志参数