Mysql连接到服务器:用户root @ localhost的访问被拒绝 [英] Mysql connect to server: Access denied for user root@localhost

查看：180
本文介绍了Mysql连接到服务器:用户root @ localhost的访问被拒绝的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

edit9:是否有可能我只是缺少文件夹的一些权限?我真的非常感谢您提出一些建议.

edit3:由于此帖子没有得到足够的答复，因此至关重要的是，我尽快完成此操作，因此我重新构建了帖子以显示我认为到目前为止所扣除的内容.

注意:通常无法通过大量不同的命令登录.

我的过程:

  1. 删除了运行以下命令的mysql(我忘记了什么吗?):

sudo rm /usr/local/mysql sudo rm -rf /usr/local/mysql* sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/MySQL* rm -rf ~/Library/PreferencePanes/MySQL* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /var/db/receipts/com.mysql.*

  1. 从 http://dev.mysql.com/downloads/mysql下载mysql-5.7.9-osx10.10-x86_64.dmg /

  2. 在标准位置使用标准安装程序安装了mysql-5.7.9-osx10.9-x86_64.pkg. (为什么下载10.10时pkg 10.9?我的OS X版本是10.10优胜美地)

  3. 我收到一条通知，指出已创建root @ localhost的临时密码.我把它写得很完美.

这是问题开始的地方.

  1. 尝试通过终端通过以下方式启动mysql服务器: sudo /usr/local/mysql/support-files/mysql.server start返回: Starting MySQL .. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld.pid).

  2. 在研究了这个pid文件之后，我意识到我需要在/etc中创建my.cnf，所以我做了. cd /usr/local/mysql/support-files/ sudo cp my-default.cnf /etc/my.cnf

  3. 编辑并保存my.cnf .. cd /etc sudo nano my.cnf，输入行 pid-file = /var/run/mysqld/mysqld.pid

  4. 创建目录.

    sudo mkdir/var/run/mysqld 须藤触摸/var/run/mysqld/mysqld.pid 须藤chown -R mysql:mysql/var/run/mysqld

  5. 假定my.cnf现在应该全部设置好，包括pid文件.我试图再次启动服务器.但是什么都没有改变.

好的，所以我决定确定，我将尝试更改安装时临时设置的密码.

  1. 我停止了mysql服务器(它不应该正在运行，但以防万一). sudo /usr/local/mysql/support-files/mysql.server stop.这可能很有趣，因为我最终会遇到某种外壳形式，无法执行任何操作.我必须从这里重新启动终端.

  2. 尝试使用sudo mysql_safe --skip-grant-tables以安全模式启动mysql会返回:

    151110 10:28:10 mysqld_safe登录到'/usr/local/var/mysql/Robs-MacBook-Pro.local.err'. 151110 10:28:10 mysqld_safe使用/usr/local/var/mysql中的数据库启动mysqld守护程序 151110 10:28:10 mysqld_safe mysqld来自pid文件/usr/local/var/mysql/Robs-MacBook-Pro.local.pid已结束

所以这又是关于这个pid文件的全部.我在这里没有为basedir/datadir/port/server_id/socket设置任何值.也许就是这样吗?如果是这样，我应该在这里设置什么值?

当我在互联网上四处寻找其他建议时，经常会发生的其他错误包括: Access denied for user root@localhost using password: yes/no或 Cant connect to mysql server through socket ..

/usr/local/var/mysql/data/mysqld.local.err最后几行:

2015-11-17T08:13:55.755115Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-11-17T08:13:55.756291Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2015-11-17T08:13:55.756310Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2015-11-17T08:13:55.780792Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-11-17T08:13:55.781750Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.9) starting as process 94 ...
2015-11-17T08:13:55.796438Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2015-11-17T08:13:55.802783Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-11-17T08:13:55.802816Z 0 [Note] InnoDB: Uses event mutexes
2015-11-17T08:13:55.802826Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2015-11-17T08:13:55.802834Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-11-17T08:13:55.804723Z 0 [Note] InnoDB: Number of pools: 1
2015-11-17T08:13:55.808009Z 0 [Note] InnoDB: Using CPU crc32 instructions
2015-11-17T08:13:55.821713Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2015-11-17T08:13:55.843514Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-11-17T08:13:55.898365Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-11-17T08:13:55.935027Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-11-17T08:13:55.935222Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-11-17T08:13:55.950640Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-11-17T08:13:55.952035Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-11-17T08:13:55.952061Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-11-17T08:13:55.952538Z 0 [Note] InnoDB: Waiting for purge to start
2015-11-17T08:13:56.070486Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2471474
2015-11-17T08:13:56.070792Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7.9-osx10.9-x86_64/data/ib_buffer_pool
2015-11-17T08:13:56.071268Z 0 [Note] InnoDB: not started
2015-11-17T08:13:56.072953Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-11-17T08:13:56.075825Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151117  9:13:56
2015-11-17T08:13:56.086709Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2015-11-17T08:13:56.112666Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2015-11-17T08:13:56.112759Z 0 [Note] IPv6 is available.
2015-11-17T08:13:56.112804Z 0 [Note]   - '::' resolves to '::';
2015-11-17T08:13:56.113081Z 0 [Note] Server socket created on IP: '::'.
2015-11-17T08:13:56.180223Z 0 [Note] Event Scheduler: Loaded 0 events
2015-11-17T08:13:56.180406Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

结果来自Vasfed的一个不错的建议: 然后，按照说明创建txt文件.输入带有我使用的目录和文件名的命令:mysqld_safe --init-file=/home/mysql-init & 返回值:Robs-MacBook-Pro:home leroyklotz$ 151118 09:25:23 mysqld_safe Logging to '/usr/local/var/mysql/Robs-MacBook-Pro.local.err'. 151118 09:25:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql /usr/local/bin/mysqld_safe: line 129: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied rm: /tmp/mysql.sock: Permission denied /usr/local/bin/mysqld_safe: line 166: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied 151118 09:25:23 mysqld_safe mysqld from pid file /usr/local/var/mysql/Robs-MacBook-Pro.local.pid ended /usr/local/bin/mysqld_safe: line 129: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied

对Robs-MacBook-Pro.local.err和mysql.sock(仍称为mysql.sock.lock，这有关系吗?)的授予权限不能完全解决问题，错误消息现在显示为: 

Robs-MacBook-Pro:~ leroyklotz$ mysqld_safe --init-file=/home/mysql-init &
[1] 747
Robs-MacBook-Pro:~ leroyklotz$ 151119 09:13:51 mysqld_safe Logging to '/usr/local/var/mysql/Robs-MacBook-Pro.local.err'.
151119 09:13:51 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
rm: /tmp/mysql.sock: Permission denied
151119 09:13:53 mysqld_safe mysqld from pid file /usr/local/var/mysql/Robs-MacBook-Pro.local.pid ended

解决方案

不要自己创建/触摸pid文件，它是由守护进程自己创建的，以指示其pid

在macos上，如果没有my.cnf，mysql应该可以正常运行

第一件事是查看/usr/local/var/mysql/Robs-MacBook-Pro.local.err上的mysqld日志.

奇怪的位置，顺便说一句，默认位置是/usr/local/mysql/data/<your_hostname>.err(需要sudo读取)，可能是您安装的服务器根本无法启动，并且您正在运行其他守护程序(例如，通过自制程序安装)

检查ps ax | grep mysql | grep -v grep-它应该有2个进程，如果还有其他mysql，则都带有/usr/local/mysql/bin中的二进制文件-这说明了所有

edit9: Is it a possibility that I'm simply missing a few permissions on folders? I'd really, REALLY appreciate some more suggestions..

edit3: As this post did not get enough replies and it is absolutely vital I get this going as soon as possible I reconstructed my post to display what I think I have deducted so far.

Note: logging in normally via numerous different commands simply did not work.

My process:

  1. Removed mysql running the following commands (did I forget anything?):

sudo rm /usr/local/mysql sudo rm -rf /usr/local/mysql* sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/MySQL* rm -rf ~/Library/PreferencePanes/MySQL* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /var/db/receipts/com.mysql.*

  1. Downloaded mysql-5.7.9-osx10.10-x86_64.dmg from http://dev.mysql.com/downloads/mysql/

  2. Installed mysql-5.7.9-osx10.9-x86_64.pkg using a standard install on standard location. (Why is the pkg 10.9 while I downloaded 10.10? My OS X version is 10.10 yosemite)

  3. I get a notification that a temporary password for root@localhost has been created. I wrote this down perfectly.

Here is where the problems start.

  1. Attempting to start mysql server via terminal with: sudo /usr/local/mysql/support-files/mysql.server start returns: Starting MySQL .. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld.pid).

  2. After some research about this pid file I realized I needed to create my.cnf in /etc, so I did.. cd /usr/local/mysql/support-files/ sudo cp my-default.cnf /etc/my.cnf

  3. Edited and saved my.cnf.. cd /etc sudo nano my.cnf, entered the line pid-file = /var/run/mysqld/mysqld.pid

  4. Created the directory..

    sudo mkdir /var/run/mysqld sudo touch /var/run/mysqld/mysqld.pid sudo chown -R mysql:mysql /var/run/mysqld

  5. Assuming my.cnf should be all set now including the pid file. I tried to start the server again. However nothing has changed.

Okay, so then I decided fine, I'll try to change the password that was temporarily set when I installed.

  1. I stop the mysql server (it shouldn't be running, but just in case). sudo /usr/local/mysql/support-files/mysql.server stop. This might be interesting because I end up in some sort of shell form in which I am not able to do anything whatsoever. I have to restart terminal from here.

  2. Trying to start mysql in safe mode with sudo mysql_safe --skip-grant-tables returns:

    151110 10:28:10 mysqld_safe Logging to '/usr/local/var/mysql/Robs-MacBook-Pro.local.err'. 151110 10:28:10 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql 151110 10:28:10 mysqld_safe mysqld from pid file /usr/local/var/mysql/Robs-MacBook-Pro.local.pid ended

So again it's all about this pid file.. I have no values set for basedir/datadir/port/server_id/socket here.. Perhaps that is it? If so what values should I set here?

Other errors that regularly occur when I fiddle around with different suggestions on the internet include: Access denied for user root@localhost using password: yes/no or Cant connect to mysql server through socket ..

/usr/local/var/mysql/data/mysqld.local.err last few lines:

2015-11-17T08:13:55.755115Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-11-17T08:13:55.756291Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2015-11-17T08:13:55.756310Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2015-11-17T08:13:55.780792Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-11-17T08:13:55.781750Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.9) starting as process 94 ...
2015-11-17T08:13:55.796438Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2015-11-17T08:13:55.802783Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-11-17T08:13:55.802816Z 0 [Note] InnoDB: Uses event mutexes
2015-11-17T08:13:55.802826Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2015-11-17T08:13:55.802834Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-11-17T08:13:55.804723Z 0 [Note] InnoDB: Number of pools: 1
2015-11-17T08:13:55.808009Z 0 [Note] InnoDB: Using CPU crc32 instructions
2015-11-17T08:13:55.821713Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2015-11-17T08:13:55.843514Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-11-17T08:13:55.898365Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-11-17T08:13:55.935027Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-11-17T08:13:55.935222Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-11-17T08:13:55.950640Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-11-17T08:13:55.952035Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-11-17T08:13:55.952061Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-11-17T08:13:55.952538Z 0 [Note] InnoDB: Waiting for purge to start
2015-11-17T08:13:56.070486Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2471474
2015-11-17T08:13:56.070792Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql-5.7.9-osx10.9-x86_64/data/ib_buffer_pool
2015-11-17T08:13:56.071268Z 0 [Note] InnoDB: not started
2015-11-17T08:13:56.072953Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-11-17T08:13:56.075825Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151117  9:13:56
2015-11-17T08:13:56.086709Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2015-11-17T08:13:56.112666Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2015-11-17T08:13:56.112759Z 0 [Note] IPv6 is available.
2015-11-17T08:13:56.112804Z 0 [Note]   - '::' resolves to '::';
2015-11-17T08:13:56.113081Z 0 [Note] Server socket created on IP: '::'.
2015-11-17T08:13:56.180223Z 0 [Note] Event Scheduler: Loaded 0 events
2015-11-17T08:13:56.180406Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Outcome to a nice suggestion from Vasfed: Then, creating the txt file as described. Entered the command with my used directory and filename: mysqld_safe --init-file=/home/mysql-init & Which returns: Robs-MacBook-Pro:home leroyklotz$ 151118 09:25:23 mysqld_safe Logging to '/usr/local/var/mysql/Robs-MacBook-Pro.local.err'. 151118 09:25:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql /usr/local/bin/mysqld_safe: line 129: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied rm: /tmp/mysql.sock: Permission denied /usr/local/bin/mysqld_safe: line 166: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied 151118 09:25:23 mysqld_safe mysqld from pid file /usr/local/var/mysql/Robs-MacBook-Pro.local.pid ended /usr/local/bin/mysqld_safe: line 129: /usr/local/var/mysql/Robs-MacBook-Pro.local.err: Permission denied

Granting permissions on Robs-MacBook-Pro.local.err and mysql.sock (which is still called mysql.sock.lock, does this matter?)does not completely solve the issue, error message now reads:

Robs-MacBook-Pro:~ leroyklotz$ mysqld_safe --init-file=/home/mysql-init &
[1] 747
Robs-MacBook-Pro:~ leroyklotz$ 151119 09:13:51 mysqld_safe Logging to '/usr/local/var/mysql/Robs-MacBook-Pro.local.err'.
151119 09:13:51 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
rm: /tmp/mysql.sock: Permission denied
151119 09:13:53 mysqld_safe mysqld from pid file /usr/local/var/mysql/Robs-MacBook-Pro.local.pid ended

解决方案

Do not create/touch pid file yourself, it's intended to be created by daemon itself to indicate its pid

On macos mysql should run fine without my.cnf

First thing is to look into mysqld logs at /usr/local/var/mysql/Robs-MacBook-Pro.local.err.

Strange location, btw, default is in /usr/local/mysql/data/<your_hostname>.err (need sudo to read), may be your installed server does not start at all and you have other daemon running (for example installed via homebrew)

Check ps ax | grep mysql | grep -v grep - it should have 2 processes, both with binaries in /usr/local/mysql/bin, if there's other mysql - this explains all

这篇关于Mysql连接到服务器:用户root @ localhost的访问被拒绝的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆