Mysql connect to server: Access denied for user root@localhost

Question

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

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

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

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

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

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

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

8. Created the directory..

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

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

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

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

Yung

My initial suspicion would be with the localhost part of 'root'@'localhost' - if you do not have a user called, specifically, 'root'@'localhost' it will not recognise this as the same user as the base root user (you might find root@myserver in your server users, or similar). You can add 'root'@'localhost' or use wildcards IE 'root'@%. Hope this helps! – Terret

While I am sure most folks here would love to help, we really don't have enough information to properly troubleshoot this issue. First be sure that you have went through all the steps here: dev.mysql.com/doc/refman/5.6/en/osx-installation-pkg.html. Assuming you have, then an excerpt from the mysql error log files would be the next thing in order. Also you should include the my.cnf file for your install. – Theophilus

Hi - it says "using password: NO... Try "mysql -u root". Without the -p – Amygdalin

@ Giles: In order to list my server users or to add a user I need to connect to the server first or am I mistaken? @ Lars Hansen: mysql -u root returns ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) – Yung

Thanks for your response John, the site you linked to is in fact exactly the one that I used to setup. I went through it without encountering any issues. I have indeed not included my.cnf for my install, I will look into it. However the file states that the template will be copied to the default location during install.. I'll have a look thanks. I will add the mysql error log to my main post, I'm not sure what you are looking for so I'll just place it as is. – Yung

Added my.cnf to my /etc folder, copying the default settings from my-default.cnf /usr/local/mysql/support-files, however still receiving access denied errors while trying to connect via numerous different ways.. – Yung

Why does this question have the sql tag? – Mahaffey

Answer 1

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

Cyndy

Mysqld logs is in the default location you describe. /usr/local/var/mysql/data/Robs-MacBook-Pro.local.err, the file is rather large and I can't post the whole thing here. Anything in particular I should look for? Because it does not really tell me a whole lot. EDIT: /usr/local/var/mysql/data/mysqld.local.err says a bit more, I'll add the last few lines to the main post. – Yung

ps ax | grep mysql | grep -v grep returns: 94 ?? Ss 0:00.31 /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid – Yung

That log looks pretty normal, have you tried official permissions reset guide dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html ? – Cyndy

I'm working with a mac, so not exactly that. But I did attempt to change the password in order to change the permissions. I described this in step 10 and 11 of my process. Have I gone wrong here? – Yung

Mac is a unix-like system, it is covered in guide, in your steps you only restarted server, but did not inject password-changing sql into it – Cyndy

I know mac is unix-like, however the commands described on the page you linked are not known to my mac. I'll look further into the correct syntax on password changing sql tomorrow as my day is coming to an end now. Thank you very much for your attempts on helping me out! – Yung

That is because they are not actual commands, but templates for writing your own ones – Cyndy

I see, well here are my results: Attempting to stop the server: sudo /usr/local/mysql/support-files/mysql.server stop Password: ERROR! MySQL server PID file could not be found! Then for the command that should start the server with the init file option, I'll add the results to the main post as it is too long to paste here. – Yung

@L.Klotz Have tried to rename mysql.sock file and restart the mysql server? – Ovid