Tema 27.docx

Tema 27. MySQL: características principales. Creación de bases de datos. Seguridad lógica: usuarios, permisos y roles. Seguridad física. Backup. Instalación y configuración. MariaDB: características principales. [[http://download.nust.na/pub6/mysql/doc/refman/5.0/es/index.html]](http://download.nus...

Tema 27. MySQL: características principales. Creación de bases de datos. Seguridad lógica: usuarios, permisos y roles. Seguridad física. Backup. Instalación y configuración. MariaDB: características principales. [[http://download.nust.na/pub6/mysql/doc/refman/5.0/es/index.html]](http://download.nust.na/pub6/mysql/doc/refman/5.0/es/index.html) MySQL es un sistema de gestión de bases de datos relacionales que se utiliza ampliamente en todo el mundo. En un principio tenía licencia GNU libre pero con el tiempo fue adquirida primero por Sun y después por Oracle corporation. Algunas de sus características principales son: - Está desarrollada bajo licencia dual dependiendo de la distribución:  - Versión Community distribuida bajo la Licencia pública general de [GNU](https://es.wikipedia.org/wiki/GNU). - Versiones Enterprise con [licencia comercial](https://es.wikipedia.org/wiki/Software_propietario) por [Oracle Corporation](https://es.wikipedia.org/wiki/Oracle_Corporation). Las versiones Enterprise incluyen productos o servicios adicionales tales como herramientas de [monitorización](https://es.wikipedia.org/w/index.php?title=Monitorizaci%C3%B3n_de_sistemas&action=edit&redlink=1) y [asistencia técnica](https://es.wikipedia.org/wiki/Soporte_t%C3%A9cnico) oficial.  - MySQL es una [base de datos](https://es.wikipedia.org/wiki/Base_de_datos) muy rápida en lectura cuando utiliza el motor de almacenamiento no - transaccional [MyISAM](https://es.wikipedia.org/wiki/MyISAM), pero puede provocar problemas de integridad en entornos de alta concurrencia en la modificación. En aplicaciones web hay baja concurrencia en la modificación de datos y en cambio el entorno es intensivo en lectura de datos, lo que hace a MySQL ideal para este tipo de aplicaciones. Desde la versión 5 de MySQL ya no tiene el motor predeterminado MyISAM y utiliza InnoDB transaccional. - Multiplataforma: MySQL funciona en varias plataformas, incluyendo Windows, Linux y macOS. - Lenguaje de consulta: MySQL utiliza el lenguaje SQL (Structured Query Language) para manipular y consultar datos. - Fácil de usar: MySQL es fácil de instalar, configurar y utilizar. - El diseño de MySQL Server es multi capa, con módulos independientes. Está desarrollada en C y C++. - MySQL Workbench: herramienta gráfica para la administración de bases de datos Mysql. - Una característica propia es que permite escoger entre múltiples [motores de almacenamiento](https://es.wikipedia.org/wiki/Motores_de_almacenamiento) para cada tabla. Para **crear una base de datos en MySQL**, puedes seguir los siguientes pasos: Se puede utilizar Workbench para crear el esquema o la base de datos. CREATE {DATABASE \| SCHEMA} \[IF NOT EXISTS\] nombre\_base\_datos \[CHARACTER SET set\_de\_caracteres\]; DATABASE y SCHEMA son sinónimos. IF NOT EXISTS crea la base de datos sólo si no existe una base de datos con el mismo nombre. Si no se especifica el set de caracteres por defecto será latin1. Los formatos de codificación que se pueden usar con Unicode se denominan UTF-8, UTF-16 y UTF-32. \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-- 1. Abre la línea de comandos de MySQL: \"mysql -u root -p\". 2. Crea la base de datos: \"CREATE DATABASE nombre\_de\_tu\_base\_de\_datos;\". Si no especificamos el set de caracteres en la creación de la base de datos, se usará latin1 por defecto. Si se quiere cambiar a UTF8: CREATE DATABASE nombre\_base\_datos CHARACTER SET utf8; 3. Verifica que la base de datos se ha creado: \"SHOW DATABASES;\". Esto mostrará una lista de todas las bases de datos disponibles en tu servidor de MySQL, incluyendo la nueva que acabas de crear. 4. Usa la base de datos: \"USE nombre\_de\_tu\_base\_de\_datos;\". Esto cambiará tu conexión actual a la base de datos que acabas de crear, lo que te permitirá comenzar a crear tablas y agregar datos a ella. MySQL cuenta con un sistema de **seguridad lógica** que te permite crear usuarios, asignar permisos y definir roles para proteger tus bases de datos. A continuación, te explicamos los conceptos clave de este sistema: [Usuarios] [[https://dev.mysql.com/doc/refman/8.3/en/reserved-accounts.html]](https://dev.mysql.com/doc/refman/8.3/en/reserved-accounts.html) Al instalar Mysql se preinstalan una serie cuentas de los usuarios iniciales. - \'root\'@\'localhost': Se utiliza con fines administrativos. Esta cuenta tiene todos los privilegios, es una cuenta del sistema y puede realizar cualquier operación. Estrictamente hablando, este nombre de cuenta no está reservado, en el sentido de que algunas instalaciones cambian el nombre de root por otro para evitar exponer una cuenta con privilegios elevados y un nombre conocido. - \'mysql.sys\'@\'localhost\': es el usuario propietario de los objetos de sistema de la base de datos/esquema sys. Esta cuenta está bloqueada para que no pueda usarse para conexiones de clientes. - \'mysql.session\'@\'localhost\': Utilizado internamente por plugins para acceder al servidor. Esta cuenta está bloqueada para que no pueda usarse para conexiones de clientes. La cuenta es una cuenta del sistema. - \'mysql.infoschema\'@\'localhost\': Es el propietario de las vistas del sistema en el esquema infoesquema. Esta cuenta está bloqueada para que no pueda usarse para conexiones de clientes. Al conectar a un servidor MySQL, el servidor aceptará o rechazará la conexión basándose en la identidad del usuario. Si el servidor identifica el usuario se deberá proporcionar la clave correcta. Si no es así, el servidor le denegará el acceso completamente. En caso contrario, el servidor acepta la conexión y espera peticiones. Los usuarios se almacenan en la tabla user. La comprobación de la identidad se realiza utilizando las tres columnas de la tabla user (Host, User, y Password). (\*) Host: desde la máquina desde donde se va a poder conectar el usuario. Los valores de Host en la tabla pueden ser especificados de las siguientes maneras: - Un valor de Host debe ser un nombre de máquina o un número IP, o \'localhost\' para indicar la máquina local. - Puede utilizar los caracteres comodín \'%\' y \'\_\' en los valores de las columnas Host. Estos tienen el mismo significado que en las operaciones de búsqueda de patrones realizadas mediante el operador LIKE. Ejemplos de valores para el campo host: \'localhost\' \--\> para conectarse desde la máquina local \'%\' \--\> para desde conectarse cualquier máquina 'xxx.xxx.xxx.%\' \--\> para conectarse desde la subred C xxx.xxx.xxx Se puede crear nuevas cuentas de usuario MySQL de varias formas: - Utilizando MySQL Workbench. - Usando comandos CREATE USER Ejemplo: CREATE USER 'usuario'@\'host\' \[IDENTIFIED BY \'password\'\]; - Manipulando las tablas de permisos MySQL directamente (tabla user y luego haciendo FLUSH PRIVILEGES; para que el servidor vuelva a leer las tablas de permisos). Una vez que se haya creado el usuario nos referiremos a él con la siguiente notación: 'usuairo'@'host' [Permisos] La función primaria del sistema de privilegios de MySQL es autenticar un usuario conectándose desde un equipo dado, y asociar dicho usuario con privilegios en una base de datos tales como SELECT, INSERT, UPDATE, y DELETE. El sistema de privilegios de MySQL asegura que todos los usuarios pueden ejecutar sólo la operación permitida a los mismos. Los permisos pueden darse en varios niveles: - Nivel global: Los permisos globales se aplican a todas las bases de datos de un servidor dado. Estos permisos se almacenan en la tabla mysql.user. GRANT lista\_privilegios ON \*.\* TO {id\_usuario\|rol}; Si queremos dar permisos de root a un usuario: GRANT ALL PRIVILEGES ON \*.\* TO {id\_usuario\|rol}; - Nivel de base de datos: Los permisos de base de datos se aplican a todos los objetos en una base de datos dada. Estos permisos se almacenan en las tablas db y host. GRANT lista\_privilegios ON db\_name.\* TO {id\_usuario\|rol}; REVOKE lista\_privilegios ON db\_name.\* TO {id\_usuario\|rol}; - Nivel de tabla: Los permisos de tabla se aplican a todas las columnas en una tabla dada. Estos permisos se almacenan en la tabla mysql.tables\_priv. GRANT lista\_privilegios ON db\_name.tbl\_name TO {id\_usuario\|rol}; REVOKE lista\_privilegios ON db\_name.tbl\_name TO {id\_usuario\|rol}; - Nivel de columna: Los permisos de columna se aplican a columnas en una tabla dada. Estos permisos se almacenan en la tabla mysql.columns\_priv. GRANT lista\_privilegios (lista\_columnas) ON db\_name.tbl\_name TO {id\_usuario\|rol}; - Nivel de rutina: A este nivel, los permisos se almacenan en la tabla mysql.procs\_priv (cuando otorgamos permiso sobre una rutina concreta. Los permisos ALTER ROUTINE, EXECUTE, y GRANT se aplican a rutinas almacenadas. GRANT lista\_privilegios ON PROCEDURE nombre\_procedimiento TO {id\_usuario\|rol}; Si añadimos WITH GRANT OPTION al final de los GRANT, indicará que el usuario en cuestión puede a la vez otorgar privilegios a otros usuarios [Roles]: Un rol de MySQL es una colección de privilegios con nombre. Al igual que las cuentas de usuario, los roles pueden tener privilegios otorgados y revocados. Esto permite la asignación de conjuntos de privilegios a las cuentas y proporciona una alternativa conveniente para otorgar privilegios individuales. La siguiente lista resume las capacidades de gestión de funciones proporcionadas por MySQL: - CREATE ROLE nombre\_rol; - DROP ROLE nombre\_rol; Para asignar permisos a usuarios: GRANT nombre\_rol \[,nombre\_rol \[,..\]\] TO id\_usuario \[,id\_usuario \[,..\]\]; Y para revocarlos: REVOKE role FROM id\_usuario; Realizar **copias de seguridad (backup) de tus bases de datos en MySQL** es esencial para proteger tus datos en caso de fallos del sistema, errores humanos o ataques cibernéticos. Existen varias maneras y estrategias para hacer copias de seguridad en Mysql, también depende de la versión de Mysql que se tenga. Por ejemplo, en la versión Enterprise se tiene la herramienta MySQL enterprise Backup. También se puede realizar copias de Mysql Workbench. Se pueden hacer copias físicas o copias lógicas, las copias lógicas serán más lentas. Para hacer las copias físicas se debe copiar los ficheros del sistema de ficheros que componen la base de datos. Si se hace en caliente nos debemos asegurar de realizar los bloqueos adecuados para que no haya cambios en los datos que estamos copiando. Para la realización de copias lógicas: Las copias de seguridad lógicas se realizan con el servidor MySQL en ejecución. El servidor no se desconecta. La copia de seguridad lógica no copia archivos de datos, sino que contiene instrucciones de creación y de inserción para recrear la creación de la base de datos, ya sean tablas o la base de datos completa. A continuación, te explicamos alguna de estas formas para hacer backup de tus bases de datos en MySQL: - Copia de seguridad completa: Una copia de seguridad completa de tu base de datos incluirá todos los datos y estructuras de tu base de datos. Puedes hacer una copia de seguridad completa utilizando el comando \"mysqldump\". Por ejemplo, para hacer una copia de seguridad de una base de datos llamada \"nombre\_de\_tu\_base\_de\_datos\", puedes utilizar el siguiente comando: También se pude hacer copias de determinadas tablas: También se pude copiar varias bases de datos a la vez: - Copia de seguridad incremental: Para ello se debe tener el registro binario activo en el servidor (binary log). Este archivo almacena todos los cambios (transacciones) que se realizaron desde la última copia de seguridad. Para que esto sea así, cuando se hace la copia (tanto completa como incremental) hay que vaciar los registros logs con FLUSH LOGS. Esto nos asegura que se registrará desde la última copia. FLUSH LOGS lo que hace es cerrar los binary logs abiertos y abre unos nuevos. Por eso es recomendable hacerlo siempre después de una copia de seguridad, tanto completa como incremental. Nos aseguramos que todos los binary logs generados después serán posteriores a la última copia. En el momento en que desee realizar una copia de seguridad incremental (que contenga todos los cambios ocurridos desde la última copia de seguridad completa o incremental) se copiarán a la ubicación donde residan las copias todos los binary logs generados desde la última copia de seguridad. Estos binarios son la copia de seguridad incremental. Para la recuperación utilizaremos el comando mysqlbinlog con los ficheros binarios a recuperar. - Almacenamiento seguro: Es importante almacenar tus copias de seguridad en un lugar seguro fuera del sitio para protegerlos en caso de una falla en el sistema o un desastre natural. Puedes almacenar tus copias de seguridad en un disco duro externo, en una nube o en un servidor remoto. - Programación de backups automáticos: Es recomendable programar tus backups automáticamente en intervalos regulares para asegurarte de tener una copia actualizada de tus datos en todo momento. Puedes utilizar herramientas como cron o programar tareas en tu sistema operativo para hacer backups automáticamente. La **instalación y configuración de MySQL** varía según el sistema operativo y el entorno de trabajo en el que se va a utilizar. A continuación, te explicamos los pasos generales para instalar y configurar MySQL: - Descargar MySQL: Descarga la versión adecuada de MySQL para tu sistema operativo desde el sitio web oficial de MySQL. - Instalar MySQL: Sigue las instrucciones de instalación que aparecen en pantalla para instalar MySQL. Dependiendo de tu sistema operativo, es posible que necesites un instalador específico para el entorno de trabajo en el que se va a utilizar MySQL. - Configurar MySQL: Una vez que hayas instalado MySQL, debes configurarlo. La mayoría de configuraciones del servidor Mysql se hace desde el fichero: my.cnf. En este fichero podremos: a. Activar o desactivar caché. b. Especificación del tamaño máximo de bloque de datos. c. Tamaño máximo de la caché de datos, de índices, de búsquedas, de lecturas d. El número máximo de tablas abiertas a la vez. e. Número máximo de conexiones f. Es el número máximo de hilos de ejecución **MariaDB** MariaDB es un sistema de gestión de bases de datos relacionales (RDBMS) gratuito y de código abierto. Los desarrolladores la construyeron para conservar la estructura y las características de MySQL. Temían que la adquisición del sistema por parte de Oracle, la corporación detrás de la [base de datos Oracle](https://www.oracle.com/es/database/), que era el mayor competidor de MySQL en ese momento, pusiera en peligro la base de datos. MariaDB sigue siendo completamente de código abierto (open-source), mientras que MySQL tiene ahora módulos de código cerrado (la versión Enterprise tiene licencia de Oracle). Algunas de las características principales de MariaDB: - MariaDB viene con un cliente de línea de comandos MySQL nativo que admite el uso interactivo y no interactivo. - Compatibilidad con MySQL: MariaDB es compatible con la mayoría de las aplicaciones y herramientas que se utilizan con MySQL. Además, la mayoría de las consultas de MySQL se pueden ejecutar en MariaDB sin cambios. - Mayor rendimiento: a. MariaDB es muy rápida a la hora de realizar consultas complejas gracias al uso del motor aria en lugar de MyIsam, que utiliza la caché para almacenar las filas de datos, en lugar de escribir en disco. El motor de almacenamiento InnoDB es sustituido por XtraDB. b. Se eliminaron/mejoraron algunas conversiones no necesarias de juegos de caracteres con respecto a Mysql. c. Cuenta con un sistema de conexiones (pool de hilos) mucho más eficiente, que permite tener más de 200.000 conexiones. - Se han introducido características NoSQL, como proporcionar columnas dinámicas que permiten poder guardar un conjunto de columnas para cada fila de una tabla. Funciona guardando un conjunto de columnas en un tipo de dato blob y contando con una pequeña serie de funciones para manipularlas. - Comunidad activa y soporte: MariaDB cuenta con una comunidad de usuarios activa y una amplia gama de recursos de soporte, incluyendo documentación, tutoriales, foros y grupos de usuarios.