3

I know that some users may say that this is a duplication but seriously I have combed the answers and those on the internet for over half a day and I am so frustrated i am going to explode.

Now that therapeutic explanation is done, I have installed MySql Server and MariaDb 10 from the Ubuntu repositories on Ubuntu 12.04 LTS and input the root password when it is installing[When the pink screen appears and prompts for the root password] and the installation goes through as smoothly as a knife through butter.

Now the real frustration begins in either of the below 2 scenarios:

  1. When I enter the sudo /usr/bin/mysql_secure_installation command nd am prompted to enter the root password

or

2.When I enter the mysql -u root -p command and am prompted for the password.

Please I really need help on this - I have lost count of the number of times I have had to uninstall and reinstall either MySQL or MariaDb because this pain in neck erf an error.

  • Hi Jobin, I have tried the first command:mysqladmin -u root -pcurrentpassword password 'newpassword' but the system just hangs.Is there a limit to the number of characters we can use as a password coz mine is over 100 characters long.I didnt have any issues when setting the password so I dont see why that should be an issue now.Thanks. – chetwynd2014 Apr 16 '14 at 7:48
  • 1
    I think I may have figured out the issue - It has to do with the long password.I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.So glad I figured this out after so much pain - surprising really that this has not been identified as a bug considering how many years MySQL has been in production. – chetwynd2014 Apr 16 '14 at 9:01
  • 1
    This is really strange coz I havnt found any definite documentation or answers on the maximum MySQL password length - not hash mind you.This is crazy coz the longer the password the harder it will be to crack. – chetwynd2014 Apr 16 '14 at 9:09
  • Have you actually enabled the root account? You're not giving your regular user's password are you? Also, I assume you know that root's password and root's mysql password are not the same right? – terdon Apr 16 '14 at 10:11
  • Hello @terdon and to answer your questions, the root account is enabled and I am not giving anyone any passwords for now.I also do know the difference between the Linux Server root password and MySQLs root password.I have answered the question I asked - please look at my below after a painful and lengthy process.I do hope that this helps anyone out there who faces a similiar issue.Cheers. – chetwynd2014 Apr 16 '14 at 20:58
8

Check the file: /etc/mysql/mariadb.conf.d/mysqld.cnf

verify if this line exists:

plugin-load-add = auth_socket.so

then go to MariaDB:

sudo mysql -u root

and make these modifications:

MariaDB [(none)]>use mysql;
MariaDB [(none)]>update user set plugin=' ' where User='root';
MariaDB [(none)]>flush privileges;
MariaDB [(none)]>exit

I didn't have access to mysqlworkbench, but after I applied this solution, everything ruan well...

  • 1
    MAN ! Fabby and David please take a moment to come back here and shed some light on whath the "plugin" thing is here for MariaDb, this solution worked for me. My issue started on an abnormal system shutdown and mariadb stopped allowing access for root. – Aukhan Jul 16 '15 at 18:12
  • Thanks David! I was able to set a root password for MariaDB by running sudo /usr/bin/mysql_secure_installation but unable to get in anywhere else (CLI or MySQL Workbench) until I followed these steps. What role does this plugin-load-add = auth_socket.so play in resolving this issue? – Conor Aug 20 '15 at 19:32
  • I wonder why does it expect default user to be of root privilege – Faizan Akram Dar Dec 3 '16 at 10:36
  • still not solved by mariadb devs a year later. your fix works though! – xcvbn Nov 13 '17 at 17:52
4

I think I may have figured out the issue - It has to do with the long password.I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.

So glad I figured this out after so much pain - surprising really that this has not been identified as a bug considering how many years MySQL has been in production.

So basically one resolution is to go through the following steps to resolve the above issue:

  1. Start with a blank password on a non-production server and is if it works

  2. If you are successful with step 1 then proceed with securing your mysql installation by typing the follwing command: sudo /usr/bin/mysql_secure_installation and try entering a more complex password - comination of lowercase letters, numbers, symbols and uppercase letters of at least 8 characters should be fine.

  3. I recommend starting with 8 and move upwards till you reach a ceiling with the number of characters you can enter.

The above should be a step in the right direction to getting your MySQL server secured.

  • 1
    I was having a similar issue, but seems like it was caused by special characters instead of length. I tried quoting the password as suggested here (superuser.com/questions/123928/…), but that didn't solve it. I eventually just used a special-character-free password. – Tayler Jun 27 '14 at 6:15
  • After installing MariaDB on Windows v10.2.12 I got this error, ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) After changing the password I got the same error, even after reinstallation. But I tried your finding, change the password to a shorter one and now it's working fine! Thank you! – julian-alarcon Jan 19 '18 at 20:12
0

Try this command in terminal:

mysqladmin -u root -pcurrentpassword password 'newpassword'

Then, type:

mysql -u root -pnewpassword
0

The best answer is here

It explains what's happening. It explains why you shouldn't just disable the plugin. It explains how to get around it (login using sudo, create a new superuser account that's not root).

Note that this solution by david-leonardo doesn't survive a reboot.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.