How to reset your root MySQL password

Stop the MySQL process
# service mysqld stop

Once MySQL has stopped, restart it with the --skip-grant-tables option
# mysqld_safe --skip-grant-tables &
or edit your /etc/my.cnf file to add the line
skip-grant-tables

Connect to MySQL using the root user.
mysql -u root

Once logged in, you should see the following prompt:
mysql>
Enter the following commands:
mysql> use mysql;
mysql> UPDATE user SET password=PASSWORD("YOUR NEW PASSWORD HERE") WHERE User='root';
mysql> flush privileges;
mysql> quit

Now stop MySQL again:
# service mysqld stop

If you edited your /etc/my.cnf file, delelete the skip-grant-tables line.

Now restart MySQL and test your new login.
# service mysqld restart
# mysql -u root -p

MariaDB [(none)]> CREATE USER xxxx;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER xxxx@hostname ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON dbane.* To ‘xxxxx@hostname’ IDENTIFIED BY ‘P@ssw0rd5768#’;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON db.* To ‘usernam’@’localhost’ IDENTIFIED BY ‘P@ssw0rd5768#’;
GRANT USAGE ON *.* TO ‘usernam’@’localhost’ IDENTIFIED BY ‘P@ssw0rd5768#’;

Reset root password

UPDATE user SET authentication_string=password(‘password’) WHERE user=’root’;

update mysql.user set password_expired = ‘N’, authentication_string=PASSWORD(‘password’) where user = ‘root’;

GRANT USAGE ON *.* TO ‘root’@’localhost’ IDENTIFIED BY ‘password’;