mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

rtwingfield

rtwingfield

This error is making me crazy:

Code: 
# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I'm trying to configure a fresh install of mysql-5.7.10. I cannot access the server to explore the database table contents, etc. Seems like a Catch22. I've copied the /usr/local/etc/my.cnf from an adjacent working server in the rack, but same failure. Apparently the fresh installed version "thinks" that the root user has a password . . .but I have no idea what that might be. I've tried toggling the
Code: 
password  = spelling
to
Code: 
password  = ''
. . .but to no avail.

Any suggestions?
 
New Install?

I find it easier to just reset the root password.

Here are the steps:

Stop the server
# service mysql-server stop

Restart it with mysqld --skip-grant-tables
# mysqld --skip-grant-tables &

Login as root no password required
# mysql -u root

mysql> use mysql;

I then like to see the users I am going to be dealing with so...

mysql> select user,host from user where user='root';


Code: 
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)

So, now I reset the password...

mysql> update user set authentication_string = password('<newPassword>) where user='root';
mysql> flush privileges;
mysql> exit

Now you can either reboot, or manually kill the mysqld processes and restart the server.

Your root password should now work.
 
Allan said:
Restart it with mysqld --skip-grant-tables
# mysqld --skip-grant-tables &
Click to expand...
. . .well, that blows up,
Code: 
 # /usr/local/libexec/mysqld --skip-grant-tables &
[1] 13910
root@bravo:/usr/ports/databases/mysql57-server # 2016-02-21T21:03:32.395377Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-21T21:03:32.395565Z 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.
2016-02-21T21:03:32.395608Z 0 [Note] /usr/local/libexec/mysqld (mysqld 5.7.10) starting as process 13910 ...
2016-02-21T21:03:32.399965Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2016-02-21T21:03:32.400004Z 0 [ERROR] Aborting

2016-02-21T21:03:32.400019Z 0 [Note] Binlog end
2016-02-21T21:03:32.400068Z 0 [Note] /usr/local/libexec/mysqld: Shutdown complete
. . .but never-the-less, skip-grant-tables . . .is specified in the /usr/local/etc/my.cnf.

Attempting to use the command, # mysql -u root, fails:
Code: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
This is what I'm calling a Catch22 -- I can't get to the MySQL prompt, i.e., " mysql >", so back to service mysql-server start(server does start) . . .the but same subsequent failure scenarios persist.
 
Hi, having the same problem here. I ran the command mysqld_safe --skip-grant-tables;, which seemed to do nothing, and I couldn't use the console anymore. I ran mysqld_safe --skip-grant-tables &, which ran the mysql process fine, but I am still getting the same error when I run mysql -u root. Any suggestions?
 
The --skip-grant-tables skips the grant tables (who would have guessed that?). By skipping those tables you are effectively bypassing any and all authentication allowing anyone to login without a password.
 
Good morning everyone. I am still struggling with this configuration, and retired around 5:30 AM this morning for about four hours of well needed sleep. I've invested several hours reading the MySQL 5.7 Reference Manual (not the FreeBSD Handbook or MAN pages) regarding Server Configuration Defaults, . . . 14.3.1 InnoDB Initialization and Startup Configuration, and other sections, etc. Quite a lot to learn . . .coming from v5.6. I've actually de/re installed several times and have some nuances to share, and I'll come back and edit this post after I've had a first cup of coffee ;)

Please standby.

jardows said:
Hi, having the same problem here. I ran the command mysqld_safe --skip-grant-tables;, which seemed to do nothing, and I couldn't use the console anymore. I ran mysqld_safe --skip-grant-tables &, which ran the mysql process fine, but I am still getting the same error when I run mysql -u root. Any suggestions?
Click to expand...

. . .and yes, permanent --skip-grant-tables is not a desirable "solution", but to date, has not worked for me either.
SirDice said:
The --skip-grant-tables skips the grant tables (who would have guessed that?). By skipping those tables you are effectively bypassing any and all authentication allowing anyone to login without a password.
Click to expand...


*** EDIT ***

Apologies for the delay. I've been at this all day, but I have resolved many problems. Getting the InnoDB engine to work has become a real "bear". I have discovered several breaks in the install scripts, the */my.cnf, /etc/rc.conf and the rc.d/mysql-server bash script. Short note . . .don't depend of the default "configurations"; plain vanilla installation is probably not going to work. I've got several pages of documented notes that I need to review and edit. I'll probably post in an uploaded PDF.
 
Since MySQL 5.7 a temporary password is created for the root@localhost account. This can be seen when one runs `mysqld --initialize` as per MySQL manual, with the flags from the rc script:

sudo /usr/local/libexec/mysqld --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --initialize
Output:
Code: 
 100
 100 200
 100 200
2016-08-23T09:40:17.766421Z 0 [Warning] InnoDB: New log files created, LSN=45790
 100
2016-08-23T09:40:18.203159Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
mysqld: Error on delete of './auto.cnf' (Errcode: 2 - No such file or directory)
2016-08-23T09:40:18.484358Z 0 [Warning] World-writable config file './auto.cnf' has been removed.

2016-08-23T09:40:18.484430Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9a4557d3-6915-11e6-9568-001f29048cd2.
2016-08-23T09:40:18.493060Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-08-23T09:40:18.774004Z 0 [Warning] CA certificate ca.pem is self signed.
2016-08-23T09:40:18.925572Z 1 [Note] A temporary password is generated for root@localhost: ,BsPikk5has.

The rc script a) uses the deprecated mysql_install_db and b) doesn't echo the temporary password, but suppresses all output. The following patch fixes the issue:
Code: 
diff -ur /usr/local/etc/rc.d.orig/mysql-server /usr/local/etc/rc.d/mysql-server
--- /usr/local/etc/rc.d.orig/mysql-server  2016-08-22 10:06:21.000000000 +0000
+++ /usr/local/etc/rc.d/mysql-server  2016-08-23 16:17:54.364601968 +0000
@@ -52,12 +52,17 @@
 procname="/usr/local/libexec/mysqld"
 start_precmd="${name}_prestart"
 start_postcmd="${name}_poststart"
-mysql_install_db="/usr/local/bin/mysql_install_db"
-mysql_install_db_args="--defaults-extra-file=${mysql_optfile} --basedir=/usr/local --datadir=${mysql_dbdir} --mysqld-file=${procname} --user=${mysql_user}"
+mysql_install_db="${procname}"
+# NOTE: --initialize MUST be at the end or other options will be
+# "unknown variables" and initialization fails.
+# This is why it's set hardcoded in mysql_create_auth_tables rather then the
+# options variable here.
+mysql_install_db_args="--defaults-extra-file=${mysql_optfile} --basedir=/usr/local --datadir=${mysql_dbdir} --user=${mysql_user}"

 mysql_create_auth_tables()
 {
-  eval $mysql_install_db $mysql_install_db_args >/dev/null 2>/dev/null
+  $mysql_install_db $mysql_install_db_args --initialize 2>&1 | \
+  /usr/bin/sed -ne '/A temporary password is generated/ s/^.*Note] //p'
 }

 mysql_prestart()
 
You must log in or register to reply here.
Back
Top