reset wordpress admin password using MySQL

If you wish to reset the password from cli, using MySQL prompt, use the following commands:

Login to server via ssh and go to MySQL prompt by entering the following in the cli.

$mysql

Use the corresponding database for the wordpress install. You can locate it from the wp-config.php file.

$use wordpress_dbname;

Check for table wp_users table.

$show tables;

Use the following to find the details of the table.

$SELECT ID, user_login, user_pass FROM (table_name);

Now update the password using the following command.

$UPDATE (name-of-table) SET user_pass=”(MD5-string_of_password)” WHERE ID = (accocunt_id_number);

For the latest MySQL versions, you can generate the MD5 using the MySQL.

$UPDATE `wp_users` SET `user_pass` = MD5(‘new_password’) WHERE `wp_users`.`user_login` = “admin_username”;

reset wordpress admin password using phpMyAdmin

First, log into phpMyAdmin and select the database associated with your WordPress installation. You can find it from the wp-config.php file. Once in your database, select the wp_users table.

Once the table is loaded, look for the username you had chosen while installing WordPress. Assuming the user name is admin in this scenario. Click on the edit icon, the little yellow pencil icon located next to the admin entry.

Look for the row named user_pass and make the following changes:

Change the Function field to MD5. Under the Value field, enter your new password and click on Save Changes.

Now you have successfully changed your WordPress password and reset the WordPress admin password. 🙂

Dump Mysql Tables

Often you have encountered problems while importing large databases via phpmyadmin. In that case, you can dump the mysql tables by using the following command.

for T in `mysql -N -B -e ‘show tables from database_name’`; do echo $T;    mysqldump database_name  $T > database_name_$T.gz ;    done

Now you can restore the tables one by one through phpmyadmin.

Change MySQL database collation

You can change database collation from the WHM/cPanel.

1. Enter your WHM/cPanel and select phpMyAdmin.

2. Select the database you wish to manage.

3. Click on the Operations tab in the top menu of your phpMyAdmin

4.  At the bottom of the page you will see the collation option. Select a collation from the drop down menu and click on the Go button.

You are all set.