Gestión Esencial de MySQL en Linux: Acceso y Operaciones Básicas

MySQL es un popular servidor de bases de datos usado en múltiples aplicaciones. SQL traduce (S)tructured (Q)uery (L)anguage: Lenguaje de Consultas eStructuradas, MySQL hace uso de este para comunicarse con otros programas. Sobre aquel lenguaje MySQL tiene sus propias funciones SQL expandidas para proporcionar funcionalidades adicionales a los usuarios. En este documento, veremos cómo hacer la instalación inicial de MySQL, generar bases de datos, tablas, y crear nuevos usuarios.

Acceso y operaciones más básicas para administrar MySQL en un sistema Linux cuando accedemos por el terminal en línea de comandos. En DesarrolloWeb.com publicamos hace algún tiempo unas notas muy interesantes para todo aquel que trabaja con MySQL y desea acceder por línea de comandos al sistema gestor de base de datos. Sin embargo, para las personas que trabajan con MySQL por GNU/Linux, ya sea porque tengan instalado Linux como ordenador de escritorio o porque accedan por SSH a servidores dedicados, algunos asuntos relacionados con el acceso a la interfaz de MySQL cambian un poco.

Acceso a MySQL en Linux

Como primer paso vamos a comentar cómo hacer una conexión por SSH a un servidor dedicado, o un servidor virtual o hosting, que permita la conexión por SSH. Para acceder por SSH a un servidor remoto desde tu ordenador de escritorio con Windows yo suelo utilizar el programa Putty, que es superligero y gratuito. Por supuesto, tendrás que editar tu usuario y la IP del servidor al que te quieres conectar. Los ordenadores con sistema Mac OS X tienen el terminal de línea de comandos también integrada entre las aplicaciones disponibles. Si en tu caso tienes Linux instalado en tu ordenador de escritorio y deseas acceder a MySQL por línea de comandos, con que abras un Terminal es suficiente.

Nota: En mi sistema Ubuntu abro un terminal con la combinación de teclas CTRL + ALT + T. Supongo que en otras distribuciones de Linux este atajo puede cambiar, pero no debe ser muy difícil abrir un terminal.

Conexión a la Consola de MySQL

Una vez en el terminal, podemos conectarnos al servidor de MySQL. Tendrás que indicar el usuario MySQL con el que quieras autenticarte. El conmutador -u establece el usuario con el que se ingresará. El conmutador -h establece el anfitrión; usualmente este será localhost a menos que este configurando un servidor remoto. Finalmente, -p le indica al cliente mysql que usted ingresará una contraseña para acceder a la base de datos.

mysql -u usuario -h localhost -p

Nota: El servidor MySQL al que nos conectamos es localhost, pues se supone que estás accediendo al sistema gestor que tienes en el servidor con el que te has conectado. Una vez ejecutado ese comando, se nos solicitará la clave del usuario que hemos utilizado para conectar. Una vez dentro de MySQL puedes realizar comandos para trabajar con el sistema gestor de base de datos en el lenguaje SQL.

Hemos ingresado y se muestra la terminal MySQL, con un mensaje de bienvenida similar a este:

* Starting mysqld (/etc/mysql/my.cnf) ... Welcome to the MySQL monitor. Copyright (c) 2000, 2017, Oracle and/or its affiliates. Type 'help;' or '\h' for help.

Note el indicador mysql> en la terminal, acá es en donde se ingresan todos sus comandos.

Gestión de Bases de Datos

Primero hagamos una revisión de las bases de datos que actualmente se tienen. Para ello se usa el comando SHOW DATABASES.

mysql> SHOW DATABASES;

A pesar de que ya existe una base de datos de prueba creada, vamos a hacer la nuestra. Aquellas son creadas usando el comando CREATE DATABASE. Vamos a fundar una llamada "gentoo".

mysql> CREATE DATABASE gentoo;

La salida nos permite saber que el comando fué ejecutado sin ningún error. Puede verificar que la base de datos fue creada ejecutando el comando SHOW DATABASES de nuevo. La base de datos ha sido efectivamente creada.

Selección de Bases de Datos y Gestión de Tablas

En orden de crear las tablas dentro de nuestra nueva base de datos Gentoo, necesitamos seleccionarla como nuestra base de datos actual. Para lograrlo, usamos el comando USE. En este comando se ingresa el nombre de la base de datos que se quiere usar como su base de datos actual. Otra opción es invocarlo en la línea de comandos luego del interruptor -D. Continuemos con el proceso cambiando a la base de datos Gentoo. Y la base de datos actual ahora es la creada anteriormente como gentoo.

mysql> USE gentoo;

En la estructura de MySQL, hay bases de datos, tablas, registros y campos. Las bases de datos contienen tablas, las tablas contienen registros, los registros contienen campos, los cuales de hecho almacenan información. Esta estructura posibilita al usuario seleccionar como desea acceder su información. Ya que introducimos las bases de datos, analicemos las tablas. Primero, las tablas pueden ser listadas de manera similar a las bases de datos usando el comando SHOW TABLES.

mysql> SHOW TABLES;

Esto significa que debemos crear algunas tablas. Para poder hacerlo, usamos el comando CREATE TABLE. No obstante, este comando es algo diferente que simplemente llamar CREATE DATABASE. Este requiere una lista de argumentos. table_name es el nombre de la tabla que queremos crear. En este caso, creemos una tabla llamada developers.

Tipos de Datos y Estructura de Tabla

field_name contiene el nombre del campo. También un campo PRIMARY KEY es definido para relacionar otras tablas y se va a auto-incrementar con cada inserción. PRIMARY KEY es sugerida, pero no requerida ya que MySQL va a guardar esta variable internamente. field_data_type es el tipo de información a ser almacenada. Para nuestros propósitos, vamos a usar el tipo de variable VARCHAR para todos nuestros campos de datos y INTEGER para los campos llave. VARCHAR es una de los tipos de datos más simples en cuanto a trabajar con oraciones se refiere. size es el tamaño de los datos que un campo individual va a almacenar. Para este caso usaremos 128. Esto implica que el campo de datos VARCHAR es de 128 bytes. Usted puede sin ningún peligro para la fecha, interpretarlo como 128 caracteres, sin embargo hay una explicación más técnica que puede revisar en el sitio web anteriormente provisto. Ahora que sabemos cómo crear una tabla ¡hagámoslo!

mysql> CREATE TABLE developers ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(128), -> email VARCHAR(128) -> );

Aparentemente nuestra tabla ha sido efectivamente creada. Sí, esta es nuestra tabla. Aunque parece no tener información alguna en los tipos de campos configurados. Para ello usamos el comando DESCRIBE (ó la contracción DESC), cuyo argumento es el nombre de la tabla. Veamos qué proporciona la tabla "developers". Ello muestra los diferentes campos y sus tipos, además de algunos atributos extra fuera del alcance de esta guía. Para mayor información revise el Manual de Referencia MySQL.

mysql> DESC developers;

La siguiente tabla muestra algunos tipos de datos comunes en MySQL:

Manipulación de Datos

El comando INSERT se usa para insertar un registro en una tabla. table contiene la tabla MySQL en la que deseamos ingresar la información. El nombre de la tabla puede ir seguido de la lista de columnas en las que se insertarán los datos, y VALUES() contiene los valores que desea insertar en la tabla. Puede omitir la lista de columnas si inserta un valor en cada una y si escribe los valores en el mismo orden en que se han definido las columnas. Para la columna de auto-incremento, un valor NULL utilizará el siguiente valor disponible. En este caso, queremos insertar datos en la tabla developers.

mysql> INSERT INTO developers (id, name, email) VALUES (NULL, 'John Doe', '[email protected]');

Según nuestro resultado de retorno, parece que el registro fue insertado correctamente.

Carga de Datos Masiva y Scripts SQL

¿Qué pasa si queremos ingresar más información que solo un registro? Ahí es donde entra en juego el comando LOAD DATA. Esto carga registros desde un archivo separado por tabulaciones. Probemos editando un archivo en nuestro directorio de inicio con los registros. Lo llamaremos records.txt.

# Contenido de ~/records.txt1 Chris White [email protected] Jane Smith [email protected]

Importante: Asegúrese de saber con qué datos está tratando.

Ahora, el comando LOAD DATA tiene una definición algo alargada, pero usaremos la forma más simple aquí. /path/to/filename es el directorio y el nombre de archivo que se utilizará. table es el nombre de nuestra tabla. En este caso, nuestro archivo es ~/records.txt y la tabla es developers.

mysql> LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;

Importante: Si se encuentra con algún comportamiento extraño, asegúrese de que sus campos estén separados por tabulaciones. Eso funcionó bien.

Muchas aplicaciones web usan scripts SQL para configurar MySQL rápida y fácilmente. Si desea usar un script SQL, deberá ejecutar MySQL en modo batch o cargar el archivo con SOURCE. Al igual que LOAD DATA, asegúrese de poder determinar qué hace el archivo SQL. No hacerlo podría comprometer su base de datos.

mysql -u usuario -p database_name < script.sql

Otra forma de lograr esto es usar el comando SOURCE. Este comando ejecutará comandos MySQL desde un archivo SQL mientras esté en el modo interactivo de MySQL. Si ve que una aplicación web le pide que ejecute un archivo SQL, los dos comandos anteriores se pueden usar para realizar esa tarea.

mysql> SOURCE /path/to/your/script.sql;

Consultas de Datos con SELECT

Tenemos nuestra tabla configurada, ¿cómo verificamos nuestros campos? Hacemos esto buscando en nuestra tabla con consultas. Las consultas son una de las características principales de cualquier base de datos SQL. Nos ayudan a convertir datos de nuestras tablas en algo útil. La mayoría de las consultas se realizan con el comando SELECT. El comando SELECT es bastante complejo, y solo veremos tres formas principales del comando en este documento.

Echemos un vistazo rápido a la primera forma. Es relativamente simple y le da una vista general de su tabla. Lo ejecutaremos para ver qué datos tenemos hasta ahora.

mysql> SELECT * FROM developers;

Vemos tanto los datos que insertamos a través de INSERT como los insertados por LOAD DATA presentes.

Ahora, digamos que solo queremos ver el registro de Chris White. Podemos hacerlo con la segunda forma de SELECT como se muestra a continuación.

mysql> SELECT * FROM developers WHERE name = 'Chris White';

Como era de esperar, se ha seleccionado la entrada específica que buscábamos.

Podemos hacerlo con la tercera forma de SELECT como se muestra aquí. Este método de selección es mucho más fácil de manejar, especialmente con mayores cantidades de información, como veremos más adelante.

mysql> SELECT name, email FROM developers LIMIT 1;

Gestión de Usuarios y Privilegios

Ahora mismo, siendo el usuario root de MySQL, tenemos permisos ilimitados para hacer lo que deseemos con la base de datos MySQL. En un entorno de servidor, un usuario con tales privilegios puede ser bastante problemático. Para controlar quién hace qué con las bases de datos, configuramos privilegios. Los privilegios son el tipo de acceso que los usuarios tienen a las bases de datos, tablas, prácticamente cualquier cosa. Ahora mismo en la base de datos gentoo, la cuenta root de MySQL es la única cuenta que puede acceder a ella, dadas sus permisos.

Vamos a crear dos usuarios algo genéricos, guest y admin, que accederán a la base de datos gentoo y trabajarán con la información en ella. La cuenta guest será restringida. Todo lo que podrá hacer es obtener información de la base de datos, y nada más. user es el nombre del usuario y host es el nombre de host desde el que el usuario accederá. En la mayoría de los casos, este será localhost.

Importante: Un host de 'localhost' no significa DNS localhost (127.0.0.1) para MySQL. Primero tenemos los privilegios que deseamos asignar.

Nota: Si está ejecutando MySQL para comunicar datos a una aplicación web, CREATE, SELECT, INSERT (discutidos aquí), DELETE y UPDATE (para más información consulte la sección de Sintaxis GRANT y REVOKE del Manual de Referencia de MySQL) son los únicos permisos que probablemente necesitará. Mucha gente comete el error de conceder todos los permisos cuando no es realmente necesario. Para nuestro usuario admin, ALL será suficiente. Para el usuario guest, SELECT será suficiente para el acceso de solo lectura. database es la base de datos sobre la cual deseamos que el usuario tenga estos permisos. En este ejemplo, gentoo es la base de datos. El .* significa todas las tablas.

Creación y Asignación de Privilegios

Ahora que tenemos los usuarios configurados, vamos a probarlos. Estamos de vuelta en la consola.

mysql> GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'contrasena_guest';

mysql> GRANT ALL PRIVILEGES ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'contrasena_admin';

mysql> FLUSH PRIVILEGES;

Ahora intentaremos iniciar sesión como el usuario guest. Actualmente, el usuario guest solo tiene privilegios SELECT. Esto básicamente se reduce a poder buscar y nada más. Continúe e inicie sesión con la cuenta de invitado.

Welcome to the MySQL monitor. Type 'help;' or '\h' for help.

Ahora deberíamos probar la(s) restricción(es) del usuario. Ahora intentemos hacer algo que no se supone que debemos hacer. Intentaremos crear una tabla.

mysql> CREATE TABLE new_table (id INT);

Como puede ver, esta función falla, ya que nuestro usuario no tiene el acceso apropiado. Sin embargo, un acceso que sí concedimos es la sentencia SELECT. El comando tiene éxito, y obtenemos una idea de lo que pueden hacer los permisos de usuario.

mysql> SELECT * FROM developers;

Sin embargo, también creamos una cuenta admin. Esto se creó para mostrar que incluso los usuarios con todos los permisos concedidos aún pueden tener limitaciones. Salga de MySQL e inicie sesión como admin.

Welcome to the MySQL monitor. Type 'help;' or '\h' for help.

Para empezar, intentaremos crear una nueva base de datos con nuestro usuario admin. Este usuario admin tendrá acceso similar a la cuenta root de MySQL, y podrá realizar cualquier tipo de modificación a la base de datos gentoo que elija. Esto probará el acceso del usuario a la base de datos principal de MySQL. Recuerde que antes solo establecimos permisos para una base de datos específica. De hecho, el usuario admin no puede crear bases de datos en la base de datos principal de MySQL, a pesar de todos sus permisos en la base de datos gentoo. Sin embargo, todavía podemos usar la cuenta admin para modificar la base de datos gentoo, como se muestra aquí con este ejemplo de inserción de datos.

mysql> USE gentoo;mysql> INSERT INTO developers (id, name, email) VALUES (NULL, 'Alice Brown', '[email protected]');

El usuario admin puede acceder a la base de datos como desee.

Revocación y Eliminación de Usuarios

Ahora, a veces, necesitamos deshacernos de los permisos de usuario. Esto podría ser cualquier cosa, desde un usuario problemático hasta un empleado jubilado. Echemos un vistazo a cómo deshabilitar los permisos de usuario con el comando REVOKE. El comando REVOKE nos permite denegar el acceso a un usuario. Podemos denegar el acceso completo o el acceso específico. De hecho, el formato es muy similar a GRANT. Las opciones aquí se explican en la sección del comando GRANT. En esta sección, sin embargo, vamos a denegar el acceso completo a un usuario. Digamos que descubrimos que la cuenta guest está causando algunos problemas de seguridad. Decidimos revocar todos los privilegios. Iniciamos sesión como root y hacemos lo necesario.

Nota: En este caso, el acceso de usuario es simple, por lo que la revocación por base de datos no es un problema.

mysql> REVOKE ALL PRIVILEGES ON gentoo.* FROM 'guest'@'localhost';mysql> FLUSH PRIVILEGES;

Ahora salgamos e intentemos iniciar sesión como usuario guest.

Welcome to the MySQL monitor. Type 'help;' or '\h' for help.

Aunque podemos iniciar sesión, nuestro acceso a gentoo ahora ha desaparecido. Y nuestro usuario problemático ya no puede acceder a la base de datos gentoo. Tenga en cuenta que el usuario aún pudo iniciar sesión. Esto se debe a que permanecen en la base de datos principal de MySQL. DROP USER eliminará el registro en la tabla de usuarios y en todas las tablas de privilegios.

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

Parece haber funcionado bien. Probemos cerrando la sesión e intentando iniciar sesión como nuestro usuario guest. ¡Nuestro usuario ha sido eliminado con éxito! Esto finaliza el tutorial introductorio de MySQL.

Información de Autoría

Esta página se basa en un documento que se encontraba anteriormente en gentoo.org. Las siguientes personas contribuyeron al documento original: Chris White, Shyam Mani, Xavier Neys. Editores: por favor no se agreguen aquí.