neal wang
asked on
How can I login as root for mysql8?
I'm on centos7, mysql8
Mysocial engine complaining that it can't login to the mysql database using the root login
I'm trying to login
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I of course went through the mysql8 documentation https://dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html
I also asked this question before but I tried the steps and it didn't work, I think it fails upon restarting my machine... how do I get a permanent solution to this?
https://www.tecmint.com/reset-root-password-in-mysql-8/ is what helped me last time.. but it is not working
Mysocial engine complaining that it can't login to the mysql database using the root login
I'm trying to login
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I of course went through the mysql8 documentation https://dev.mysql.com/doc/refman/8.0/en/changing-mysql-user.html
I also asked this question before but I tried the steps and it didn't work, I think it fails upon restarting my machine... how do I get a permanent solution to this?
https://www.tecmint.com/reset-root-password-in-mysql-8/ is what helped me last time.. but it is not working
If you must reset the root password https://robbinespu.github.io/eng/2018/03/29/Reset_mariadb_root_password.html provides details showing how to reset the root password for MariaDB + MySQL.
ASKER
[1] 72942
[# -bash: mysqld_safe: command not found
[1]+ Exit 127 mysqld_safe --skip-grant-tables
[# -bash: mysqld_safe: command not found
[1]+ Exit 127 mysqld_safe --skip-grant-tables
ASKER
the worst thing is, I'm on the documentation page.. the command should be working
https://dev.mysql.com/doc/refman/8.0/en/mysqld-safe.html
https://dev.mysql.com/doc/refman/8.0/en/mysqld-safe.html
First start at the beginning.
State whether the file ~root/.my.cnf exists or is missing.
State whether the file ~root/.my.cnf exists or is missing.
ASKER
yes it exists,
its located
/etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plu gin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
#sysvar_default_authentica tion_plugi n
#default-authentication-pl ugin=mysql _native_pa ssword
default_authentication_plu gin= mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq l.sock
log-error=/var/log/mysqld. log
pid-file=/var/run/mysqld/m ysqld.pid
its located
/etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plu
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
#sysvar_default_authentica
#default-authentication-pl
default_authentication_plu
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
ASKER
I rebooted my system and now, I'm not able to start up mysql service
systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@dxpdemo ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/m ysqld.serv ice; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2020-01-24 14:44:16 UTC; 9s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5562 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 5533 ExecStartPre=/usr/bin/mysq ld_pre_sys temd (code=exited, status=0/SUCCESS)
Main PID: 5562 (code=exited, status=1/FAILURE)
Status: "Server startup in progress"
Error: 13 (Permission denied)
Jan 24 14:44:15 dxpdemo systemd[1]: Starting MySQL Server...
Jan 24 14:44:16 dxpdemo systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jan 24 14:44:16 dxpdemo systemd[1]: Failed to start MySQL Server.
Jan 24 14:44:16 dxpdemo systemd[1]: Unit mysqld.service entered failed state.
Jan 24 14:44:16 dxpdemo systemd[1]: mysqld.service failed.
systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@dxpdemo ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/m
Active: failed (Result: exit-code) since Fri 2020-01-24 14:44:16 UTC; 9s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5562 ExecStart=/usr/sbin/mysqld
Process: 5533 ExecStartPre=/usr/bin/mysq
Main PID: 5562 (code=exited, status=1/FAILURE)
Status: "Server startup in progress"
Error: 13 (Permission denied)
Jan 24 14:44:15 dxpdemo systemd[1]: Starting MySQL Server...
Jan 24 14:44:16 dxpdemo systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jan 24 14:44:16 dxpdemo systemd[1]: Failed to start MySQL Server.
Jan 24 14:44:16 dxpdemo systemd[1]: Unit mysqld.service entered failed state.
Jan 24 14:44:16 dxpdemo systemd[1]: mysqld.service failed.
ASKER
should I just reinstall mysql at this point
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Normally CentOS package installation saves the root user/pass into the ~root/.my.cnf file for future use.
So you must prepend all your mysql commands with a pointer to this file, as in...
Open in new window
As with all root level commands + permissions, you must be root to use a root level login.
If this fails, you can run with --skip-grants or reset the root password.
Careful: If you reset the root password + some other code has cached the root password, then database related code may become unstable.