Jak mogę przenieść bazę danych z jednego serwera na inny?

Jak mogę przenieść tabele MySQL z jednego serwera fizycznego na inny?

Tak jak w tym dokładnie scenariuszu: Mam serwer MySQL, który używa tabeli innodb i ma rozmiar około 20 GB.

Chcę przenieść go na nowy serwer, jaki jest najbardziej efektywny sposób?

Mój ulubiony sposób to potokowanie polecenia sqldump do polecenia sql. Możesz zrobić wszystkie bazy danych lub określoną. Na przykład

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword 

Możesz zrobić wszystkie bazy danych za pomocą

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

Jedynym problemem jest to, że baza danych jest zbyt duża, a rura się zapada. W takim przypadku możesz wykonać tabelę po tabeli lub dowolną z innych metod wymienionych poniżej.

Niedawno przeniosłem bazę danych o pojemności 30 GB z następującą strategią:

Stary serwer

• Zatrzymaj serwer mysql
• Skopiuj zawartość datadir do innej lokalizacji na dysku ( ~/mysqldata/*)
• Uruchom ponownie serwer mysql (przestój wynosił 10-15 minut)
• kompresować dane ( tar -czvf mysqldata.tar.gz ~/mysqldata)
• skopiuj skompresowany plik na nowy serwer

Nowy serwer

• zainstaluj mysql (nie uruchamiaj)
• rozpakuj skompresowany plik ( tar -xzvf mysqldata.tar.gz)
• przenieś zawartość mysqldata do datadir
• Upewnij się, że twój plik innodb_log_file_size jest taki sam na nowym serwerze, a jeśli nie, nie kopiuj starych plików dziennika ( mysql je wygeneruje )
• Uruchom mysql

Zgodnie z MySQL 5.0 Certification Study Guide , rozdział 32 sekcja 32.3.4, strony 456,457 opisują warunki przenoszenia danych binarnych, które przedstawiają:

Przenośność binarna jest ważna, jeśli chcesz wykonać kopię binarną utworzoną na jednym komputerze i użyć jej na innym komputerze o innej architekturze. Na przykład użycie binarnej kopii zapasowej jest jednym ze sposobów kopiowania baz danych z jednego serwera MySQL na inny.

W przypadku MyISAM przenośność binarna oznacza, że ​​możesz bezpośrednio skopiować pliki tabeli MyISAM z jednego serwera MySQL na inny na innym komputerze, a drugi serwer będzie mógł uzyskać dostęp do tabeli.

W przypadku InnoDB przenośność binarna oznacza, że ​​możesz bezpośrednio skopiować pliki obszaru tabel z serwera MySQL na jednym komputerze na inny serwer na innym komputerze, a drugi serwer będzie mógł uzyskać dostęp do obszaru tabel. Domyślnie wszystkie tabele InnoDB zarządzane przez serwer są przechowywane razem w obszarze tabel, więc przenośność obszaru tabel jest funkcją tego, czy wszystkie poszczególne tabele InnoDB są przenośne. Jeśli choć jedna tabela nie jest przenośna, nie ma też obszaru tabel.

Tabele MyISAM i obszary tabel InnoDB są binarnie przenośne z jednego hosta na inny, jeśli spełnione są dwa warunki:

• Obie maszyny muszą używać arytmetyki liczb całkowitych z dopełnianiem dwóch liczb całkowitych
• Oba komputery muszą używać formatu zmiennoprzecinkowego IEEE, w przeciwnym razie tabele nie mogą zawierać kolumn zmiennoprzecinkowych (FLOAT lub DOUBLE)

W praktyce te dwa warunki stanowią niewielkie ograniczenie. Arytmetyka liczb całkowitych z uzupełnieniem do dwóch i format zmiennoprzecinkowy IEEE są normą na nowoczesnym sprzęcie. Trzecim warunkiem przenośności binarnej InnoDB jest użycie małych liter w tabelach i bazach danych. Wynika to z faktu, że InnoDB przechowuje te nazwy wewnętrznie (w słowniku danych) małymi literami w systemie Windows. Używanie małych liter umożliwia przenośność binarną między Windows a Unixem, aby wymusić użycie małych liter, możesz umieścić następujące linie w pliku opcji:

[mysqld]
lower_case_table_names=1

Jeśli skonfigurujesz InnoDB do korzystania z przestrzeni tabel dla tabeli, warunki przenośności binarnej zostaną rozszerzone o pliki .ibd dla tabel InnoDB. (Warunki współdzielonych obszarów tabel nadal obowiązują, ponieważ zawiera słownik danych, który przechowuje informacje o wszystkich tabelach InnoDB.)

Jeśli warunki przenośności binarnej nie są spełnione, możesz skopiować tabele MyISAM lub InnoDB z jednego serwera na inny, zrzucając je przy użyciu jakiegoś formatu tekstowego (na przykład z mysqldump) i ponownie ładując je na serwer docelowy.

Istnieją dwa główne sposoby przenoszenia poszczególnych tabel w oparciu o silnik pamięci masowej.

W podanym przykładzie założymy, że:

1. datadir to / var / lib / mysql
2. baza danych o nazwie mydb
3. tabela w bazie danych mydb o nazwie mytable .

Stoły MyISAM

Jeśli mydb.mytable korzysta z silnika pamięci MyISAM, tabela będzie fizycznie zamanifestowana jako trzy osobne pliki

1. /var/lib/mysql/mydb/mytable.frm (plik .frm)
2. /var/lib/mysql/mydb/mytable.MYD (plik .MYD)
3. /var/lib/mysql/mydb/mytable.MYI (plik .MYI)

Plik
.frm zawiera strukturę tabeli .MYD zawiera dane tabeli
.MYI zawiera stronę indeksu tabeli

Pliki te są używane niezależnie do reprezentowania tabeli z logicznego punktu widzenia w mysql. Ponieważ do tych plików nie są dołączone żadne dalsze logiczne powiązania, migracja tabeli z jednego serwera DB na inny. Możesz to zrobić nawet z serwera Windows na Linux Server lub MacOS. Oczywiście możesz zamknąć mysql i skopiować 3 pliki tabel. Możesz uruchomić następujące czynności:

LOCK TABLES mydb.mytable READ;
SELECT SLEEP(86400);
UNLOCK TABLES;

w jednej sesji ssh, aby utrzymać tabelę jako tylko do odczytu i przytrzymać zamek przez 24 godziny. Sekundę później wykonaj kopię w innej sesji ssh. Następnie zabij sesję mysql z 24-godzinną blokadą. Nie musisz czekać 24 godziny.

Tabele InnoDB

Na podstawie wyżej wymienionego cytatu z książki certyfikacji istnieje wiele czynników, które określają sposób tworzenia kopii zapasowej określonej tabeli InnoDB. Ze względu na prostotę, przejrzystość i zwięzłość, po prostu wykonaj mysqldump żądanej tabeli, używając parametrów --single-transaction, aby uzyskać idealny zrzut tabeli w czasie. Nie musisz cncernować się semantyką InnoDB, jeśli chcesz tylko jedną tabelę. Możesz ponownie załadować ten plik zrzutu na dowolny wybrany serwer MySQL.

Ponieważ łączono tutaj dwa pytania (jcolebrand): EDYCJA

Jeśli jesteś bardziej niż chętny do życia z małą wydajnością DB, możesz wykonać serię rsyncs ze starego serwera (ServerA) na nowy serwer (ServerB), nawet gdy mysql nadal działa na ServerA.

Krok 01) zainstaluj tę samą wersję mysql na ServerB, którą ma ServerA

Krok 02) Na serwerze A uruchom SET GLOBAL innodb_max_dirty_pages_pct = 0;z mysql i około 10 minut (Spowoduje to usunięcie brudnych stron z puli buforów InnoDB. Pomaga to także szybciej wykonać zamknięcie mysql) Jeśli cała baza danych to MyISAM, możesz pominąć ten krok.

Krok 03) rsync --archive --verbose --stats --partial --progress --human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql

Krok 04) Powtarzaj krok 03, aż rsync zajmie mniej niż 1 minutę

Krok 05) service mysql stopna serwerze A

Krok 06) Wykonaj jeszcze jedną synchronizację

Krok 07) scp ServerA:/etc/my.cnf ServerB:/etc/

Krok 08) service mysql startna serwerze B

Krok 08) service mysql startna serwerze A (opcjonalnie)

Spróbuj !!!

CAVEAT

Możesz utworzyć taki slave replikacji w ten sposób. Pamiętaj tylko, aby mieć identyfikator serwera w sposób jawny ustawiony w master /etc/my.cnf i inny numer identyfikatora serwera w slave /etc/my.cnf

Nie potrzebujesz nawet mysqldump, jeśli przenosisz cały schemat bazy danych, i chcesz zatrzymać pierwszą bazę danych (więc jest spójna podczas przenoszenia)

1. Zatrzymaj bazę danych (lub zablokuj ją)
2. Przejdź do katalogu, w którym znajdują się pliki danych mysql.
3. Przenieś folder (i jego zawartość) do katalogu danych mysql nowego serwera
4. Uruchom kopię zapasową bazy danych
5. Na nowym serwerze wydaj polecenie „utwórz bazę danych”.
6. Utwórz ponownie użytkowników i udziel uprawnień.

Nie pamiętam, czy mysqldump obsługuje użytkowników i uprawnienia, czy tylko dane ... ale nawet jeśli tak, jest to o wiele szybsze niż zrobienie zrzutu i uruchomienie go. Użyłbym tego tylko, gdybym musiał zrzucić bazę danych mysql, a następnie ponownie wstawić do innego RDBMS, gdybym musiał zmienić opcje przechowywania (innodb vs. myisam), a może gdybym zmieniał główne wersje mysql (ale Myślę jednak, że zrobiłem to między 4 a 5)

Jeśli chcesz tylko przenieść konkretny stół, spróbuj:

mysqldump -u username -ppassword databasename tablename > databasename.tablename.sql

Możesz podać więcej nazw tabel powyżej, w tym samym poleceniu. Po zakończeniu polecenia przenieś plik databasename.tablename.sql na inny serwer, a następnie przywróć za pomocą:

mysql -u username -ppassword databasename < databasename.tablename.sql

Zauważ, że tylny plik .sql jest tworzony za pomocą programu mysqldump , a przywracanie odbywa się bezpośrednio w mysql .

1. Jeśli masz dostęp do ssh, możesz użyć mysqldump z wiersza poleceń
2. Jeśli nie masz dostępu do ssh, ale masz dostęp do phpMyAdmin, możesz go użyć do eksportu / importu
3. Jeśli nie masz dostępu do phpMyAdmin, istnieje kilka przydatnych skryptów php, które zostaną zrzucone i zaimportowane (jednak z własnego doświadczenia nigdy nie znalazłem takiego, który byłby tak niezawodny jak phpMyAdmin).

Może istnieć taka możliwość, gdy przenosisz rzeczywiste pliki bazy danych (dla mojej instalacji znajdują się one w katalogu / var / lib / mysql), ale nie jestem pewien, jak to będzie działać / działać.

Musisz przestać. Zajmie to trochę czasu, w zależności od prędkości sieci. Zakładam, że używasz MySQL na Linux / Unix. Oto proces, którego używam:

1. Zatrzymaj demona mysql na hoście źródłowym.
2. Utwórz folder tmp na hoście docelowym, aby otrzymać pliki.
3. Użyj screena, aby utworzyć sesję powłoki, która przetrwa, jeśli twój ssh zostanie rozłączony.
4. Użyj rsync, aby przenieść pliki między hostami. Coś w stylu: rsync -avhP użytkownik źródłowy @ celhost: / path / to / folder /
5. Uruchom swoje testy, aby upewnić się, że nic nie straciłeś podczas transferu.

Następnie postępuj jak zwykle, konfigurując lokalny MySQL.

* Uwaga: możesz również użyć parametru -c z rsync, aby dodać sumę kontrolną do transferu, ale będzie to wolniejsze w zależności od szybkości procesora.

Mogę potwierdzić, że metoda DTest działa również w przypadku kopiowania między ubuntu i osx.

Aby skopiować wszystkie bazy danych bez konieczności zrzutu lub podobnego:

Upewnij się, że masz czysty mysql mysql (zainstalowany dmg pobrany z mysql http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.63-osx10.6-x86_64.dmg ), który (BARDZO WAŻNE) nigdy nie był uruchamiany.

Skopiuj zawartość folderu / var / lib / mysql / z maszyny ubuntu na / usr / local / mysql / data / content na komputerze Mac. Aby uzyskać dostęp do folderu na maszynie ubuntu, musiałem użyć sudo tj .:

sudo cp /var/lib/mysql /home/foouser/mysql_data_folder
sudo chown -R foouser /home/foouser/mysql_data_folder

Skopiowałem folder za pomocą scp.

Zanim zaczniesz, weź kopię folderu mysql na komputerze Mac, aby upewnić się, że nic nie zepsujesz.

Po skopiowaniu folderu wykonaj następujące czynności na komputerze Mac:

sudo chown -R _mysql /usr/local/mysql/data/
sudo chgrp -R wheel /usr/local/mysql/data/
sudo chmod -R g+rx /usr/local/mysql/data/

Uruchom serwer mysql po raz pierwszy (z panelu preferencji w obszarze Preferencje systemowe-> mysql). Wszyscy użytkownicy i bazy danych powinny być teraz poprawnie skonfigurowane.

Działa to z mysql 5.1.61 na Ubuntu 64 bit 11.10 i mysql 5.1.63 na osx lion (Macbook pro).

Myślę, że wszystkie wcześniejsze odpowiedzi prawdopodobnie działają dobrze, ale tak naprawdę nie rozwiązują problemu ustawienia nazwy bazy danych podczas przesyłania.

Tak właśnie zrobiłem z bash:

Lepiej jest używać rsyncniż scpi nie kompresować pliku, jeśli robisz to często.

Na moim serwerze źródłowym:

me@web:~$ d=members
me@web:~$ mysqldump $d | gzip > $d.sql.gz
me@web:~$ scp -i .ssh/yourkeynamehere $d.sql.gz $sbox:$d.sql.gz

Na moim serwerze docelowym:

me@sandbox:~$ d1=members
me@sandbox:~$ d2=members_sb
me@sandbox:~$ mysqladmin create $d2
me@sandbox:~$ cat $d1.sql.gz | gunzip |  mysql $d2

Na obu komputerach można zobaczyć postęp:

me@sandbox:~$ ls *.gz 
me@sandbox:~$ cat $d.sql.gz | gunzip |  less

To wszystko zakłada, że ​​masz plik konfiguracyjny MySQL w katalogu domowym na obu komputerach i ustaw uprawnienia:

$ echo "
[client]
user=drupal6
password=metoknow
host=ord-mysql-001-sn.bananas.com
[mysql]
database=nz_drupal" > .my.cnf
$ chmod 0600 ~/.my.cnf

przenosisz go na inną bazę danych serwera mysql? jeśli tak, zrób na nim eksport

# mysqldump -u username -ppassword database_name > FILE.sql

Ogólna metoda linux:

/etc/init.d/mysqld stop
rsync -avz source_files destination
vi /etc/my.cnf

edytuj datadir (i gniazdo) zarówno mysqld, jak i mysqld_safe (jeśli dotyczy), aby wskazywały nową lokalizację, a następnie

/etc/init.d/mysql start

Wysłałem to, ponieważ nikt nie wydawał się po prostu wymieniać najmniejszej liczby kroków, aby to zrobić i uważam, że jest to najprostszy sposób osobiście.

Być może jest to lepszy sposób:

Wersja 1 : kopiowanie plików danych (tylko MYISAM)

ssh server1
service mysql stop
cd $mysql-data-dir
rsync -avz dirs-or-files server2:$mysql-data-dir
service mysql start

usługa ssh server2 restartuje mysql

• Jeśli pliki bazy danych są tylko do odczytu, możesz pominąć zatrzymywanie serwera.

Wersja 2 : mysqldump

Zainstaluj pigz - na nowoczesnych procesorach Xeon lub Opteron, zwłaszcza gdy masz 2 lub więcej procesorów, jest O DUŻO szybszy niż gzip.

ssh server1 
mysqldump ... | pigz > backup-YYMDD.sql.gz
rsync backup-YYMDD.sql.gz server:location

ssh server2
pigz -dc location/backup-YYMDD.sql.gz | mysql ..

Wersja 3 : master / slave + mysqldump / file-copy

In HA environment you should use the following trick:
setup slave server & do all backups from it
before backups - do "slave stop"; 
then do version 1 or version 2

scenariusz:

touch full.start
mysqladmin -h slave-db stop-slave
echo "show slave status \G" | mysql -h slave-db > FULL/comfi-$NOW.master-position
/usr/bin/mysqldump -h slave-db --default-character-set=utf8 -A --opt --skip-lock-tables | pigz > "FULL/XXXX-$NOW.sql.gz"
mysqladmin -h slave-db start-slave
touch full.end

ln -fs "FULL/XXXX-$NOW.sql.gz" FULL.sql.gz

PS:

do skopiowania małych tabel użyj:

ssh server1 mysqldump tabela schematów | ssh server2 schemat mysql

Sugerowałbym dwa proste kroki, aby przenieść całą bazę danych z jednego serwera na drugi.

Krok 1 : Wykonaj pełną kopię zapasową baz danych na serwerze źródłowym przy użyciu mysqldump .

Krok 2 : Możesz użyć polecenia rsync, aby przenieść całe bazy danych na serwer docelowy.