Percona XtraBackup ãããããŒã¿ããŒã¹ããã¯ã¢ããçšã®ãŠãŒãã£ãªãã£ã§ã MySQL.
ããŒã¿ã®ããã¯ã¢ãããäœæãããšããããŒãã«ã¯ããã¯ããããã·ã¹ãã ã¯å¶éãªãåäœãç¶ããŸãã
ãšã¯ã¹ãã©ããã¯ã¢ãã 2.4 ããŒãã«ã®ããã¯ã¢ããã³ããŒãäœæã§ãã InnoDB, XtraDB О MyISAM ãµãŒããŒäžã§ MySQL 5.11ã5.5ã5.6ãããã³ 5.7ããµãŒããŒäžã§ã ãã«ã³ã ã®ããã« MySQL Ñ XtraDB.
äžç·ã«åãã«ã¯ MySQL 8.x ããŒãžã§ã³ã䜿çšããå¿ èŠããããŸã ãšã¯ã¹ãã©ããã¯ã¢ãã 8.xã ãã®èšäºã§ã¯ä»¥äžã«ã€ããŠã®ã¿èª¬æããŸã ãšã¯ã¹ãã©ããã¯ã¢ãã 2.4ã
äž»ãªå©ç¹ ãšã¯ã¹ãã©ããã¯ã¢ãã ãã®ãŠãŒãã£ãªãã£ã¯ãé«è² è·ã®ãµãŒããŒã®ããã¯ã¢ããã®äœæãšããã©ã³ã¶ã¯ã·ã§ã³æ°ã®å°ãªãã·ã¹ãã ã®äž¡æ¹ã«é©ããŠãããšããããšã§ãã
MySQL ããŒã¿ããŒã¹ã®åèšãµã€ãºã倧ããå Žå (æ°åã®ã¬ãã€ã)ãæšæºãŠãŒãã£ãªã㣠mysqldump ããã¯ã¢ãã ã³ããŒãããã«äœæããããšã¯ã§ããããã³ãã®åŸ©å ã«ã¯æéãããããŸãã
ã€ã³ã¹ããŒã«
ã€ã³ã¹ããŒã« ãšã¯ã¹ãã©ããã¯ã¢ãã ãªããžããªãã apt ãã«ã³ãã
次ã®ã³ãã³ããé çªã«å®è¡ããŸãã
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24
2ã ã€ã³ã¹ããŒã«åŸãã³ãã³ããå®è¡ããŸã xtrabackup -v
ã ãããã£ãŠããŠãŒãã£ãªãã£ããµãŒããŒäžã§æ£ããåäœããããšã確èªããããšãéèŠã§ãã ãã®çµæã次ã®ãããªãã®ãç»é¢ã«è¡šç€ºãããŸãã
xtrabackup: èªèããããµãŒããŒåŒæ°: - datadir=/var/lib/mysql - tmpdir=/tmp -server-id=1 - logbin=/var/log/mysql/mysql-bin.log - innodbbufferpoolsize=16384M - innodbfilepertable=1 - innodbflushmethod=Odirect â innodbflushlogattrxcommit=0xtrabackup ããŒãžã§ã³ 2.4.20 (MySQL ãµãŒã㌠5.7.26 Linux (x8664) ããŒã¹) (ãªããžã§ã³ ID: c8b4056)
ã¢ã¯ã»ã¹æš©ãèš±å¯ãç¹æš©
XtraBackup ã¯æ¬¡ã®ããšãã§ããå¿ èŠããããŸãã
-
MySQL ãµãŒããŒã«æ¥ç¶ããŸãã
-
ãã£ã¬ã¯ããªãžã®ã¢ã¯ã»ã¹æš©ããã ããŒã¿ãã£ã¬ã¯ããª.
-
ããã¯ã¢ãã ã³ããŒãäœæããå Žåãæå®ããããã©ã¡ãŒã¿ãžã®æžã蟌ã¿æš©éãå¿ èŠã§ã ã¿ãŒã²ãããã£ã¬ã¯ã㪠ã«ã¿ãã°ã
ããŒã¿ãã£ã¬ã¯ããªãšã¯äœã§ãã?
ããŒã¿ãã£ã¬ã¯ã㪠ããŒã¿ããŒã¹ãµãŒããŒã眮ãããŠãããã£ã¬ã¯ããªã§ã MySQL ããŒã¿ãä¿åããŸãã ãã¹ãŠã®ããŒã¿ããŒã¹ããã¹ãŠã®ããŒãã«ãããã«ãããŸãã ã»ãšãã©ã® Linux ãã£ã¹ããªãã¥ãŒã·ã§ã³ã§ã¯ãããã©ã«ãã®ãã£ã¬ã¯ããªã¯æ¬¡ã®ãšããã§ãã / var / lib / mysql.
target-dir ãã£ã¬ã¯ããªãšã¯äœã§ãã?
ã¿ãŒã²ãããã£ã¬ã¯ã㪠- ããã¯ããã¯ã¢ãããä¿åããããã£ã¬ã¯ããªã§ãã
ããŒã¿ããŒã¹ ãŠãŒã¶ãŒã«ã¯ãããã¯ã¢ããããããŒãã«ãšããŒã¿ããŒã¹ã«å¯Ÿãã次ã®ã¢ã¯ã»ã¹æš©ãå¿ èŠã§ãã
-
ããŒãã«ã®ãªããŒããšããã¯
-
ã¬ããªã±ãŒã·ã§ã³ã¯ã©ã€ã¢ã³ã
-
ããŒãã«ã¹ããŒã¹ã®äœæ
-
ããã»ã¹
-
ã¹ãŒããŒ
-
CREATE
-
INSERT
-
SELECT
èšå®
èšå® ãšã¯ã¹ãã©ããã¯ã¢ãã æšæºã® MySQL ãªãã·ã§ã³ãšåãããã«åäœãããªãã·ã§ã³ã䜿çšããŠå®è¡ãããŸãã
ããã¯ã©ãããæå³ã§ããïŒ
æ§æãã©ã¡ãŒã¿ã¯ãã³ãã³ãã©ã€ã³ãŸã㯠DBMS æ§æãã¡ã€ã«ã§æå®ã§ããŸããããšãã°ã / etc / my.cnfã®.
XtraBackup ãŠãŒãã£ãªãã£ãèµ·ååŸã«ããŒãã£ã·ã§ã³ãèªã¿åã [mysqldã] О [ãšã¯ã¹ãã©ããã¯ã¢ãã] MySQL èšå®ãã¡ã€ã«ããã ããã¯ãããã¯ã¢ããã®ãã³ã«ãã©ã¡ãŒã¿ãæåã§æå®ããããšãªãããŠãŒãã£ãªãã£ã DBMS ã®èšå®ã䜿çšã§ããããã«ããããã«è¡ãããŸãã
ããšãã°ãå€ã¯ ããŒã¿ãã£ã¬ã¯ã㪠ããã³ããã€ãã®ãã©ã¡ãŒã¿ InnoDB DBMS ã®æ§æãã XtraBackup ãååŸããŸãã
XtraBackup ãæ©èœããããã«ããã«ã¯ãã»ã¯ã·ã§ã³ã«ãããã©ã¡ãŒã¿ããªãŒããŒã©ã€ãããå¿ èŠããããŸãã [mysqldã]ãã»ã¯ã·ã§ã³ã®æ§æãã¡ã€ã«ã§ããããæå®ããã ãã§ãã [ãšã¯ã¹ãã©ããã¯ã¢ãã]ã åŸã§èªã¿èŸŒãŸãããããåªå 床ãé«ããªããŸãã
ãã©ã¡ãŒã¿ãè¿œå ããå¿ èŠã¯ãããŸãã my.cnfã å¿ èŠãªãã©ã¡ãŒã¿ã¯ãã¹ãŠã³ãã³ã ã©ã€ã³ã§æå®ã§ããŸãã éåžžãã»ã¯ã·ã§ã³ã«äŸ¿å©ã«é 眮ã§ããã®ã¯ããã ãã§ã [ãšã¯ã¹ãã©ããã¯ã¢ãã] ããªãã® my.cnf ãã©ã¡ãŒã¿ã§ã ã¿ãŒã²ãããã£ã¬ã¯ããªãããã©ã«ãã§ã¯ãããã¯ã¢ãããé 眮ããããã£ã¬ã¯ããªãæå®ããŸãã ãã ããããã¯ãªãã·ã§ã³ã§ãã
ããã¯ã¢ããã³ããŒã®ãããã£ã¬ã¯ããªãžã®ãã¹ãæå®ããäŸ my.cnf:
[xtrabackup]
target_dir = /data/backups/mysql/
ããã¯ã¢ããã¹ã¯ãªãã
次ã®ã¹ã¯ãªããã䜿çšããŠããã¯ã¢ãããäœæã§ããŸãã
#!/bin/bash
# УЎалÑеЌ ЎаММÑе в каÑалПге бекапа
rm -rf /mysql/backup
# CПзЎаÑÐŒ бекап
xtrabackup --user=xtrabackup
--password=xxxx_SECRET_xxxx
--backup
--target-dir=/mysql/backup
# ÐÑпПлМÑеЌ пПЎгПÑÐŸÐ²ÐºÑ Ð±ÐµÐºÐ°Ð¿Ð° ÐŽÐ»Ñ ÑазвÑÑÑÑваМОÑ
xtrabackup --prepare --target-dir=/mysql/backup
# СПзЎаÑÐŒ аÑÑ
Ов
tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-"$(date +%F-%H:%M:%S)".gz /mysql/backup
ã¹ã¯ãªããã®å®è¡äžã«äœãèµ·ããã®ã§ãããã?
ãŸãæåã«ãããã¯ã¢ãã ã³ããŒãä¿åãããã£ã¬ã¯ããªãã¯ãªã¢ (åé€) ããŸãã
rm -rf /mysql/backup.
次ã«ããŠãŒãã£ãªãã£ã䜿çšããŠã ãšã¯ã¹ãã©ããã¯ã¢ãã ããã¯ã¢ããã³ããŒãäœæããŠä¿åããŸã /mysql/ããã¯ã¢ãã/:
xtrabackup --user=xtrabackup --password=xxxxz1cYf95550Gc6xxxxxxxpE3rB03xxxx --backup --target-dir=/mysql/backup
以åã¯ãMySQL ã§ãŠãŒã¶ãŒãäœæããŸãã xtrabackup
å¿
èŠãªæš©éãåããŠããŸãã ãã©ã¡ãŒã¿ã®äœ¿çš target-dir
ããã¯ã¢ãããä¿åãããã£ã¬ã¯ããªãæå®ããŸãã
éèŠãªãã€ã³ãïŒ
次ã®ã¹ã¯ãªããè¡ã«æ³šç®ããŠãã ããã
xtrabackup --prepare --target-dir=/mysql/backup
ã«ã¿ãã°ããŒã¿ /mysql/ããã¯ã¢ãã 調çãããŸã§äžè²«æ§ããããŸããã
å®éããã¡ã€ã«ã®ã³ããŒäžã«å€æŽãçºçããå¯èœæ§ããããŸãã æè¡ xtrabackup --prepare --target-dir=/mysql/backup
ããã¯ã¢ãã ããŒã¿ãé·æéã«ããã£ãŠå®å
šã«äžè²«æ§ã®ãããã®ã«ããŸãã
ããŒã¿æºåæäœã¯ã©ã®ãã·ã³ã§ãå®è¡ã§ããŸãã ãœãŒã¹ DBMS ãé 眮ãããŠãããµãŒããŒäžã§ãããè¡ãå¿ èŠã¯ãããŸããã ããã¯ã¢ãããã¿ãŒã²ãã ãµãŒããŒã«ã³ããŒããããã§æºåããããšãã§ããŸãã
æåŸã«ãããã¯ã¢ãããé 眮ããã¢ãŒã«ã€ããäœæããŸãã
tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-«$(date % F% H% M% S)».gz /mysql/backup
ããã¯ã¢ããã®åŸ©å
ããã¯ã¢ãããã¿ãŒã²ãã ãµãŒããŒã«åŸ©å ããåã«ãããŒã¿ã¯æºåãã§ãŒãºãééããå¿ èŠããããŸãã ãããè¡ãæ¹æ³ã«ã€ããŠã¯äžèšãåç §ããŠãã ããã
ããŒã¿åŸ©æ§ããã»ã¹ã¯éåžžã«ç°¡åã§ãã ã¢ãŒã«ã€ãããããã¯ã¢ãããæœåºãã次ã®ããŒã¿ã眮ãæããå¿ èŠããããŸãã ããŒã¿ãã£ã¬ã¯ããª.
datadirå ã®ããŒã¿ã眮ãæããã«ã¯ã©ãããã°ããã§ãã?
XNUMX ã€ã®ãªãã·ã§ã³ãèããŠã¿ãŸãããã
ãªãã·ã§ã³1
ãŠãŒãã£ãªãã£ã䜿çšãã ãšã¯ã¹ãã©ããã¯ã¢ããã ãªãã·ã§ã³ãæå®ããå¿ èŠããããŸã --ã³ããŒããã¯.
以äžã®ã³ãã³ãã¯ããã¯ã¢ããã転éããŸã ããŒã¿ãã£ã¬ã¯ã㪠ã¿ãŒã²ãããµãŒããŒ:
xtrabackup --copy-back --target-dir=/mysql/backup
ãªãã·ã§ã³2
ãŠãŒãã£ãªãã£ã䜿çšããã«å¥ã®æ¹æ³ã§å®è¡ããããšãã§ããŸã ãšã¯ã¹ãã©ããã¯ã¢ãã.
ããã¯ã¢ãããã³ããŒããã ãã§ãã ããŒã¿ãã£ã¬ã¯ããªã ãããè¡ãããšãã§ããŸã cp ãŸã㯠rsync.
ããã¯ã¢ããã埩å ããæé ã¯ãçµå±ã¯ãã£ã¬ã¯ããªã®å 容ã眮ãæããã ãã§ããããšãç解ããããšãéèŠã§ãã ããŒã¿ãã£ã¬ã¯ããª.
ã¿ãŒã²ãã ãµãŒããŒã§ããã¯ã¢ããã®åŸ©å ãéå§ããåã«ã次ã®ããšãè¡ãå¿ èŠããããŸãã
-
MySQLãµãŒããŒãåæ¢ããŸãã
-
空ã®ãã©ã«ã ããŒã¿ãã£ã¬ã¯ã㪠ãŸãã¯ããã®å 容ãå¥ã®å Žæã«ç§»åããŸãã ã«ã¿ãã° ããŒã¿ãã£ã¬ã¯ã㪠空ã§ãªããã°ãªããŸããã
ããŒã¿ç§»è¡å®äºåŸã ããŒã¿ãã£ã¬ã¯ã㪠MySQLãµãŒããŒãèµ·åã§ããããã«ãªããŸããã
䜿çšææ
åºæïŒ habr.com