Administración de MySQL

Hoy hablaremos acerca de las actividades mas comunes que se realizan al administrar un servidor MySQL. Para tener una idea  mas clara sobre que hacen los comandos se recomienda leer mi articulo sobre manejo de bases de datos MySQL (en construcción).

Lo primero que debemos saber es como acceder a Mysql, esto lo podemos hacer de la siguiente manera:

$ mysql -h nombre_host_o_ip -P numero_de_puerto -u nombre_de_usuario -p

Donde: -h permite especificar a que host o dirección IP conectarse (por defecto localhost), -P el numero de puerto en el que escucha MySQL (por defecto 3306), -u significa usuario (root por defecto).

Nota: si queremos conectarnos con la cuenta root a localhost en el puerto 3306 solo basta con colocar

$ mysql -p

Crear usuarios

Existen tres métodos para crear los usuarios en MySQL, uno es mediante el comando CREATE, otro es modificando manualmente la tabla user de la base de datos mysql y el último es mediante el comando GRANT.

El primer método es el recomendado por los desarrolladores de MySQL porque es menos propenso a errores humanos (depediendo del humano ;)).

La sintaxis para crear usuarios es la siguiente:

mysql> CREATE USER 'nombre_usuario'@'nombre_host_o_ip'\ 
       IDENTIFIED BY mi_password';

Con el comando anterior se creó un usuario llamado nombre_usuario el cual accede al servidor MySQL que se encuentra en nombre_host_o_ip y posee el password mi_password. Por ejemplo, para crear el usuario aang, que accede a localhost y posee el password appa se usa el siguiente comando:

mysql> CREATE USER 'aang'@'localhost' IDENTIFIED BY 'appa';

Para crear un usuario mediante el segundo método se debe hacer lo siguiente:

mysql> INSERT INTO mysql.user VALUES ('nombre_host_o_ip',\
      'nombre_usuario', PASSWORD('mi_password'), 'Y', 'Y', 'Y', 'Y',\
      'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
      'Y', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '',\
      '', 0, 0, 0, 0);

Así que si creamos el usuario aang, que accede a localhost con el password appa usaremos lo siguiente:

mysql> INSERT INTO mysql.user VALUES ('localhost', 'aang',\
       PASSWORD('appa'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);

Con GRANT lograremos lo mismo si hacemos lo siguiente:

GRANT ALL ON nombre_base_de_datos.* TO 'nombre_usuario'@'nombre_host_o_IP'\
      IDENTIFIED BY 'mi_password';

Eliminar usuarios

Tenemos dos métodos para borrar usuarios: DROP USER y DELETE FROM mysql.user.
Método 1:

mysql> DROP USER 'aang'@'localhost';

Método 2:

mysql> DELETE FROM mysql.user WHERE User='aang';

Nota: si un usuario se crea por medio del comando GRANT (o se le dieron ciertos permisos) y nosotros borramos dicho usuario ya sea con DROP o DELETE, la permisología se mantendrá almacenada a menos que los revoquemos con el comando REVOKE, el cual, explicaremos mas tarde en este artículo.

Permisología

Quizás una de las partes mas importantes del MySQL es el tema relacionado con la permisología. Para modificar los permisos de determinado usuario a acceder a alguna base de datos en una dirección IP dada se debe usar el comando GRANT.

Supongamos que creamos un usuario llamado acidburn y queremos que este tenga permiso de modificar la base de datos primulus pero solamente desde localhost (no desde un sitio remoto). Para eso usaríamos el siguiente comando:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'localhost';
mysql> FLUSH PRIVILEGES;

Se debe tener cuidado al asignar permisos a los usuarios, si se coloca el comando anterior se puede acceder a la base de datos primulus con el usuario acidburn sin necesidad de colocar contraseña (osea con tan solo colocar mysql -u acidburn -D primulus). Para evitar este posible hueco de seguridad se debe agregar IDENTIFIED BY 'mi_password'. Quedando el comando final de la siguiente manera:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'localhost'\
       IDENTIFIED BY 'mi_password';
mysql> FLUSH PRIVILEGES;

Ahora imaginémonos que necesitamos acceder de manera directa al servidor MySQL desde otra computadora. Para lograrlo debes darle permiso al usuario para acceder a MySQL desde la dirección IP de la computadora desde a cuál se desea acceder. Si la IP de origen es 200.44.32.12 el comando quedaría de la siguiente manera:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'200.44.32.12'\
       IDENTIFIED BY 'mi_password';
mysql> FLUSH PRIVILEGES;

Para eliminar privilegios anteriormente dados a un usuario se debe eliminar de la tabla mysql.user esto se logra así:

mysql> DELETE FROM mysql.user WHERE User ='acidburn' and\
       Host = 200.44.32.12;
mysql> FLUSH PRIVILEGES;

En la instalación por defecto de MySQL el usuario root puede darle privilegios a los demás usuarios y a el mismo únicamente desde la maquina local (localhost). Si desea realizar esto desde otra máquina deberá agregar la sentencia WITH GRANT OPTION. Ejemplo:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'200.44.32.12'\
       IDENTIFIED BY 'mi_password' WITH GRANT OPTIONS;
mysql> FLUSH PRIVILEGES;

En todos los ejemplos mostrados anteriormente se uso GRANT ALL PRIVILEGES, este comando no es nada seguro debido a que le otorga todos los privilegios existentes a un usuario. Solo le debe dar a un usuario los privilegios que necesita sobre las bases de datos requeridas. Para una lista de privilegios con su explicación lea https://dev.mysql.com/doc/refman/5.0/es/privileges-provided.html

Respaldo de bases de datos

Para respaldar bases de datos MySQL se usa el comando mysqldump. La sintaxis de este programa es bastante sencilla:

$ mysqldump --user usuario -p nombre_base_de_datos > respaldo.dump

Después de colocar el password del usuario se creará un archivo llamado respaldo.dump con las instrucciones necesarias para reconstruir la base de datos que se respaldó.
Si se quieren respaldar todas las bases de datos se coloca la opcion --all-databases. Ejemplo:

$ mysqldump --user usuario --all-databases -p > respaldototal.dump

Si se desea solamente respaldar algunas tablas de una base de datos determinada se le agrega despues del nombre de la base de datos una lista separada con comas (,) de las tablas a respaldar. Si por ejemplo se quiere respaldar de la base de datos compras las tablas artículos y clientes el comando quedaría de la siguiente manera:

$ mysqldump --user usuario -p compras articulos,clientes > rcompras.dump;

Restaurar bases de datos
Para restaurar bases de datos creadas con el comando mysqldump solo se debe enviar la información guardada al MySQL para que ejecute los comandos y recree la base de datos. Ejemplo:

$ mysql -u root -p < respaldo.dump

Nota: en algunos casos a la hora de restaurar el respaldo pueden surgir errores como por ejemplo:

ERROR 1049 (42000): Unknown database 'nombre_base_datos'
Esto ocurre porque en el servidor MySQL que se esta corriendo el respaldo no existe la base de datos nombre_base_datos ( quizás porque solo se respaldaron las tablas ). Para corregir esto puedes editar manualmente el respaldo y agregarle la sentencia mysql> CREATE DATABASE nombre_base_datos; antes de las sentencias de creación de las tablas.

ERROR 1046 (3D000): No database selected
Esto ocurre porque a la hora de realizar el respaldo solo se respaldaron las tablas. Se puede reparar el archivo editándolo y colocando antes de las sentencias de creación de las tablas mysql> USE nombre_base_datos;.

ERROR 1007 (HY000): Can't create database 'nombre_base_datos'; database exists
Esto pasa porque la base de datos ya existe así que solo se debe editar el archivo dump y comentar o eliminar la sentencia mysql> CREATE nombre_base_datos;.

Resetear el password de root

Para resetar el password de root se deben seguir cuatro pasos:
1) Detener el servidor de bases de datos. En linux se logra con # /etc/init.d/mysql stop, en BSD y OPENBSD # /etc/rc.d/mysqld stop.

2) Iniciar en modo a prueba de fallos. En linux y unix se logra con # mysqld_safe & (posiblemente deba darle a enter dos veces).

3) Acceder y cambiar el password de root. Ahora acceder sin necesidad de password

$ mysql -u root

Luego coloca el comando 

mysql> UPDATE mysql.user SET password=PASSWORD('Nuevo_password')\
      WHERE User='root';

No es mala idea reiniciar los privilegios con : mysql> FLUSH PRIVILEGES;.

4) Reiniciar mysql: lo mismo que en el paso 1 pero cambiando el stop por restart.

Access denied for User debian-sys-maint WTF ???

Cuando se realizan migraciones de bases de datos entre servidores distintos pueden ocurrir errores. Uno de los mas comunes consiste en copiar las TODAS las bases de datos manualmente y enviarlas al otro servidor por ejemplo: # scp -R /var/lib/mysql/* root@ipdestino:/var/lib/mysql/. Lo que ocurre con el comando anterior es que copia las bases de datos que están en el servidor viejo y sobrescribe la base de datos MySQL del servidor destino. Cuando sobrescribimos la base de datos MySQL se crean los usuarios tal y como están en el servidor anterior incluyendo a un amigo de nombre debian_sys_maint.

El debian-sys-maint es un usuario especial del MySQL para Debian que posee permisos de iniciar, parar y reiniciar el servidor MySQL y el password de este usuario es único para cada instalación de Debian; cambiarlo implica que el sistema no será capaz de realizar las operaciones descritas anteriormente.

Para rescatar el usuario debian-sys-maint debemos primero revisar el archivo llamado debian.cnf en el directorio /etc/mysql/ # cat /etc/mysql/debian.cnf. Luego de copiar el contenido de este archivo accedemos al MySQL y cambiamos el password por el obtenido anteriormente:

Nota: Si no pueden acceder al mysql porque no lo pudieron arrancar, no entren en pánico !!! con solo colocar el comando # mysqld & iniciara.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost'\
       IDENTIFIED BY 'password_debian.cnf' WITH GRANT OPTION;mysql>
mysql> FLUSH PRIVILEGES;

Luego de salirnos notaremos que ya podemos arrancar, parar y reiniciar nuestro servidor mysql.

De acuerdo con un post de un usuario llamado RedScourge en http://www.linuxquestions.org/questions/linux-software-2/mysql-debian-sy... la permisología que se le debería otorgar al usuario debian-sys-maint debería ser SHUTDOWN y SELECT, ya que, esas son las únicas funciones que ejecuta el usuario antes mencionado.

mysql> GRANT SHUTDOWN ON *.* TO 'debian-sys-maint'@'localhost';
mysql> GRANT SELECT ON `mysql`.`user` TO 'debian-sys-maint'@'localhost';
mysql> FLUSH PRIVILEGES;

EOF