odzyskać pojedynczą bazę danych mysql w zajętym systemie replikacji master-slave

Poszukuje strategii lub narzędzia do radzenia sobie z odzyskiwaniem pojedynczej bazy danych do określonego momentu w zajętym replikowanym systemie.

Mam 12 baz danych działających na 2 serwerach MySQL 5.0.77 w konfiguracji replikowanej master-slave. Codziennie wykonywany jest pełny zrzut urządzenia slave tylko do odczytu i dostępne są przyrostowe zrzuty SQL, z tymi kopiami zapasowymi poza siedzibą, a status replikacji jest monitorowany.

Edycja: Tabele to mieszanki InnoDB i myISAM, dlatego też rozwiązania specyficzne dla silnika nie są dostępne.

Więc biorąc pod uwagę całkowitą awarię serwera nadrzędnego, mogę przerwać replikację i wypromować serwer podrzędny, mam również opcję przebudowy nowego serwera i konfiguracji z kopii zapasowej FULL przy użyciu kopii zapasowej, a następnie zastosować różnicę pobieraną co godzinę z urządzenia podrzędnego.

Martwię się jednak, jak radzić sobie z częściową awarią lub awarią pojedynczej bazy danych. Mogę wymyślić 2 scenariusze, które są całkiem prawdopodobne;

1. baza danych 7 (na przykład) ulega uszkodzeniu, nadal obsługuje niektóre żądania, dopóki ktoś nie zauważy, że jest uszkodzona, lub ostrzega z plików dziennika ...
2. Niektóre zapytania, takie jak upuszczanie bazy danych, upuszczanie tabeli, zapytanie typu „aktualizuj gdzie ...”, uszkadza pojedynczą bazę danych lub pewien jej podzbiór.

W tej chwili mam kilka pełnych zrzutów w postaci plików FULL- $ DATE-all-databases.sql.gz oraz różnic, które można zastosować do pełnych zrzutów jako DIFF- $ DATE-all-databases.sql.gz

Aby przywrócić bazę danych 7 do pewnego momentu w czasie, konieczne będzie grep przez pliki FULL i DIFF oraz ręczne zastosowanie tej sql.

Jak powinienem postępować, aby móc odzyskać dane na jednym z poprzednich zrzutów DIFF do głównej bazy danych?

Czy muszę wykonać kopię zapasową do poszczególnych plików bazy danych, tj

mysqldump --databases "database1" | gzip > database1.sql.gz
mysqldump --databases "database2" | gzip > database2.sql.gz
mysqldump --databases "database3" | gzip > database3.sql.gz

zamiast..

mysqldump --master-data --lock--all-databases --all-databases | gzip > all-databases.sql.gz

Jeśli wybiorę osobne pliki mysqldump, co stanie się z dziennikiem binarnym danych podstawowych i czy powinienem nawet ustawić - dane-główne dla zrzutów odzyskiwania serwera głównego?

Jeśli cała baza danych korzysta tylko z InnoDB, mam dobre wieści.

Powinieneś zrzucić całą bazę danych równolegle z urządzenia podrzędnego.

W rzeczywistości możesz wymusić na wszystkich bazach danych ten sam punkt w czasie.

Pierwszą rzeczą do zapamiętania o Slave jest to, że nie jest wymagane włączenie rejestrowania binarnego, jeśli nie jest on Master dla innych Slave.

Nie można użyć --master-dataopcji dla zrzutów równoległych, ponieważ każdy zrzut będzie miał inną pozycję zapisaną w linii 22 każdego pliku zrzutu. Lepiej jest zapisać ostatni plik dziennika Master i ustawić Slave za pomocą SHOW SLAVE STATUS\G. W ten sposób wszystkie bazy danych mają tę samą pozycję w czasie.

Możesz zebrać wszystkie bazy danych i wykonać skrypt równoległego zrzutu całej bazy danych.

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

for DB in `cat ${DBLIST}` 
do 
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & 
done 
wait 

mysql -h... -u... -p... -e"START SLAVE;"

Jeśli istnieje po prostu zbyt wiele baz danych, zrzuć je 10 lub 20 na raz w następujący sposób:

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DB in `cat ${DBLIST}` 
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 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

Jeśli potrzebujesz odzyskać pojedynczą tabelę, możesz równolegle zrzucać tabele 20 na raz w kolejności wielkości.

Spróbuj tego:

TBLIST=/tmp/ListOfTablesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY data_length" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DBTB in `cat ${TBLIST}` 
do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    DUMPFILE=$DB-{DB}-TBL-${TB}.sql.gz
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} ${TB} | gzip >  ${DUMPFILE} & 
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

Teraz, gdy masz skrypty do zrzucania baz danych lub pojedynczych tabel, możesz załadować te dane według własnego uznania. Jeśli chcesz uruchomić SQL z dzienników binarnych w systemie głównym, możesz użyć mysqlbinlogi nadać mu pozycję datetime i wyprowadzić SQL do innych plików tekstowych. Musisz tylko dołożyć należytej staranności, aby znaleźć potrzebną ilość danych z dowolnych znaczników czasu, jakie mają dzienniki bnary. Pamiętaj tylko, że znacznik czasu każdego dziennika binarnego w systemie operacyjnym oznacza, że ​​był ostatnio zapisywany.