PlanetMysql.ru — информация о СУБД MySQL

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 data directory ONLY readable and writable by the user used to start/stop mysql (don’t user *nix root user to own mysql since that opens another can of worms … it’s a whole other blog post).

PlanetMySQL Voting: Vote UP / Vote DOWN

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 to add all these users to a simplenews newsletter, the proverbial light bulb came on.

A spot of theory

I have previously done some bulk simplenews subscriptions by selecting Drupal data from one database table into another. In theory, that works fine across different databases as well. By inserting selected data from one Drupal's users table into another (avoiding uid clashes) I should be able to copy all users, keeping a predictable uid, so I could then do the same for their profile nodes.

First off, export the profile content type to the new Drupal site using the CCK content export module.

The grab the schema of all the tables I would need to transpose data to. These should be the same as the tables the data will come from. In my case the tables were: users, content_type_profile, node and node_revisions. The easiest way to grab the schema is via the mysql command line tool, using this query:

MySQL [source]> SHOW CREATE TABLE users\G

The \G means the data is shown in a single column, not in a column per field, which wouldn't fit across the terminal. Just repeat this for the other tables that need to be transposed and keep the output in a text editor window or something. Basically, this is just so you can easily copy and paste the field names.

At the bottom of each schema, you'll see a line like this one:

) ENGINE=InnoDB AUTO_INCREMENT=6221 DEFAULT CHARSET=utf8

The one from the node_revisions table is the important one. The AUTO_INCREMENT value tells us the next id that will be assigned on the primary auto_increment field in that table. In this case that is the vid field. It's important, because in all likelihood there is already a vid in this table for each value below the AUTO_INCREMENT value.

If you simply copy the node data across, chances some node.nid or node_revisions.vid will clash, causing the copy to break, leaving you with a cleanup to do. They do need to be included in the copy, though, because they are the way the user, her profile node and its CCK fields are linked together. If I simply copied all the other table columns and had the database assign new uids, nids and vids, I'd have a mess of information without its relational links intact.

My table has at most 6220 node revisions, so if I start with 10000 as the nid and vid for the copied nodes, there will never be a clash. Because I already had some users on the new Drupal, I decided to restart all copied user uid from 10000 as well.

At the end of the day the user.uid, node.nid and node_revisions.vid are just automatically assigned numbers, so if a chunk of them between 6220 and 10000 are not used, that hardly matters.

Run some queries

Time to migrate some data. In the following examples I execute all queries on the target database. All source data is coming from the Drupal tables in the oldevent database. These queries all need to be run by a MySQL user with INSERT privileges on the target Drupal database and at least SELECT privileges on the source Drupal database.

First, copy the users and give them a predictable non-clashing new user.uid. I exclude user id 1, as it already exists on the new Drupal site. I knew the other user's names would not clash, so I did not need to limit the query any further.

MySQL [source]> INSERT INTO users (SELECT uid+10000, name, pass, mail, mode, sort, threshold, theme, signature, signature_format, created, access, login, status, timezone, language, picture, init, data, timezone_name FROM oldevent.users WHERE oldevent.users.uid > 1);

Now copy all profile node CCK fields, making sure the nid and vid will not clash.

MySQL [source]> INSERT INTO content_type_profile (SELECT vid+10000, nid+10000, field_profile_firstname_value, field_profile_surname_value, field_profile_position_value, field_profile_organisation_value, field_profile_phone_value FROM oldevent.content_type_profile);

Note that iIf you have multi-value fields in your user's profiles, you will also need to perform this for all content_field_profile_* tables.

Next, copy across all profile nodes:

MySQL [source]> INSERT INTO node (SELECT nid+10000, vid+10000, type, language, title, uid+10000, status, created, changed, comment, promote, moderate, sticky, tnid, translate FROM oldevent.node WHERE oldevent.node.type='profile');

And finally, copy all profile node revisions across as well:

MySQL [source]> INSERT INTO node_revisions (SELECT nid+10000, vid+10000, uid+10000, title, body, teaser, log, timestamp, format FROM oldevent.node_revisions WHERE oldevent.node_revisions.nid IN (SELECT nid FROM oldevent.node WHERE oldevent.node.type='profile'));

And that's it. I did not need to copy any of the user's roles across, so at this point I was done. I migrated just under 1500 users and all revisions of their profiles from one Drupal to another in approximately two minutes.

Inevitable caveat!

If your users are set up with profile pictures, you'll need to make sure the users.picture field points at the right directory under files on the new Drupal site. You can do a quick search & replace query to update the path using REPLACE().

MySQL [source]> UPDATE users SET picture=REPLACE(picture, 'old-sub-string', 'new-sub-string');

Note that the picture filename will not match the user's uid anymore. If you use a tool like mmv or a shell script to rename the picture files, be sure to also update the field in the database. (Hint: generate a new file path via CONCAT() , using the user.uid field)

PlanetMySQL Voting: Vote UP / Vote DOWN

As we have seen for other events, the MySQL community has been invited to attend and participate in conferences organized by the Oracle User Groups. After the past, present and future events in the United States, now we start with Europe. There is a MySQL Track at the DOAG 2010 Conference, the main event of the German Oracle User Groups, and the CfP expires on July 10th.

The event is of course important for German speakers, but English speakers are also accepted.

As the other events in the US, this is a good occasion for MySQL users to get acquainted with the independent Oracle user group organization, and find common business needs. There are many MySQL users among the Oracle User Group members, and much curiosity about this small database that powers the Internet.

If you want to get a talk at this conference, feel free to submit a proposal. Or simply mark the dates: November 16th to 18th.

MySQL users, don't be shy!

Follow Paul McCullagh's example, and get ready to explore a yet uncharted but promising territory.

PlanetMySQL Voting: Vote UP / Vote DOWN

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:

uid	participated	correct	wrong	created	updated
6508097	3	6	1	2010-02-26 06:51:01	2010-02-26 06:51:01
14535811	2	4	2	2010-02-26 06:51:01	2010-02-26 06:51:01

For simplicity here is only 2 users. But It may contain 1 to unlimited users.  So to get rank each time you’ve to check what the status of the user, how many correct answers each user solved. For better performance you can do caching. Now I’m showing how a user will get rank from this table.

Long time ago, I saw a solution of a programmer. Look how he did the solution

function getUserRank($userId){
   // no limit they did the query to get all result
   $sql     =  "SELECT * FROM quiz_user ORDER BY correct DESC";
   $result =  mysql_query($sql);
   $rows  =  '';

   $data = array();
   if (!empty($result))
        $rows      =  mysql_num_rows($result);
   else
        $rows      =  '';

    if (!empty($rows)){
        while ($rows = mysql_fetch_assoc($result)){
                $data[]   = $rows;
        }
    }

   // now they did a php loop to get the user rank by user id
   $rank = 1;
   foreach($data as $item){
       if ($item['uid'] == $userId){
           return $rank;
       }
       ++$rank;
   }
   return 1;
}

Just look at the as usual or brute force solution. This is simply a fucky code. To check each user’s rank you first retrieve all records from mysql query then using a loop you check what the user’s rank. So if the table contains 10000 users and suppose 1000 users concurrently playing this game, then just think how much time will take this function and thus this will decrease performance of your application.

So the question is how could you write the optimal solution for this problem. If you make rank using mysql query then it would be more optimal and faster for your application. Here I’m showing how could I solve the problem:

The MySQL query that solved my problem is here:

SET @rownum := 0;
SELECT rank, correct FROM (
                    SELECT @rownum := @rownum + 1 AS rank, correct, uid
                    FROM quiz_user ORDER BY correct DESC
                    ) as result WHERE uid=xxxxxxxx

Look carefully the solution. If you run this query directly in mysql either using phpmyadmin or other mysql admin tools, then you’ll get the user’s ‘xxxxxxxx’ rank in quiz. Let’s explain the query:

SET @rownum := 0;

In mysql you can assign variable and can use later. So at first I declare a variable rownum and asing 0 as value. To declare a variable in mysql you have to use ‘@’ sign before variable name.

Then I used a subquery within query. Look the subquery. This is the query that actually makes the rank and returns result as a table. This is a simple query where I’ve just added “@rownum := @rownum + 1 AS rank” so you assigned rank in each row.

SELECT @rownum := @rownum + 1 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC

So after this query’s result now I check what the user’s rank

SELECT rank, correct FROM result WHERE uid=xxxxxxxx;

where result is

SELECT @rownum := @rownum + 1 AS rank, correct, uid FROM quiz_user ORDER BY correct DESC;

So the complete query is:

SET @rownum := 0;
SELECT rank, correct FROM (
                    SELECT @rownum := @rownum + 1 AS rank, correct, uid
                    FROM quiz_user ORDER BY correct DESC
                    ) as result WHERE uid=xxxxxxxx

Now look the php solution:

function getUserRank($userId){
        $sql1       = "SET @rownum := 0";

        $sql2       =   "SELECT rank, correct FROM (
                        SELECT @rownum := @rownum + 1 AS rank, correct, uid
                        FROM quiz_user ORDER BY correct DESC
                        ) as result WHERE uid=$uid";

        // here model.php is a class for database connectivity
        include_once "model.php";
        global $dbconfig; //configuration of database that I assigned in config file
        $md = new Model($dbconfig);

        $md->connectDb();
        mysql_query($sql1); /*as mysql_query function can execute one query at a time */
        $result = mysql_query($sql2);
        $rows = '';
        $data = array();
        if (!empty($result))
            $rows      =  mysql_num_rows($result);
        else
            $rows      =  '';

        if (!empty($rows)){
            while ($rows = mysql_fetch_assoc($result)){
                $data[]   = $rows;
            }
        }

        $md->closeDb();

        //rank of the user
        if (empty($data[0]['rank']))
            return 1;
        return $data[0]['rank'];
}

As you make the ranking in mysql, so this is much faster solution than the ordinary solution. By this way I solved the problem and get the user’s rank in my quiz. You can use this technique for any game or application. For more better performance you can use caching. Hope this article will help you for better understand.

PlanetMySQL Voting: Vote UP / Vote DOWN

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 can change this with the -n option to strings).

$ cd /data/mysql/
$ strings -f */mysql/user.MYD | grep username
instance5/mysql/user.MYD: username*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
instance7/mysql/user.MYD: username*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
$ 

While writing this tidbit up, I realized I could have easily run grep and gotten the same results:

$ grep username */mysql/user.MYD
Binary file instance5/mysql/user.MYD matches
Binary file instance7/mysql/user.MYD matches

So do not underestimate the power of basic tools such as strings and grep. They can really help you! (I often use strings mysql/user.MYD to see if a particular mysql user has been set up, especially when I cannot seem to login. This way I can know whether or not I am typing an incorrect password, or if the user just does not exist at all.)

PlanetMySQL Voting: Vote UP / Vote DOWN

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 [...]
PlanetMySQL Voting: Vote UP / Vote DOWN