Czy jest możliwe mysqldump podzbiór bazy danych wymagany do odtworzenia zapytania?

tło

Chciałbym podać podzbiór mojej bazy danych wymagany do odtworzenia selectzapytania. Moim celem jest, aby mój przepływ pracy obliczeniowej był odtwarzalny (jak w odtwarzalnych badaniach ).

Pytanie

Czy istnieje sposób na włączenie tej instrukcji select do skryptu, który zrzuca dane, których dotyczy zapytanie, do nowej bazy danych, tak aby baza danych mogła zostać zainstalowana na nowym serwerze mysql, a instrukcja działałaby z nową bazą danych. Nowa baza danych nie powinna zawierać zapisów oprócz tych, które zostały użyte w zapytaniu.

Aktualizacja: Dla wyjaśnienia nie jestem zainteresowany zrzutem csv wyników zapytań. Muszę tylko zrzucić podzbiór bazy danych, aby można go było zainstalować na innym komputerze, a następnie samo zapytanie można odtworzyć (i zmodyfikować w odniesieniu do tego samego zestawu danych).

Przykład

Na przykład moja analiza może zapytać o podzbiór danych, który wymaga rekordów z wielu (w tym przykładzie 3) tabel:

select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum'); 

mysqldump ma opcję --where , aby wykonać klauzulę WHERE dla danej tabeli.

Chociaż nie jest możliwe wykonanie mysqldump kwerendy łączenia, można wyeksportować określone wiersze z każdej tabeli, aby każdy wiersz pobrany z każdej tabeli był później zaangażowany w łączenie.

W przypadku podanego zapytania konieczne byłoby trzykrotne mysqldump:

Najpierw mysqldump wszystkie wiersze table3 o nazwie w („opłata”, „fi”, „fo”, „fum”):

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql

Następnie mysqldump wszystkie wiersze table2, które mają pasujące wartości table3_id z pierwszego mysqldump:

mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql

Następnie mysqldump wszystkie wiersze table1, które mają pasujące wartości table1_id z drugiego mysqldump:

mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

Uwaga: Ponieważ drugi i trzeci mysqldump wymagają użycia więcej niż jednej tabeli, należy użyć opcji --lock-all-tables .

Utwórz nową bazę danych:

mysqladmin -u... -p... mysqladmin create newdb

Na koniec załaduj trzy mysqldumps do innej bazy danych i spróbuj dołączyć do niej w nowej bazie danych.

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

W kliencie mysql uruchom zapytanie dotyczące łączenia

mysql> use newdb
mysql> select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum'); 

Spróbuj !!!

OSTRZEŻENIE: Jeśli nie zostanie poprawnie zindeksowany, drugi i trzeci mysqldump mogą trwać wiecznie !!!

Na wszelki wypadek zindeksuj następujące kolumny:

ALTER TABLE table2 ADD INDEX (table1_id);
ALTER TABLE table2 ADD INDEX (table3_id);
ALTER TABLE table3 ADD INDEX (name,id);

Zakładam, że id jest kluczem podstawowym table3.

Uznałbym za pomocą „outfile” jako część SELECT zamiast mysqldump aby rozwiązać ten problem. Możesz utworzyć dowolną instrukcję SELECT, a następnie dołączyć „INTO OUTFILE” /path/to/outfile.csv „...” na końcu z odpowiednią konfiguracją dla wyjścia w stylu CSV. Następnie możesz po prostu użyć czegoś takiego jak składnia „ LOAD DATA INFILE ...”, aby załadować dane do nowej lokalizacji schematu.

Na przykład za pomocą SQL:

select table1.id, table1.level, table2.name, table2.level 
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum')
INTO OUTFILE '/tmp/fee-fi-fo-fum.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
; 

Pamiętaj, że potrzebujesz wystarczającej ilości miejsca na docelowej partycji dysku.

Narzędzie mysqldump ma opcję --tables, która pozwala określić, które tabele należy zrzucić. Pozwala określić listę tabel.

Nie znam żadnego łatwiejszego (zautomatyzowanego) sposobu.

Przydało mi się coś takiego:

mysqldump -u db_user -p db_name table_name --no_create_info \
--lock-all-tables --where 'id in (SELECT tn.id FROM table_name AS tn \
JOIN related_table AS rt ON tn.related_table_id = rt.id \
WHERE rt.some_field = 1)' > data.sql

From http://krosinski.blogspot.com/2012/12/using-table-join-with-mysqldump.html

Czy wypróbowałeś już funkcję cytowania w mysql?

SELECT CONCAT("insert into table4(id,level,name,levelt2) VALUES(",   quote(table1.id),   ",",    quote(table1.level),   ",",    quote(table2.name),   ",",    quote(table2.level),    ");") as q
       from table1 join table2 on table1.id = table2.table1_id 
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum'); 

zapisz powyższe, jako query.sql

cat query.sql|mysql --skip-column-names --raw > table4.sql

W MySQL:

SHOW CREATE TABLE table1; -- use these two create statements
SHOW CREATE TABLE table2; -- to design table4's create statement
CREATE TABLE table4( .... );
INSERT INTO table4(id,level,name,levelt2)
SELECT table1.id, table1.level, table2.name, table2.level 
   from table1 join table2 on table1.id = table2.table1_id 
   join table3 on table3.id = table2.table3_id
   where table3.name in ('fee', 'fi', 'fo', 'fum'); 

W wierszu poleceń:

mysqldump mydb table4 |gzip > table4.sql.gz

Na serwerze docelowym skonfiguruj ~ / .my.cnf

[client]
default-character-set=utf8

Importuj na serwer docelowy

zcat table4.sql.gz | mysql

napisałem mały skrypt dla podobnego problemu, oto on: https://github.com/digitalist/mysql_slice

include ('queryDumper.php');


$exampleQuery="select * from information_schema.columns c1 
left join information_schema.columns c2 on 1=1 limit 1";

//define credentials
$exampleMysqli = new mysqli($host, $user, $password, $database);
$exampleResult=$exampleMysqli->query($exampleQuery);

//if  mysqlnd (native driver installed), otherwise use wrapper
$exampleData=fetchAll($exampleResult);
$exampleMeta=$exampleResult->fetch_fields();

/*
 * field content removal options
 * column name => function name in queryDumper.php, namespace QueryDumperHelpers
 * 
 * */

$forbiddenFields=array(
'password'=>'replacePassword', //change password -> md5("password")
'login'=>'replaceLogin', //change login vasya@mail.ru -> vasya@example.com
'comment'=>'sanitizeComment' //lorem ipsum or 
);


//get tables dump
$dump=(\queryDumper\dump($exampleData, $exampleMeta, $forbiddenFields));



$dropDatabase=true; //default false
$dropTable=true; //default false

$dbAndTablesCreationDump=\QueryDumperDatabaseAndTables\dump($exampleMysqli,$exampleMeta, $dropDatabase, $dropTable);

$databases=$dbAndTablesCreationDump['databases'];
$tables=$dbAndTablesCreationDump['tables'];
$eol=";\n\n";
echo implode($eol, $databases)."\n";
echo implode($eol, $tables).";\n";
echo "\n";

//consider using array_unique($dump) before imploding
echo implode("\n\n", $dump);
echo "\n";
?>

tzn. masz to zapytanie :

SELECT * FROM employees.employees e1 
LEFT JOIN employees.employees e2 ON 1=1 
LIMIT 1; 

masz ten zrzut :

DROP DATABASE `employees`;

CREATE DATABASE `employees`;
CREATE TABLE `employees` ( /* creation code */ ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT IGNORE INTO `employees`.`employees` VALUES ("10001","1953-09-02","Georgi","Facello","M","1986-06-26");

INSERT IGNORE INTO `employees`.`employees` VALUES ("10001","1953-09-02","Georgi","Facello","M","1986-06-26");