Jak wykonać kopię zapasową i przywrócić bazy danych mysql za pomocą mysqldump

W tym samouczku wyjaśniono, jak wykonać kopię zapasową i przywrócić bazy danych MySQL lub MariaDB z wiersza polecenia za pomocą narzędzia mysqldump.

Pliki kopii zapasowych utworzone przez narzędzie mysqldump są w zasadzie zestawem instrukcji SQL, których można użyć do odtworzenia oryginalnej bazy danych. Polecenie mysqldump może również generować pliki w formacie CSV i XML.

Możesz także użyć narzędzia mysqldump, aby przenieść bazę danych MySQL na inny serwer MySQL.

Składnia polecenia Mysqldump

Zanim przejdziemy do korzystania z polecenia mysqldump, zacznijmy od zapoznania się z podstawową składnią.

Wyrażenia narzędzia mysqldump mają następującą postać:

mysqldump > file.sql

• options - file.sql opcji mysqldump.sql - plik zrzutu (kopii zapasowej)

Aby użyć polecenia mysqldump, serwer MySQL musi być dostępny i uruchomiony.

Wykonaj kopię zapasową pojedynczej bazy danych MySQL

Najczęstszym przypadkiem użycia narzędzia mysqldump jest utworzenie kopii zapasowej pojedynczej bazy danych.

Na przykład, aby utworzyć kopię zapasową bazy danych o nazwie database_name przy użyciu root użytkownika i zapisać ją w pliku o nazwie database_name.sql , uruchom następującą komendę:

mysqldump -u root -p database_name > database_name.sql

Zostaniesz poproszony o podanie hasła roota. Po udanym uwierzytelnieniu rozpocznie się proces zrzutu. W zależności od wielkości bazy danych proces może zająć trochę czasu.

mysqldump database_name > database_name.sql

Utwórz kopię zapasową wielu baz danych MySQL

Aby wykonać kopię zapasową wielu baz danych MySQL za pomocą jednego polecenia, musisz użyć opcji --database , a następnie listy baz danych, których kopię zapasową chcesz wykonać. Każda nazwa bazy danych musi być oddzielona spacją.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

Powyższe polecenie utworzy plik zrzutu zawierający obie bazy danych.

Utwórz kopię zapasową wszystkich baz danych MySQL

Użyj opcji --all-databases , aby wykonać kopię zapasową wszystkich baz danych MySQL:

mysqldump -u root -p --all-databases > all_databases.sql

Podobnie jak w poprzednim przykładzie powyższe polecenie utworzy pojedynczy plik zrzutu zawierający wszystkie bazy danych.

Wykonaj kopię zapasową wszystkich baz danych MySQL w osobnych plikach

Narzędzie mysqldump nie zapewnia opcji tworzenia kopii zapasowych wszystkich baz danych w osobnych plikach, ale łatwo to osiągnąć za pomocą prostej pętli bash FOR :

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

Powyższe polecenie utworzy osobny plik zrzutu dla każdej bazy danych, używając nazwy bazy danych jako nazwy pliku.

Utwórz skompresowaną kopię zapasową bazy danych MySQL

Jeśli rozmiar bazy danych jest bardzo duży, dobrym pomysłem jest skompresowanie danych wyjściowych. Aby to zrobić, po prostu potokuj dane wyjściowe do narzędzia gzip i przekieruj je do pliku, jak pokazano poniżej:

mysqldump database_name | gzip > database_name.sql.gz

Utwórz kopię zapasową za pomocą znacznika czasu

mysqldump database_name > database_name-$(date +%Y%m%d).sql

Powyższe polecenie utworzy plik w następującym formacie database_name-20180617.sql

Przywracanie zrzutu MySQL

Możesz przywrócić zrzut MySQL za pomocą narzędzia mysql . Ogólna składnia polecenia jest następująca:

mysqld database_name < file.sql

W większości przypadków musisz utworzyć bazę danych, do której chcesz zaimportować. Jeśli baza danych już istnieje, najpierw musisz ją usunąć.

W poniższym przykładzie pierwsze polecenie utworzy bazę danych o nazwie database_name a następnie zaimportuje do niej zrzut database_name.sql :

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Przywróć pojedynczą bazę danych MySQL z pełnego zrzutu MySQL

mysql --one-database database_name < all_databases.sql

Eksportuj i importuj bazę danych MySQL w jednym poleceniu

Zamiast tworzyć plik zrzutu z jednej bazy danych, a następnie zaimportować kopię zapasową do innej bazy danych MySQL, możesz użyć następującego liniału:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

Powyższe polecenie remote_database_name dane wyjściowe do klienta mysql na zdalnym hoście i zaimportuje je do bazy danych o nazwie remote_database_name . Przed uruchomieniem polecenia upewnij się, że baza danych już istnieje na zdalnym serwerze.

Zautomatyzuj tworzenie kopii zapasowych za pomocą Crona

Automatyzacja procesu tworzenia kopii zapasowych baz danych jest tak prosta, jak utworzenie zadania cron, które uruchomi polecenie mysqldump w określonym czasie.

Aby skonfigurować automatyczne kopie zapasowe bazy danych MySQL przy użyciu usługi cronjob, wykonaj następujące czynności:

1. Utwórz plik o nazwie .my.cnf w katalogu .my.cnf użytkownika:

   sudo nano ~/.my.cnf

   Skopiuj i wklej następujący tekst do pliku.my.cnf.

   user = dbuser password = dbpasswd

   Nie zapomnij zastąpić dbuser i dbpasswd użytkownikiem bazy danych i hasłem użytkownika.

   Ogranicz uprawnienia do pliku poświadczeń, aby tylko Twój użytkownik miał do niego dostęp:

   chmod 600 ~/.my.cnf

   Utwórz katalog do przechowywania kopii zapasowych:

   mkdir ~/db_backups

   Otwórz plik crontab użytkownika:

   crontab -e

   Dodaj następujące zadanie cron, które będzie tworzyło kopię zapasową nazwy bazy danych mydb codziennie o 3 nad ranem:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   Nie zapomnij zastąpić username użytkownika rzeczywistą nazwą użytkownika.

Możesz także utworzyć kolejną usługę współdziałania, aby usunąć kopie zapasowe starsze niż 30 dni:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Oczywiście musisz dostosować polecenie do lokalizacji kopii zapasowej i nazw plików. Aby dowiedzieć się więcej o poleceniu find, zapoznaj się z naszym przewodnikiem Jak znaleźć pliki w systemie Linux.

Wniosek

Ten samouczek obejmuje tylko podstawy, ale powinien być dobrym początkiem dla każdego, kto chce nauczyć się tworzyć i przywracać bazy danych MySQL z wiersza poleceń za pomocą narzędzia mysqldump.

Możesz także sprawdzić samouczek dotyczący resetowania hasła root MySQL na wypadek, gdybyś go zapomniał.

mysql mariadb backup mysqldump