Mysql连接到服务器:用户root@localhost拒绝访问。

时间:2022-01-24 03:13:10

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

edit9:我可能只是缺少一些文件夹的权限吗?我真的很感激你的建议。

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.

edit3:由于这篇文章没有得到足够的回复,所以我必须尽快地把它重新写出来,以显示我认为我已经扣除的内容。

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?):
  2. 删除了运行以下命令的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.*

sudo rm /usr/local/mysql sudo rm -rf / usr/local/mysqlsudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/ MySQL* rm -rf /Library/ MySQL* sudo rm -rf /Library/收据/MySQL* sudo rm -rf /Library/ 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/

    mysql-5.7.9-osx10.10-x86_64下载。dmg从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)

    mysql-5.7.9-osx10.9-x86_64安装。pkg使用标准位置上的标准安装。(为什么pkg 10.9在我下载10.10的时候?我的OS X版本是10.10 yosemite

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

    我得到一个通知,创建了root@localhost的临时密码。我写得很完美。

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).

    试图通过终端启动mysql服务器:sudo / usr/local/mysql/supportfiles /mysql。服务器启动返回:启动MySQL ..错误!服务器在不更新PID文件(/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

    在对这个pid文件进行了一些研究之后,我意识到我需要在/etc中创建my.cnf,所以我做到了。cd / usr/local/mysql/supportfiles / 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

    . .编辑并保存my . cnf中所做cd /etc sudo nano my.cnf,输入行文件= /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

    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.

    假设my.cnf现在都设置为pid文件。我试着重新启动服务器。但是什么都没有改变。

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.

    我停止mysql服务器(它不应该运行,只是以防万一)。sudo /usr/local/mysql/support-files / mysql。服务器停止。这可能很有趣,因为我最终以某种形式,我不能做任何事情。我必须从这里重新启动终端。

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

    尝试使用sudo mysql_safe——skip-grant表返回安全模式的mysql。

    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

    151110 10:10 mysqld_safe登录到/usr/local/var/mysql/Robs-MacBook-Pro.local.err。mysqld_safe启动mysqld守护进程,数据库来自/usr/local/var/mysql 151110 10:28:10 mysqld_safe mysqld,从pid文件/ usr/local/var/mysql/robs-macbookpro.local。pid结束

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?

这就是这个pid文件。我没有为basedir/datadir/port/server_id/socket设置值。也许这是吗?如果有,我应该在这里设置什么值?

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 ..

当我在互联网上随意修改建议时,经常会出现的其他错误包括:用户root@localhost被拒绝使用密码:yes/no或不能通过socket连接到mysql服务器。

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

/usr/local/var/mysql/data/mysqld.local。做错了最后几行:

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

从Vasfed得到一个不错的建议:然后,按照描述创建txt文件。使用我使用的目录和文件名输入命令:mysqld_safe—init-file=/home/mysql-init &返回:robs - macbookpro:home leroyklotz$ 151118 09:25:23 mysqld_safe日志到'/usr/local/var/mysql/Robs-MacBook-Pro.local.err'。使用数据库从/usr/local/var/mysql /usr/local/bin/mysqld_safe:第129行:/ usr/local/var/mysql/robs-macbookpro.local。err:权限拒绝rm: /tmp/mysql。sock:权限拒绝/usr/local/bin/mysqld_safe:第166行:/ usr/local/var/mysql/robs-macbookpro.local。err:允许从pid文件/usr/local/var/mysql/Robs-MacBook-Pro.local中删除mysqld_safe mysqld。pid结束/usr/local/bin/mysqld_safe:第129行:/ usr/local/var/mysql/robs-macbookpro.local。错:没有权限

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.local授予权限。犯错和mysql。袜子(它仍然被称为mysql.sock。锁,这要紧吗?)不完全解决问题,错误消息现在读:

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

1 个解决方案

#1


1  

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

不要自己创建/触摸pid文件,它是由守护进程自己创建的,以显示它的pid ?

On macos mysql should run fine without my.cnf

在macos mysql中,没有my.cnf就可以运行。

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

首先要查看/ usr/local/var/mysql/robs-macbookpro.local.err中的mysqld日志。

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)

奇怪的位置,btw,默认值是/usr/local/mysql/data/ 。err(需要sudo来读取),可能是您安装的服务器没有启动,并且您还运行了其他守护进程(例如通过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

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

#1


1  

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

不要自己创建/触摸pid文件,它是由守护进程自己创建的,以显示它的pid ?

On macos mysql should run fine without my.cnf

在macos mysql中,没有my.cnf就可以运行。

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

首先要查看/ usr/local/var/mysql/robs-macbookpro.local.err中的mysqld日志。

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)

奇怪的位置,btw,默认值是/usr/local/mysql/data/ 。err(需要sudo来读取),可能是您安装的服务器没有启动,并且您还运行了其他守护进程(例如通过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

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