Three ways to recover a root user password:

The order of solutions here under gets more creative on the way down :)

1. obviously, before starting messing around check my.cnf or scripts for passwords entries, then try home directories for password files
2. secondly – can you restart mysql? if yes, restart with –skip-grant-tables, log into mysql, change your password and restart without –skip-grant-tables
3. third option – (on linux / unix ONLY)
If you haven’t found the password anywhere and can’t afford to restart your mysql.

cd data/mysql
cp -rp user.MYD bck_user.MYD_`date +%Y%m%d`
cp -rp user.MYD /tmp/user.MYD
vi /tmp/user.MYD #(edit the hashed passwords next to root*)
cp -rp /tmp/user.MYD user.MYD
sudo kill -HUP `pidof mysqld`

Note that the latter method of recovering a root password CAN be easily used maliciously leaving no trace! The only way to avoid such an attack is to make the …

Disclaimer:

This post is for educational purposes only and no responsibility will be taken if you execute any of the commands. You mess it, you fix it!

Replacing a password for a user on MySQL can be done in at least four ways. Three ways at least.

1. set password for ‘user’@'host’=password(‘abc’);

2. grant usage on *.* to ‘user’@'host’ identified by ‘abc’;

3. update mysql.user set password=password(‘abc’) where user=’user’ and host=’host’;

mysql Wed Mar  9 14:27:17 2011 > set password for 'dc'@'%' = password('d');
Query OK, 0 rows affected (0.00 sec)

mysql Wed Mar  9 14:27:39 2011 > show grants for 'dc'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for dc@%                                                                                   | …

For some time now I've been working on a Drupal site that consists mainly of scraped content from a proprietary, ASP based CMS from the late nineties. The Simple HTML Dom Parser, used from within a drush script, has been invaluable. It made scraping the old site content and importing it as Drupal nodes a relative breeze. (No access to the database used by the CMS, boo!)

Part of setting up the new site is importing users and their content profile nodes from a different Drupal site, that was setup a year or two ago to manage an event.

I had hoped there would be a way for me to export these users and their profile nodes from one Drupal to the other, but though I found modules to export one or the other, I might still end up with profile nodes that were no longer related to their users. Of course, that's pretty useless.

When I remembered I was also supposed …

Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.

Here is the table structure:

CREATE  TABLE IF NOT EXISTS `quiz_user` (
`uid` BIGINT UNSIGNED NOT NULL ,
`participated` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`correct` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`wrong` SMALLINT UNSIGNED NULL DEFAULT 0 ,
`created` DATETIME NULL ,
`updated` DATETIME NULL ,
PRIMARY KEY (`uid`) )
ENGINE = InnoDB

This table contains user records of quiz game. Here is an examples:

A broken VIEW was caused by the view’s definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.

So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:

instance 1 has a datadir of /data/mysql/instance1
instance 2 has a datadir of /data/mysql/instance2

Now, the unix tool strings can be used against many types of files. In particular, though, you can use strings on the mysql/user.MYD file to see the username, host, and password hash. (note that strings only shows strings longer than 3 characters, so if your host or username is 3 characters or less, it will not show up in the output of strings. You …

Just a small note to advise that Securich reached 0.1.4.
Some new tools include:
* Added Password complexity
* Enhanced `set_password` – Old password is now necessary to replace it by a new one
* Enhanced Revoke privileges to accept regexp
* Added Block user@hostname on a database level
* Added Creation of reserved usernames
* Added Help stored procedure displays help for each stored proc
* Enhanced `create_update_role` to include the removal of privilages from roles
* Enhanced `grant_priveleges` on `alltables` for a database without tables would terminate with an error instead of gracefully (now fixed)
* Added Restore user@hostname on a database level
* Removed ’show warnings’ from sql installation

The database design using workbench is also available in the db folder (for easier understanding of what lies …

Several people commented on our last blog post or contacted us via our Twitter account, asking questions and raising concerns about migrating the MySQL user group organization to FaceBook. It seems like there is some confusion that we would like to address: you are not required to move to FaceBook. This was just a recommendation, you are of course free to choose whatever service you prefer to manage your MySQL user group. In fact, you are of course welcome to continue using Meetup.com for this! However, be advised that you will have to take care of any occurring fees by yourself from now on, as the sponsorship agreement between MySQL and Meetup.com was not renewed.

And if Facebook is not your cup of tea, there are other services that provide similar functionality. We've tried …