B4-T2 Administración de base de datos.pdf

1. Funciones del DBA 1.1. Introducción Database administrators (DBAs) usa software especializado para almacenar y organizar datos. El rol puede incluir la planificación de la capacidad, la instalación, la configuración, el diseño de la base de datos, la migración, la supervisión d...

1. Funciones del DBA 1.1. Introducción Database administrators (DBAs) usa software especializado para almacenar y organizar datos. El rol puede incluir la planificación de la capacidad, la instalación, la configuración, el diseño de la base de datos, la migración, la supervisión del rendimiento, la seguridad, la resolución de problemas, así como la copia de seguridad y la recuperación de datos. Application Database Administrator (ADBA):Los DBA o ADBA de la aplicación son responsables de cuidar las tareas de la aplicación relacionadas con una aplicación específica. Esto incluye la creación de objetos de base de datos, instantáneas, ajuste de SQL, etc.. Información según orafq: http://www.orafaq.com/wiki/Roles_and_Responsibilities 1.2. Listado de administrador de BBDD Instalación y actualización del SGBD Cuidar el diseño e implementación de la base de datos. Establecer la política y necesidades de almacenamiento. ○ Nota: los índices ocupan espacio. ○ En una tabla muy pesada, cada vez que se inserta un dato hay que actualizar un índice (muy costoso), para ello se plantea(estrategias): particionamiento: aunque lógicamente parezca una tabla, estas tablas se dividen en otras, es decir, particiona sharding(fragmentación): concepto basado en la división de bases de datos grandes en otras más pequeñas para aumentar la capacidad de almacenamiento del sistema y su rapidez Permite una operación de escalado horizontal. ○ Esto quiere decir que la información puede repartirse en varios servidores, de forma que cada servidor tenga una parte del conjunto completo de datos. El conjunto de servidores que contiene la totalidad de un conjunto de datos se le denomina sharded cluster / Shards (conjunto fragmentado). Creación de políticas de backup y restauración Planificar el crecimiento y los cambios (planificación de la capacidad, crecimiento vegativo) Establecimiento de mecanismos de seguridad. ○ Disponibilidad/HA: esto es una disponibilidad física ○ Vistas/Permisos Implementar y mantener el diseño de la base de datos.  Monitorización y optimización del rendimiento. ○ ○ Explain Plan: son los pasos que sigue el motor de ejecución de query para lanzar una query Esto lo puede ver el DBA y detectar donde tarda más tiempo ○ Clustered Index Scan: método de acceso a los datos almacenados en una tabla que tiene un índice agrupado. muchas veces las queries pueden ser muy lentas por falta de índice. Si no hay, el gestor hace un “full scan” para recorrer toda la tabla (en todos los elementos, desde el primero al último) Los gestores crean un Árbol B + para gestionar los índices. Nota: Sistemas de ficheros como Apple, APFS, algunos Linux, Ext4… también utilizar estos Árbol B+ Creación de usuarios (de aplicación, no nominales). Script de creación / carga de BBDD. Implementar y mantener la seguridad de la base de datos (crear y mantener usuarios y roles, asignar privilegios) Soporte a desarrollo ○ Esquemas ○ restricciones ○ Integridad Configurar y mantener la documentación y los estándares. ADBA responsibilities: Crear objetos de base de datos (tablas, índices, etc.) Escribir procedimientos de base de datos, funciones y disparadores. Ayudar a los desarrolladores con las actividades de la base de datos. Ajustar consultas de base de datos Supervisa los trabajos relacionados con la aplicación y las actividades de replicación de datos 2. Administración Oracle 2.1. ** Administración de BBDD. Oracle Un servidor oracle está formado por: La instancia de oracle: conjunto de procesos y las estructuras de datos en memoria que requiere el servidor cuando está en funcionamiento. Archivos de la BBDD Cómo funciona la arquitectura Cuando se crea una instancia de Oracle se crea una sola BBDD ○ Nota: En SQL Server y Mysql tú puedes crear una bbdd tal cual, sin embargo en Oracle, una base de datos es todo, que contiene diferentes aplicaciones El gestor de la BBDD creará un schema para la aplicación no una bbdd  Instancia (puerto 1521, reemplazado por el 2483): sería el gestor de la bbdd/software Listener: es un proceso servidor que provee la conectividad de red con la base de datos Oracle. El listener está configurado para escuchar la conexión en un puerto específico en el servidor de base de datos Los datos en la instancia poseen dos grandes estructuras de almacenamiento: ○ PGA(Program Global Area). Proceso de servidor que atiende a peticiones de usuarios (ej la aplicación de nóminas) Para hace esta conexión interviene el listener, Zona de la memoria en la que se guardan los datos referentes a un proceso servidor concreto Esta PGA es la que da acceso al SGA ○ SGA (Server Global Area). Zona de la memoria (caché) en la que se guardan los datos globales de la instancia. Diferentes Caches Diccionario de datos REDO LOG (Circular):su función es almacenar la información acerca de los últimos cambios (DML confirmados y DDL) realizados sobre la base de datos Buffer de datos Shared Pool  Procesos: son módulos que tienen cierta funcionalidad ○ ○ DBWn: son los responsables de escribir los buffers modificados (dirty) en la cache de buffer a disco se dispara cuando se produce en evento de tipo checkpoint ○ CKPT: escribe en los ficheros de control los checkpoints. ○ *LGWR: Proceso encargado de escribir los registros redo log en los ficheros redo log (Escribe los datos del buffer de Redo Log (en la SGA) a los archivos Redo Log en disco). ○ PMON:Se encarga de restaurar las transacciones no validadas de los procesos de usuario que abortan, liberando los bloqueos y los recursos ○ SMON:encargado de recuperar la instancia si fuera necesario ○ RECO: Se usa solo en bases de datos distribuidas. Resuelve los fallos ocurridos en transacciones distribuidas. ○ MMON: Es el proceso monitor de manejabilidad (manageability monitor process), encargado de realizar tareas relacionadas con el AWR, área de volcado de estadísticas de los servidores Oracle. ○ MMNL: s el proceso ligero de monitorización de manejabilidad (manageability monitor lite process), encargado de escribir estadísticas desde el histórico de sesiones activas (ASH) en la SGA de Oracle a el disco ○ CJQ0 y Jnnn → gestor de colas de trabajo (job queue processes). ○ FBDA → l FlashBack Data Archiver Process, es el proceso encargado de grabar la información del área de Flashback 2.2. Estructura y lógica de almacenamiento relación 1-N Separar estructura lógica de la física El archivo de datos es el datafile: almacenamiento físico Tablespace(lógico): es una ubicación de almacenamiento donde pueden ser guardados los datos correspondientes a los objetos de una base de datos. ○ Este provee una capa de abstracción entre los datos físicos y lógicos​y sirve para asignar espacio para todos los segmentos administrados ○ Nota: no es un espacio de tablas, sino que es un espacio para almacenar cosas diferentes, por ejemplo segmentos, tablas, Archivo de Datos ○ ○ Un tableSpace se almacena físicamente en un o varios “archivo de datos”, que es lo que se llama “datafiles” Por ejemplo una tabla puede estar en 2 sitios  ○ Tablespace por defecto: USER : Almacén por defecto en el que los diferentes usuarios de la BBDD almacenan sus objetos. Los esquemas de las aplicaciones irían en este SYSTEM: para los objetos del sistema como Diccionario de datos SYSAUX: Para componentes adicionales de la base de datos como por ejemplo el repositorio del Enterprise Manager UNDO TBS1: sirve para retener los cambios que se realizan sobre los datos en Oracle y asi poder deshacerlos. Con un tiempo limite de retención configurable y siempre y cuando los bloques del dato que queramos recuperar no hayan sido sobreescritos en el UNDO TEMP Segmento: Es una unidad lógica de almacenamiento en una base de datos Oracle. Representa una porción de espacio de almacenamiento dedicado a un objeto de la base de datos, como una tabla, un índice o una vista materializada ○ *table ○ table partition ○ *index ○ index partition ○ cluster ○ rollback - Undo ○ deferred rollback ○ temporary ○ cache ○ lobsegment ○ lobindex - LOB Extensión: es una unidad física de almacenamiento en el nivel del sistema de archivos. ○ Son unidades de asignación de espacio que se utilizan para organizar y gestionar el almacenamiento de datos ○ capacidad para crecer Bloque (múltiplos del bloque del SO) Por qué creo un tablespace para índice Gestión del Espacio: Al crear un tablespace separado para índices, puedes gestionar mejor el espacio y la asignación de recursos. Optimización del Rendimiento: Al almacenar índices en un tablespace dedicado, puedes ajustar el tablespace para satisfacer las necesidades de rendimiento específicas de los índices.. Administración de Almacenamiento: Proporciona una capa adicional de organización para la administración del almacenamiento. ○ Puedes respaldar y restaurar índices de manera independiente Separación de Datos e Índices: mejorar la eficiencia en operaciones específicas, como la reconstrucción de índices, ya que estas operaciones no afectarán directamente al tablespace de datos. Almacenamiento en Dispositivos Específicos: Puedes asignar tablespaces a dispositivos de almacenamiento específicos, lo que te permite controlar en qué dispositivos físicos se almacenan los índices. Seguridad y Auditoría:. NOTA:Los índices deberían de ir en la parte de almacenamiento más rápida. Esto es gobierno propia de la BBDD no de las aplicaciones Ejemplos ○ ○ ○ datos_tbs: Nombre del tablespace. (pude ser para datos o para índices) ○ DATAFILE 'datos_tbs.dbf' SIZE 100M: Especifica el archivo de datos asociado al tablespace y su tamaño inicial.  ○ AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED: Permite que el archivo de datos se extienda automáticamente en incrementos de 10M y sin límite máximo. ○ LOGGING: Indica que se realiza el registro de cambios en el archivo de redo logs. ○ ONLINE: Permite que el tablespace esté disponible para operaciones en línea. Creación de una tabla CONSTRAINT chk_edad CHECK (edad >= 0): Restricción que asegura que la columna edad sea mayor o igual a cero. TABLESPACE datos_tbs: Especifica el tablespace en el que se almacenarán los datos de la tabla. STORAGE: Define opciones de almacenamiento para la tabla, como el tamaño inicial, el tamaño siguiente, etc.  LOGGING: Indica que se realizará el registro de cambios en el archivo de redo logs. Creación de un usuario Un usuario es un esquema de la BBDD (esto no pasa en Mysql o SQL server). Sin usuario no hay tablas ○ nombre_usuario. ○ contraseña: ○ DEFAULT TABLESPACE datos_tbs: Especifica el tablespace por defecto para el usuario, donde se almacenarán sus objetos (tablas, índices, etc.). ○ TEMPORARY TABLESPACE temp_tbs: Especifica el tablespace temporal para el usuario, que se utiliza para realizar operaciones temporales. ○ QUOTA UNLIMITED ON datos_tbs: Asigna cuotas para el tablespace de datos Por defecto un usuario no puede hacer nada, hay que darle permisos, por eso se le da luego el GRANT. Ahora esto ha cambiado GRANT CONNECT, RESOURCE TO nombre_usuario: Otorga los roles CONNECT y RESOURCE al nuevo usuario. Estos roles proporcionan privilegios básicos necesarios para conectarse a la base de datos y crear objetos.  2.3. Herramientas de Backup Backup (Herramienta RMAN), hay alguna otra mas ○ ○ Oracle Data Pump: utilidad integrada en Oracle Database que permite realizar exportación e importación de datos y metadatos de la base de datos. Oracle Secure Backup (OSB): solución para entornos empresariales Veritas NetBackup for Oracle 2.4. Miscelánea Lenguaje procedimientos almacenados: PL/SQL Columnas autoincrementales: create sequence Creación de sinónimos: Es un objeto de base de datos que proporciona una referencia alternativa a otro objeto existente(Para que otro usuario de app(ej: nominas_lector) vea las tablas de otro (ej: nominas_owner))(Es como un link a la otra tabla).  ○ nombre_del_syn: Especifica el nombre del sinónimo que estás creando. ○ esquema.objeto: Especifica la referencia al objeto para el cual estás creando el sinónimo ○ Después de crearlo hay que darle permisos con un grant create synonym + grant select … Solución de cluster: RAC (el que nos da al alta disponibilidad) Vistas materializadas (esto ya está tb en otros gestores): Cuando hay una vista, se ejecuta una select que muestra los datos. Es muy posible que estos datos cambien muy poco, y cada vez que accedemos se tiene que lanzar la query Con materialized, la select está cacheada, por lo que se evita hacer las select (hay una serie de parámetros para ver cuando se quiere hacer el refresco) ○ BUILD IMMEDIATE: La vista materializada se construye de inmediato con los resultados de la consulta. ○ REFRESH FAST ON COMMIT: Esta política de refresco: FAST: actualización de la vista materializada de la manera más eficiente posible, utilizando sólo los registros afectados por las transacciones COMPLETE: sin intentar identificar solo los registros afectados por cambios recientes. FORCE: intente realizar una actualización FAST, pero si no puede hacerlo de manera eficiente, cambiará automáticamente a una actualización completa (COMPLETE) NEVER ○ SELECT id, nombre, salario: Define la estructura y los datos que se almacenarán en la vista materializada. En este caso, la vista materializada selecciona las filas de la tabla empleados donde el salario es mayor a 50000 Oracle XE: Oracle Database gratis para todo el mundo 3. Administración SQL Server 3.1. Introducción Puerto 1433 Columnas identity: en vez del objeto sequence, son los autoincrementales sysadmin y sa ○ sysadmin: es el rol más alto en SQL Server y tiene todos los privilegios de administración del sistema. ○ sa: es la cuenta de inicio de sesión predeterminada que se crea durante la instalación de SQL Server y, por defecto, se asigna al rol sysadmin Pueden existir varias BBDD, como puede ser la de vacaciones, nóminas… EL cliente de administración se llama: SSMS (Management Studio) File Groups es equivalente a los datafile sin pasar por el table space. Tipos: ○ Log Files o registro de transacciones: extensión.ldf ○ Primary Data Files: extensión.mdf ○ Secondary Data Files: extension.ndf  4. Administración Mysql/Maria DB 4.1. Arquitectura Puerto 3306 MySQL: licencia GPL y Propietario Maria DB: open source  4.2. Gestores de Almacenamiento Las tablas pueden ser de tipo InnoDB, MyISAM,ISAM,ARIA InnoDB (ACID): (Atomicidad, Consistencia, Aislamiento y Durabilidad) Si se necesitan transacciones, claves foráneas y bloqueos. Soporta bloqueos de registros e integridad referencial ○ Garantiza la integridad ○ Si la aplicación hace un uso elevado de INSERT y UPDATE se notará un aumento de rendimiento con respecto a MyISAM. ○ Los nuevos motores de almacenamiento de llaman xtraDB(versión mejorada de innodb) MyISAM(NO ACID): en las que predominen las consultas tipo select. Ficheros secuenciales indexados ○ No es transaccional. No se cumple todas las dimensiones de ACID ○ No soporta FK ○ Soporta FULLTEXT indexes ○ Soporta de tipo de datos GIS: Sistema de Información Geográfica ○ Bloqueo a nivel de tabla ○ No soporta transiciones: No soporta Commit y Rollback ○ Bloqueos para lectura y escritura ○ Genera 3 ficheros diferentes.frm: formato de la tabla.myd: datos.myi: índices ○ ○ ○ usado por defecto por el sistema administrador de bases de datos relacionales MySQL hasta su versión ISAM (tipo de ficheros creado por IBM: indexed sequential access method) es un método para crear, mantener y manipular archivos informáticos de datos para que los registros puedan recuperarse secuencial o aleatoriamente mediante una o más claves. ○ mysql implementa ISAM pero lo extiende a MyISAM Aria: MariaDB desarrolló un sistema similar a MyISAM llamado Aria ○ Desde la versión 10.4 de MariaDB todas la tablas internas usan este motor de almacenamiento ○ Recomendable para aplicaciones en las que dominan las sentencias SELECT ante los INSERT /UPDATE. ○ Ausencia de características de atomicidad ya que no tiene que hacer comprobaciones de la integridad referencial, ni bloquear las tablas para  realizar las operaciones, esto nos lleva como los anteriores puntos a una mayor velocidad. ○ Aria es es nuevo motor de almacenamiento Mariadb: Hay otros motores de almacenamiento: Spider ColumnStore InnoDB MyISAM Memory CSV Merge Archive Blackhole Federated 4.3. Crear tablas en MyISAM ENGINE= InnoDB  4.4. Soluciones en HA master-slave, cluster Replicación de datos master-slave hay un nodo que da servicio. El resto por si se cae el nodo master, Para que los nodos tengan la misma información que el master: Configuración master-slave async ○ Existe un log en el maestro, donde se almacenan (en el master) las sentencias que ha recibido (Binlog) y manda asíncronamente ese binlog a los slaves ( se almacena en el relay log) y lo replican cluster: activo-activo. Distribuida Tipo de Nodos: ○ Sql Nodes( Nodo del servicio - mysqld): permiten a las aplicaciones conectarse al clúster y ejecutar consultas SQL ○ Nodo de Coordinador (Coordinator Node): maneja la gestión del clúster ○ Nodo de Gestión: responsables de la administración y el monitoreo del clúster ○ Nodos de Servicio (DataNode): almacenan datos en memoria y proporcionan acceso a los datos para las consultas ndbd: es el demonio que está corriendo dentro de los DataNode. Se usa para escribir o leer información Nota: ndb Cluster ndb: es un motor de almacenamiento 4.5. Configuraciones MariaDB /etc/mysql por defecto El fichero my.cnf: en vez de tener toda la configuración, lo que tiene es una serie de enlace a ficheros/directorios donde está toda la configuración definida ○ Solamente hace referencia a los directorio conf.d y mariadb.conf.d. Tiene el inicio para cargar el resto de ficheros/directorios  ○ No toda la configuración está en el mismo fichero, sino que está separado como es en caso de Apache La mayoría de los ficheros están en ○ ○ vi 50-server.conf ○ Práctica de instalación de MariaDB (ya ha caido en algún examen): https://mariadb.com/kb/en/mysql_secure_installation/ Ejemplo de unos comandos en MariaDB: Nos conectamos a una bbdd de mysql Le pedimos que nos muestre las base de datos que tiene 4.6. Copia de seguridad y restauración. Comandos ○ -- all-databases ○ -- lock-tables = false/true ○ -- add-drop-database mysqldump: utilidad de línea de comandos que se utiliza para realizar copias de seguridad de bases de datos MySQL Genera un fichero con todos los inserts que hay que ejecutar. Tb se puede meter drop y create Comando: mysqldump -u usuario -p contraseña --databases nombre_de_la_base_de_datos --add-drop-database --add-drop-table --complete-insert > backup.sql ○ df--add-drop-database: Agrega la instrucción DROP DATABASE al inicio del archivo de respaldo. ○ --add-drop-table: Agrega la instrucción DROP TABLE antes de cada creación de tabla. ○ --complete-insert: Utiliza la sintaxis completa de INSERT para cada fila. Comando para copiar todas las bases de datos ○ mysqldump -u usuario -p contraseña --all-databases > backup.sql Comando para copiar solamente la estructura de datos:  ○ mysqldump -u usuario -p contraseña --no-data --databases nombre_de_la_base_de_datos > backup.sql Es necesario realizar la restauración del dump anterior: ○ mysql -u usuario -p contraseña nombre_de_la_base_de_datos < backup.sql ○ Ejemplo de un dump/restore  LOCK Tables / UNLOCK: Sentencia para bloquear / desbloquear una tabla para que nadie pueda afectar mientras se hacen los cambios ○ Nota: Si lo pones con la opción --no-data te crea solo el DDL Nota: crea la bbdd para empezar de 0 Restauración: hemos borrado aeropuerto Cómo sabemos si la BBDD está arriba processList Nota Segunda vuelta: veremos comandos de Mysql MariaDB 5. Postgres 5.1. Introducción Puerto 5432 Se crean bases de datos y schemas (organizar tablas lógicamente), como cosas independientes Toda la seguridad de usuarios se basa en ROLES: un "rol" es una entidad a la que se le asignan permisos. Un rol puede ser un usuario o un grupo de usuarios. CREATE ROLE nombre [Opciones] LOGIN → role de usuario CREATEDB SUPERUSER pg_hba.conf: es un archivo de configuración que define cómo se realizará la autenticación y qué conexiones serán permitidas o denegadas. ○ Ubicación: /etc/postgresql/{versión}/main/pg_hba.conf  Se pueden particionar tablas, por ejemplo por que se tiene un volumen elevado de registros ○ CREATE TABLE ventas ( id SERIAL PRIMARY KEY, fecha DATE, cantidad INTEGER ) PARTITION BY RANGE (fecha); CREATE TABLE ventas_enero PARTITION OF ventas FOR VALUES FROM ('2024-01-01') TO ('2024-01-31'); CREATE TABLE ventas_febrero PARTITION OF ventas FOR VALUES FROM ('2024-02-01') TO ('2024-02-29'); -- y así sucesivamente para cada mes ○ Se maneja el concepto de tablas heredadas (INHERIT) ○ Existe la seguridad basada en filas (RLS - Seguridad a nivel de fila):Row Level Security. Seguridad del dato. ○ Permite determinar que usuario o grupo de usuarios pueden ver cada una de las filas de una tabla en base a un criterio. ○ Para ello podemos crear políticas, donde se defina a que tiene acceso el usuario ○  en el campo filtro podría ser por ejemplo que el usuario no pueda ver los productos que tienen un precio > 100.000$ Podemos implementar procedimientos almacenados ○ PL/TCL ○ PL/Perl ○ PL/Python ○ Wikipedia también muestra los siguientes C C++ Java PL/Java web PL/PHP PL/sh PL/schema Replicación entre master-slave: Archivos WAL (Write Ahead Log) ○ mecanismo de registro de transacciones y cambios , que garantiza la durabilidad y consistencia de los datos almacenados en la base de datos. ○ El registro de transacciones WAL es una técnica utilizada para asegurar que las modificaciones a la base de datos sean persistentes incluso en caso de fallos del sistema. ○ Se mandan de los máster a los slave Modelo de concurrencia avanzada(MVCC → Multi-version Concurrency Control) ○ Se aislan las transacciones con “fotos” de los datos en diferentes tiempos se reducen mucho los bloqueos. Luego se mergean estas imágenes 5.2. Herramientas de línea de comando createdb/dropdb createuser/ dropuser pg-dump nombre-bd > fichero.bkp (backup) psql nombre-bd< fichero.bkp (restaurar) pg-dump-all > fichero.bkp (Copia todas las BD) [psql -f fichero.bkp postgres] vacuumdb (limpia y analiza una BD). Recupera almacenamiento de tuplas muertas cuando se hace un create user , se crea un role con capacidad de login Segunda vuelta, hacer ejercicios con: ○ pg_ctl ○ vacuumdb ○ reindexeddb