{"id":486,"date":"2015-12-28T04:15:10","date_gmt":"2015-12-28T12:15:10","guid":{"rendered":"https:\/\/cloudinsidr.com\/content\/?p=486"},"modified":"2016-03-22T14:14:26","modified_gmt":"2016-03-22T22:14:26","slug":"locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb","status":"publish","type":"post","link":"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/","title":{"rendered":"Locked out of WordPress? How to Reset Your User Password to the Admin Backend in MySQL or MariaDB"},"content":{"rendered":"

Have you locked\u00a0yourself\u00a0out of WordPress due to some upgrade or restore mishap in combination with the lack of a valid email address to restore it to?\u00a0Welcome to the Club of lucky administrators! There is a solution to this particular problem that just begs to be shouted from the rooftops: why don’t you reset your WordPress password using MySQL (or MariaDB). Let’s get right to it.<\/p>\n

<\/p>\n

Resetting a user’s password for any CMS, in fact, involves roughly the same steps.<\/p>\n

Step 1. Connect to the remote WordPress host via ssh<\/h4>\n

Connect to your remote WordPress host using SSH (our preferred SSH client is PuTTY, but any other client will do just as well):<\/p>\n

ssh -i \"your-secret-key-file.pem\" username@ip.add.re.ss<\/pre>\n
You may need to become root in order to access the web server document directory tree. This is done using:<\/p>\n
sudo su<\/pre>\n
Step 2. Navigate to the directory containing the affected WordPress installation<\/h4>\n
The web server document directory may be located in any number of places. Its exact location\u00a0depends on how the server\u00a0was set up originally (find out more here<\/a>). NGINX can use\u00a0several web server directories and each of them can be located in a different place. This information is found in the configuration file that corresponds to each particular website. NGINX site-specific\u00a0configuration files are stored in \/etc\/nginx or one of its subdirectories (try sites-available\/). Open the appropriate configuration file and make a note of the location of the web server root for your website in a notepad on your local computer or on good old-fashioned piece of paper, then\u00a0cd to this place, for example:<\/p>\n
cd \/var\/www\/www.website1.tld\/\r\n\r\n<\/pre>\n
Navigate to the WordPress installation that is found inside the web server document directory.<\/span><\/p>\n
Step 3. Open\u00a0wp-config.php and extract some useful\u00a0intel about the database<\/h4>\n
Once inside the WordPress directory, find and open the file wp-config.php for read access with page-by-page scrolling :<\/span><\/p>\n
cat wp-config.php | more<\/pre>\n
(HINT: Hit return to scroll down line-by-line; hit Return to scroll down page by page or Q to quit.)<\/p>\n
This file contains important configuration information that you will need to talk to the database. From this WordPress configuration file, you need to\u00a0extract the database name, the database user’s name, and that user’s password. Copy this\u00a0information and paste it into a text editor on your local computer (WARNING: Don’t even thing of pressing any Windows keyboard shortcuts when using PuTTY! The current selection in PuTTY automatically ends up in your clipboard, so all you need to do is paste it somewhere in an app on your local computer).<\/p>\n
Find this line:<\/p>\n
define('DB_NAME', 'your_database');<\/pre>\n
Your database name is your_database. Now look at this line:<\/p>\n
define('DB_USER', 'dbuser_wrdp1');<\/pre>\n
Your user name is Whatever_wrdp1. How about the password? WordPress happens to store it in clear text in the very same\u00a0configuration file wp-config.php. (That’s one of the reasons why the database user should have only strictly\u00a0limited privileges and why the web server directory should only be accessible by the root user and perhaps the website owner; the user nginx should\u00a0never have the ability to start an interactive login session!)<\/p>\n
Here is your password for the database user’s access to the database system:<\/p>\n
define('DB_PASSWORD', '99123456');\r\n<\/pre>\n
This is NOT the same as the WordPress user’s password. The database password for DB_USER (99123456 in the above example) grants this\u00a0user access to the WordPress database\u00a0via the mysql client, not the WordPress frontend.\u00a0<\/span><\/p>\n
The\u00a0main administrator of WordPress is\u00a0not even a user in the MySQL sense. That’s merely a WordPress user whose account credentials happen\u00a0to be\u00a0stored in a MySQL database.<\/p>\n
Make a note of this line as well:<\/p>\n
$table_prefix = 'wp1_';<\/pre>\n
It may simplify identifying the database table later on.<\/p>\n
Here is what you need to do next.<\/p>\n
Step 4. Connect to MySQL (or MariaDB)<\/h4>\n
Connect to your database using the mysql client (MySQL and MariaDB support identical SQL syntax, so these commands will work for either one of these database systems):<\/p>\n
mysql -u dbuser_wrdp1 -p<\/pre>\n
Enter your password at the prompt (it’s\u00a099123456 in our example).<\/p>\n
This is the prompt for MySQL and MariaDB:<\/p>\n
MySQL [(none)]>\r\n\r\nMariaDB [(none)]><\/pre>\n
Whatever appears after these characters in the examples below, is the command you need to enter.<\/p>\n
Tell the mysql client which database\u00a0you want to look into (in the following command, replace the string ‘your_database’ with the actual\u00a0name of your database):<\/p>\n
MariaDB [(none)]> use your_database;<\/pre>\n
Let the mysql client\u00a0show you the tables:<\/p>\n
MariaDB [info_content]> show tables;<\/pre>\n
Depending on the table prefix set during WordPress installation, what you are looking for could be named wp_users or wp1_users, or something to that effect. Make your pick in accordance with the value of the $table_prefix from wp-config.php; that’s how you know what to look for.<\/p>\n
Adjust the name of the wp_users table for your specific scenario in the following line, then issue this command at the mysql prompt:<\/p>\n
MariaDB [info_content]> SELECT ID, user_login, user_pass FROM wp1_users;<\/pre>\n
The output of this command will show you a table that looks something like this:<\/p>\n
+----+----------------+------------------------------------+\r\n| ID | user_login | user_pass |\r\n+----+----------------+------------------------------------+\r\n| 1 | WPadmin | $4$5p21hqTn45fghskdfgjhsjhkdgtrhf\/ |\r\n+----+----------------+------------------------------------+\r\n1 row in set (0.00 sec)\r\n\r\n<\/pre>\n
Look at the second column (user_login), find the name of the WordPress user whose password you want to reset, then find that user’s ID (in our example: 1). Issue this command at the mysql prompt (pay attention to the quotation marks!!):<\/span><\/p>\n
MariaDB [info_content]> UPDATE wp1_users SET user_pass = MD5('NEWPASSWORD') WHERE ID=1 LIMIT 1; Query OK, 1 row affected (0.01 sec)\r\nRows matched: 1 Changed: 1 Warnings: 0<\/pre>\n
Congratulations, you have changed the WordPress password of the main WordPress administrator and restored access to the CMS!<\/p>\n
Enter<\/p>\n
exit<\/pre>\n
a couple of times to leave the mysql command line and end the ssh session.<\/p>\n
Navigate\u00a0to your website in a web browser and feel free so sign in.\u00a0Done!<\/p>\n
\n
If you are, by any chance, in the process of migrating WordPress,\u00a0you so need to read this: \u201eWordPress Migration the Easy Way: with the All-in-One WP Migration Plug-In<\/a>\u201c.<\/p>\n","protected":false},"excerpt":{"rendered":"
Have you locked\u00a0yourself\u00a0out of WordPress due to some upgrade or restore mishap in combination with the lack of a valid email address to restore it to?\u00a0Welcome to the Club of lucky administrators! There is a solution to this particular problem that just begs to be shouted from the rooftops: why don’t you reset your WordPress […]<\/p>\n","protected":false},"author":101011,"featured_media":850,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[17,33,6],"tags":[81,82,19,84],"yoast_head":"\nLocked out of WordPress? How to Reset Your User Password to the Admin Backend in MySQL or MariaDB - CloudInsidr<\/title>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Locked out of WordPress? How to Reset Your User Password to the Admin Backend in MySQL or MariaDB - CloudInsidr\" \/>\n<meta property=\"og:description\" content=\"Have you locked\u00a0yourself\u00a0out of WordPress due to some upgrade or restore mishap in combination with the lack of a valid email address to restore it to?\u00a0Welcome to the Club of lucky administrators! There is a solution to this particular problem that just begs to be shouted from the rooftops: why don’t you reset your WordPress […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/\" \/>\n<meta property=\"og:site_name\" content=\"CloudInsidr\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-28T12:15:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-03-22T22:14:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.cloudinsidr.com\/content\/wp-content\/uploads\/2016\/02\/checkYourMail.png\" \/>\n\t<meta property=\"og:image:width\" content=\"662\" \/>\n\t<meta property=\"og:image:height\" content=\"245\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/#website\",\"url\":\"https:\/\/www.cloudinsidr.com\/content\/\",\"name\":\"CloudInsidr\",\"description\":\"Cyber security, infotech\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.cloudinsidr.com\/content\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.cloudinsidr.com\/content\/wp-content\/uploads\/2016\/02\/checkYourMail.png\",\"width\":662,\"height\":245,\"caption\":\"Success: check your mail\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/#webpage\",\"url\":\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/\",\"name\":\"Locked out of WordPress? How to Reset Your User Password to the Admin Backend in MySQL or MariaDB - CloudInsidr\",\"isPartOf\":{\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/#primaryimage\"},\"datePublished\":\"2015-12-28T12:15:10+00:00\",\"dateModified\":\"2016-03-22T22:14:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/#\/schema\/person\/21ce63bea726ea64da1beed97e63ba84\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.cloudinsidr.com\/content\/locked-out-of-wordpress-how-to-reset-your-user-password-to-the-admin-backend-in-mysql-or-mariadb\/\"]}]},{\"@type\":[\"Person\"],\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/#\/schema\/person\/21ce63bea726ea64da1beed97e63ba84\",\"name\":\"Anna E Kobylinska\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.cloudinsidr.com\/content\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/7a3e9bd152f9d5cd41bf2b92df649857?s=96&d=mm&r=g\",\"caption\":\"Anna E Kobylinska\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/posts\/486"}],"collection":[{"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/users\/101011"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/comments?post=486"}],"version-history":[{"count":7,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/posts\/486\/revisions"}],"predecessor-version":[{"id":1040,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/posts\/486\/revisions\/1040"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/media\/850"}],"wp:attachment":[{"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/media?parent=486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/categories?post=486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cloudinsidr.com\/content\/wp-json\/wp\/v2\/tags?post=486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}