Linux 安装Mysql 问题汇总

• 1130 - Host XXX is not allowed to connect to this MySQL server

安装好Mysql后，本地使用Navicat连接数据库时，报上面的错误。
因为我们还没给 MySQL 配置支持远程连接。

解决办法：
登录进入 MySQL

mysql -u root -p
输入密码：
执行命令：
user mysql;
select host from user where user='root'

可以看到当前的主机信息为localhost：

我们可以将 host 设为为 % 通配符

update user set host = '%' where user = 'root';
flush  privileges;

设置后，就可以远程访问了。
记得执行flush，使配置立即生效。
这是去 Navicat 客户端连接数据库，即可生效。

• 更改数据库的数据文件路径

有时会有需求，需要更改数据文件的保存路径。

原路径：/var/lib/mysql
目标路径：/home/mysql_data/mysql

操作步骤

1. 首先创建文件夹

mkdir -p /home/mysql_data

2. 关闭Mysql

systemctl stop mysqld

3. 复制数据文件

cp -arp /var/lib/mysql /home/mysql_data

4. 修改配置 /etc/my.cnf
   将datadir 和 socket 的原路径修改为目标路径
   并加上 client 的 socket

datadir = /home/mysql_data/mysql
socket = /home/mysql_data/mysql/mysql.sock

# 增加的client
[client]
socket = /home/mysql_data/mysql/mysql.sock

保存退出。

执行：

setenforce 1

启动Mysql 服务

systemctl start mysqld

中间遇到的问题：

Could not open or create the system tablespace. 
If you tried to add new data files to the system tablespace, 
and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. 
InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

2020-02-26T05:57:12.214700Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-02-26T05:57:12.220662Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-02-26T05:57:12.220738Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2020-02-26T05:57:12.220758Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2020-02-26T05:57:12.220765Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2020-02-26T05:57:12.220768Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2020-02-26T05:57:12.220774Z 0 [ERROR] InnoDB: Cannot open datafile './ibtmp1'
2020-02-26T05:57:12.220785Z 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
2020-02-26T05:57:12.220789Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file
2020-02-26T05:57:12.821212Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-02-26T05:57:12.821241Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-02-26T05:57:12.821248Z 0 [ERROR] Failed to initialize builtin plugins.
2020-02-26T05:57:12.821252Z 0 [ERROR] Aborting

看起来是权限问题， 但是看了权限是有的， 又重新分配了权限了，还是不行；

chown -R mysql:mysql /home/mysql_data/mysql/
chmod -R 755 /home/mysql_data/mysql/

百度，google了多篇文章，最后找到这篇参考。
https://www.cnblogs.com/ajianbeyourself/p/4158874.html
才算解决了，执行命令：

setenforce 0

应该是上面在修改数据文件路径时，执行了 setenforce 1 后导致的。
坑了。

setenforece 介绍
https://www.cnblogs.com/pandachen/p/7624788.html

重启后，在服务器登入数据库时，报下面错误

Can't connect to local MySql server through socket '/var/lib/mysql/mysql.sock' (2)

经过各种测试，最终发现是 /etc/my.cnf 里忘记加下面这段话，加完后，就正常了。

[client]
socket = /home/mysql_data/mysql/mysql.sock

• only_full_group_by 模式问题

运行时异常: ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'znkf.t.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in class path resource

这个是因为Mysql 5.7后，默认禁止了该模式，sql 代码里有使用的话，需要将其开启。

解决方法：
https://blog.csdn.net/u013948858/article/details/80541602

修改 my.cnf 配置文件，删掉 only_full_group_by 这一项
在[mysqld] 模块下添加：

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;

一定在加在 mysqld 下，否则无效。