遇到的问题一
报错如下：
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.验证远程访问
