Below is my running mysql instance:
sudo systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-09-22 16:49:09 UTC; 9s ago
Process: 229030 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 228946 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Process: 228921 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 228984 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 24879)
Memory: 390.6M
CGroup: /system.slice/mysqld.service
└─228984 /usr/libexec/mysqld --basedir=/usr
Sep 22 16:49:08 SGGS systemd[1]: Starting MySQL 8.0 database server...
Sep 22 16:49:09 SGGS systemd[1]: Started MySQL 8.0 database server.
Below are mysql packages that are installed.
$ yum list installed | grep mysql
mysql.x86_64 8.0.26-1.module_el8.4.0+915+de215114 @appstream
mysql-common.x86_64 8.0.26-1.module_el8.4.0+915+de215114 @appstream
mysql-errmsg.x86_64 8.0.26-1.module_el8.4.0+915+de215114 @appstream
mysql-server.x86_64 8.0.26-1.module_el8.4.0+915+de215114 @appstream
Below are the configuration files:
$ cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
and
$ ls -ltr /etc/my.cnf.d
total 12
-rw-r--r--. 1 root root 295 Dec 17 2020 client.cnf
-rw-r--r--. 1 root root 565 Sep 1 2021 mysql-default-authentication-plugin.cnf
-rw-r--r--. 1 root root 612 Sep 1 2021 mysql-server.cnf
$ cat /etc/my.cnf.d/*
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#
[client]
# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
#
# MySQL 8.0.4 introduced 'caching_sha2_password' as its default authentication plugin.
# It is faster and provides better security then the previous default authentication plugin.
#
# Until now (09/2018), it does not work with some other software (eg. MariaDB client, MariaDB connectors, ...)
#
# This configuration file changes MySQL default server configuration, so it behaves the same way as in MySQL 5.7.
#
# To change the behaviour back to the upstream default, comment out the following lines:
[mysqld]
default_authentication_plugin=mysql_native_password
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
Right after setup I wrote and ran the below script to create user and Database:
$ cat mysql.create
PASSWDDB="NGPASS@12"
MAINDB="NGSGGS_DB"
mysql -P 3306 -e "CREATE DATABASE ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;"
mysql -P 3306 -e "CREATE USER ${MAINDB}@localhost IDENTIFIED BY '${PASSWDDB}';"
mysql -P 3306 -e "GRANT ALL PRIVILEGES ON ${MAINDB}.* TO '${MAINDB}'@'localhost';"
mysql -P 3306 -e "FLUSH PRIVILEGES;"
However, running the script got me the below error:
$ ./mysql.create
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Note: This is a plain fresh installation and I have not made any significant changes to the database.
These are the only logging after the first start:
$ vi /var/log/mysql/mysqld.log
2022-09-23T05:13:05.359946Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.26) starting as process 243792
2022-09-23T05:13:05.370217Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-09-23T05:13:05.561488Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-09-23T05:13:05.748196Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-09-23T05:13:05.748300Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-09-23T05:13:05.749569Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-09-23T05:13:05.749714Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-09-23T05:13:05.766115Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2022-09-23T05:13:05.766181Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.26' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution.
Tried the below as well and I get the same error:
$ sudo mysql -u root -p'p@sSw0rd' -e "CREATE DATABASE ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I also tried resetting the password but same error:
$ mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)
Can you please suggest?
The issue was due to yum remove mysql-server was not clearing all files and I had to manually delete the folder rm -rf /var/lib/mysql after which a fresh installation helped me log in without password.