Jak wspomniano w odpowiedzi Grega , mysqldump db_name | mysql new_db_namejest to darmowy, bezpieczny i łatwy sposób przesyłania danych między bazami danych. Jednak jest też bardzo wolny .
Jeśli chcesz wykonać kopię zapasową danych, nie możesz pozwolić sobie na utratę danych (w tej lub innych bazach danych) lub używasz tabel innych niż innodb, powinieneś użyć mysqldump.
Jeśli szukasz czegoś do programowania, masz kopie zapasowe wszystkich baz danych w innym miejscu i możesz wygodnie czyścić i ponownie instalować mysql(prawdopodobnie ręcznie), gdy wszystko pójdzie nie tak, to mogę po prostu mieć dla Ciebie rozwiązanie.
Nie mogłem znaleźć dobrej alternatywy, więc stworzyłem scenariusz, aby zrobić to sam. Spędziłem dużo czasu, aby to zadziałało za pierwszym razem i szczerze mówiąc, trochę mnie przeraża, że mogę teraz wprowadzić zmiany. Bazy danych Innodb nie były przeznaczone do kopiowania i wklejania w ten sposób. Drobne zmiany powodują, że kończy się to niepowodzeniem w wspaniały sposób. Nie miałem problemu, odkąd sfinalizowałem kod, ale to nie znaczy, że tego nie zrobisz.
Systemy testowane na (ale nadal mogą zawieść):
- Ubuntu 16.04, domyślny mysql, innodb, oddzielne pliki na tabelę
- Ubuntu 18.04, domyślny mysql, innodb, oddzielne pliki na tabelę
Co to robi
- Uzyskuje
sudouprawnienia i sprawdza, czy masz wystarczającą ilość miejsca do sklonowania bazy danych
- Pobiera uprawnienia roota mysql
- Tworzy nową bazę danych o nazwie zgodnej z bieżącą gałęzią git
- Struktura klonów do nowej bazy danych
- Przełącza się w tryb odzyskiwania dla innodb
- Usuwa domyślne dane w nowej bazie danych
- Zatrzymuje mysql
- Klonuje dane do nowej bazy danych
- Uruchamia mysql
- Linki zaimportowane dane do nowej bazy danych
- Wyłącza tryb odzyskiwania dla innodb
- Ponownie uruchamia mysql
- Daje użytkownikowi mysql dostęp do bazy danych
- Czyści pliki tymczasowe
Jak to wypada w porównaniu z mysqldump
W przypadku bazy danych 3 GB użycie mysqldumpi mysqlzajęłoby 40-50 minut na moim komputerze. Używając tej metody, ten sam proces zajmie tylko ~ 8 minut.
Jak tego używamy
Zapisujemy nasze zmiany SQL wraz z naszym kodem, a proces uaktualniania jest zautomatyzowany zarówno pod względem produkcji, jak i rozwoju, a każdy zestaw zmian tworzy kopię zapasową bazy danych w celu przywrócenia w przypadku błędów. Jednym z problemów, na który natknęliśmy się, było to, że pracowaliśmy nad długoterminowym projektem ze zmianami w bazie danych i musieliśmy przełączać gałęzie w środku, aby naprawić błąd lub trzy.
W przeszłości używaliśmy jednej bazy danych dla wszystkich gałęzi i musielibyśmy odbudować bazę danych za każdym razem, gdy przełączaliśmy się na gałąź, która nie była zgodna z nowymi zmianami bazy danych. A kiedy wróciliśmy, musielibyśmy ponownie uruchomić aktualizacje.
Próbowaliśmy mysqldumpzduplikować bazę danych dla różnych gałęzi, ale czas oczekiwania był zbyt długi (40-50 minut) i w międzyczasie nie mogliśmy zrobić nic więcej.
To rozwiązanie skróciło czas klonowania bazy danych do 1/5 czasu (pomyśl o przerwie na kawę i łazienkę zamiast długiego lunchu).
Wspólne zadania i ich czas
Przełączanie między gałęziami z niekompatybilnymi zmianami bazy danych zajmuje ponad 50 minut w jednej bazie danych, ale nie ma czasu po początkowym czasie konfiguracji z mysqldumptym kodem. Tak się składa, że ten kod jest ~ 5 razy szybszy niż mysqldump.
Oto kilka typowych zadań i przybliżony czas ich trwania w przypadku każdej metody:
Utwórz gałąź funkcji ze zmianami w bazie danych i natychmiast scal:
- Pojedyncza baza danych: ~ 5 minut
- Klonuj za pomocą
mysqldump: 50-60 minut
- Sklonuj z tym kodem: ~ 18 minut
Utwórz gałąź funkcji ze zmianami w bazie danych, przełącz się masterna, aby naprawić błąd, dokonaj edycji w gałęzi funkcji i scal:
- Pojedyncza baza danych: ~ 60 minut
- Klonuj za pomocą
mysqldump: 50-60 minut
- Sklonuj z tym kodem: ~ 18 minut
Utwórz gałąź funkcji ze zmianami w bazie danych, przełącz się masterna 5 razy w celu usunięcia błędu, dokonując zmian w gałęzi funkcji między nimi i scal:
- Pojedyncza baza danych: ~ 4 godziny, 40 minut
- Klonuj za pomocą
mysqldump: 50-60 minut
- Sklonuj z tym kodem: ~ 18 minut
Kod
Nie używaj tego, chyba że przeczytałeś i zrozumiałeś wszystko powyżej.
#!/bin/bash
set -e
# This script taken from: https://stackoverflow.com/a/57528198/526741
function now {
date "+%H:%M:%S";
}
# Leading space sets messages off from step progress.
echosuccess () {
printf "\e[0;32m %s: %s\e[0m\n" "$(now)" "$1"
sleep .1
}
echowarn () {
printf "\e[0;33m %s: %s\e[0m\n" "$(now)" "$1"
sleep .1
}
echoerror () {
printf "\e[0;31m %s: %s\e[0m\n" "$(now)" "$1"
sleep .1
}
echonotice () {
printf "\e[0;94m %s: %s\e[0m\n" "$(now)" "$1"
sleep .1
}
echoinstructions () {
printf "\e[0;104m %s: %s\e[0m\n" "$(now)" "$1"
sleep .1
}
echostep () {
printf "\e[0;90mStep %s of 13:\e[0m\n" "$1"
sleep .1
}
MYSQL_CNF_PATH='/etc/mysql/mysql.conf.d/recovery.cnf'
OLD_DB='YOUR_DATABASE_NAME'
USER='YOUR_MYSQL_USER'
# You can change NEW_DB to whatever you like
# Right now, it will append the current git branch name to the existing database name
BRANCH=`git rev-parse --abbrev-ref HEAD`
NEW_DB="${OLD_DB}__$BRANCH"
THIS_DIR=./site/upgrades
DB_CREATED=false
tmp_file () {
printf "$THIS_DIR/$NEW_DB.%s" "$1"
}
sql_on_new_db () {
mysql $NEW_DB --unbuffered --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')
}
general_cleanup () {
echoinstructions 'Leave this running while things are cleaned up...'
if [ -f $(tmp_file 'errors.log') ]; then
echowarn 'Additional warnings and errors:'
cat $(tmp_file 'errors.log')
fi
for f in $THIS_DIR/$NEW_DB.*; do
echonotice 'Deleting temporary files created for transfer...'
rm -f $THIS_DIR/$NEW_DB.*
break
done
echonotice 'Done!'
echoinstructions "You can close this now :)"
}
error_cleanup () {
exitcode=$?
# Just in case script was exited while in a prompt
echo
if [ "$exitcode" == "0" ]; then
echoerror "Script exited prematurely, but exit code was '0'."
fi
echoerror "The following command on line ${BASH_LINENO[0]} exited with code $exitcode:"
echo " $BASH_COMMAND"
if [ "$DB_CREATED" = true ]; then
echo
echonotice "Dropping database \`$NEW_DB\` if created..."
echo "DROP DATABASE \`$NEW_DB\`;" | sql_on_new_db || echoerror "Could not drop database \`$NEW_DB\` (see warnings)"
fi
general_cleanup
exit $exitcode
}
trap error_cleanup EXIT
mysql_path () {
printf "/var/lib/mysql/"
}
old_db_path () {
printf "%s%s/" "$(mysql_path)" "$OLD_DB"
}
new_db_path () {
printf "%s%s/" "$(mysql_path)" "$NEW_DB"
}
get_tables () {
(sudo find /var/lib/mysql/$OLD_DB -name "*.frm" -printf "%f\n") | cut -d'.' -f1 | sort
}
STEP=0
authenticate () {
printf "\e[0;104m"
sudo ls &> /dev/null
printf "\e[0m"
echonotice 'Authenticated.'
}
echostep $((++STEP))
authenticate
TABLE_COUNT=`get_tables | wc -l`
SPACE_AVAIL=`df -k --output=avail $(mysql_path) | tail -n1`
SPACE_NEEDED=(`sudo du -s $(old_db_path)`)
SPACE_ERR=`echo "$SPACE_AVAIL-$SPACE_NEEDED" | bc`
SPACE_WARN=`echo "$SPACE_AVAIL-$SPACE_NEEDED*3" | bc`
if [ $SPACE_ERR -lt 0 ]; then
echoerror 'There is not enough space to branch the database.'
echoerror 'Please free up some space and run this command again.'
SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
exit 1
elif [ $SPACE_WARN -lt 0 ]; then
echowarn 'This action will use more than 1/3 of your available space.'
SPACE_AVAIL_FORMATTED=`printf "%'d" $SPACE_AVAIL`
SPACE_NEEDED_FORMATTED=`printf "%'${#SPACE_AVAIL_FORMATTED}d" $SPACE_NEEDED`
echonotice "$SPACE_NEEDED_FORMATTED bytes needed to create database branch"
echonotice "$SPACE_AVAIL_FORMATTED bytes currently free"
printf "\e[0;104m"
read -p " $(now): Do you still want to branch the database? [y/n] " -n 1 -r CONFIRM
printf "\e[0m"
echo
if [[ ! $CONFIRM =~ ^[Yy]$ ]]; then
echonotice 'Database was NOT branched'
exit 1
fi
fi
PASS='badpass'
connect_to_db () {
printf "\e[0;104m %s: MySQL root password: \e[0m" "$(now)"
read -s PASS
PASS=${PASS:-badpass}
echo
echonotice "Connecting to MySQL..."
}
create_db () {
echonotice 'Creating empty database...'
echo "CREATE DATABASE \`$NEW_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" | mysql -u root -p$PASS 2>> $(tmp_file 'errors.log')
DB_CREATED=true
}
build_tables () {
echonotice 'Retrieving and building database structure...'
mysqldump $OLD_DB --skip-comments -d -u root -p$PASS 2>> $(tmp_file 'errors.log') | pv --width 80 --name " $(now)" > $(tmp_file 'dump.sql')
pv --width 80 --name " $(now)" $(tmp_file 'dump.sql') | sql_on_new_db
}
set_debug_1 () {
echonotice 'Switching into recovery mode for innodb...'
printf '[mysqld]\ninnodb_file_per_table = 1\ninnodb_force_recovery = 1\n' | sudo tee $MYSQL_CNF_PATH > /dev/null
}
set_debug_0 () {
echonotice 'Switching out of recovery mode for innodb...'
sudo rm -f $MYSQL_CNF_PATH
}
discard_tablespace () {
echonotice 'Unlinking default data...'
(
echo "USE \`$NEW_DB\`;"
echo "SET foreign_key_checks = 0;"
get_tables | while read -r line;
do echo "ALTER TABLE \`$line\` DISCARD TABLESPACE; SELECT 'Table \`$line\` imported.';";
done
echo "SET foreign_key_checks = 1;"
) > $(tmp_file 'discard_tablespace.sql')
cat $(tmp_file 'discard_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
import_tablespace () {
echonotice 'Linking imported data...'
(
echo "USE \`$NEW_DB\`;"
echo "SET foreign_key_checks = 0;"
get_tables | while read -r line;
do echo "ALTER TABLE \`$line\` IMPORT TABLESPACE; SELECT 'Table \`$line\` imported.';";
done
echo "SET foreign_key_checks = 1;"
) > $(tmp_file 'import_tablespace.sql')
cat $(tmp_file 'import_tablespace.sql') | sql_on_new_db | pv --width 80 --line-mode --size $TABLE_COUNT --name " $(now)" > /dev/null
}
stop_mysql () {
echonotice 'Stopping MySQL...'
sudo /etc/init.d/mysql stop >> $(tmp_file 'log')
}
start_mysql () {
echonotice 'Starting MySQL...'
sudo /etc/init.d/mysql start >> $(tmp_file 'log')
}
restart_mysql () {
echonotice 'Restarting MySQL...'
sudo /etc/init.d/mysql restart >> $(tmp_file 'log')
}
copy_data () {
echonotice 'Copying data...'
sudo rm -f $(new_db_path)*.ibd
sudo rsync -ah --info=progress2 $(old_db_path) --include '*.ibd' --exclude '*' $(new_db_path)
}
give_access () {
echonotice "Giving MySQL user \`$USER\` access to database \`$NEW_DB\`"
echo "GRANT ALL PRIVILEGES ON \`$NEW_DB\`.* to $USER@localhost" | sql_on_new_db
}
echostep $((++STEP))
connect_to_db
EXISTING_TABLE=`echo "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$NEW_DB'" | mysql --skip-column-names -u root -p$PASS 2>> $(tmp_file 'errors.log')`
if [ "$EXISTING_TABLE" == "$NEW_DB" ]
then
echoerror "Database \`$NEW_DB\` already exists"
exit 1
fi
echoinstructions "The hamsters are working. Check back in 5-10 minutes."
sleep 5
echostep $((++STEP))
create_db
echostep $((++STEP))
build_tables
echostep $((++STEP))
set_debug_1
echostep $((++STEP))
discard_tablespace
echostep $((++STEP))
stop_mysql
echostep $((++STEP))
copy_data
echostep $((++STEP))
start_mysql
echostep $((++STEP))
import_tablespace
echostep $((++STEP))
set_debug_0
echostep $((++STEP))
restart_mysql
echostep $((++STEP))
give_access
echo
echosuccess "Database \`$NEW_DB\` is ready to use."
echo
trap general_cleanup EXIT
Jeśli wszystko pójdzie gładko, powinieneś zobaczyć coś takiego:
