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

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