cannot reset root password
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
hyh, better create second user
killall mysqld mysqld_safe killall -9 mysqld mysqld_safe mkdir -p /var/run/mysqld chown mysql:mysql /var/run/mysqld mysqld_safe --skip-grant-tables & CREATE USER 'poweruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MYPASSSS'; GRANT ALL PRIVILEGES ON *.* TO 'poweruser'@'localhost' WITH GRANT OPTION;
or better way, just reset whole database structure using this method
https://knowledge.broadcom.com/external/article/209019/mysql-8-cannot-startup-after-remove-ib-f.html
copy:
If the id* files are removed, and there is no snapshot/backup, then re-init mysql would be needed,
0. take snapshot before any change
1. if already mysqldump all databases, jump to step 2
mkdir /tmp/mysql
vi /etc/my.cnf (delete following variables if exist: innodb_file_per_table, innodb_force_recovery)mysqld --console --user=mysql --initialize --datadir=/tmp/mysql
cp -p /tmp/mysql/ib* /var/lib/mysql
vi /etc/my.cnf
add one line: innodb_force_recovery=6
systemctl start mysql
mysqldump --all-databases > alldb.sql2. initialize mysql
rm -Rf /var/lib/mysql/*
vi /etc/my.cnf (delete variables if exist: innodb_file_per_table, innodb_force_recovery)
mysqld --console --user=mysql --initialize --datadir=/var/lib/mysql
(the output will show temp password, for example, "A temporary password is generated for root@localhost: MWq.j0>RMdV>")
systemctl start mysqlmysql -u root -p
(login with the temp password, and set password for root -- replace '7layer' with the default password you currently have in ~/.my.cnf)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '7layer';
mysql> exit;3. restore data
mysql < alldb.sql(replace the sql file name if needed)
4. restore user privileges
mysql> flush privileges;