All posts tagged MySQL

Si fem backup de dades MySQL amb mysqldump, necessitarem un usuari que pugui accedir a les dades únicament per a executar el backup. Els privilegis mínims que necessita són:

  • SELECT: permet la lectura dels registres.
  • LOCK TABLES: permet el bloqueig de taules, per tal de garantir la consistència de les dades guardades.
  • SHOW DATABASES: permet conèixer el directori de bases de dades existent.
  • SHOW VIEW: permet veure la definició de les vistes.
  • EXECUTE: permet executar stored procedures, necessaris per a SHOW FIELDS FROM de les vistes.
  • RELOAD: permet l’ús de FLUSH per a la neteja de la memòria cau (cache).

Així doncs, amb l’ordre GRANT crearem l’usuari de backup amb els permisos adhients:

GRANT SELECT, LOCK TABLES, SHOW DATABASES, SHOW VIEW, EXECUTE, RELOAD ON *.* to 'backup'@'host'
IDENTIFIED BY 'password';

El llistat complert de privilegis el trobarem a la documentació de MySQL.

A partir de la versió 4.1 de MySQL va canviar el mètode de creació de contrasenyes, la funció PASSWORD() retorna una cadena molt més gran i segura amb una encriptació unidireccional.

Si des de PHP volem generar una contrasenya validable des de MySQL ens dotarem d’aquest mètode:


/**
* Retornem una cadena entenedora per la funció PASSWORD()
* de MySQL
*
* @see http://php.net/manual/en/function.sha1.php
* @see http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password
* @param string $pwd
* @return string
*/
protected function mySqlPassword($pwd)
{
return "*" . strtoupper(sha1(sha1((string) $pwd,true)));
}

Amb aquest senzill script obtindrem una còpia de seguretat de tots els schemes MySQL un per arxiu emprant mysqldump i arxivant-los tots en un arxiu comprimit tipus .tar.gz.

#!/bin/bash

# Filtrem les bases de dades per a obtenir únicament el nom
for db in `mysql -u admin -pxxx -e "show databases;" | grep "| " | sed 1d`
do
##  Fem el volcat de les dades de la base de dades
    echo $db
    mysqldump -u admin -pxxx --routines --skip-extended-insert $db > dump.$db.sql
done
tar -zcpf sql-byDb.tar.gz dump.*
##rm -vf dump.*

Hem de substituir admin per l’usuari amb prous privilegis per a fer mysqldump i xxx per la contrasenya d’aquest.

Fem servir –routines per afegir la creació d’stored procedures si s’escau i –skip-extended-insert per a fer un insert per registre i així facilitar la trobada de diferències entre còpies.

Captura-MySQL-Query-Browser

De vegades, ja sigui per seguretat o per necessitat de saltar-se proteccions d’accés a xarxa de servidors MySQL, necessitem accedir-hi al servidor a través d’un tunel assegurat amb ssh.

Seguin les passes indicades per Marion Bates a l’article MySQL ssh tunnel Quickstart crearem ràpidament una connexió per ssh cap a un servidor MySQL.

Només cal picar en una terminal:

ssh -L 3307:nom.servidor.mysql:3306 usuari@nom.servidor.ssh

Això obre un tunel a localhost:3307 que apunta al servidorMySQL:3306 via connexió ssh.
Hem indicat el port local 3307 pq a la màquina on fem això tenim un servidor MySQL de proves.
Si la màquina oberta a ssh és la mateixa que te el servidor MySQL, nom.servidor.mysql serà localhost.
Aquesta connexió la deixarem oberta, mentre estigui activa la terminal, estarà actiu el tunel.
Per a connectar-nos, obrim el MySQL QueryBrowser i indiquem host 127.0.0.1, port el 3307 i un usuari i contrasenya vàlid per a connectar-se a la base de dades MySQL.

Oracle te un component força interessant per a la gestió de comptadors. És l’objecte SEQUENCE.

Per obtenir el següent valor de la seqüència executarem
SELECT nom_sequencia.nextval FROM DUAL;

A MySQL podem muntar un workarround per a emular aquesta característica d’Oracle creant una taula i dues funcions pròpies.

Creem una taula anomenada ubqSequencia on el nom de la seqüencia és clau primaria:
CREATE TABLE `ubqSequencia` (
`sqId` varchar(64) NOT NULL COMMENT 'Nom de la seqüència',
`sqCnt` bigint(20) UNSIGNED NOT NULL COMMENT 'Comptador de la seqüència',
`sqInc` int(11) NOT NULL default '1' COMMENT 'Increment per omissió de la seqüència',
`sqObs` longtext COMMENT 'Observacions',
PRIMARY KEY (`sqId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Comptadors emulem create sequence a Oracle'

Creem una funció anomenada ubqSequenciaNextVal que li passem el nom de la seqüència i ens retorna el comptador un cop incrementat. És l’equivalent a la pseudo columna NEXTVAL d’Oracle per a objectes SEQUENCE.
DELIMITER $$

DROP FUNCTION IF EXISTS `ubqSequenciaNextVal`$$
CREATE FUNCTION `ubqSequenciaNextVal`(psqId CHAR(64)) RETURNS bigint(20)
BEGIN

insert into ubqSequencia (sqId, sqCnt, sqInc) values(psqId,1,1)
on duplicate key update sqCnt = sqCnt + sqInc;

RETURN (select sqCnt from ubqSequencia where sqId = psqId);

END$$

DELIMITER ;

Aquesta funció inicialitza sempre la seqüència que em passat com a paràmetre. En cas de que salti la clau duplicada, augmenta el comptador amb l’increment establert a la mateixa taula pel mateix comptador, per omissió +1.

I la funció ubqSequenciaCurrVal que retorna el valor actual del comptador. L’equivalent a la pseudo columna CURRVAL d’Oracle pels objectes SEQUENCE.
DELIMITER $$

DROP FUNCTION IF EXISTS `ubqSequenciaCurrVal`$$
CREATE FUNCTION `ubqSequenciaCurrVal`(psqId CHAR(64)) RETURNS bigint(20)
BEGIN

RETURN (select sqCnt from ubqSequencia where sqId = psqId);

END$$

DELIMITER ;

Un dels aspectes positius de tenir els comptadors a nivell de base de dades i no per lògica de programes, és que podem actualitzar els comptadors en MySQL des d’instruccions massives. Per exemple:


update PROVA set nouComptador = ubqSequenciaNextVal('XXYYZZ');

Actualitzant el camp nouComptador de tots els registres de la taula PROVA amb la seqüència XXYYZZ. Les proves realitzades han consumit 0.0946 segons en fer la seqüència de 414 registres en un update massiu.

Certifiquem que el comptador ha quedat actualitzat

mysql> select ubqSequenciaCurrVal('XXYYZZ');
+-------------------------------+
| ubqSequenciaCurrVal('XXYYZZ') |
+-------------------------------+
| 414 |
+-------------------------------+
1 row in set (0.00 sec)

I mostrem com afegim un

mysql> select ubqSequenciaNextVal('XXYYZZ');
+-------------------------------+
| ubqSequenciaNextVal('XXYYZZ') |
+-------------------------------+
| 415 |
+-------------------------------+

Save MySQL!

Categories: MySQL
Comments: No
FBGrpLgo

Michael Widenius, fundador i primerenc desenvolupador de MySQL ens informa de que s’ha creat una campanya per a salvar la llicència GPL del MySQL.

La compra de SUN per Oracle, pot fer perillar el desenvolupament del MySQL, pedra angular de la InterNet que coneixem avui en dia.
La majoria d’aplicacions a InterNet còrren en un clàssic LAMP: Linux, Apache, MySQL i PHP, i un canvi en el model de llicència de MySQL seria catastròfic per al desenvolupament de la xarxa, doncs el fet de poder tenir un entorn de programació totalment OpenSource aporta un munt d’aplicacions al consum de la xarxa de xarxes.
És important donar suport a aquesta petició abans del 4 de gener del 2010:

Per a importar un arxiu de dades separades per comes (CSV) a una taula MySQL, emprarem la instrucció LOAD DATA INFILE:

load data local infile 'categories.csv'
into table categories
fields terminated by ','
enclosed by '"'
lines terminated by "n"
(catCodi, catDesc);

Tots els fulls de càlcul permeten la exportació a CSV, pel que amb aquesta senzilla instrucció podrem importar ràpidament dades a MySQL.