Crear usuario con minimos privilegios para realizar respaldo de MySQL

Este post es complemento del script para realizar respaldos mediante archivos crontab. Pero se puede utilizar de forma manual para realizar los respaldos.

1. Iniciar mysql y crear usuario

$ sudo mysql -u usuario -p 
mysql> CREATE USER usuario_respaldo@localhost IDENTIFIED BY "MiClave";

2. Asignamos los privilegios

mysql> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO usuario_respaldo@localhost;

Algunos privilegios no estan disponibles en versiones anteriores de MySQL:

• EVENT, este privilegio es requerido para crear, modificar, eliminar o ver eventos del EVENT SHEDULER. Este privilegio fue agregado en MySQL 5.1.6
• TRIGGER, este privilegio habilita las operaciones de los disparadores. Este privilegio fue agregado en MySQL 5.1.6
• REPLICATION_CLIENT, este privilegio habilita el uso de SHOW_MASTER_STATUS y SHOW_SLAVE_STATUS. En MySQL 5.1.6 y versiones superiores habilita ademas el uso de la sentencia SHOW_BINARY_LOGS

3. Agregamos el privilegio para bloquear las tablas

mysql> GRANT LOCK TABLES ON *.* TO usuario_respaldo@localhost;

4. Probamos el respaldo de una base de datos
 
Nos salimos de mysql y probamos el comando mysqldump

$ mysqldump -u usuario_respaldo -p --databases dbmibase > dbmibase.sql

Si todo fue correcto en nuestro directorio debe existir nuestro respaldo dbmibase.sql.

Actualizar diferentes filas con una sola consulta en MySQL

Hace poco tiempo tuve la necesidad de actualizar varios registros a la vez, lo mas sencillo es crear una consulta y repetirla n veces de acuerdo a la cantidad de registros que deseo actualizar, he aquí la solución sencilla:

UPDATE Directorio SET Orden = 1 WHERE DirectorioId = 1021;
// Ejecutarla
UPDATE Directorio SET Orden = 0 WHERE DirectorioId = 1022;
// Ejecutarla
UPDATE Directorio SET Orden = 2 WHERE DirectorioId = 1023;
// Ejecutarla

Esto funcionaria sin problemas, sin embargo al tener mas registros esto consumiría demasiados recursos de nuestro servidor porque se ejecutarían n sentencias, la opción es simplificar todo a una sola consulta usando CASE, WHEN y THEN. A continuación como quedaría nuestra consulta:

UPDATE Directorio
   SET Orden = CASE DirectorioId
      WHEN 1021 THEN 1
      WHEN 1022 THEN 0
      WHEN 1023 THEN 2
   END
WHERE DirectorioId IN (1021, 1022, 1023) 

De esta forma nuestro servidor ejecutara una sola consulta. En este caso que vimos solo actualizamos un campo pero también es posible actualizar varios campos a la vez, he aquí el ejemplo:

UPDATE Directorio
    SET Orden = CASE DirectorioId
        WHEN 1021 THEN 1
        WHEN 1022 THEN 0
        WHEN 1023 THEN 3
    END,
    SET DepartamentoId = CASE DirectorioId
        WHEN 10 THEN 11
        WHEN 12 THEN 21
        WHEN 13 THEN 11
    END
WHERE DirectorioId IN (1021, 1022, 1023)

Ahora solo un poco de ingenio para incluirlo en nuestro código PHP ya sea con sentencias de control FOR, DO o While. Espero haya sido de utilidad este post.

Mostrar y Revocar privilegios de MySQL

Hace unos días me encontré en la necesidad de revisar que privilegios tenían los usuarios de MySQL, así que me di a la tarea de investigarlo y les comparto una breve guía de como realizarlo.

1. Mostrar privilegios de un usuario
Ingresamos a nuestro servidor MySQL como ya lo hemos hecho anteriormente y solo bastara con escribir el siguiente comando:

mysql> SHOW GRANTS FOR miUser@localhost;

y se nos desplegara la información de los privilegios de este usuario

GRANT USAGE ON *.* TO 'miUser'@'localhost' IDENTIFIED BY PASSWORD '*519FB...'
GRANT SELECT, INSERT, UPDATE, DELETE ON `dbDatos`.* TO 'miUser'@'localhost'
GRANT ALL PRIVILEGES ON `miBase`.* TO 'miUser'@'localhost' 

2. Revocar privilegios
Para revocar privilegios se emplea la función REVOKE, su sintaxis es muy parecida a la de GRANT, he aquí la forma de utilizarla.

mysql> REVOKE ALL ON miBase.* FROM miUser@localhost

Este comando quitara todos los privilegios sobre todos los elementos de la base de datos miBase al usuario miUser.

Con REVOKE se pueden quitar privilegios sobre elementos en particular de las bases de datos. Su sintaxis de acuerdo al manual oficial de MySQL es:

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

Para asignar privilegios y borrar usuarios, puedes consultar el siguiente enlace:
http://mgermano.blogspot.mx/2014/04/crear-y-borrar-usuarios-de-mysql-por.html

Sensibilidad de mayusculas y minusculas en nombres de tablas MySQL

Hace poco me encontré con un problema al pasar una base de datos de Windows a Linux, el problema fue que en Windows los nombres de las bases de datos y las tablas no son sensibles a mayúsculas y minúsculas, una posible solución seria manejar todo en minusculas para que no exista ese problema, pero sabemos que eso, sobre todo si se tiene un estandar de base de datos, no es una solución viable.

La solución es cambiar el valor a una variable de MySQL: lower_case_table_names. He aqui una explicación obtenida del manual de referencia de MySQL.

Valor	Significado
0	Los nombres de tablas y bases de datos se almacenan en disco usando el esquema de mayúsculas y minúsculas especificado en las sentencias CREATE TABLE o CREATE DATABASE. Las comparaciones de nombres son sensibles a mayúsculas. Esto es lo predeterminado en sistemas Unix. Nótese que si se fuerza un valor 0 con --lower-case-table-names=0 en un sistema de ficheros insensible a mayúsculas y se accede a tablas MyISAM empleando distintos esquemas de mayúsculas y minúsculas para el nombre, esto puede conducir a la corrupción de los índices.
1	Los nombres de tablas se almacenan en minúsculas en el disco y las comparaciones de nombre no son sensibles a mayúsculas. MySQL convierte todos los nombres de tablas a minúsculas para almacenamiento y búsquedas. En MySQL 5.0, este comportamiento también se aplica a nombres de bases de datos y alias de tablas. Este valor es el predeterminado en Windows y Mac OS X.
2	Los nombres de tablas y bases de datos se almacenan en disco usando el esquema de mayúsculas y minúsculas especificado en las sentencias CREATE TABLE o CREATE DATABASE, pero MySQL las convierte a minúsculas en búsquedas. Las comparaciones de nombres no son sensibles a mayúsculas.Nota: Esto funciona solamente en sistemas de ficheros que no son sensibles a mayúsculas. Los nombres de las tablas InnoDB se almacenan en minúsculas, como cuandolower_case_table_names vale 1.

1. Para verificar que valor tiene esta variable, podemos hacerlo de 3 formas:
  a) buscar la opción de mostrar variables (show variables) en nuestro cliente de MySQL
  b) en la pestaña variables mediante phpMyAdmin buscarla
  c) entrar mediante la consola de comandos a mysql y escribir el siguiente comando:
  mysql> show variables like "%lower%";

2. En Windows por defecto el valor es 1, para cambiar el valor podemos usar el phpMyAdmin, si es que tenemos un usuario con los suficientes privilegios, y mediante la pestaña variables podemos editarla cambiando su valor a 0, posterior a ello tendremos que reiniciar el servicio de MySQL.

En caso de no tener phpMyAdmin o el usuario, habrá que ubicar el archivo de configuración de MySQL (my.ini o my.cnf), la ubicación dependerá de la versión y como hayan instalado, he aquí algunas ubicaciones donde podría estar:

c:\Windows\my.ini
c:\ruta_de_mysql\MySQL\my.ini
c:\Program Data\MySQL\MySQLversion\my.ini

Editamos este archivo actualizando o agregando la siguiente linea en caso de que no exista:
[mysqld]
...
lower_case_table_names = 0

Guardamos y reiniciamos nuestro servicio MySQL. Verificamos que el valor de nuestra variable este en 0.

Posterior a ello tendremos que renombrar nuestras bases de datos y/o tablas sensibles a mayúsculas y minúsculas antes de hacer una migración a nuestro servidor en producción de Linux.

Solucionar el error "Got a packet bigger than 'max_allowed_packet' bytes" en MySQL

Cuando importamos o realizamos una carga de datos a una instancia ya existente en MySQL, y se nos muestra un error como el siguiente:

ERROR 1153 (08S01) at line 625: Got a packet bigger than 'max_allowed_packet' bytes

Es porque desde nuestro cliente enviamos un paquete mayor del que esta configurado nuestro servidor, por defecto esta variable (max_allowed_packet) esta configurada con 1Mb. Tanto el cliente como el servidor tienen su propia variable max_allowed_packet así que si se desea gestionar paquetes grandes, se debe aumentar esta variable tanto en el cliente como en el servidor.

A) Para cambiar esta variable en nuestro servidor tenemos que modificar el archivo my.cnf (my.ini en Windows),

$ sudo nano /etc/my.cnf

y cambiar el tamaño de la variable o agregarla si es que no existe:

[mysqld]
max_allowed_packet = 16M

En versiones inferiores a MySQL 4.0 el tamaño maximo permito es de 16Mb, en versiones superiores es de 1Gb. Es seguro incrementar el valor de esta variable porque la memoria extra tan solo es utilizada cuando se necesita.

Despues de modificar el archivo, tendremos que reiniciar nuestro servicio:

$ sudo service mysqld restart

B) Para cambiar la configuración del cliente, solo necesita en su instancia indicar el tamaño a usar, aunque una vez configurado nuestro servidor ya no sera necesario en nuestro cliente:

mysql> mysqld --max_allowed_packet=16Mb

Instalar FreeTDS en Ubuntu para usar mssql en PHP

A partir de PHP 5.3 la extensión (mssql) para permitir el acceso a base de datos MS SQL Server ya no esta disponible en Windows, en lugar de ello tendremos que usar la extensión de ODBC pero ese es otro tema, aqui veremos una opción para acceder a bases de datos de SQL Server utilizando la libreria FreeTDS (Tabular Data Stream).

FreeTDS es un conjunto de librerias distribuidas libremente bajo licencia GNU/GPL, que nos permite comunicar PHP con bases de datos de SQL Server y Sybase.

1. Desde linea de comandos instalamos freeTDS asi como la libreria sybase de PHP

$ sudo apt-get install freetds-common freetds-bin tdsodbc php5-sybase

Reiniciamos nuestro servidor web y probamos en el navegador, creando un archivo php con la función phpinfo(), verificando que la libreria mssql se encuentra ya instalada.

2. Configuramos FreeTDS
Editamos el archivo freetds.conf que se encuentra en /etc/freetds

$ sudo nano /etc/freetds/freetds.conf

Dentro del archivo vienen algunos ejemplos de configuración, he aquí un ejemplo de como configurar:

[nombreservidor]
host = 100.100.100.1
port = 1433
tds version = 7.2

con [nombreservidor] es como haremos referencia a nuestro servidor , en host agregamos nuestra IP del servidor, el puerto (port) para servidores de SQL Server es el 1433, tds version es la versión del SQL que estaremos usando.

1.  7.0 -- Microsoft SQL Server 7
2.  7.1 -- Microsoft SQL Server 2000
3.  7.2 -- Microsoft SQL Server 2005 o posterior

Una vez terminado de configurar el archivo, lo guardamos.

3. Probando nuestra conexión
Para ver si todo esta correcto realizamos una prueba:

$ tsql -S nombreservidor -U usuariosql

en seguida se pedira el password, si no hay problema, habremos ingresado a nuestro servidor SQL. Recordemos que nombreservidor es nuestra referencia que ingresamos en nuestro archivo de configuración freetds.conf.

4. Probando nuestra conexión en PHP
Ya tenemos todo listo para comenzar a trabajar en php con mssql, podemos crear un archivo en php y probar nuestra conexion.

<?php
/** Conexión a SQL Server **/
$host    = "nombreservidor"; // esta es nuestra referencia
$usuario = "sa";
$pass    = "12345;
$bd      = "miBase";
$conexion = mssql_connect($host, $usuario, $pass);
if (!$conexion) die ("Error al conectar a SQL server");
mssql_select_db($bd) or die ("Error al seleccionar la Base de datos");
echo "Conexion establecida con éxito";
?>

Guardamos nuestro archivo y lo probamos en nuestro navegador, si no hubiese problemas debe mostrarse el mensaje de Conexión establecida con éxito.