报错如下:
this is incompatible with sql_mode=only_full_group_by
1、原因
这个错误一般发生在mysql 5.7以及 5.7以上的版本中,其原因是mysql的默认配置中,sql_mode="ONLY_FULL_GROUP_BY" 这个配置严格执行了 'SQL92标准',
2、解决方法
我们可以修改 sql_mode 的配置,使其能兼容。
修改 my.ini 文件,通过手动添加sql_mode的方式强制指定不需要ONLY_FULL_GROUP_BY属性,
在 [mysqld] 下面添加代码:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
解决方法:
停止服务
[root@cdh2 ~]# systemctl stop mariadb
[root@cdh2 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Mon 2020-09-07 11:03:55 CST; 11min ago
Main PID: 3638 (code=exited, status=0/SUCCESS)
Aug 23 23:06:29 cdh2.macro.com mariadb-prepare-db-dir[3558]: http://dev.mysql.com
Aug 23 23:06:29 cdh2.macro.com mariadb-prepare-db-dir[3558]: Support MariaDB development by buying support/new features from MariaDB
Aug 23 23:06:29 cdh2.macro.com mariadb-prepare-db-dir[3558]: Corporation Ab. You can contact us about this at sales@mariadb.com.
Aug 23 23:06:29 cdh2.macro.com mariadb-prepare-db-dir[3558]: Alternatively consider joining our community based development effort:
Aug 23 23:06:29 cdh2.macro.com mariadb-prepare-db-dir[3558]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
Aug 23 23:06:29 cdh2.macro.com mysqld_safe[3638]: 200823 23:06:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Aug 23 23:06:29 cdh2.macro.com mysqld_safe[3638]: 200823 23:06:29 mysqld_safe Starting mysqld daemon with databases from /va.../mysql
Aug 23 23:06:31 cdh2.macro.com systemd[1]: Started MariaDB database server.
Sep 07 11:03:17 cdh2.macro.com systemd[1]: Stopping MariaDB database server...
Sep 07 11:03:55 cdh2.macro.com systemd[1]: Stopped MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
修改/etc/my.conf
[root@cdh2 ~]# vim /etc/my.cnf
[mysqld]
......
skip-grant-tables # 在[mysqld]中加入该字段
重新启动
[root@cdh2 ~]# vim /etc/my.cnf
[root@cdh2 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2020-09-07 11:16:42 CST; 5h 23min ago
Process: 7199 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 7170 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 7198 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─7198 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─7367 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/va...
Sep 07 11:16:05 cdh2.macro.com systemd[1]: Starting MariaDB database server...
Sep 07 11:16:06 cdh2.macro.com mysqld_safe[7198]: 200907 11:16:06 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Sep 07 11:16:06 cdh2.macro.com mysqld_safe[7198]: 200907 11:16:06 mysqld_safe Starting mysqld daemon with databases from /va.../mysql
Sep 07 11:16:42 cdh2.macro.com systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.
登录
[root@cdh2 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 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
MariaDB [mysql]> update user set password=password('password') where user='root' and Host = 'localhost';
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> exit
Bye
停止服务,恢复配置
[root@cdh2 ~]# systemctl stop mariadb
[root@cdh2 ~]# vim /etc/my.cnf
[mysqld]
......
# skip-grant-tables
重新登录
[root@cdh2 ~]# systemctl start mariadb
[root@cdh2 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
遇到的问题:用本地navicat无法连接远程MySQL
解决方法:
切换mysql数据库
MariaDB [(none)]> 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
执行查询,查看数据库当前有访问权限的信息
MariaDB [mysql]> SELECT User, Password, Host FROM user;
+------+-------------------------------------------+----------------+
| User | Password | Host |
+------+-------------------------------------------+----------------+
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | localhost |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | cdh3.macro.com |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | 127.0.0.1 |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | ::1 |
+------+-------------------------------------------+----------------+
4 rows in set (0.00 sec)
可以看到只有本机才能访问
设置访问权限
MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
其中"."代表所有资源所有权限, “'root'@%”其中root代表账户名,%代表所有的访问地址,也可以使用一个唯一的地址进行替换,只有一个地址能够访问。如果是某个网段的可以使用地址与%结合的方式,如10.0.42.%。IDENTIFIED BY 'password',这个password是指访问密码。WITH GRANT OPTION允许级联授权。
刷新访问权限表(不要忘了)
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
1.重新查看用户表
MariaDB [mysql]> SELECT User, Password, Host FROM user;
+------+-------------------------------------------+----------------+
| User | Password | Host |
+------+-------------------------------------------+----------------+
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | localhost |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | cdh3.macro.com |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | 127.0.0.1 |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | ::1 |
| root | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | % |
+------+-------------------------------------------+----------------+
5 rows in set (0.00 sec)
8.验证远程访问