Uruchom MySQLDump bez blokowania tabel


437

Chcę skopiować produkcyjną bazę danych na żywo do mojej lokalnej bazy danych programowania. Czy można to zrobić bez blokowania produkcyjnej bazy danych?

Obecnie używam:

mysqldump -u root --password=xxx -h xxx my_db1 | mysql -u root --password=xxx -h localhost my_db1

Ale blokuje każdą tabelę podczas działania.


Kolejne późne rozwiązanie: możesz także użyć Percona XtraBackup do zrzucenia produkcyjnej bazy danych bez zakłóceń w przetwarzaniu transakcji. Pozwala na wykonanie kopii zapasowej na gorąco, tzn. Nie wpływa na bieżące działania. Zobacz tutaj: percona.com/software/mysql-database/percona-xtrabackup (Nie mam żadnego powiązania z
Percona

Odpowiedzi:


625

Czy --lock-tables=falseopcja działa?

Według strony man , jeśli dumping tabel InnoDB można użyć --single-transactionopcji:

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ
LOCAL to allow concurrent inserts in the case of MyISAM tables. For
transactional tables such as InnoDB and BDB, --single-transaction is
a much better option, because it does not need to lock the tables at
all.

Dla innodb DB :

mysqldump --single-transaction=TRUE -u username -p DB

23
dla innodb DB mysqldump --single-transakcja = PRAWDA -u nazwa użytkownika -p DB
AMB

19
Co jeśli masz innodb i myisam?
CMCDragonkai,

Czy to jest domyślnie włączone?
CMCDragonkai,

oczywiście włączony (tzn. zablokowany)?
evandrix

290

To jest za późno, ale dobre dla każdego, kto szuka tematu. Jeśli nie jesteś innoDB i nie martwisz się blokowaniem podczas zrzutu, skorzystaj z opcji:

--lock-tables=false

1
Dzięki za odpowiedź Warren, to było bardzo pomocne i działało jak urok.
Gavin

7
using '--lock-table = false --quick' wykorzystuje najmniej zasobów serwera
SyntaxGoonoo 14.03.13

43
Ale powinieneś martwić się blokowaniem stołów. Jeśli zapisanych jest wiele tabel podczas działania mysqldump (i używasz kluczy obcych), zrzut może być niespójny. Nie będziesz wiedział, dopóki go nie przywrócisz i nie uda ci się uruchomić zapytań JOIN na niespójnych danych. Odkrywanie niespójnych danych może chwilę potrwać, ponieważ JOIN są używane przez aplikację, a nie MySQL (z tabelami MyISAM); przywracanie będzie działać dobrze, mysql nie ostrzeże Cię przed niespójnościami. Więc: MyIsam -> zawsze blokuj swoje stoły. InnoDB -> użyj --single-transaction.
Costa

12
@Costa Nie sądzę, że blokowanie tabel jest wystarczające dla tabel MyISAM. Jeśli mysqldump blokuje tabele między zapytaniami wykonywanymi przez aplikację, to kończysz z tymi samymi niespójnościami. Odpowiedź jest jeszcze prostsza: MyISAM -> zamiast tego użyj InnoDB.
cdhowie,

@ Koszt, zdecydowanie powinieneś martwić się blokowaniem tabel, ale tylko wtedy, gdy potrzebujesz spójnego zrzutu . Są rzadkie przypadki, kiedy tego nie robisz. Na przykład prymitywne narzędzie do zrzutu w całej bazie danych (debugowanie): Założę się, że nikt nie chce, aby użytkownicy czekali około 20 minut na utworzenie zrzutu produkcyjnej bazy danych (prawdziwa historia). Jeśli chodzi o zrzut nie tylko jak najszybciej, ale także ZGODNY , należy zrzucić replikowanego slave lub użyć migawek niższego poziomu (lvm, zfs, btrfs itp.), Pamiętając o różnych FLUSH TABLES WITH READ LOCKrzeczach.
Alex Offshore,

44

Odpowiedź różni się w zależności od używanego silnika pamięci. Idealnym scenariuszem jest użycie InnoDB. W takim przypadku możesz użyć --single-transactionflagi, która zapewni spójną migawkę bazy danych w momencie rozpoczęcia zrzutu.


35

--skip-add-locks pomógł mi


2
lub też --kompakt, aby uwzględnić pomijanie blokad z innymi optymalizacjami.
ppostma1,

77
Usuwa to instrukcje LOCK TABLES i UNLOCK TABLES z pliku zrzutu, nie wpływa na blokowanie podczas eksportu.
dabest1

11
Nie, nie tego szukasz! Zobacz komentarz dabest1. To NIC nie powstrzyma twoich tabel przed zablokowaniem podczas robienia mysqldump. To NIE jest odpowiedź na pytanie.
2016

@dabest i @orrd są poprawne: --skip-add-lockspo prostu przyspieszy przywracanie zrzutu. To nie jest poprawna odpowiedź.
dr_



10

Szczerze mówiąc, skonfigurowałbym do tego replikację, ponieważ jeśli nie zablokujesz tabel, otrzymasz niespójne dane z zrzutu.

Jeśli zrzut zajmuje więcej czasu, tabele, które zostały już zrzucone, mogły ulec zmianie wraz z tabelą, która ma zostać zrzucona.

Więc zablokuj tabele lub użyj replikacji.


Cała baza danych jest prawie w całości tylko do odczytu, więc nie martwię się zbytnio o jej zmianę.
Greg,

2
Ten komentarz jest niepoprawny. MVCC pozwala na odczyt spójnego stanu bez blokad na InnoDB.
Scott Hyndman,

5
Jeśli nie masz jeszcze skonfigurowanej replikacji, musisz zrobić zrzut, aby ją skonfigurować. Ten sam problem istnieje.
Matt Connolly,

3
Jeśli nie masz jeszcze skonfigurowanej replikacji, musisz zablokować tabele, aby wykonać zrzut, aby jednak zapewnić integralność danych. Więc to jest haczyk 22.
JordanC

9

To mniej więcej tak późno w porównaniu z facetem, który powiedział, że spóźnił się tak jak na pierwotną odpowiedź, ale w moim przypadku (MySQL przez WAMP na Windows 7) musiałem użyć:

--skip-lock-tables

To działało dla mnie, aby rzucić Information_schema bez błędu „Odmowa dostępu dla użytkownika” debian-sys-maint „@ 'localhost” do bazy danych „information_schema” przy użyciu LOCK TABLES ”
Rui F Ribeiro

6
    mysqldump -uuid -ppwd --skip-opt --single-transaction --max_allowed_packet=1G -q db |   mysql -u root --password=xxx -h localhost db

Do góry, ten zadziałał dla mnie, po prostu dodaj parametry --skip-opt --single-transakcja --max_allowed_packet = 1G
Steven Lizarazo

1
Nie polecam w tym celu opcji „--skip-opt”. To znacznie więcej niż pierwotne pytanie. Wyłącza tryb szybki, to nie obejmuje zestaw znaków, itp itd
orrd

3

Korzystając z MySQL Workbench, w opcji Eksport danych kliknij opcję Opcje zaawansowane i usuń zaznaczenie opcji blokowania tabel.

wprowadź opis zdjęcia tutaj


1

Ponieważ żadne z tych podejść nie działało dla mnie, po prostu zrobiłem:

mysqldump [...] | grep -v "LOCK TABLE" | mysql [...]

Wyłączy to zarówno polecenia, jak LOCK TABLE <x>i UNLOCK TABLESpolecenia.

Uwaga: mam nadzieję, że twoje dane nie zawierają tego ciągu!


2
--skip-add-locks podczas zrzutu również to robi
codewandler


0

Kolejna późna odpowiedź:

Jeśli próbujesz wykonać kopię zapasową bazy danych serwera (w środowisku linux), a silnikiem bazy danych wszystkich tabel jest MyISAM, powinieneś użyć mysqlhotcopy.

Zgodnie z dokumentacją:

Używa FLUSH TABLES, LOCK TABLES oraz cp lub scp do wykonania kopii zapasowej bazy danych. Jest to szybki sposób na wykonanie kopii zapasowej bazy danych lub pojedynczych tabel, ale można ją uruchomić tylko na tym samym komputerze, na którym znajdują się katalogi bazy danych. mysqlhotcopy działa tylko do tworzenia kopii zapasowych tabel MyISAM i ARCHIWUM.

LOCK TABLESCzas zależy od czasu serwer może skopiować pliki MySQL (to nie robi zrzut).


0

Dzisiaj nawet napotkałem ten sam problem, ale nie miałem dostępu do wiersza poleceń, więc otworzyłem plik sql w edytorze Notatnika i usunąłem poniżej wiersza z tabel

LOCK TABLES `yourtable name` WRITE;

następnie zaimportowałem do mojego środowiska programistycznego. Działa dobrze. mam nadzieję, że to pomoże komuś

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.