Mysql无法远程连接解决方案
发表于:2024-10-19 作者:千家信息网编辑
千家信息网最后更新 2024年10月19日,前言Mysql 版本:5.7.23操作系统:Linux问题描述:只能通过Linux系统账号Root命令行进入数据库,无法使用JDBC,远程连接工具进入数据库。报错:ERROR 1698 (28000)
千家信息网最后更新 2024年10月19日Mysql无法远程连接解决方案
前言
Mysql 版本:5.7.23
操作系统:Linux
问题描述:
只能通过Linux系统账号Root命令行进入数据库,无法使用JDBC,远程连接工具进入数据库。
报错:ERROR 1698 (28000): Access denied for user 'root'@'localhost'
这个问题明显就是没有开放远程连接授权,所以导致只能使用Linux的Root账号登录。
解决流程
1.找到/etc/mysql/mysql.conf.d/mysqld.cnf文件,在[mysqld]最后面加skip-grant-tables
## The MySQL database server configuration file.## You can copy this to one of:# - "/etc/mysql/my.cnf" to set global options,# - "~/.my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with --help to get a list of available options and with# --print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html# This will be passed to all mysql clients# It has been reported that passwords should be enclosed with ticks/quotes# escpecially if they contain "#" chars...# Remember to edit /etc/mysql/debian.cnf when changing the socket location.# Here is entries for some specific programs# The following values assume you have at least 32M ram[mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = 0[mysqld]## * Basic Settings#user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc-messages-dir = /usr/share/mysqlskip-external-locking# 加入的内容,开启跳过权限校验skip-grant-tables## Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address = 127.0.0.1## * Fine Tuning#key_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8# This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam-recover-options = BACKUP#max_connections = 100#table_open_cache = 64#thread_concurrency = 10## * Query Cache Configuration#query_cache_limit = 1Mquery_cache_size = 16M## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file = /var/log/mysql/mysql.log#general_log = 1## Error log - should be very few entries.#log_error = /var/log/mysql/error.log## Here you can see queries with especially long duration#slow_query_log = 1#slow_query_log_file = /var/log/mysql/mysql-slow.log#long_query_time = 2#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.#server-id = 1#log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI "tinyca".## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem
保存后,重启服务sudo service mysql restart.
2.无验证进入mysql数据库修改user表中root账号信息,flush privileges;
ckmike@ckmikePC:~$ mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> update mysql.user set authentication_string=password('账号密码') where user='root';Query OK, 0 rows affected, 1 warning (0.03 sec)Rows matched: 3 Changed: 0 Warnings: 1mysql> update user set plugin="mysql_native_password";Query OK, 1 row affected (0.00 sec)Rows matched: 7 Changed: 1 Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye
3./etc/mysql/mysql.conf.d/mysqld.cnf文件,去掉skip-grant-tables,开启校验
4.重启服务
sudo service mysql restart;至此就搞定了,使用jdbc、非Linuxroot账号都可以登录了。
账号
数据
数据库
文件
系统
问题
服务
登录
明显
操作系统
信息
内容
前言
命令
密码
就是
工具
权限
流程
版本
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发不用造前任的轮子
蒋建春 网络安全宣传周
数据中心服务器连接设置
现在热门的网络技术
oracle数据库去重查表
定期组织开展网络安全检查检测
数据库本地备份方案
广东交友软件开发哪家正规
2021国考网络安全
软件开发中开发人员间协作
网络安全意识心得800字
齐向东谈网络安全投入
矿大网络安全实验
数果s1手机服务器地址怎么注册
刀片机服务器价格
图片服务器租用
数据导入hive数据库的办法
花式宣传让网络安全知识进万家
服务器游戏吾爱破解
服务器分享
汽车为什么要用车载网络技术
防火墙 服务器
苏州龙宫果软件开发有限公司
怎样在服务器上传软件
昆明软件开发机构
北京亿网络技术有限公司
生存战争服务器版联机
lol服务器怎么用
做软件开发方案
大型应用软件开发语言选择