How to set MySQL root password
SET MYSQL ROOT PASSWORD
Overview: This article provides an overview of setting, resetting, and recovering the MySQL root password across different versions, including MySQL 8.0, MySQL 5.7, and MariaDB, along with troubleshooting steps for password-related errors.
What is Mysql default Password?
The default user for MySQL is the root; by default, it has no password. If you set a password for MySQL and you can’t recall it, you can always reset it and choose another one.
For mysql 8.0 version
1. How to set Mysql root Password?
Login to MySQL > Assign a password using Alter command > Flush the privileges to reload the grant tables in the database.
root@ubuntu ~]# mysql -u root
mysql> Alter user 'root'@'localhost' identified WITH mysql_native_password by 'KAvghytfJA{3ab';
mysql> flush privileges;
mysql> exit;
2. How can you reset or recover your MySQL password if you've forgotten it?
Step 2(a) Stop Mysql Service.
root@ubuntu ~]# systemctl stop mysql
Step 2(b) Ensure mysqld dir exists and set the owner.
root@ubuntu ~]# mkdir /var/run/mysqld
root@ubuntu ~]# chown mysql /var/run/mysqld
Step 2(c) Start MySQL with --skip grant-tables& option
root@ubuntu ~]# mysqld_safe --skip-grant-tables &
Note:
When the --skip-grant-tables option is used, anyone can connect to the database server without a password and with all privileges granted.
Step 2(d). Login without a Password.
root@ubuntu ~]# mysql -u root
Step 2(e) Reset the Root Password
mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
mysql> flush privileges;
Step 2(f) After Resetting make sure all MYSQL processes are stopped before starting the service again.
root@ubuntu ~]# killall -u mysql
Step 2(g) Start Mysql service
root@ubuntu ~]# systemctl start mysql
ERROR: After mysql/MariaDB installation on the Linux server, followed by the launch of mysql_secure_installation script, we may encounter this error:
For MySQL 5.7.6 and newer versions / For MariaDB 10.1.20 and newer versions, use the following command.
Step1. Log in to MySQL and run the below command to set/change a password for the root user.
- Follow the steps to set MySQL root password in Ubuntu/RHEL/Debian/Cent OS (MYSQL):
root@ubuntu ~]# mysql
mysql> Alter user 'root'@'localhost' identified by 'KAvghytfJA{3ab';
mysql> flush privileges;
mysql> exit;
mysql Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
For MySQL 5.7.4 and older versions / MariaDB 10.1.20 and older versions, use the following command:
- Follow the steps to set MySQL root password in Ubuntu/RHEL/Debian/Cent OS (MariaDB):
MariaDB [(none)]>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('KAvghytfJA{3ab');
MariaDB [(none)]>flush privileges;
MariaDB [(none)]> exit;
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
Step 2. Restart the MYSQL server and continue installing Ezeelogin
root@ubuntu ~]# systemctl restart mysql
Related Articles:
ERROR creating DB user in MySQL 8.0
How can i disable MySQL strict mode ?
ERROR granting access for DB user: Access denied for user 'root'@'%' to database during upgrade
Basic MySQL commands for troubleshooting database related issues in Ezeelogin