Tag Archives: SET

  • Home
  • Posts tagged "SET"

Recover or reset mysql root password when locked out

/etc/init.d/mysql stop

[ ok ] Stopping mysql (via systemctl): mysql.service.
[[email protected]] ~ #mysqld_safe --skip-grant-tables --skip-networking &
[1] 25335
[[email protected]] ~ #160619 00:45:55 mysqld_safe Can't log to error log and syslog at the same time. Remove all --log-error configuration options for --syslog to take effect.
160619 00:45:55 mysqld_safe Logging to '/var/log/mysql/error.log'.
160619 00:45:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

mysql mysql -uroot
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.49-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>UPDATE user SET password=PASSWORD("MYNEWPASSWORD") WHERE User="root";

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye

Manually reset WordPress master password

It may have happened to many of us who maintain multiple wordpress installations with very secure passwords. One day, we just forget our passwords and then realize that the email setup for the user is invalid, and we cant reset the password through email.

If it happens, there is a technique to reset the password through mysql.

Login to the Phpmyadmin control panel.

Identify the username of the account we’re about to reset from the wp_users table.

Then execute the following sql code in Phpmyadmin:

UPDATE `wp_users`
SET `user_pass` = MD5('newpassword')
WHERE `user_login` ='username_to_reset_for'
LIMIT 1;

You may need to modify the quoted strings for your database.

Voila, now you can login with the new password.

Another GUI based method for PhpMyadmin and other ways to reset password are described in the WordPress Codex.

Search and replace MySQL databases (Replace field)

This article is Part 1 of MySQL Search and Replace. Part 2 deals with replacing a string within a bigger string which is the value of the field, and replace it with just the smaller string replaced in the bigger string.

How to search and replace the value of a single field within a table in MySQL database:

 

Syntax:

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");
 To change a field named uid in Table tablename, do this:
UPDATE `tablename` SET `uid`= replace(`uid`,"155","1");
In MyBB, these are the tables which need to be searched and replaced to fix a wrong uid issue:
posts
threads
I needed to do this because because of merging my admin account with another account, I lost Super admin permissions in MyBB. This was the fix, changing the newuid to uid 1 of Superadmin
To reset the MyBB Plugin uploader password:
UPDATE `mybb_pluginuploader` SET `files` = '0e2eced896c9c8ba9bb0bdc678f19c53', `version` = '6iEMPlPpFv' WHERE `name` = '_password';

Then the password will be “test” and you can change it with the ‘Change password’ link in the Plugin Uploader.

Adding default icons to post without set icons:

UPDATE `mybb_posts` SET `icon`= replace(`icon`,"0","6");

Changing the default post edit reason:

UPDATE `mybb_posts` SET `markedit_reason`= replace(`markedit_reason`,"Improve grammar","Added more information");

Switching a comment author:
The table wp_4_comments has a field (row) called comment_author, which had entries ‘droidzone’ which needed to be replaced by ‘joelgm’

UPDATE `wp_4_comments` SET `comment_author`= replace(`comment_author`,"droidzone","joelgm");

Another case where Google blacklisted my comment URL (which made no sense), as part of complete blacklist of my domain. It causes a loop in which the domain continued to be blacklisted because my author url was part of the blacklist. I had no way but to globally search and replace the author url and the author id. 😉

UPDATE `wp_4_comments` SET `comment_author_url`= replace(`comment_author_url`,"http://www.droidzone.in/blog/author/droidzone/","http://www.droidzone.in/blog/author/droidzone/joelgm");

Globally change the “Member only” to “Anyone can download”, in WordPress Download Monitor plugin. I tried finding out what files are Member only. The plugin has a view which can display that. Next, I searched for the filename in the database and found that these resided in a table ‘wp_4_download_monitor_files’, in a field ‘members’. If the value is 1, it is member only. If 0, then anyone can download.

UPDATE `wp_4_download_monitor_files` SET `members`= replace(`members`,"1","0");

 

PhpMyadmin MySQL Search and replace

For some weird reason, MyBB’s default edit reason for posts is “Improve grammar”, which makes no sense for an Android forum! So I had to change the string for a lot of users. Manually editing the database is unnecessary!

 

You can exec the following operation in the SQL tab of MySQL:

 

UPDATE `mybb_posts` SET markedit_reason = replace(markedit_reason, “Improve grammar”, “Unspecified reason”);

 

So, format is:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");[/code]


[/code]