Tag Archives: UPDATE

  • Home
  • Posts tagged "UPDATE"

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

Install Pydio on webserver

Get it from here: http://sourceforge.net/projects/ajaxplorer/files/pydio/stable-channel/

Eg:

wget -O pydio-core-5.0.4.tar.gz http://sourceforge.net/projects/ajaxplorer/files/pydio/stable-channel/5.0.4/pydio-core-5.0.4.tar.gz/download

Extract the file to a chosen directory.

tar xf pydio-core-5.0.4.tar.gz
mv pydio-core-5.0.4 /var/www/explore
chown -R www-data /var/www/explore/data/

Install mysql-server if not already installed.

apt-get install mysql-server-5.5

Install mcrypt, php5-gd, php5-mysql:

apt-get install php5-mcrypt php5-gd php5-mysql

Now, add “AllowOverride All” to /etc/apache2/sites-available/default

<Directory "/var/www/explore">
                    AllowOverride All
 </Directory>

Restart apache2:

service apache2 restart

Create a mysql database, then a user and assign a password for the user on the database.

mysql -u root -p

And the following on the mysql shell.

CREATE DATABASE IF NOT EXISTS pydiodbase;
CREATE USER 'pydiouser'@'localhost' IDENTIFIED BY 'yourpassword';
GRANT ALL PRIVILEGES ON pydiodbase.* TO 'pydiouser'@'localhost';
FLUSH PRIVILEGES;

To change password of the dbuser:

UPDATE mysql.user SET Password=PASSWORD('yourpassword') WHERE User='pydiouser';

Then, access the web install at http://yourip/explore, and follow the onscreen prompts.

Your files appear in ./data/files/

Troubleshoot:
Error message during installation:

It seems that your data/ folder is not correctly protected, and that subfolders (like the data/cache/ folder) are web-accessible. If you are using Apache, make sure the AllowOverride All option is active for your current directory. If you are running Windows IIS, you must manually add a RequestFiltering/HiddenSegments configuration to prevent web access to these folders. If you have defined a different AJXP_DATA_PATH pointing outside the webfolder, you can ignore this warning.

Solution:
Check the default apache config:


ServerAdmin [email protected]
DocumentRoot /var/www

Options FollowSymLinks
AllowOverride None


Options Indexes FollowSymLinks MultiViews
AllowOverride None
Order allow,deny
allow from all

ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/

AllowOverride None
Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
Order allow,deny
Allow from all

ErrorLog ${APACHE_LOG_DIR}/error.log
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
LogLevel warn
CustomLog ${APACHE_LOG_DIR}/access.log combined

In the code:


Options Indexes FollowSymLinks MultiViews
AllowOverride none
Order allow,deny
allow from all

change none to All:

Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all

Warning about locale not set:

dpkg-reconfigure locales
Generating locales...
  en_IN.UTF-8... up-to-date
Generation complete.

Now, 

grep -in AJXP_LOCALE conf/bootstrap_conf.php
30: * define("AJXP_LOCALE", "");
32://define("AJXP_LOCALE", "en_EN.UTF-8");
33://define("AJXP_LOCALE", "");

And edit line 33 of the file to change it to:

33:define("AJXP_LOCALE", "en_IN.UTF-8");

Warning: PHP Output Buffer disabled
You should disable php output_buffering parameter for better performances with Pydio.

Solution:
Check php value:

#grep -in output_buffering /etc/php5/apache2/php.ini
126:; output_buffering
245:output_buffering = 4096

Edit it to:

245:output_buffering = Off

Restart apache2 when you’re done.

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.

Assign posts to another author in WordPress

First find the USER ID from wp_users table.

Execute the following SQL command from the Database:

UPDATE wp_posts SET post_author = '1' WHERE post_author = '10';[/code] 


switches the posts authored by user No 10, to ownership by user No 1

Switching UIDs for a user

As zitronenroellchen told us, there is a much simpler way to do this.
First identify the UID of the user you want to change. Then get the target UID. For me, I wanted to replace 155 by 1, the default value for Superuser.

Search the database and identify the tables where UID field is present.

For any table in the list
Change over the UID of the destination if it exists.

UPDATE `mybb_threads` SET uid=999 WHERE uid = 1;

UPDATE `mybb_threads` SET uid=1 WHERE uid = 155;

UPDATE `mybb_posts` SET uid=999 WHERE uid = 1;

UPDATE `mybb_posts` SET uid=1 WHERE uid = 155;[/code]


And finally, replace the user in mybb_users:


UPDATE `mybb_users` SET uid=1 WHERE uid = 155;[/code]


Of course, an easier way is to add the user to the list of SuperAdmins


Just discovered that, to retake SuperAdmin permissions for a user, there is a much easier solution, to edit config.php


$config['super_admins'] = 'UID1,UID2'; 


Plugin uploader issues once again!


DROP TABLE `mybb_pluginuploader`;

alter table `mybb_users` drop column `pluginuploader_key`;[/code]

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]