INSIGHT IN THE RING BACKUPS WITH mysqldump
Tworzenie kopii zapasowych przez IMHO stało się bardziej formą sztuki, jeśli wiesz, jak się do tego zbliżyć
Masz opcje
Opcja 1: mysqldump całą instancję mysql
To jest najłatwiejsze, bez myślenia !!!
mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz
Wszystko zapisane w jednym pliku: struktury tabel, indeksy, wyzwalacze, procedury składowane, użytkownicy, zaszyfrowane hasła. Inne opcje mysqldump mogą również eksportować różne style poleceń INSERT, plik dziennika i współrzędne pozycji z dzienników binarnych, opcje tworzenia bazy danych, częściowe dane (opcja --where) i tak dalej.
Opcja 2: mysqldump oddzielne bazy danych w osobne pliki danych
Zacznij od utworzenia listy baz danych (w tym celu 2 techniki)
Technika 1
mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt
Technika 2
mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt
Technika 1 to najszybszy sposób. Technika 2 jest najpewniejsza i najbezpieczniejsza. Technika 2 jest lepsza, ponieważ czasami użytkownicy tworzą foldery do ogólnych celów w / var / lib / mysql (datadir), które nie są powiązane z bazą danych. Information_schema zarejestruje folder jako bazę danych w tabeli information_schema.schemata. Technika 2 pomija foldery, które nie zawierają danych mysql.
Po skompilowaniu listy baz danych możesz przejść do jej przeglądania i mysqldump, nawet jeśli to konieczne, równolegle.
for DB in `cat ListOfDatabases.txt`
do
mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait
Jeśli istnieje zbyt wiele baz danych, aby uruchomić je jednocześnie, zrzuć je równolegle 10:
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
(( COMMIT_COUNT++ ))
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
then
COMMIT_COUNT=0
wait
fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
wait
fi
Opcja 3: mysqldump oddzielne tabele w osobne pliki danych
Zacznij od utworzenia listy tabel
mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt
Następnie zrzuć wszystkie tabele w grupach po 10
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
mysqldump -h... -u... -p... --hex-blob --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
(( COMMIT_COUNT++ ))
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
then
COMMIT_COUNT=0
wait
fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
wait
fi
Opcja 4: WYKORZYSTAJ WYOBRAŹNIĘ
Wypróbuj odmiany wyżej wymienionych Opcji oraz techniki czyszczenia migawek
Przykłady
- Uporządkuj listę tabel według wielkości każdej tabeli rosnąco lub malejąco.
- Korzystając z osobnego procesu, uruchom „FLUSH TABLES WITH READ LOCK; SELECT SLEEP (86400)” przed uruchomieniem mysqldumps. Zabij ten proces po zakończeniu mysqldumps. Jest to pomocne, jeśli baza danych zawiera zarówno InnoDB, jak i MyISAM
- Zapisz mysqldumps w datowanych folderach i obróć stare foldery kopii zapasowych.
- Załaduj mysqldumps całej instancji do samodzielnych serwerów.
CAVEAT
Tylko opcja 1 przynosi wszystko. Wadą jest to, że mysqldump utworzone w ten sposób można przeładować tylko do tej samej wersji mysql wydania Majot, w której został wygenerowany mysqldump. Innymi słowy, mysqldump z bazy danych MySQL 5.0 nie może zostać załadowany do wersji 5.1 lub 5.5. Powód ? Schemat mysql różni się w zależności od głównych wersji.
Opcje 2 i 3 nie obejmują zapisywania nazw użytkowników i haseł.
Oto ogólny sposób na zrzucenie Grantów SQL dla użytkowników, który jest czytelny i bardziej przenośny
mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql
Opcja 3 nie zapisuje procedur przechowywanych, więc możesz wykonać następujące czynności
mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &
Inną kwestią, na którą należy zwrócić uwagę, jest InnoDB. Jeśli masz dużą pulę buforów InnoDB, opróżnij ją najlepiej, jak potrafisz, przed wykonaniem jakichkolwiek kopii zapasowych. W przeciwnym razie MySQL spędza czas na opróżnianiu tabel z resztek brudnej strony z puli buforów. Oto, co sugeruję:
Na około godzinę przed wykonaniem kopii zapasowej uruchom to polecenie SQL
SET GLOBAL innodb_max_dirty_pages_pct = 0;
W MySQL 5.5 domyślna wartość innodb_max_dirty_pages_pct wynosi 75. W MySQL 5.1 i starszych domyślna wartość innodb_max_dirty_pages_pct wynosi 90. Ustawienie wartości innodb_max_dirty_pages_pct na 0 przyspieszy opróżnianie brudnych stron na dysk. Zapobiegnie to lub przynajmniej zmniejszy wpływ czyszczenia niekompletnych dwufazowych zatwierdzeń danych InnoDB przed wykonaniem jakiegokolwiek mysqldump względem jakichkolwiek tabel InnoDB.
OSTATECZNE SŁOWO NA mysqldump
Większość ludzi unika mysqldump na rzecz innych narzędzi i te narzędzia są naprawdę dobre.
Takie narzędzia obejmują
- MAATKIT (równoległe skrypty zrzutu / przywracania , z Percona [Przestarzałe, ale świetne])
- XtraBackup (TopNotch Snapshot Backup from Percona)
- CDP R1Soft ( opcja modułu MySQL, która wykonuje migawki w określonym momencie)
- MySQL Enterprise Backup (wcześniej InnoDB Hot Backup) [komercyjny])
Jeśli masz ducha prawdziwego MySQL DBA, możesz objąć mysqldump i mieć nad nim pełną biegłość. Niech wszystkie twoje kopie zapasowe będą odzwierciedleniem twoich umiejętności jako MySQL DBA .