How to set the MySQL root password in the Debian 9 (or MySQL 5.7+, MariaDB 10.1+)

You can think that there is no problem to set the root password in installation time. It’s true until 5.7 version of MySQL (or MariaDB 10.1). Now you can be authorized under root (and without password), but only through the auth_socket. For example, authorization under root in mysql-client:

mysql -u root

It means that the phpMyAdmin or other scripts will not be able to use root access more.

Now the next methods do not work:

mysqladmin -u root password
SET PASSWORD FOR root@localhost=PASSWORD, UPDATE user SET Password=PASSWORD

What can we make to use root privileges as earlier? We can create new user with root privileges and use it. Let’s do that.

First step:

mysql -u root

Create new a user with root privileges:

CREATE USER 'root2'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root2'@'localhost';
FLUSH PRIVILEGES;

Where root2 and PASSWORD change to what you want.

Unfortunately, the user created by such method will not be able to give privileges to other users through the same phpMyAdmin. Therefore the following command can be useful to you for giving privileges to users through mysql-client:

GRANT ALL PRIVILEGES ON `DB_NAME`.* TO 'DB_USER'@'localhost' WITH GRANT OPTION;

Where DB_NAME and DB_USER change to what you want again.

 44   1 mon  
Popular