Mysql

From Fxp Wiki

Passwords - Users

Mysql 4

 
 
USE mysql;
SELECT host, user, password FROM user;
DELETE FROM user WHERE user='';
DELETE FROM user WHERE host='%';
 
 
UPDATE user SET password=PASSWORD('NEWPASSWORD') WHERE host='localhost' AND user='root';
 
FLUSH privileges;
 

Or

 
UPDATE mysql.user SET Password = PASSWORD('Montaigne') WHERE User = 'root';
 
FLUSH privileges;
 

Mysql5

 
UPDATE mysql.user SET Password = PASSWORD('newpwd') WHERE Host = 'some_host' AND User = 'some_user';
FLUSH PRIVILEGES;
 

Or

 
UPDATE mysql.user SET Password = OLD_PASSWORD('Montaigne') WHERE User = 'root';
 
FLUSH privileges;
 

Dump to older version

 
 
shell> mysqldump --create-options --compatible=mysql40 db_name > dump_file
 

Multiple instances

• 1) créer un fichier .conf avec les localisation du server

c:\mysql\myopts.conf
*******
[mysqld]
datadir = C:\mysql\data
port = 3306

• 2)démarrer le server en ligne de commande avec:

mysqld-nt --defaults-file=C:\mysql\myopts.conf

Special queries Requêtes spéciales

Touchgraph 1

 
 
SELECT 
cur_id,
cur_namespace,
cur_title,
cur_is_redirect 
FROM cur 
WHERE cur_namespace=14 
OR cur_namespace=0 
ORDER BY `cur_id` 
DESC;
 
 

Touchgraph 2

 
 
SELECT 
cfrom.cur_id AS bfrom, 
cto.cur_id AS bto
FROM categorylinks
INNER JOIN cur AS cfrom 
ON (cfrom.cur_id = categorylinks.cl_from AND cfrom.cur_namespace=14)
INNER JOIN cur AS cto 
ON (cto.cur_title = categorylinks.cl_to AND cto.cur_namespace=14);
 
 

String replacement Remplacement chaîne de caractères

 
 
UPDATE text SET old_text = REPLACE(old_text,'<span class="dc_creator">','<span class="dc_creator">');
 
 

MYSQL XML

A page containing an XML fragment (well formed). This feature is available from mysql 5, not for earlier versions.

 
<opus>
<author type="name">Shakespeare</author>
<title lg="en">Hamlet</title>
<ref type="act">II</ref>, <ref type="scene">I</ref> 
<text><speaker>Mercutio</speaker>
<l>I'have seen the ghost</l>
<l>I fear its presence</l>
</text>
</opus>
 

 
SELECT `old_id` , `old_text` , `old_flags` , COUNT( DISTINCT old_text ) AS t1, ExtractValue(old_text, '/opus') AS t2
FROM `wikidbtest`.`text`
WHERE `old_text`
LIKE '%Hamlet%'
GROUP BY 'old_id'
 

Links Liens

• tutorial on myslq joins