How to create super user in MariaDB MySQL-CodePudding

I have MyCloudMirror NAS box and I accidentally delete root user from MySQL now I can't login to mysql.

I have uninstall and reinstall the phpmyadmin app but that did not make any different

I also try following but nothing is working

mysqld_safe --skip-grant-tables &

below command show that all permission are assign

CREATE USER 'root'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

this doesnt assign all permission the trigger are uncheck

CREATE USER 'root'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

If I try to check triger from myphpadmin and click on apply i get error message

GRANT ALL PRIVILEGES ON . TO 'root'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; MySQL said: Documentation

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

CodePudding user response：

GRANT ALL PRIVILEGES is not enough.

On MySQL 8 you must execute the next statements:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, 
      FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
      CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
      REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, 
      CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE 
ON *.* TO `root`@`localhost` WITH GRANT OPTION;

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,
      AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,
      CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,
      FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,
      INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,
      PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,
      RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,
      SERVICE_CONNECTION_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,
      SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN 
ON *.* TO `root`@`localhost` WITH GRANT OPTION;

GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION;

FLUSH PRIVILEGES;

CodePudding user response：

This is how I fixed the issue

Run /usr/bin/mysql_reset.py --reset

The above command reset all settings on DB