{"id":165,"date":"2017-09-28T17:15:29","date_gmt":"2017-09-28T17:15:29","guid":{"rendered":"http:\/\/cheapwindowsvps.com\/blog\/?p=165"},"modified":"2025-01-20T09:44:30","modified_gmt":"2025-01-20T09:44:30","slug":"how-to-change-the-root-password-on-mariadb","status":"publish","type":"post","link":"https:\/\/cheapwindowsvps.com\/blog\/how-to-change-the-root-password-on-mariadb\/","title":{"rendered":"How to Change the Root Password on MariaDB"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">If you\u2019re a sysadmin, you know how scary losing a root password can be. Don\u2019t worry, though. If you lose or forget the password to your <\/span><a href=\"https:\/\/mariadb.com\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">MariaDB<\/span><\/a><span style=\"font-weight: 400;\"> or <\/span><a href=\"https:\/\/www.mysql.com\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">MySQL<\/span><\/a><span style=\"font-weight: 400;\"> database, you can reset it in a few easy steps. <\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">First, you\u2019ll need a few things:<\/span><\/p>\n<p>&nbsp;<\/p>\n<ul style=\"list-style-type: circle;\">\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">A Linux server running MariaDB<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">A sudo-enabled user account for the server<\/span><\/li>\n<\/ul>\n<h2><span style=\"font-weight: 400;\">Stop the Database Server<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">In order to reset the root password, you\u2019ll need to stop the database service. Make sure all users have saved their work, then run this command to shutdown the database:<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ sudo systemctl stop mariadb<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><b>Note<\/b><span style=\"font-weight: 400;\">: If the above command fails with <\/span><span style=\"font-weight: 400;\">systemctl: command not found<\/span><span style=\"font-weight: 400;\">, you may be running an older system or a distribution that doesn\u2019t support <\/span><a href=\"https:\/\/fedoraproject.org\/wiki\/SysVinit_to_Systemd_Cheatsheet\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">systemd<\/span><\/a><span style=\"font-weight: 400;\"> yet. Don\u2019t worry, though. You can run this command instead to stop the database:<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ sudo service mariadb stop<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">After you\u2019ve stopped the database, you\u2019ll need to access it manually to reset the MariaDB password.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Restart the Database Server in Safe Mode<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">MySQL (and MariaDB, by extension) have a <\/span><a href=\"https:\/\/mariadb.com\/kb\/en\/the-mariadb-library\/mysqld_safe\/\" target=\"_blank\" rel=\"noopener\"><span style=\"font-weight: 400;\">safe mode<\/span><\/a><span style=\"font-weight: 400;\"> whereby you can log in to the database without checking permissions. You can enable this by running the following command:<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ sudo mysqld_safe &#8211;skip-grant-tables &#8211;skip-networking &amp;<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">There are a couple of different parts to this command, so we\u2019ll go through them in order:<\/span><\/p>\n<p>&nbsp;<\/p>\n<ul style=\"list-style-type: circle;\">\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">mysqld_safe<\/span><span style=\"font-weight: 400;\"> refers to the \u201csafe mode\u201d for MySQL and MariaDB.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">&#8211;skip-grant-tables<\/span><span style=\"font-weight: 400;\"> tells the database to skip loading the tables that contain user permission information. This means you won\u2019t need a password to log in, because the database has not loaded information about users and passwords.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">&#8211;skip-networking<\/span><span style=\"font-weight: 400;\"> means that no one else will be able to access the database while you are resetting the password. While this parameter is not necessary, it is useful because running the database in skip permissions mode can be a security risk.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">&amp; <\/span><span style=\"font-weight: 400;\">at the end tells the command to run in the background. That means the command will execute and you\u2019ll get a new terminal prompt on the next line. Alternatively, you could open a new shell session while MariaDB runs in this one.<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">After executing this command, you should be able to connect to the database as the root user:<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ mysql -u root<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Since we\u2019ve disabled permission checking, the MariaDB prompt should appear.<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">Type \u2018help;\u2019 or \u2018\\h\u2019 for help. Type \u2018\\c\u2019 to clear the current input statement.<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">MariaDB [(none)]&gt;<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Good news. We\u2019ve accessed the database and have root privileges. Now we can reset the MariaDB password.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Change the Root Password<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">Once you\u2019re at the database prompt, you\u2019ll need to go through a couple of steps to reset the root password in MariaDB.<\/span><\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Run <\/span><span style=\"font-weight: 400;\">FLUSH PRIVILEGES;<\/span><span style=\"font-weight: 400;\"> to reload the grant tables we disabled on login. This will reload the user and password settings so you can edit them.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">To edit the password for the <\/span><span style=\"font-weight: 400;\">root<\/span><span style=\"font-weight: 400;\"> user, you can use the <\/span><span style=\"font-weight: 400;\">update user<\/span><span style=\"font-weight: 400;\"> command. Here are the steps you\u2019ll need to take:<\/span>\n<ol>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">use mysql;<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">update user set password=PASSWORD(\u201castrongpasswordhere\u201d) where User=\u2019root\u2019;<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">flush privileges;<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">quit<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">This sequence of commands updates the <\/span><span style=\"font-weight: 400;\">root<\/span><span style=\"font-weight: 400;\"> user\u2019s profile to use the new password, reloads the permissions table, and then disconnects from the database. At this point, the MariaDB root password should be reset. The hard part is over; now you just need to restart the server and test that it works.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Restart the Database Server<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">Once you\u2019ve reset the password successfully, you\u2019ll need to bring the server back up and then try to log into it as root to make sure the new password has taken effect.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Bring up the server the same way you brought it down, using one of the two commands below depending on your Linux installation:<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ sudo systemctl start mariadb<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">or<\/span><\/p>\n<p>&nbsp;<\/p>\n<blockquote>\n<p><span style=\"font-weight: 400;\">$ sudo service mariadb start<\/span><\/p>\n<\/blockquote>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Then you can run <\/span><span style=\"font-weight: 400;\">mysql -u root -p<\/span><span style=\"font-weight: 400;\"> and it should prompt you for the new password. Enter the password you set in the previous steps, and you should be back into your database.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Wrapping Up<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">Misplacing or improperly configuring the root password on MariaDB doesn\u2019t have to be the end of the world. Using these simple steps, you can get your database up and running again.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Have we missed something? Have a question not covered here? Be sure to let us know in the comments.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you\u2019re a sysadmin, you know how scary losing a root password can be. Don\u2019t worry, though. If you lose or forget the password to your MariaDB or MySQL database, you can reset it in a few easy steps. &nbsp; First, you\u2019ll need a few things: &nbsp; A Linux server running MariaDB A sudo-enabled user [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-165","post","type-post","status-publish","format-standard","hentry","category-blog"],"_links":{"self":[{"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/posts\/165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":3,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":169,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/posts\/165\/revisions\/169"}],"wp:attachment":[{"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cheapwindowsvps.com\/blog\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}