Forum

mysql 8 - Operation...
 
Notifications
Clear all

mysql 8 - Operation ALTER USER failed for 'root'@'localhost'

2 Posty
1 Users
0 Reactions
3,019 Widok
0
Topic starter

cannot reset root password

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

2 Answers
0
Topic starter

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;

 

 

0
Topic starter

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.sql

2. 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 mysql

mysql -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;

Odpowiedź

Author Name

Author Email

Your question *

 
Preview 0 Revisions Saved
Share: