A root account is a superuser account that offers a wide array of privileges throughout the databases of MySQL. By default, the initial password for the root account is ‘empty/blank,’ thus allowing access to the MySQL server as root to anyone. Show
Note: Empty/blank password means there is no password; hence anyone can log in as long as MySQL privileges have been granted. To log in as a root user in Linux:
If the password is not set up or you forgot or need to change your MySQL password, below is a guide on how to do set up/recover the password. How to set up, change, and recover a MySQL root passwordThere is a high possibility that you already have MySQL running somewhere else in your database. In this instance, there might come a time when you have to change or set up the root user password. This can be as a result of forgetting the root password or simply wanting to strengthen your password. This process is handled through the command line and works with either MySQL or MariaDB installations. And well, as long as you have admin access, either by using sudo or su syntaxes, the Linux distribution you are using does not matter since the process is similar. Please note: there have been many attacks across the IT landscape, thus necessitating very strong passwords, which I highly recommend for your databases. You can try out an unsystematic password generator, then save the password via the aid of a password manager. Let’s carry on. Setting the password for the first timeTypically, during the installation of MySQL, you are required to set an initial password. However, if this was not done while installing and setting up MySQL, you will first have to set up a password. Here is how to do that:
mysqladmin -u root password Fosslinux Where Fosslinux is the password that you will be using from now on, this means that every time you log into MySQL using the command mysql -u root -p, you will have to key in the password we have just configured. Note: If you encounter the following error: Access denied for user ‘root’@’localhost,’ scroll down to find the solution provided in this article. Alternatively, you can use the following method to set up the root password for the first time. Use the command below: mysql_secure_installationMySQL secure installation This command will set the root user password and allow you to get rid of anonymous users and the test database. In addition, it will also disallow remote root login. This, in turn, further assures you of concrete security to your MySQL database. After setting up the MySQL root password for the first time, it is time to learn how to change it. Changing the MySQL root user passwordTo change the MySQL root password, follow the steps mentioned herein:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Fosslinux$11';create new password Where Fosslinux$11 is the new password that will be used. Remember to satisfy the current password policy by combining uppercase, lowercase, numeric and special characters. Save the file as ~/mysql-pwd
sudo systemctl stop mysqlstop MySQL service
sudo mysqld -init-file=~/mysql-pwdsave MySQL password
sudo systemctl start mysqlstart MySQL service
mysql -u root -p When a prompt appears, key in the admin password you created, and you will be all set to go. login as a root userNow that we have fruitfully learned how to change the newly created MySQL root password, it is time to learn how to recover a forgotten or lost MySQL root password. Recover your MySQL passwordWell, let’s say that you have forgotten your MySQL root user password, and you desperately need to recover it. All you need to do is to follow the steps mentioned herein: 1. Stop the MySQL server process using the command: sudo service mysql stopservice stop MySQL 2. Start the MySQL server using the following command: sudo mysqld_safe --skip-grant-tables --skip-networking & 3. Connect to the MySQL server as the root user using the command: mysql -u root You will be required to issue the following MySQL commands for you to reset the root password after completing the steps mentioned above: mysql> use mysql; mysql> update user set authentication_string=password('NEWPASSWORD') where user='root'; mysql> flush privileges; mysql> quit Note: Please do not forget that NEWPASSWORD refers to the new password you will be using to log in as a root user. Once you are done running all the commands mentioned above, proceed and restart the MySQL daemon using this command: sudo service mysql restartrestart MySQL service By now, you should be able to log into MySQL using the newly set password. That’s all. We believe you are now in a position to set, reset, and recover your MySQL root password. A key point to note: Remember to set a very strong password for the MySQL root user. A password that is difficult to crack. This will boost the security of your databases. As you already know, databases house very sensitive data, so the last thing you want is for anyone to access your information without your consent. Use of a long password and ones that cannot be easily memorized and guessed are highly recommended. If you use a password generator to create your password, remember to store it in a password vault. While using MySQL, you might be so embarrassed if you encounter error messages. For instance, most users complain of facing this error message: Access denied for user root@localhost. This might not be very pleasant, but we have a solution for you. How To Solve The MySQL Error: Access denied for user root@localhostThis error message usually appears for new installations of MySQL when you attempt to connect to MySQL with the root user. Here is how to quickly resolve the access denied for user root on localhost. The outlined steps can be used for both MySQL and MariaDB. You do not have to make modifications to any tables or carry out any complex configurations. As we have already seen in this article, when you install MySQL and want to gain access to it on the local machine with the root user, you use the following command: mysql -u root -p In most instances, you will receive the error message we just mentioned: “Access denied for user root@localhost.” For you to log into MySQL as root, first use sudo to make modifications to the root user: sudo mysql Then, input your passcode at the prompt. A MySQL shell will load. MySQL shellUse the ALTER USER command and change the method of authentication to log into MySQL as root: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password'; Example: For the sake of the tutorial, I will change the password to ‘Fosslinux as shown in the command line and output below: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Fosslinux'; Output open MySQL shellWhat the above command-line does, is it changes the password for the user root and sets the authentication method to mysql_native_password. This is a traditional mode of authentication. Unfortunately, in most cases, the error above occurs since the authentication mode used is auth_plugin which is not safe, thus the error. Note: While typing your desired password, make sure it follows the password-provided rules, a combination of capital and small letter plus numerics if you intend to input a stronger password. After running the commands listed above, exit the MySQL shell by clicking CTRL + D on your keyboard or key in “exit” on the MySQL shell and click enter. You do not have to restart the MySQL service for you to log in using the command below: restart MySQL serviceAt this point, you can now attempt to access MySQL with root again. In the terminal, key in: mysql -u root -p Input the passcode you used with the ALTER USER command and do not input the system password to try and access MySQL because it will not work. You should now see the MySQL welcome message if it all went well. login to MySQL as rootPlease note: The ALTER USER command may fail to work for the versions of MySQL and MariaDB older than 5.7.6 and 10.1.20, respectively. In summary, now you are in a position to bypass the MySQL ERROR 1698 (28000): Access denied for user ‘root’@’localhost’. There are various ways to bypass this error, but we used the much simpler and faster method. Ensure that you input the commands as listed in this article to avoid errors in SQL syntax. It is more advisable to copy and paste them. ConclusionThis article explains and breaks down all the subject matters relating to the root user login in MySQL. It not only shows you how to log in as a root user in MySQL, but it further shows you how to ensure you do so without encountering any errors and how to bypass the most common error in case it does occur. The article further breaks down the steps to set up, change and recover a MySQL root password and set up the root password for the first time. Ensure to stick to all the processes provided in this article to avoid security breaches by using strong password combinations. We hope the article will be helpful. If yes, give a thumbs up via the comments section. How do you connect to MySQL as root?This is the case if you initialized the data directory using mysqld --initialize-insecure.. Connect to the server as root using no password: $> mysql -u root --skip-password.. Assign a password: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';. How do I find my MySQL root password?In order to recover the password, you simply have to follow these steps: Stop the MySQL server process with the command sudo service mysql stop. Start the MySQL server with the command sudo mysqld_safe –skip-grant-tables –skip-networking & Connect to the MySQL server as the root user with the command mysql -u root.
What is the password for root user MySQL?In MySQL, by default, the username is root and there's no password.
Can we connect to MySQL as root without password?If the root account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as root without a password and be granted all privileges. The 'root'@'localhost' account also has a row in the mysql.
|