{"id":12,"date":"2022-11-13T05:52:00","date_gmt":"2022-11-13T05:52:00","guid":{"rendered":"https:\/\/nguyenhieu.name.vn\/?p=12"},"modified":"2023-11-15T02:28:50","modified_gmt":"2023-11-15T02:28:50","slug":"reset-the-mysql-root-password","status":"publish","type":"post","link":"https:\/\/nguyenhieu.name.vn\/?p=12","title":{"rendered":"How to reset the MySQL Root Password on Ubuntu 22.04"},"content":{"rendered":"\n<p>Have you failed \/ forgotten to remember the root password for MySQL database? Stress not, it works out, and there is an answer!<\/p>\n\n\n\n<p>In this article, we will reset the MySQL root password in Ubuntu 22.04 LTS by starting MySQL with the<strong>&nbsp;\u2013skip-grant-tables option<\/strong>.<\/p>\n\n\n\n<p>It is a simple guide that works with any modern Linux distribution like CentOS 7 and Ubuntu 18.04, 20.04 LTS<\/p>\n\n\n\n<p>Before moving on to the solutions, it is assumed that you have the MYSQL 8.0&nbsp;version of the MySQL database for Ubuntu.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Reset the MySQL Root Password on Ubuntu 22.04 LTS<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1. Check Mysql Version.<\/h3>\n\n\n\n<p>First, you must confirm the version of MySQL on Ubuntu that you perform because the commands will be different.<\/p>\n\n\n\n<p><strong>MySQL<\/strong>&nbsp;Version Output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ mysql -V<\/code><\/pre>\n\n\n\n<p>If on MySQL version 8, you will see something like:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ mysql Ver 8.0.29-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))<\/code><\/pre>\n\n\n\n<p>If you are on MySQL version 5, you will see something similar to:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ mysql Ver 14.14 Distrib 5.7.36, for Linux (x86_64) using EditLine wrapper<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2. Stop MySQL Server.<\/h3>\n\n\n\n<p>To change the Root MySQL password, you must first stop the MySQL server, run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql stop<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3. Check MySQL Status.<\/h3>\n\n\n\n<p>Check the&nbsp;<strong>status<\/strong>&nbsp;of the MySQL server to verify. Run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql status<\/code><\/pre>\n\n\n\n<p><strong>OUTPUT:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql status\n\u25cb mysql.service - MySQL Community Server\n Loaded: loaded (\/lib\/systemd\/system\/mysql.service; enabled; vendor preset: enabled)\n Active: inactive (dead) since Thu 2022-06-30 17:35:46 IST; 48min ago\n Process: 41280 ExecStartPre=\/usr\/share\/mysql\/mysql-systemd-start pre (code=exited, status=0\/SUCCESS)\n Process: 41288 ExecStart=\/usr\/sbin\/mysqld (code=exited, status=0\/SUCCESS)\n Main PID: 41288 (code=exited, status=0\/SUCCESS)\n Status: \"Server shutdown complete\"\n CPU: 1.439s\n\n Jun 30 17:33:56 KELLGGNLPTP01038 systemd&#91;1]: Starting MySQL Community Server...\n Jun 30 17:33:57 KELLGGNLPTP01038 systemd&#91;1]: Started MySQL Community Server.\n Jun 30 17:35:45 KELLGGNLPTP01038 systemd&#91;1]: Stopping MySQL Community Server...\n Jun 30 17:35:46 KELLGGNLPTP01038 systemd&#91;1]: mysql.service: Deactivated successfully.\n Jun 30 17:35:46 KELLGGNLPTP01038 systemd&#91;1]: Stopped MySQL Community Server.\n Jun 30 17:35:46 KELLGGNLPTP01038 systemd&#91;1]: mysql.service: Consumed 1.439s CPU time.<\/code><\/pre>\n\n\n\n<p>Now we will restart MySQL with skip-grant-table command<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4. Make sure we have the directory&nbsp;<strong>\/var\/run\/mysqld<\/strong>&nbsp;exists and have correct owner set.<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo mkdir \/var\/run\/mysqld<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo chown mysql \/var\/run\/mysqld<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5. Starting the MySQL database server without loading grant tables.<\/h3>\n\n\n\n<p>Now start MySQL with the&nbsp;<strong>\u2013skip-grant-tables<\/strong>&nbsp;option. The&nbsp;<strong>&amp;<\/strong>&nbsp;is required here.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo mysqld_safe --skip-grant-tables&amp;<\/code><\/pre>\n\n\n\n<p><strong>OUTPUT:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ 2022-06-30T12:06:55.799610Z mysqld_safe Logging to '\/var\/log\/mysql\/error.log'. 2022-06-30T12:06:55.819458Z mysqld_safe Starting mysqld daemon with databases from \/var\/lib\/mysql<\/code><\/pre>\n\n\n\n<p>Now press&nbsp;<strong>ENTER<\/strong>&nbsp;to return to the Linux&nbsp;<strong>BASH<\/strong>&nbsp;prompt.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 6. Now it\u2019s time to change MySQL root password.<\/h3>\n\n\n\n<p>You can now connect to the Root MySQL account without password.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo mysql --user=root mysql<\/code><\/pre>\n\n\n\n<p>Once logged in, you will see the&nbsp;<strong>mysql&gt;<\/strong>&nbsp;prompt.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo mysql --user=root mysql \nReading table information for completion of table and column names \nYou can turn off this feature to get a quicker startup with -A \nWelcome to the MySQL monitor. Commands end with ; or g. \nYour MySQL connection id is 7 \nServer version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) \nCopyright (c) 2000, 2022, Oracle and\/or its affiliates. \nOracle is a registered trademark of Oracle Corporation and\/or its affiliates. Other names may be trademarks of their respective owners. \nType 'help;' or 'h' for help. Type 'c' to clear the current input statement. \nmysql><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">MySQL 8 \u2013 Change Root Password on Ubuntu<\/h4>\n\n\n\n<p>For MySQL 8 on Ubuntu, run following commands.<\/p>\n\n\n\n<p>Update command will help you to update the password.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; UPDATE mysql.user SET authentication_string=null WHERE User='root'; \nQuery OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0<\/code><\/pre>\n\n\n\n<p>Now,&nbsp;<strong>flush the privileges<\/strong>&nbsp;first. Run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; flush privileges;<\/code><\/pre>\n\n\n\n<p>Replace&nbsp;<strong>your_password_here<\/strong>&nbsp;with your own<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';<\/code><\/pre>\n\n\n\n<p>Flush privileges again.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; flush privileges;<\/code><\/pre>\n\n\n\n<p>Now exit Mysql<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; exit \nBye<\/code><\/pre>\n\n\n\n<p>Now Start the Mysql Server&nbsp; and login with new password<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql start<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ mysql -u root -p\u00a0<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">MySQL 5.7 \u2013 Change Root Password on Ubuntu<\/h4>\n\n\n\n<p>For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace&nbsp;<strong>your_password_here<\/strong>&nbsp;with your own.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; update user set authentication_string=PASSWORD('your_password_here') where user='root';<\/code><\/pre>\n\n\n\n<p>Now run this below command to change the auth plugin to&nbsp;<strong>mysql_native_password<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; update user set plugin=\"mysql_native_password\" where User='root';<\/code><\/pre>\n\n\n\n<p>Now,&nbsp;<strong>flush the privileges<\/strong>&nbsp;first. Run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; flush privileges;<\/code><\/pre>\n\n\n\n<p>Now exit Mysql<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; exit \nBye<\/code><\/pre>\n\n\n\n<p>Now Start the Mysql Server&nbsp; and login with new password<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql start\nvikash@server:~$ mysql -u root -p\u00a0<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">MySQL 5.6 \u2013 Reset Root Password on Ubuntu<\/h4>\n\n\n\n<p>For MySQL 5.7 on Ubuntu, run this command to change the root password. Replace&nbsp;<strong>your_password_here<\/strong>&nbsp;with your own.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; update user set Password=PASSWORD('your_password_here') where user='root';<\/code><\/pre>\n\n\n\n<p>Now run this below command to change the auth plugin to&nbsp;<strong>mysql_native_password<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; update user set plugin=\"mysql_native_password\" where User='root';<\/code><\/pre>\n\n\n\n<p>Now,&nbsp;<strong>flush the privileges<\/strong>&nbsp;first. Run the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; flush privileges;<\/code><\/pre>\n\n\n\n<p>Now exit Mysql<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql&gt; exit \nBye<\/code><\/pre>\n\n\n\n<p>Now Start the Mysql Server&nbsp; and login with new password<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql start\nadmin@server:~$ mysql -u root -p<\/code><\/pre>\n\n\n\n<p>If you are facing any issue then follow the following below OUTPUT box<\/p>\n\n\n\n<p><strong>Command executed in single interface.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>admin@server:~$ sudo \/etc\/init.d\/mysql stop \nStopping mysql (via systemctl): mysql.service.\n\nadmin@server:~$ sudo mkdir \/var\/run\/mysqld \n\nadmin@server:~$ sudo chown mysql \/var\/run\/mysqld \n\nadmin@server:~$ sudo mysqld_safe --skip-grant-tables&amp; &#91;1] 41711 \n\nadmin@server:~$ 2022-06-30T12:06:55.799610Z mysqld_safe Logging to '\/var\/log\/mysql\/error.log'. 2022-06-30T12:06:55.819458Z mysqld_safe Starting mysqld daemon with databases from \/var\/lib\/mysql \n\nadmin@server:~$ sudo mysql --user=root mysql \nReading table information for completion of table and column names \nYou can turn off this feature to get a quicker startup with -A \nWelcome to the MySQL monitor. Commands end with ; or g. \nYour MySQL connection id is 7 \nServer version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) \nCopyright (c) 2000, 2022, \nOracle and\/or its affiliates. Oracle is a registered trademark of Oracle Corporation and\/or its affiliates. Other names may be trademarks of their respective owners. \nType 'help;' or 'h' for help. Type 'c' to clear the current input statement. \n\nmysql> UPDATE mysql.user SET authentication_string=null WHERE User='root'; \nQuery OK, 1 row affected (0.00 sec) \nRows matched: 1 Changed: 1 Warnings: 0 \n\nmysql> flush privileges; \nQuery OK, 0 rows affected (0.01 sec) \n\nmysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123'; \nQuery OK, 0 rows affected (0.02 sec) \n\nmysql> flush privileges; \nQuery OK, 0 rows affected (0.01 sec) \n\nmysql> exit \nBye \n\nadmin@server:~$ mysql -u root -p \nEnter password: \nWelcome to the MySQL monitor. Commands end with ; or g. \nYour MySQL connection id is 9 \nServer version: 8.0.29-0ubuntu0.22.04.2 (Ubuntu) \nCopyright (c) 2000, 2022, \nOracle and\/or its affiliates. Oracle is a registered trademark of Oracle Corporation and\/or its affiliates. Other names may be trademarks of their respective owners. \nType 'help;' or 'h' for help. Type 'c' to clear the current input statement. \n\nmysql> show databases; \n+--------------------+ \n| Database | \n+--------------------+ \n| information_schema | \n| mysql | \n| performance_schema | \n| sys | \n+--------------------+ \n4 rows in set (0.00 sec) \n\nmysql> exit \nBye<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion:<\/h2>\n\n\n\n<p>Congratulation you have successfully&nbsp; reset your MySQL password on Ubuntu.<\/p>\n\n\n\n<p>Let us know if you are facing any issue while reset password in comment section, we will happy to help you.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Have you failed \/ forgotten to remember the root password for MySQL database? Stress not, it works out, and there is an answer! In this article, we will reset the MySQL root password in Ubuntu 22.04 LTS by starting MySQL with the&nbsp;\u2013skip-grant-tables option. It is a simple guide that works with any modern Linux distribution [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":72,"comment_status":"closed","ping_status":"open","sticky":false,"template":"single-with-sidebar","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[19,21,10,20,22],"class_list":["post-12","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql","tag-password","tag-reset","tag-root","tag-ubuntu"],"_links":{"self":[{"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=12"}],"version-history":[{"count":4,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions"}],"predecessor-version":[{"id":24,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/posts\/12\/revisions\/24"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=\/wp\/v2\/media\/72"}],"wp:attachment":[{"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nguyenhieu.name.vn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}