Worstedset Worstedset - 7 months ago 20
PHP Question

Mysql - [1045] access denied for user

First of all I want to say I'm completely new to working with client/server architecture, beginner at using the command line, and have never fixed a program before. I downloaded MySql 5.6 for Windows 7 64 bit along with the MySql workbench.

So everything was working fine at first with logging in, databases working, sql queries, etc. What I wanted to do next was use php to access my database from a webpage. So I Installed Wampserver (which includes Apache, phpmyadmin, and I guess an Api for using mysql with php?) and afterwards I was not able to get into my server at all.

But, it's possible it's not because of installing Wampserver, because I did not check to see if it worked right after the install. What I did was, after I installed Wampserver, I changed my root password from Mysqlworkbench in order to have a password I could use. So, it must be that I can't connect to the server because (a) I changed the root login password from the workbench, or (b) I installed Wampserver. I have been messing with this for several hours trying different things, and I think the problem is probably with the files. From what I understand, if you want to log in to the server, it first authenticates your info by comparing it with another file? I think I probably have a problem with the my.ini file that I've seen mentioned.

This is usually what happens when I try to connect

owner>mysql -u root -p
Enter password: ********
Error 1045 (28000): Access denied for user 'root'@'localhost' (using password:YES)


I'll list some of the things that I've tried already. One thing was that I probably need to reset the password, but I already tried doing that using the command line using the

owner>mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pass123'); FLUSH PRIVILEGES;

Btw, I'm not sure if you're supposed to type the '>' manually because I had to type that '>' sign after mysql for that change password statement to work.

I tried that with the mysql service turned on. Then i tried again with mysql service turned off, but still gives same error that access is denied.

So I tried another suggestion after googling. I turned off the mysql service, went to Workbench, clicked on my root instance and then it gets me into the database. So then it tells me to try starting server from inside there, so I do and it starts, but then prompts me for password. The message log from workbench is:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2016-04-15 18:20:46 - Workbench will use cmd shell commands to start/stop this instance
2016-04-15 18:20:50 - Starting server...

FROM OWNER-PC.err:
2016-04-15 18:20:52 51636 Note Plugin 'FEDERATED' is disabled.
2016-04-15 18:20:52 c84c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-04-15 18:20:52 51636 Note InnoDB: Using atomics to ref count buffer pool pages
2016-04-15 18:20:52 51636 Note InnoDB: The InnoDB memory heap is disabled
2016-04-15 18:20:52 51636 Note InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-04-15 18:20:52 51636 Note InnoDB: Memory barrier is not used
2016-04-15 18:20:52 51636 Note InnoDB: Compressed tables use zlib 1.2.3
2016-04-15 18:20:52 51636 Note InnoDB: Not using CPU crc32 instructions
2016-04-15 18:20:52 51636 Note InnoDB: Initializing buffer pool, size = 269.0M
2016-04-15 18:20:52 51636 Note InnoDB: Completed initialization of buffer pool
2016-04-15 18:20:52 51636 Note InnoDB: Highest supported file format is Barracuda.
2016-04-15 18:20:52 51636 Note InnoDB: 128 rollback segment(s) are active.
2016-04-15 18:20:52 51636 Note InnoDB: Waiting for purge to start
2016-04-15 18:20:52 51636 Note InnoDB: 5.6.28 started; log sequence number 10175537
2016-04-15 18:20:52 51636 Note Server hostname (bind-address): '*'; port: 3306
2016-04-15 18:20:52 51636 Note IPv6 is available.
2016-04-15 18:20:52 51636 Note - '::' resolves to '::';
2016-04-15 18:20:52 51636 Note Server socket created on IP: '::'.
2016-04-15 18:20:52 51636 Note Event Scheduler: Loaded 0 events
2016-04-15 18:20:52 51636 Note C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: ready for connections.
Version: '5.6.28-log' socket: '' port: 3306 MySQL Community Server (GPL)
2016-04-15 18:21:14 - Server start done.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I believe the point of starting the server from workbench was for me to be able to access the users and privileges but says I cannot do that without a connection to server. So, I'm at a loss could be something simple, maybe my syntax for changing pass is wrong, maybe a files messed up, maybe wampserver changed something.

Its worth noting that inside of wampserver files "C:\wamp\bin\mysql\mysql5.6.17\bin" is alot of the same .exe files that are in "C:\Program Files\MySQL\MySQL Server 5.6\bin"

Any help appreciated.

Answer

So I was googling around but nothing I tried quite worked. So I went on Youtube, and there I found my answer. I will post the link here Youtube password video since it's easier than just explaining, though I will give a short summary. It was done with command line and making a .txt file named "mysql-init.txt". So I had to make a .txt file that contained a command to use mysql and update the mysql.user table, and update the password, then flush privileges. Then had to turn the mysql server service off. After that, I moved the "my.ini" file into the folder of the mysql server ("MySQL Server 5.6" for me). Then ,using the command prompt, went into the mysql server directory. And then I did this:

C:\Program Files\MySQL\MySQL Server 5.6>bin\mysqld.exe --defaults-file="my.ini" --init-file="C:\\Users\\Johnny\\Desktop\\mysql-init.txt" --console

After that, I just restarted the mysql server service, and the new password worked.

I'm using windows 7, it's probably different for other operating systems. There are a few things to note about the video though. He's using a different version of Mysql, so the name of the service for his mysql server is just mysql, but for me with mysql 5.6 installed, the name of the service is mysql56. Also, in the video, he has "my.ini" in the directory of his server, but for me, it was not there. After reading the comments, the "my.ini" file was in a hidden folder, so I unhid the folders (very easy) and found the "my.ini" and placed it in the "MySQL Server 5.6" folder. Also, in the video he types a very sneaky space that will cause some people problems if they are not paying close attention. It is: --defaults-file="my.ini"SPACE--init-file="........." Another thing, make sure you are in the actual directory, because even if you have mysqld.exe on the system path, it didn't work with me, I had to be in the actual directory as in the video.