Cannot login to MySQL database after fresh install with root ID and empty/no password like other older MySQL versions do
问题:
回答1:
After you installed MySQL-community-server 5.7 from fresh on linux, you will need to find the temporary password from /var/log/mysqld.log to login as root.
grep 'temporary password' /var/log/mysqld.log
- Run
mysql_secure_installation
to change new password
ref: http://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
回答2:
There's so many answers out there saying to reinstall mysql or use some combo of
mysqld_safe --skip-grant-tables
and / or
UPDATE mysql.user SET Password=PASSWORD('password')
and / or something else ...
... None of it was working for me
Here's what worked for me, on Ubuntu 18.04, from the top
With special credit to this answer for digging me out of the frustration on this ...
$ sudo apt install mysql-server
$ sudo cat /etc/mysql/debian.cnf
Note the lines which read:
user = debian-sys-maint
password = blahblahblah
Then:
$ mysql -u debian-sys-maint -p
Enter password: // type 'blahblahblah', ie. password from debian.cnf
mysql> USE mysql
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | plugin |
+------------------+-----------+-----------------------+
| root | localhost | auth_socket |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> COMMIT; // When you don't have auto-commit switched on
Either:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Or:
// For MySQL 5.7+
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') where user='root';
Then:
mysql> FLUSH PRIVILEGES;
mysql> COMMIT; // When you don't have auto-commit switched on
mysql> EXIT
$ sudo service mysql restart
$ mysql -u root -p
Enter password: // Yay! 'new_password' now works!
回答3:
In case you want to install mysql or percona unattended (like in my case ansible), you can use following script:
# first part opens mysql log
# second part greps lines with temporary password
# third part picks last line (most recent one)
# last part removes all the line except the password
# the result goes into password variable
password=$(cat /var/log/mysqld.log | grep "A temporary password is generated for" | tail -1 | sed -n 's/.*root@localhost: //p')
# setting new password, you can use $1 and run this script as a file and pass the argument through the script
newPassword="wh@teverYouLikE"
# resetting temporary password
mysql -uroot -p$password -Bse "ALTER USER 'root'@'localhost' IDENTIFIED BY '$newPassword';"
回答4:
MySQL 5.7 changed the secure model: now MySQL root login requires a sudo
The simplest (and safest) solution will be create a new user and grant required privileges.
1. Connect to mysql
sudo mysql --user=root mysql
2. Create a user for phpMyAdmin
CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Reference - https://askubuntu.com/questions/763336/cannot-enter-phpmyadmin-as-root-mysql-5-7
回答5:
Mysql generates a default temporary password as it is installed so to use mysql firstly you would be required to get that password from the log file which is present at the /var/log/mysqld.log. So follow the following process -
grep 'temporary password' /var/log/mysqld.log
mysql_secure_installation - This is required to change the password for mysql and also to make certain other changes like removing temporary databases , allow or disallow remote access to root user , delete Anonymous users etc.
回答6:
I just installed Linux Mint 19 (based on Ubuntu 18.04) on my machine. I installed MySQL 5.7 from the repo (sudo apt install mysql-server) and surprisingly during installation, the setup didn't prompt to enter root password. As a result I wasn't able to login into MySQL. I googled here and there and tried various answers I found on the net, including the accepted answer above. I uninstalled (purging all dpkgs with mysql in its name) and reinstalled again from the default Linux Mint repositories. NONE works.
After hours of unproductive works, I decided to reinstall MySQL from the official page. I opened MySQL download page (https://dev.mysql.com/downloads/repo/apt) for apt repo and clicked Download button at the bottom right.
Next, run it with dpkg:
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
At the installation setup, choose the MySQL version that you'd like to install. The default option is 8.0 but I changed it to 5.7. Click OK to quit. After this, you have a new MySQL repo in your Software Sources.
Update your repo:
sudo apt update
Finally, install MySQL:
sudo apt install mysql-server
And now I was prompted to provide root password! Hope it helps for others with this same experience.
回答7:
I to was experiencing the same problem and the only thing I was able to do to make it work was to go this route:
drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'
This allowed me to recreate my username and enter a password for the user name
回答8:
In my case the data directory was automatically initialized with the --initialize-insecure
option. So /var/log/mysql/error.log
does not contain a temporary password but:
[Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
What worked was:
shell> mysql -u root --skip-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Details: MySQL 5.7 Reference Manual > 2.10.4 Securing the Initial MySQL Account
回答9:
None of these answers worked for me on Ubuntu Server 18.04.1 and MySQL 5.7.23. I spent a bunch of time trying and failing at setting the password and auth plugin manually, finding the password in logs (it's not there), etc.
The solution is actually super easy:
sudo mysql_secure_installation
It's really important to do this with sudo
. If you try without elevation, you'll be asked for the root password, which you obviously don't have.
回答10:
It seems things were designed to avoid developers to se the root user, a better solution would be:
sudo mysql -u root
Then create a normal user, set a password, then use that user to work.
create user 'user'@'localhost' identified by 'user1234';
grant all on your_database.* to 'user'@'localhost';
select host, user from mysql.user;
Then try to access:
mysql -u user -p
Boom!