Kopia zapasowa i przywracanie bazy danych PostgreSQL i MySQL
Kopia zapasowa jest jednym z kluczowych procedur zapewniających bezpieczeństwo danych. Nawet przy niezawodnym hostingu mogą wystąpić błędy aplikacji, nieprawidłowe aktualizacje lub przypadkowe usunięcie danych.
W tym przewodniku pokażemy, jak tworzyć i przywracać kopie zapasowe baz danych PostgreSQL i MySQL za pomocą standardowych narzędzi wiersza poleceń — pg_dump i mysqldump.
1. Kopia zapasowa PostgreSQL
1.1. Co to jest pg_dump
pg_dump to standardowe narzędzie PostgreSQL do tworzenia logicznej kopii zapasowej bazy danych. Zapisuje zarówno strukturę (DDL), jak i dane (DML) do pliku tekstowego SQL, który można później użyć do przywracania.
1.2. Składnia polecenia
pg_dump -U <user> -h <host> -p <port> -d <database> -F p -f <path_to_file.sql>
1.3. Przykład kopii zapasowej
pg_dump -U myuser -h localhost -d mydatabase -F p -f /backups/mydatabase_$(date +%F).sql
Wyjaśnienie parametrów:
- -U myuser — nazwa użytkownika PostgreSQL;
- -h localhost — host serwera (można podać IP);
- -d mydatabase — nazwa bazy danych;
- -F p — format wyjściowy (plain — zwykły SQL);
- -f — ścieżka do pliku, w którym zostanie zapisana kopia zapasowa;
- $(date +%F) — dodaje bieżącą datę do nazwy pliku (np. 2025-11-03).
1.4. Skompresowana kopia zapasowa
Aby zmniejszyć rozmiar pliku, można użyć kompresji gzip:
pg_dump -U myuser mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz
2. Przywracanie bazy danych PostgreSQL
2.1. Użycie psql
Aby przywrócić bazę danych z kopii zapasowej tekstowej, użyj:
psql -U myuser -d mydatabase -f /backups/mydatabase_2025-11-03.sql
Jeśli kopia zapasowa jest skompresowana:
gunzip -c /backups/mydatabase_2025-11-03.sql.gz | psql -U myuser -d mydatabase
2.2. Jeśli baza danych nie istnieje
Najpierw utwórz nową bazę danych:
createdb -U myuser newdatabase
psql -U myuser -d newdatabase -f /backups/mydatabase_2025-11-03.sql
3. Kopia zapasowa MySQL
3.1. Co to jest mysqldump
mysqldump to standardowe narzędzie do tworzenia zrzutów MySQL. Zapisuje polecenia SQL potrzebne do odtworzenia tabel i wstawienia danych.
3.2. Składnia polecenia
mysqldump -u <user> -p -h <host> <database> > <path_to_file.sql>
3.3. Przykład kopii zapasowej
mysqldump -u myuser -p mydatabase > /backups/mydatabase_$(date +%F).sql
Wyjaśnienie parametrów:
- -u myuser — nazwa użytkownika MySQL;
- -p — prosi o hasło;
- mydatabase — nazwa bazy danych;
- > — przekierowuje wyjście do pliku.
3.4. Skompresowany zrzut
mysqldump -u myuser -p mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz
4. Przywracanie bazy danych MySQL
4.1. Przywracanie z pliku SQL
mysql -u myuser -p mydatabase < /backups/mydatabase_2025-11-03.sql
4.2. Jeśli baza danych nie istnieje
Utwórz nową bazę danych przed przywróceniem:
mysql -u myuser -p -e "CREATE DATABASE newdatabase;"
mysql -u myuser -p newdatabase < /backups/mydatabase_2025-11-03.sql
4.3. Przywracanie ze skompresowanego zrzutu
gunzip -c /backups/mydatabase_2025-11-03.sql.gz | mysql -u myuser -p mydatabase
5. Automatyzacja i zalecenia dotyczące bezpieczeństwa
5.1. Automatyczne kopie zapasowe z Cron
Aby regularnie wykonywać kopie zapasowe bez interwencji manualnej, skonfiguruj automatyzację za pomocą cron.
Podstawowa konfiguracja cron:
# Otwórz edytor cron crontab -e
Dodaj linię, na przykład:
0 2 * * * /usr/local/bin/db_backup.sh
To polecenie uruchomi skrypt codziennie o 2:00 rano.
Zaawansowane podejście z użyciem skryptu. Utwórz /usr/local/bin/db_backup.sh:
#!/bin/bash DATE=$(date +%F) BACKUP_DIR="/backup/$DATE" mkdir -p $BACKUP_DIR # Kopia zapasowa PostgreSQL pg_dump -U myuser mydatabase | gzip > $BACKUP_DIR/postgres_mydatabase.sql.gz # Kopia zapasowa MySQL mysqldump -u myuser -p mydatabase | gzip > $BACKUP_DIR/mysql_mydatabase.sql.gz # Usuń kopie zapasowe starsze niż 7 dni find /backup/ -type f -mtime +7 -delete
Uczyń skrypt wykonywalnym:
chmod +x /usr/local/bin/db_backup.sh
I dodaj do cron:
0 2 * * * /usr/local/bin/db_backup.sh
5.2. Zalecenia dotyczące bezpieczeństwa i przechowywania
- Przechowywanie poza serwerem: przechowuj kopie zapasowe w chmurze (AWS S3, Google Cloud) lub na zewnętrznych dyskach.
- Kontrola dostępu: ogranicz dostęp do folderu /backups, ponieważ pliki mogą zawierać poufne dane.
chmod 700 /backup
chmod 600 /backup/*.sql.gz
- Weryfikacja integralności: przeprowadzaj testowe przywracanie co miesiąc, aby zweryfikować funkcjonalność kopii zapasowej.
- Poufność: pliki kopii zapasowych zawierają zawartość bazy danych — traktuj je jako poufne informacje.
Podsumowanie
Użycie pg_dump i mysqldump to łatwy, niezawodny i uniwersalny sposób na tworzenie kopii zapasowych baz danych PostgreSQL i MySQL. Narzędzia te nadają się zarówno do ręcznych kopii zapasowych, jak i zautomatyzowanych procesów przy minimalnym wysiłku.
Regularne kopie zapasowe są kluczem do stabilności projektu i bezpieczeństwa danych.
