![]() ![]() Login to MySQL as root: mysql -u root mysql.Start the mysqld configuration: sudo mysqld -skip-grant-tables &.( In some cases, if /var/run/mysqld doesn't exist, you have to create it at first: sudo mkdir -v /var/run/mysqld & sudo chown mysql /var/run/mysqld.Stop the MySQL Server: sudo /etc/init.d/mysql stop. ![]() Enter the following lines in your terminal. In my experience, the issue only happens with the root user, as other users will be added manually not part of an initial install/update.Set / change / reset the MySQL root password on Ubuntu Linux. Otherwise: ALTER USER IDENTIFIED WITH mysql_native_password BY 'NEWPASSWORD' įor the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant statement exactly right - easier to simply update the root you already have. So this is an alternative.) UPDATE er SET plugin = '', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root' (It's possible that setting the plugin to empty would work. UPDATE er SET plugin = 'mysql_native_password', Password = PASSWORD('NEWPASSWORD') WHERE User = 'root' On MariaDB (<10.2, see comments below) which is on the Ubuntu version 16 as of 2017 this should suffice. So the solution is to update the Users to not use the auth_socket/unix_socket and properly set a password. This is why you can log in with root but not with a different user. These sockets ignore passwords and allow the corresponding Unix user in without a password check. You'll note auth_socket (which may read unix_socket on MariaDB). So what's happening? Well, if you log in as root and do the following: select User,host,plugin from er (You may even be able to log in from the unix root by simply # mysql as the password is irrelevant and the user is defined). ![]() Then enter either no password or the wrong password. You can test this as follows by switching to system root, and then type: mysql -uroot -p The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user. Where you replace NEWPASSWORD with the password you want, and everything else verbatim. Password = PASSWORD('NEWPASSWORD') WHERE User = 'root' In short, on MariaDB UPDATE er SET plugin = 'mysql_native_password', UPDATE er SET plugin = 'mysql_native_password', Password = PASSWORD('secret') WHERE User = 'root' ![]() to change the password too (credits goes to Pothi Kalimuthu) Just for the record, (and MariaDB < 10.2 users) there is also another way to only change the plugin without providing a password (leaving it empty): update er set plugin = 'mysql_native_password' where User='root' Of course you can also use the command above to set an empty password. So in order to to change the plugin back to mysql_native_password:Ĭhange the plugin and set a password with a single command: ALTER USER IDENTIFIED WITH mysql_native_password BY 'test' Taken from Change User Password in MySQL 5.7 With "plugin: auth_socket" Using a UNIX socket and then compares the username. That plugin doesn’t care andĭoesn’t need a password. If you install 5.7 and don’t provide a password to the root user, it Both solutions work equally as well for local development. Give permissions to your user (don't forget to flush privileges) mysql> GRANT ALL PRIVILEGES ON *.* TO WITH GRANT OPTION Įxit MySQL and try to reconnect without sudo from your local machine.Įdit: a previous version of the answer post recommended creating a user However, it is more secure to create so connections can only be made from localhost, and not remotely. Recreate your user mysql> CREATE USER IDENTIFIED BY '' I recently upgrade my Ubuntu 15.04 to 16.04 and this has worked for me:įirst, connect in sudo mysql sudo mysql -u rootĬheck your accounts present in your db SELECT User,Host FROM er ĭelete current account mysql> DROP USER OK, 0 rows affected (0,00 sec) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |