Práctica tema 5. Copias de Seguridad

En esta práctica utilizaremos una base de datos de ejemplo employees de las que nos ofrece MySQL. Por tanto partimos de la práctica anterior donde ya disponemos de un laboratorio con un servidor y un cliente (Xubuntu) conectados por una red interna.

  1. Crea ua copia de seguridad completa de todas las bases de datos. Utiliza las opciones necesarias para que se pueda recuperar mediante: 
    $> mysql -u root -p < backup.sql

$> mysqldump -h192.168.100.1 -u alumno -p --single-transaction --flush-logs --source-data --all-databases --dlete-source-logs > copia_completa_all_db.sql

Volcado fichero sql 

-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: 192.168.100.1    Database: 
-- ------------------------------------------------------
-- Server version       8.0.26-0ubuntu0.20.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0>
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000313', MASTER_LOG_POS=156;

--
-- Current Database: `mysql`
--

CREATE DATABASE

...
...
...
En el volcado indica que la opción --delete-master-logs está catalogada como deprecated. Habrá que mirar en la documentación de MySql cómo ha sido sustituda.

Comprobar los ficheros binlog

``` terminal $> sudo cat /var/lib/mysql/binlog.index ./binlog.000313 $> 

2. Crea una copia de seguridad solo de la base de datos *employees*.

```shell
$> mysqldump -h 192.168.100.1 -u alumno -p --databases employees > copia_completa_employees.sql


-- MySQL dump 10.13  Distrib 8.0.26, for Linux (x86_64)
--
-- Host: 192.168.100.1    Database: employees
-- ------------------------------------------------------
-- Server version       8.0.26-0ubuntu0.20.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0>
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `employees`
...
...


mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000313 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0,00 sec)
3. Realiza cambios en la base de datos. Añade un departamento y añade cuatro empleados a la base de datos.

He preparado tres ficheros, uno para empleados, otro para empleados y otro la relación entre empleados y departamentos. Aunque a continuación muestro el volcado de los 3 ficheros juntos. 

USE employees;
-- empleados...
INSERT INTO `employees` VALUES (999901,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO `employees` VALUES (999902,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO `employees` VALUES (999903,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO `employees` VALUES (999904,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO `employees` VALUES (999905,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO `employees` VALUES (999906'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO `employees` VALUES (999907,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO `employees` VALUES (999908,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO `employees` VALUES (999909,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO `employees` VALUES (999910,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
-- departamento
INSERT INTO `departments` VALUES ('d999','Practica Tema 5');
-- realción empleados departamentos
INSERT INTO `dept_emp` VALUES (999901,'d999','1986-06-26','9999-01-01');
INSERT INTO `dept_emp` VALUES (999902,'d999','1996-08-03','9999-01-01');
INSERT INTO `dept_emp` VALUES (999903,'d999','1995-12-03','9999-01-01');
INSERT INTO `dept_emp` VALUES (999904,'d999','1986-12-01','9999-01-01');
INSERT INTO `dept_emp` VALUES (999905,'d999','1989-09-12','9999-01-01');
INSERT INTO `dept_emp` VALUES (999906,'d999','1990-08-05','9999-01-01');
INSERT INTO `dept_emp` VALUES (999907,'d999','1989-02-10','9999-01-01');
INSERT INTO `dept_emp` VALUES (999908,'d999','1998-03-11','2000-07-31');
INSERT INTO `dept_emp` VALUES (999909,'d999','1985-02-18','9999-01-01');
INSERT INTO `dept_emp` VALUES (999910,'d999','1996-11-24','2000-06-26');


$> mysql -h 192.168.100.1 -u alumno -p employees< insert_empleados.sql
$> mysql -h 192.168.100.1 -u alumno -p employees< insert_departamentos.sql
$> mysql -h 192.168.100.1 -u alumno -p employees< insert_dept_emp.sql


mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000313 |      6883 | No        |
+---------------+-----------+-----------+
1 row in set (0,00 sec)

mysql>
4. Localiza el directorio donde están los ficheros binarios con los últimos cambios. Y muestra los ficheros (ls). Muestro parte del contenido de ls -la donde se ven los ficheros y tamaños. 
-rw-r-----  1 mysql mysql      179 Nov 13 09:46  binlog.000313
-rw-r-----  1 mysql mysql       32 Nov 13 11:45  binlog.index

  1. Ejecuta el comando DROP DATABASE employees, y comprueba que la base de datos no existe.

Antes de hacer el DROP, cambio el fichero binlog para poder realizar la recuperación de manera más sencilla.

mysql> flush binary logs;
Query OK, 0 rows affected (0,08 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000313 |      6927 | No        |
| binlog.000314 |       156 | No        |
+---------------+-----------+-----------+
2 rows in set (0,00 sec)

Realizo el drop y compruebo. 

mysql> drop database employees;
Query OK, 8 rows affected (0,24 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0,00 sec)
mysql>
Ya no está la base de datos employees en el sistema.

  1. Recupera la base de datos a partir de la copia inicial que hicimos y los ficheros binarios (deben estar todos los datos y el departamento y empleados que se añadieron)

Como la copia ya se hizo con la opción --database, ahora solo hay que recuperar ya que la copia inclue todo lo necesario para generar la base de datos y estructuras.

$> mysql -h 192.168.100.1 -u alumno -p -t < copia_completa_employees.sql
Vemos que realmente se han restaurado la base de datos 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0,02 sec)

También se han restaurado los datos iniciales, pero faltan los cambios que se hicieron tras la copia. Podemos ver que no está el departamento d999 que añadimos con posterioridad a la copia.

mysql> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0,01 sec)
Ahora vamos a recuperar los datos añadidos tras la copia completa. Para eso tenemos que recuperar los datos de las copias incrementales que hay en los ficheros binarios. Cuando realizamos la copia completa en el feichero binlog.index ya vimos que el siguiente fichero era: binlog.0304 que será por el que iniciaremos la recuperación.

$> sudo cat /var/lib/mysql/binlog.index
./binlog.000313
./binlog.000314
Tenemos tres ficheros. Vamos a recuperar solo el 313.

$> sudo mysqlbinlog --verbose  /var/lib/mysql/binlog.000313 > data.sql
$> mysql -ualumno -p < data.sql

Podemos compraobar que ya está el departamento y los empleados.

mysql> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d999    | Practica Tema 5    |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
10 rows in set (0,00 sec)

mysql> select * from employees where emp_no > 999900;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 999901 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
| 999902 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
| 999903 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
| 999904 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
| 999905 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
| 999906 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
| 999907 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
| 999908 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
| 999909 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
| 999910 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0,00 sec)

Ahora eliminamos todas las copias incrementales.

mysql> flush binary logs;
Query OK, 0 rows affected (0,05 sec)

mysql> purge binary logs before now();
Query OK, 0 rows affected, 1 warning (0,03 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000315 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0,00 sec)

mysql>

  1. Ahora vamos a borrar solo una parte de la base de datos. Volvemos a realizar el paso 2. Ahora creamos una tabla Temporal con dos campos. Insertamos información en la tabla temporal (4 o 5 registros). Eliminamos la tabla temporal. Ahora volvemos a realizar el paso 3.

  2. Ahora recuperamos la base de datos, desde la copia completa y las incrementales, obviando claro está el DROP de la tabla temporal. Comprobamos que la tabla temporal está, con los datos insertados

  3. Haz una copia de base de datos en dos ficheros, en el primero estarán la definición de base de datos y de tablas. En la segunda los datos. Utilizando mysqldump.

  4. Utilizando las tareas del SO (cron), crea una estrategia de copias de seguridad. Donde de lunes a viernes se guardará una copia completa de la base de datos employees en una hora concreta. Los domingos se creará una copia se seguridad completa de todas las bases de datos. Si cae el servidor y tienes que reinstalar explica cómo restaurarias si es lunes a primera hora o si fuera viernes a última. ¿Cómo cambiarias la estrategia descrita en este ejercicio para que fuera más eficiente?