{"id":5412,"date":"2020-12-15T09:59:42","date_gmt":"2020-12-15T10:29:42","guid":{"rendered":"https:\/\/ultering.com\/it4us\/?p=5412"},"modified":"2020-12-20T22:58:11","modified_gmt":"2020-12-20T23:28:11","slug":"mariadb-reset-root-password","status":"publish","type":"post","link":"https:\/\/ultering.com\/it4us\/?p=5412","title":{"rendered":"MariaDB: Reset Root Password"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_73 counter-hierarchy ez-toc-counter ez-toc-white ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69e11d76471f0\" class=\"ez-toc-cssicon-toggle-label\"><span class><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\" \/><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\" \/><\/svg><\/span><\/span><\/label><input type=\"checkbox\" id=\"ez-toc-cssicon-toggle-item-69e11d76471f0\" checked aria-label=\"Toggle\"><nav><ul class=\"ez-toc-list ez-toc-list-level-1 \"><li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#PROBLEM\" title=\"PROBLEM\">PROBLEM<\/a><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#ENV\" title=\"ENV\">ENV<\/a><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#SOLUTION\" title=\"SOLUTION\">SOLUTION<\/a><ul class=\"ez-toc-list-level-3\"><li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#USING_A_TEMPORARY_PASSWORD\" title=\"USING A TEMPORARY PASSWORD\">USING A TEMPORARY PASSWORD<\/a><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#WHEN_THE_DATABASE_FAILS_TO_START\" title=\"WHEN THE DATABASE FAILS TO START\">WHEN THE DATABASE FAILS TO START<\/a><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#TESTING_AND_SWITCHING_TO_DEFINITIVE_PASSWORD\" title=\"TESTING AND SWITCHING TO DEFINITIVE PASSWORD\">TESTING AND SWITCHING TO DEFINITIVE PASSWORD<\/a><\/li><\/ul><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#DIFFERENT_BEHAVIORS_DUE_TO_ENVIRONMENT_DIFFERENCES\" title=\"DIFFERENT BEHAVIORS DUE TO ENVIRONMENT DIFFERENCES\">DIFFERENT BEHAVIORS DUE TO ENVIRONMENT DIFFERENCES<\/a><\/li><li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#ADDITIONAL_SOURCES\" title=\"ADDITIONAL SOURCES\">ADDITIONAL SOURCES<\/a><ul class=\"ez-toc-list-level-3\"><li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/ultering.com\/it4us\/?p=5412\/#ALTERNATIVE_SITE\" title=\"ALTERNATIVE SITE\">ALTERNATIVE SITE<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"PROBLEM\"><\/span><span data-preserver-spaces=\"true\">PROBLEM<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span data-preserver-spaces=\"true\">This procedure resets the MariaDB root password when it is not possible to access the database returning:<br>\n<\/span><\/p>\n<p><strong><span data-preserver-spaces=\"true\">ERROR 1045 (28000): Access denied for user &#8216;root&#8217;@&#8217;localhost&#8217; (using password: YES)<\/span><\/strong><\/p>\n<p><\/p>\n<p><em><span data-preserver-spaces=\"true\"><noscript><img decoding=\"async\" class=\"alignnone size-full wp-image-5414\" src=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied.jpg\" alt width=\"673\" height=\"53\" srcset=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied.jpg 673w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied-300x24.jpg 300w\" sizes=\"(max-width: 673px) 100vw, 673px\"><\/noscript><img decoding=\"async\" class=\"alignnone size-full wp-image-5414 lazyload\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20673%2053%22%3E%3C%2Fsvg%3E\" alt width=\"673\" height=\"53\" srcset=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%20673%2053%22%3E%3C%2Fsvg%3E 673w\" sizes=\"(max-width: 673px) 100vw, 673px\" data-srcset=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied.jpg 673w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied-300x24.jpg 300w\" data-src=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_root_login_denied.jpg\"><\/span><\/em><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"ENV\"><\/span>ENV<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>debian 10\/9<br>\n10.x , 10.3.27-MariaDB<\/p>\n<h2><span data-preserver-spaces=\"true\"><br>\nSOLUTION<\/span><\/h2>\n<p>&#8211; Stop database:<br>\nsudo systemctl stop mariadb<\/p>\n<p>&#8211; Test:<br>\nsudo systemctl status mariadb<\/p>\n<p>You shall get;<br>\nStatus: &#8220;MariaDB server is down&#8221;<br>\nTo return to console, use Ctrl+C<\/p>\n<p>&#8211; This command shall return empty:<br>\nsudo pgrep mysqld<\/p>\n<p>&#8211; Starting the database using safe mode:<br>\nsudo mysqld_safe &#8211;skip-grant-tables &#8211;skip-networking &amp;<br>\nor<br>\nsudo mysqld_safe &#8211;skip-grant-tables &amp;<\/p>\n<p>&#8220;Enter&#8221; to return to prompt.<\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"USING_A_TEMPORARY_PASSWORD\"><\/span><strong>USING A TEMPORARY PASSWORD<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>&#8211; Entering the database prompt without user or password:<br>\nmysql -u root<\/p>\n<p>use mysql;<br>\nflush privileges;<br>\nupdate user SET PASSWORD=PASSWORD(&#8216;secret&#8217;) WHERE USER=&#8217;root&#8217;;<br>\nGRANT ALL PRIVILEGES ON root.* TO &#8216;root&#8217;@&#8217;localhost&#8217; WITH GRANT OPTION;<br>\nflush privileges;<br>\nquit<\/p>\n<p>&#8211; kill all mysqld processes<br>\nsudo pgrep mysqld<\/p>\n<p>&#8211; Kill by the PID numbers returned, for example:<br>\nsudo kill -9 1234<br>\nsudo kill -9 4567<\/p>\n<p>&#8211; Start the database:<br>\nsudo systemctl start mariadb<\/p>\n<h3><b>\n<p>WHEN THE DATABASE FAILS TO START<\/p><\/b><\/h3>\n<div><b>&nbsp;<\/b>During the start, if it hangs, it is necessary to kill all processes.<\/div>\n<div>It happens when some process is left behind, not being killed.<\/div>\n<div>Unfortunately, sometimes when killing the processes doesn&#8217;t work because another new process starts just after, I just got solution rebooting the machine even though using killall, etc.<\/div>\n<div><\/div>\n<div>\n<div>&#8211; To check:<\/div>\n<div>sudo systemctl status mariadb<\/div>\n<div><\/div>\n<\/div>\n<h2><em><span data-preserver-spaces=\"true\"><noscript><img decoding=\"async\" class=\"alignnone size-full wp-image-5413\" src=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout.jpg\" alt width=\"1349\" height=\"373\" srcset=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout.jpg 1349w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout-300x83.jpg 300w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout-768x212.jpg 768w\" sizes=\"(max-width: 1349px) 100vw, 1349px\"><\/noscript><img decoding=\"async\" class=\"alignnone size-full wp-image-5413 lazyload\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%201349%20373%22%3E%3C%2Fsvg%3E\" alt width=\"1349\" height=\"373\" srcset=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%201349%20373%22%3E%3C%2Fsvg%3E 1349w\" sizes=\"(max-width: 1349px) 100vw, 1349px\" data-srcset=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout.jpg 1349w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout-300x83.jpg 300w, https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout-768x212.jpg 768w\" data-src=\"https:\/\/ultering.com\/it4us\/wp-content\/uploads\/2020\/12\/debian9_mariadb10_fails_to_start_timeout.jpg\"><\/span><\/em><\/h2>\n<h3><\/h3>\n<h3><\/h3>\n<h3><span class=\"ez-toc-section\" id=\"TESTING_AND_SWITCHING_TO_DEFINITIVE_PASSWORD\"><\/span>TESTING AND SWITCHING TO DEFINITIVE PASSWORD<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>&#8211; Access the database again to test the &#8220;secret&#8221; temporary password and also to change to the definitive password:<\/p>\n<p>mysql -u root -p<br>\npass: secret<\/p>\n<p>use mysql;<br>\nflush privileges;<br>\nUPDATE mysql.user SET authentication_string = PASSWORD(&#8216;MY_DEFINITIVE_PASSWORD&#8217;) WHERE User = &#8216;root&#8217; AND Host = &#8216;localhost&#8217;;<br>\nGRANT ALL PRIVILEGES ON root.* TO &#8216;root&#8217;@&#8217;localhost&#8217; WITH GRANT OPTION;<br>\nGRANT RELOAD ON *.* TO &#8216;root&#8217;@&#8217;localhost&#8217;;<br>\nflush privileges;<br>\nquit<\/p>\n<p>&nbsp;<\/p>\n<p>&#8211; Access the database again to test the definitive password:<br>\nmysql -u root -p<br>\npass: MY_DEFINITIVE_PASSWORD<\/p>\n<p><strong><br>\n***NOTE: <\/strong><\/p>\n<p>If after switching to the definitive password, the problem comes back denying access, it is because the characters used in the password caused the issue.<br>\nAvoid password like this:<br>\nAbc1234#-_ (the -_ didn&#8217;t work during my attempts)<br>\nFor sure, use a password with just numbers and letters.<br>\nTo get confidence, repeat the procedure.<br>\nIf you desire to use special chars, you may try after making sure that you got success at least once, that way you may discover which special char to avoid in your password.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"DIFFERENT_BEHAVIORS_DUE_TO_ENVIRONMENT_DIFFERENCES\"><\/span>\nDIFFERENT BEHAVIORS DUE TO ENVIRONMENT DIFFERENCES<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This procedure was executed several times successfully, but you may get some issue with some command shown above.<br>\nBelow, there are some alternatives, not all of them of course, that you may try if some command fails.<\/p>\n<p>set password for &#8216;root&#8217;@&#8217;localhost&#8217; = password(&#8216;secret&#8217;);<br>\n&#8211; or<br>\nALTER USER &#8216;root&#8217;@&#8217;localhost&#8217; IDENTIFIED BY &#8216;secret&#8217;;<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"ADDITIONAL_SOURCES\"><\/span><span data-preserver-spaces=\"true\">ADDITIONAL SOURCES<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><a href=\"https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-reset-your-mysql-or-mariadb-root-password#:~:text=How%20To%20Reset%20Your%20MySQL%20or%20MariaDB%20Root,Server%20Without%20Permission%20Checking.%20...%20More%20items...%20\">www.digitalocean.com\/community\/tutorials\/how-to-reset-your-mysql-or-mariadb-root-password<\/a><\/p>\n<p><a class=\"editor-rtfLink\" href=\"https:\/\/robbinespu.github.io\/eng\/2018\/03\/29\/Reset_mariadb_root_password.html\" target=\"_blank\" rel=\"noopener\"><span data-preserver-spaces=\"true\">robbinespu.github.io\/eng\/2018\/03\/29\/Reset_mariadb_root_password.html<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"ALTERNATIVE_SITE\"><\/span>ALTERNATIVE SITE<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><a href=\"https:\/\/alsdias.blogspot.com\/2020\/12\/mariadb-error-1045-28000-access-denied.html\">alsdias.blogspot.com\/2020\/12\/mariadb-error-1045-28000-access-denied.html<\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"background-color: #808080;\">@SYSREF:<\/span><\/p>\n<p><span style=\"background-color: #808080;\"><em>y;reset root password&lt;memo&lt;mariadb;.&nbsp;<\/em><\/span><\/p>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>PROBLEM This procedure resets the MariaDB root password when it is not possible to access the database returning: ERROR 1045 (28000): Access denied for user &#8216;root&#8217;@&#8217;localhost&#8217; (using password: YES) &nbsp; ENV debian 10\/9 10.x , 10.3.27-MariaDB SOLUTION &#8211; Stop database: sudo systemctl stop mariadb &#8211; Test: sudo systemctl status mariadb You shall get; Status: &#8220;MariaDB &#8230; <a href=\"https:\/\/ultering.com\/it4us\/?p=5412\" class=\"more-link\">Read More<span class=\"screen-reader-text\"> &#8220;MariaDB: Reset Root Password&#8221;<\/span> &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[17],"tags":[],"class_list":["post-5412","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/posts\/5412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5412"}],"version-history":[{"count":5,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/posts\/5412\/revisions"}],"predecessor-version":[{"id":5454,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=\/wp\/v2\/posts\/5412\/revisions\/5454"}],"wp:attachment":[{"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ultering.com\/it4us\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}