How do I make a partial backup of a table?

Navigation
2

In a database called TEST I have a table called CULTIVES in which one of its fields is a numeric type and is called PID.

This table has this structure: 

mysql> DESCRIBE CULTIVOS;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| PID      | smallint(9) unsigned   | NO   | PRI | 0       |       |
| datetime | decimal(13,0) unsigned | NO   | PRI | 0       |       |
| value    | float                  | NO   |     | 0       |       |
+----------+------------------------+------+-----+---------+-------+

The table contains thousands of lines with PID = 37. And I have passed some data on the crop 37 that supposedly correct those that are currently in the database that are wrong. There are lines that the datetime is wrong, lines where the value is wrong, lines with both wrong and lines with both well. The information I have received is complete, if I remove what was there and add the new, everything is supposed to be fine.
With what my plan is to erase the current data with PID = 37 with: 

DELETE FROM CULTIVOS WHERE PID=37;

Then add the thousands of lines with the data that have happened to me and then the staff that takes care of this will tell me if it's okay or I'll go back.

To go back I need a backup but I can not do it from the whole table because:

  • It has hundreds of millions of lines. And as always, this is in a hurry.
  • The table will continue to be used and if it is decided to go back, I can only backtrack the data with PID = 37, not the rest of the table.

How do I back up the table that contains only the rows with PID = 37?

    
asked by Jose Antonio Dura Olmos 01.08.2017 в 09:13
source

2 answers

2

If you can have the table within the same base, you can run a 

CREATE TABLE tabla_backup
  AS (SELECT * FROM tabla_original where pid = 37);

I do not know what engine you use, but it's the equivalent of creating a table with the same fields and doing an insert into ... select ...

EDIT I

There I saw that it is mysql. MySql supports "Create Table As".

    
answered by 01.08.2017 / 14:22
source
0
mysqldump --host=localhost --user=tu_usuario --password=tu_password \  
          --no-create-info TEST  CULTIVOS \
          --where="PID=37" > copia_seguridad_CULTIVOS_PID37.sql

Explanation: 

mysqldump        #El comando de volcado de mysql.
--host=ip        #La dirección del servidor de BD. localhost si es en el mismo PC
--user=nombre    #El nombre de usuario con que accedes a la BD.
--password=algo  #La contraseña.
--no-create-info #Hace que no se vuelquen instruccines de creación de la tabla.
TEST             #El nombre de tu base de datos, en este caso TEST.
CULTIVO          #El nombre de la tabla a volcar parcialmente.
--where="cond"   #Una condición. Solo se vuelcan las líneas que cumplen
                 #esta condición.
> nombre_fichero #Redirección de salida para que se guarde el volcado en fichero.
    
answered by 01.08.2017 в 09:13
Link Pandas dataframes with a loop Are the e.HResult of the exceptions in c # unique?