Reset/Set the MySQL Root Password

refer here
refer here
how-to-change-mysql-root-password-on-linux

Reset the MySQL Root Password

If you forget your root MySQL password, it can be reset.

Stop the current MySQL server instance, then restart it with an option to not ask for a password.

1.sudo systemctl stop mysqld
2.sudo mysqld_safe --skip-grant-tables &

Reconnect to the MySQL server with the MySQL root account.
1.mysql -u root
Use the following commands to reset root’s password. Replace password with a strong password.

1.use mysql;
2.update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
3.flush privileges;
4.exit

Then restart MySQL.

1.sudo systemctl start mysqld

How to Reset MySQL or MariaDB Root Password

Follow these steps to reset your MySQL/MariaDB root password:

Stop the MySQL/MariaDB service

To change the root password first, you need to stop the MySQL server. To do so type the following command:

$ sudo systemctl stop mysql

Start the MySQL/MariaDB server without loading the grant tables

Start the database server without loading the grant tables:

$ sudo mysqld_safe --skip-grant-tables &

The ampersand & at the end of the command above will cause the program to run in the background , so you can continue to use the shell.

When the --skip-grant-tables option is used, anyone can to connect to the database server without a password and with all privileges granted.

Log in to the MySQL shell

Now you can connect to the database server as the root user:

$ mysql -u root

Set a new root password

Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;

Step 1: First set MYSQL environment variable in Ubuntu
Step 2:login using username and password
In root vi .bashsrc

export PATH=$PATH:/opt/lampp/bin

====================or===================

other than root user

ALTER USER 'root'@'localhost' IDENTIFIED BY 'pws?~,F(?Fkdfgbdfb';
FLUSH PRIVILEGES;

====================OR=======================

ALTER USER 'wizbrandos'@'localhost' IDENTIFIED BY 'myphpmyadminpassword';
FLUSH PRIVILEGES;

Set db name,username and pwd

If ALTER USER statement doesn’t work for you, try to modify the user table directly:

UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Set db name,username and pwd

Run the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;

In both cases if all goes well, you should see the following output:

5. Stop and Start the database server normally

Now that the root password is set, stop the database server and start it normally:

mysqladmin -u root -p shutdown

You will be prompted to enter the new root password.

Start the database server normally:

For MySQL, type:

sudo systemctl start mysql

For MariaDB, type:

sudo systemctl start mariadb

6. Verify the password

To verify that the new root password has been applied correctly, type:

mysql -u root -p

Step 1: First set MYSQL environment variable in Ubuntu
Step 2:login using username and password
In root vi .bashsrc

export PATH=$PATH:/opt/lampp/bin

====================or===================

other than root user

ALTER USER 'root'@'localhost' IDENTIFIED BY 'pws?~,F(?Fkdfgbdfb';
FLUSH PRIVILEGES;

====================OR=======================

ALTER USER 'wizbrandos'@'localhost' IDENTIFIED BY 'myphpmyadminpassword';
FLUSH PRIVILEGES;

You will be prompted to enter the new root password. Enter it, and you should be logged in to your database server.

If the ALTER USER statement fails to reset the password, try repeating the procedure using the following statements to modify the user table directly:

UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;