Przejdź do treści

Backup/Restore MySQL database

Backup and restore MySQL database easy

Dziś szybki wpis na temat automatycznego backupu bazy danych MySQL. Opiszę jak łatwo i szybko można zautomatyzować backup baz danych MySQL.

Backup MySQL database:

1) Ręczny Backup MySQL database:

Na początku zacznijmy od ręcznego wykonania backupu. Po pierwsze trzeba zainstalować mysql-client, na komputerze który będzie robił backup. Gdy już go mamy określamy parametry połączenia, podajemy IP address serwera mysql i port jeśli jest niestandardowy. Będzie nam także potrzebna nazwa użytkownika i jego hasło. W przykładzie użyłem roota i jego hasła zapisanego w zmiennej. Teraz możemy określić jaką bazę chcemy backupować, albo wpisać ‘–all-databases‘ co spowoduje wybór wszystkich. Na końcu zapisujemy całość we wskazanej lokalizacji u mnie jest to /mnt/cifs/backup_alldb_$DATA1.sql. Lokalizacja zawiera zmienną $DATA1 która dopisuje w pliku aktulaną datę.

apt-get install mysql-client
DATA1=$(date +"%Y%m%d")
mysqldump --host=10.10.10.10 --port=30006 --user=root --password=$MYSQL_PASS --all-databases  > /mnt/cifs/backup_alldb_$DATA1.sql

2) Automatyzacja Backup MySQL database – Sposób 1:

Automatyzacja tego procesu jest prosta, ale zabieramy się za to dopiero gdy sprawdziliśmy, że działa nam ręczne wykonanie backupu. Taki automatyczny backup można zamontować np w runnerze GitLaba, który może wykonać backup według schedulera. Tutaj używam obrazu mysql:5.7.28 który jest kompatybilny z tym na serwerze który będzie backupowany. Następnie montowany jest dysk sieciowy, na którym będą zapisywały się backupy. Sam backup wygląda niemal identycznie. Dodatkowo na końcu dopisałem fragment, który usuwa pliki starsze niż 30 dni. To czego tutaj nie napisałem, a co jeszcze trzeba zrobić, to zdefiniować zmienne, loginy i hasła oraz dodać schedulera.

backup_mysql_X :
   stage: deploy
   image: mysql:5.7.28
   script:
     - DATA1=$(date +"%Y%m%d")
       # tworzenie folderów i montowanie dysków
     - mkdir -p /mnt/cifs
     - mount -t cifs -o username=$backup_user,password=$backup_pass //11.11.11.11/backup/db_mysql_nazwa1 /mnt/cifs
     - ls /mnt/cifs/
     
     # wykonywanie backupu wszystkich baz
     - mysqldump --host=10.10.10.10 --port=30006 --user=$MYSQL_USER --password=$MYSQL_PASS --all-databases > /mnt/cifs/backup_alldb_NAZWA1_$DATA1.sql
     
     #usuwanie starszych plików niż 30 dni
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_alldb_NAZWA1*'
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_alldb_NAZWA1*' -exec rm {} \;
   only:
     refs:
       - schedules
   only:
     variables: 
       - $ACTION == "backup"

3) Automatyzacja Backup MySQL database – Sposób 2:

Zamiast tworzyć runnery można wykonać backup na serwerze mysql za pomocą crona. Wystarczy do crontaba(crontab -e) dopisać polecenie:

0 2 * * * /usr/bin/mysqldump -u dbuser mydb > /mnt/cifs/db_backups/mysql-db-$(date +\%Y\%m\%d).sql
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.sql" -mtime +30 -delete

A jeśli chcemy zaoszczędzić trochę miejsca to można pliki dodatkowo spakować:

0 2 * * * /usr/bin/mysqldump -u dbuser mydb | gzip > /mnt/cifs/db_backups/mysql-db-$(date +\%Y\%m\%d).sql.gz
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.sql.gz" -mtime +30 -delete

Restore MySQL database:

Często przydatna w przypadku awarii jest także opcja restore, by odtworzyć bazę danych z wykonanego wcześniej backupu. Najłatwiej jest to zrobić za pomocą polecenia:

# lokalnie:
mysql  database_name < file.sql

# zdalnie:
mysql -h 10.10.10.10 -u root --port=30543 -p NAZWA_BAZY < /mnt/db/file.sql

Poniżej kilka prostych dodatkowych poleceń dla mysql, które też czasem są przydatne by sprawdzić czy baza albo użytkownik jest na serwerze. Na serwer logujemy się lokalnie albo np za pomocą ‘mysql -h 10.10.10.10 -u root –port=30006 -p‘

# spis baz danych:
SHOW DATABASES;

# usuwa bazę danych:
# DROP DATABASE database_name;
# wyświetla tabele z bazy danych 'database_name':
USE database_name;
SHOW TABLES;
# wyświetla użytkowników:
SELECT user FROM mysql.user;
SELECT DISTINCT user FROM mysql.user;
SELECT user,host FROM mysql.user;
# wyświetla uprawnienia:
SHOW GRANTS;
SHOW GRANTS FOR 'user_name';
# wychodzimy wpisując poprostu
quit

Więcej informacji na temat samego mysql znajdziecie pod adresem https://dev.mysql.com/doc/refman/8.0/en/programs-client.html

Już niedługo kolejny artykuł mówiący o tym jak wykonać i zautomatyzować Backup PostgreSQL database.

Backup/Restore PostgreSQL database
Logowanie SSH bez hasła – jak używać kluczy
Tagi: