MySQL常见问题一

soundhearer

发布于 2020-10-16 11:06:37

6160

发布于 2020-10-16 11:06:37

文章被收录于专栏：数据湖数据湖

遇到的问题一

报错如下：

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

MySQL忘记root密码

解决方法：

停止服务

[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.验证远程访问