Respaldar y Restaurar desde Linux de forma remota

http://www.youtube.com/watch?v=vAH_yMjCKQs

Añadiendo Usuarios con Privilegios

Crear user

CREATE USER 'usuario1';

SET PASSWORD FOR 'usuario1' = PASSWORD ('password')

--Asignar Privilegios con Grant

GRANT privilegio1,privilegio2,.. ON db.tablas
TO usuario@'localhost' identified by 'passw' with grant option;

--El usuario2 con todos los privilegios en la BD.
GRANT all privileges ON fiestas.*
TO usuario2@'localhost' identified by 'us2' with grant option;

--El usuario3 insertar,modificar,seleccionar tablas de la BD.
GRANT insert,update,select ON fiestas.*
TO usuario3@'localhost' identified by 'us3';

--El usuario iman puede insertar,modificar en la tabla toreros
GRANT insert,update ON fiestas.toreros
TO usuario4@'localhost' identified by 'us4';

--EL usuario balvino puede inserta,modifica ye elimina de ganaderia
GRANT insert,update,delete ON fiestas.ganaderia
TO usuario5@'localhost' identified by 'us5';

FLUSH PRIVILEGES;

vistas

1.- create view tp as select x.nombreCompleto,x.apodo,x.idApoderado,y.descripcion from torero x, premio y where x.RFC=x.RFC order by y.descripcion;

2.-create view tgf as select a.descripcion,b.Nombre,b.localidad,b.nombre,count(*) as Total from toro c, ganaderia b, feria a where c.idGanaderia=b.idGanaderia
group by b.idGanaderia,a.descripcion order by a.descripcion;

3.-create view aft as select x.descripcion,y.nombreCompleto,count(*) as Total from feria x,apoderado y, torero z,
where z.RFC=y.RFC and y.RFC=y.RFC group by x.descripcion, y.RFC order by x.descripcion;

4.- create view edadtoriado as select t.Nombre,
(year(Anio)- year (fechaNacimiento)) -(right(Anio,5) as Edad,month(Anio_nacimiento) as Mes from toro t;


5.-create view pt as select nombreCompleto,nombrePadrino from torero order by idtorero;

Consultas SQL

1.- ¿Qué toreros han salido por la puerta grande?

SELECT torero.apodo FROM premio INNER JOIN (torero INNER JOIN corrida_torero ON torero.idTorero = corrida_torero.idTorero) ON premio.idPremio = corrida_torero.idPremio where premio.descripcion like '%Puerta Grande%';


2.- ¿Qué ganaderia participo con mas toros en el festival de granada?

SELECT COUNT(corrida_toro.idToro) AS T,ganaderia.nombre, feria.descripcion FROM feria INNER JOIN ((ganaderia INNER JOIN toro ON ganaderia.idGanaderia = toro.idGanaderia) INNER JOIN (corrida INNER JOIN corrida_toro ON corrida.idCorrida = corrida_toro.idCorridas) ON toro.idToro = corrida_toro.idToro) ON feria.idFeria = corrida.idFeria where feria.descripcion='Festival de Granada' GROUP BY ganaderia.nombre order by t desc limit 0,1;


3.- ¿Cuál es la edad del toro mas viejo que participo en la tradicional fiesta de Barcelo?

SELECT datediff(corrida.fechaRealizada, toro.fechaNacimiento)/365 as Edad, toro.nombre, toro.fechanacimiento FROM toro INNER JOIN ((feria INNER JOIN corrida ON feria.idFeria = corrida.idFeria) INNER JOIN corrida_toro ON corrida.idCorrida = corrida_toro.idCorridas) ON toro.idToro = corrida_toro.idToro WHERE feria.descripcion='Tradicional fiesta de Barcelo' and corrida_toro.numeroorden in (SELECT min(Corrida_toro.numeroOrden) FROM feria,corrida,corrida_toro WHERE corrida_toro.idCorridas=corrida.idCorrida and corrida.idFeria=feria.idFeria and feria.descripcion='Tradicional fiesta de Barcelo');


4.- quien fue el apoderado que participo mas en la corrida de otoño

SELECT count(corrida_torero.idtorero)as Participacion,apoderado.nombreCompleto FROM feria INNER JOIN (corrida INNER JOIN ((apoderado INNER JOIN torero ON apoderado.idApoderado = torero.idApoderado) INNER JOIN corrida_torero ON torero.idTorero = corrida_torero.idTorero) ON corrida.idCorrida = corrida_torero.idCorrida) ON feria.idFeria = corrida.idFeria WHERE Feria.descripcion='Corrida de inicio de otoño' group by apoderado.nombrecompleto order by Participacion desc limit 1;

5.- ¿En cuál corrida de ha tenido la participacion de mas toros grises?

SELECT COUNT(corrida_toro.idtoro) as ToroGris, feria.descripcion FROM feria INNER JOIN (toro INNER JOIN (corrida INNER JOIN corrida_toro ON corrida.idCorrida = corrida_toro.idCorridas) ON toro.idToro = corrida_toro.idToro) ON feria.idFeria = corrida.idFeria where toro.color like '%Gris%' GROUP BY Feria.descripcion order by ToroGris desc limit 1;

Integridad Referencial

ALTER TABLE `corrida`
ADD CONSTRAINT `idFeria` FOREIGN KEY (`idFeria`) REFERENCES `feria` (`idFeria`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `idPlaza` FOREIGN KEY (`idPlaza`) REFERENCES `plaza` (`idPlaza`) ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE `corrida_torero`
ADD CONSTRAINT `idCorrida` FOREIGN KEY (`idCorrida`) REFERENCES `corrida` (`idCorrida`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `idPremio` FOREIGN KEY (`idPremio`) REFERENCES `premio` (`idPremio`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `idTorero` FOREIGN KEY (`idTorero`) REFERENCES `torero` (`idTorero`) ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE `corrida_toro`
ADD CONSTRAINT `idCorridas` FOREIGN KEY (`idCorridas`) REFERENCES `corrida` (`idCorrida`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `idToro` FOREIGN KEY (`idToro`) REFERENCES `toro` (`idToro`) ON DELETE SET NULL ON UPDATE CASCADE;


ALTER TABLE `torero`
ADD CONSTRAINT `idApoderado` FOREIGN KEY (`idApoderado`) REFERENCES `apoderado` (`idApoderado`) ON DELETE SET NULL ON UPDATE CASCADE;


ALTER TABLE `toro`
ADD CONSTRAINT `idGanaderia` FOREIGN KEY (`idGanaderia`) REFERENCES `ganaderia` (`idGanaderia`) ON DELETE CASCADE ON UPDATE CASCADE;

Insetando Datos

Tabla corrida (Actualizada)

INSERT INTO `corrida` VALUES (1, 14, 1, 2, '2010-07-25 00:00:00');
INSERT INTO `corrida` VALUES (2, 15, 2, 1, '2010-08-03 00:00:00');
INSERT INTO `corrida` VALUES (3, 16, 3, 2, '2010-07-25 00:00:00');


Tabla corrida_torero (Actualizada)

INSERT INTO `corrida_torero` VALUES (1, 1, 3, 2);
INSERT INTO `corrida_torero` VALUES (2, 1, 3, 4);
INSERT INTO `corrida_torero` VALUES (3, 1, 4, 1);
INSERT INTO `corrida_torero` VALUES (4, 1, 4, 2);
INSERT INTO `corrida_torero` VALUES (5, 1, 1, 2);
INSERT INTO `corrida_torero` VALUES (6, 1, 1, 1);
INSERT INTO `corrida_torero` VALUES (7, 2, 1, 2);
INSERT INTO `corrida_torero` VALUES (8, 2, 1, 1);
INSERT INTO `corrida_torero` VALUES (9, 2, 2, 3);
INSERT INTO `corrida_torero` VALUES (10, 2, 2, 2);
INSERT INTO `corrida_torero` VALUES (11, 2, 4, 1);
INSERT INTO `corrida_torero` VALUES (12, 2, 4, 4);
INSERT INTO `corrida_torero` VALUES (13, 3, 5, 2);
INSERT INTO `corrida_torero` VALUES (14, 3, 5, 1);
INSERT INTO `corrida_torero` VALUES (15, 3, 2, 2);
INSERT INTO `corrida_torero` VALUES (16, 3, 2, 1);
INSERT INTO `corrida_torero` VALUES (17, 3, 3, 1);
INSERT INTO `corrida_torero` VALUES (18, 3, 3, 2);
INSERT INTO `corrida_torero` VALUES (19, 3, 3, 5);

Tabla corrida_toro (Actualizada)

INSERT INTO `corrida_toro` VALUES (1, 1, 10, 2);
INSERT INTO `corrida_toro` VALUES (2, 1, 3, 1);
INSERT INTO `corrida_toro` VALUES (3, 1, 16, 5);
INSERT INTO `corrida_toro` VALUES (4, 1, 11, 3);
INSERT INTO `corrida_toro` VALUES (5, 1, 9, 6);
INSERT INTO `corrida_toro` VALUES (6, 1, 7, 4);
INSERT INTO `corrida_toro` VALUES (7, 2, 4, 5);
INSERT INTO `corrida_toro` VALUES (8, 2, 13, 3);
INSERT INTO `corrida_toro` VALUES (9, 2, 6, 1);
INSERT INTO `corrida_toro` VALUES (10, 2, 19, 2);
INSERT INTO `corrida_toro` VALUES (11, 2, 5, 4);
INSERT INTO `corrida_toro` VALUES (12, 2, 18, 6);
INSERT INTO `corrida_toro` VALUES (13, 3, 15, 5);
INSERT INTO `corrida_toro` VALUES (14, 3, 12, 7);
INSERT INTO `corrida_toro` VALUES (15, 3, 8, 3);
INSERT INTO `corrida_toro` VALUES (16, 3, 21, 2);
INSERT INTO `corrida_toro` VALUES (17, 3, 17, 4);
INSERT INTO `corrida_toro` VALUES (18, 3, 14, 1);
INSERT INTO `corrida_toro` VALUES (19, 3, 20, 6);


Tabla feria (Actualizada)

INSERT INTO `feria` VALUES (1, 'Tradicional fiesta de Barcelo');
INSERT INTO `feria` VALUES (2, 'Festival de Granada');
INSERT INTO `feria` VALUES (3, 'Corrida de inicio de otoño');


Tabla ganaderia (Actualizada)

INSERT INTO `ganaderia` VALUES (1, '0001', 'granada', '2000-01-01 00:00:00', 'La herradura');
INSERT INTO `ganaderia` VALUES (2, '0002', 'sevilla', '1997-05-28 00:00:00', 'El encino');
INSERT INTO `ganaderia` VALUES (3, '0003', 'Barcelona', '2002-03-13 00:00:00', 'Valladolid');


Tabla plaza (Actualizada)

INSERT INTO `plaza` VALUES (1, 'Plaza de toros de Pinos Puente', 'Granada, España', 'San Jeronimo 27, Granada', 3000);
INSERT INTO `plaza` VALUES (2, 'Plaza monumental de Barcelona', 'Barcelona, España', 'Gran Via Corts Catalacs, 749', 19582);


Tabla premio (Actualizada)

INSERT INTO `premio` VALUES (1, 'Ninguno');
INSERT INTO `premio` VALUES (2, '1 Oreja');
INSERT INTO `premio` VALUES (3, '2 Orejas');
INSERT INTO `premio` VALUES (4, '2 Orejas, Rabo, Puerta Grande');
INSERT INTO `premio` VALUES (5, '2 Orejas, Rabo');



Tabla torero (Actualizada)

INSERT INTO `torero` VALUES (1, 'ROPJ891002', 'Jose Antonio Rodriguez Perez', 'Jose Antonio Campuzano', NULL, 1, '');
INSERT INTO `torero` VALUES (2, 'PERJ781212', 'Jose Miguel Perez', 'Joselillo', NULL, 1, '');
INSERT INTO `torero` VALUES (3, 'LODE690124', 'Eulalio Lopez Diaz', 'El Zotoluco', NULL, 1, '');
INSERT INTO `torero` VALUES (4, 'RURJ910131', 'Juan Antonio Ruiz Roman', 'Espartaco', NULL, 2, '');
INSERT INTO `torero` VALUES (5, 'ROSM881122', 'Manuel Rodriguez Sanchez', 'Manolete', NULL, 2, '');


Tabla toro (Actualizada)

INSERT INTO `toro` VALUES (3, 1, '2007-03-06 00:00:00', 'Pajarito', 'Negro', 0);
INSERT INTO `toro` VALUES (4, 1, '2008-12-08 00:00:00', 'Bravo', 'Cafe', 0);
INSERT INTO `toro` VALUES (5, 1, '2008-10-23 00:00:00', 'Luz', 'Gris', 0);
INSERT INTO `toro` VALUES (6, 1, '2007-05-17 00:00:00', 'Toribio', 'Negro', 0);
INSERT INTO `toro` VALUES (7, 1, '2008-07-21 00:00:00', 'Negro', 'Negro', 0);
INSERT INTO `toro` VALUES (8, 2, '2008-04-16 00:00:00', 'Solitario', 'Negro', 0);
INSERT INTO `toro` VALUES (9, 2, '2010-01-02 00:00:00', 'Loco', 'Gris', 0);
INSERT INTO `toro` VALUES (10, 2, '2007-09-19 00:00:00', 'Expo', 'Negro', 0);
INSERT INTO `toro` VALUES (11, 2, '2008-03-02 00:00:00', 'Maltrata', 'Gris', 0);
INSERT INTO `toro` VALUES (12, 2, '2009-12-15 00:00:00', 'Sevilla', 'Cafe', 0);
INSERT INTO `toro` VALUES (13, 2, '2008-10-03 00:00:00', 'Tarado', 'Negro', 0);
INSERT INTO `toro` VALUES (14, 2, '2007-08-28 00:00:00', 'Pinto', 'Cafe', 0);
INSERT INTO `toro` VALUES (15, 3, '2008-09-06 00:00:00', 'Tieso', 'Gris', 0);
INSERT INTO `toro` VALUES (16, 3, '2009-05-05 00:00:00', 'Testa', 'Gris', 0);
INSERT INTO `toro` VALUES (17, 3, '2008-07-29 00:00:00', 'Minotauro', 'Negro', 0);
INSERT INTO `toro` VALUES (18, 3, '2009-08-30 00:00:00', 'Chocolate', 'Cafe', 0);
INSERT INTO `toro` VALUES (19, 3, '2008-02-19 00:00:00', 'Ymbro', 'Negro', 0);
INSERT INTO `toro` VALUES (20, 3, '2009-01-20 00:00:00', 'Tajo', 'Negro', 0);
INSERT INTO `toro` VALUES (21, 3, '2007-10-29 00:00:00', 'Ventorrilo', 'Negro', 0);

Nueva Estructura de la BD

Estructura de la Base de Datos modificada segun necesidades

CREATE TABLE `apoderado` (
`idApoderado` int(3) unsigned NOT NULL auto_increment,
`RFC` varchar(13) default NULL,
`nombreCompleto` varchar(50) default NULL,
`direccion` varchar(200) default NULL,
`telefono` varchar(10) default NULL,
PRIMARY KEY (`idApoderado`),
KEY `idApoderado` (`idApoderado`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE `corrida` (
`idCorrida` int(4) unsigned NOT NULL auto_increment,
`numOrden` int(4) unsigned default '0',
`idFeria` int(4) unsigned NOT NULL default '0',
`idPlaza` int(4) unsigned NOT NULL default '0',
`fechaRealizada` datetime default NULL,
PRIMARY KEY (`idCorrida`),
KEY `feriacorrida` (`idFeria`),
KEY `idPlaza` (`idPlaza`),
KEY `plazacorrida` (`idPlaza`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE `corrida_torero` (
`idCorrida_Torero` int(4) unsigned NOT NULL auto_increment,
`idCorrida` int(4) unsigned NOT NULL default '0',
`idTorero` int(4) unsigned NOT NULL default '0',
`idPremio` int(1) unsigned default '0',
PRIMARY KEY (`idCorrida_Torero`),
KEY `corridacorrida_torero` (`idCorrida`),
KEY `idCorrida` (`idCorrida`),
KEY `idCorrida_Torero` (`idCorrida_Torero`),
KEY `idPremio` (`idPremio`),
KEY `idTorero` (`idTorero`),
KEY `premiocorrida_torero` (`idPremio`),
KEY `torerocorrida_torero` (`idTorero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

CREATE TABLE `corrida_toro` (
`idCorrida_toro` int(4) unsigned NOT NULL auto_increment,
`idCorridas` int(4) unsigned NOT NULL,
`idToro` int(4) unsigned default NULL,
`numeroOrden` int(1) unsigned NOT NULL,
PRIMARY KEY (`idCorrida_toro`),
KEY `idCorridas` (`idCorridas`),
KEY `idToro` (`idToro`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

CREATE TABLE `feria` (
`idFeria` int(4) unsigned NOT NULL auto_increment,
`descripcion` varchar(255) default NULL,
PRIMARY KEY (`idFeria`),
KEY `idFeria` (`idFeria`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE `ganaderia` (
`idGanaderia` int(4) unsigned NOT NULL auto_increment,
`codigo` varchar(20) default NULL,
`localidad` varchar(20) default NULL,
`antiguedad` datetime default NULL,
`nombre` varchar(45) default NULL,
PRIMARY KEY (`idGanaderia`),
KEY `idGanaderia` (`idGanaderia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE `plaza` (
`idPlaza` int(4) unsigned NOT NULL auto_increment,
`nombre` varchar(50) default NULL,
`localidad` varchar(20) default NULL,
`direccion` varchar(200) default NULL,
`aforo` int(5) unsigned default NULL,
PRIMARY KEY (`idPlaza`),
KEY `idPlaza` (`idPlaza`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE `premio` (
`idPremio` int(1) unsigned NOT NULL auto_increment,
`descripcion` varchar(200) default NULL,
PRIMARY KEY (`idPremio`),
KEY `idPremio` (`idPremio`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `torero` (
`idTorero` int(4) unsigned NOT NULL auto_increment,
`RFC` varchar(13) default NULL,
`nombreCompleto` varchar(50) default NULL,
`apodo` varchar(50) default NULL,
`fechaInicio` datetime default NULL,
`idApoderado` int(3) unsigned default NULL,
`nombrePadrino` varchar(50) default NULL,
PRIMARY KEY (`idTorero`),
KEY `apoderadotorero` (`idApoderado`),
KEY `idApoderado` (`idApoderado`),
KEY `idTorero` (`idTorero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `toro` (
`idToro` int(4) unsigned NOT NULL auto_increment,
`idGanaderia` int(4) unsigned NOT NULL default '0',
`fechaNacimiento` datetime default NULL,
`nombre` varchar(20) default NULL,
`color` varchar(15) default NULL,
`vivo` smallint(1) unsigned default NULL,
PRIMARY KEY (`idToro`),
KEY `ganaderiatoro` (`idGanaderia`),
KEY `idGanaderia` (`idGanaderia`),
KEY `idToro` (`idToro`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;