ProHoster > Π‘Π»ΠΎΠ³ > Kev tswj hwm > Tsim MySQL Backup Siv XtraBackup Utility

Tsim MySQL Backup Siv XtraBackup Utility

Percona XtraBackup yog ib qho kev pab cuam rau kub database backups MySQL.

Thaum tsim cov ntaub ntawv thaub qab, tsis muaj cov ntxhuav raug kaw, thiab koj lub cev tseem ua haujlwm yam tsis muaj kev txwv.

XtraBackup 2.4 tuaj yeem tsim cov ntawv luam ntawm cov rooj InnoDB, XtraDB ΠΈ MyISAM ntawm servers MySQL 5.11, 5.5, 5.6 thiab 5.7, thiab tseem nyob ntawm server percona rau MySQL Ρ XtraDB.

Ua haujlwm nrog MySQL 8.x version yuav tsum tau siv XtraBackup 8.x. Kab lus no tsuas yog tham txog XtraBackup 2.4.

Lub ntsiab kom zoo dua XtraBackup yog tias qhov kev siv hluav taws xob no tsim nyog rau ob qho tib si rau kev tsim cov thaub qab ntawm cov servers siab heev thiab rau cov tshuab uas muaj cov nqi qis.

Yog tias tag nrho qhov loj me ntawm koj MySQL databases tseem ceeb (tsawg ntawm gigabytes), ces tus qauv siv hluav taws xob mysqldump yuav tsis tso cai rau koj los tsim cov ntawv thaub qab sai sai, thiab rov qab kho cov pob tseg yuav siv sijhawm ntau.

chaw

chaw XtraBackup los ntawm qhov chaw cia khoom chav tsev Percona.

Khiav cov lus txib nram qab no ua ntu zus:

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. Tom qab installation, khiav cov lus txib xtrabackup -v. Txij li thaum, nws yog ib qho tseem ceeb kom paub tseeb tias cov nqi hluav taws xob ua haujlwm raug ntawm lub server. Yog li ntawd, ib yam dab tsi zoo li no yuav tshwm sim ntawm qhov screen:

xtrabackup: lees paub cov lus sib cav: - datadir = / var / lib / mysql - tmpdir = / tmp - server-id = 1 - logbin = / var / log / mysql / mysql-bin.log - innodbbufferpoolsize = 16384M - innodbfilepertable = 1 - innodbflushmethod=Odirect β€” innodbflushlogattrxcommit=0xtrabackup version 2.4.20 raws li MySQL server 5.7.26 Linux (x8664) (kho kho id: c8b4056)

Tsim MySQL Backup Siv XtraBackup Utility

Nkag mus rau cov cai, kev tso cai thiab cov cai 

XtraBackup yuav tsum muaj peev xwm:

  • Txuas rau koj lub MySQL server.

  • Muaj cai nkag mus rau hauv phau ntawv teev npe yog data. 

  • Thaum tsim ib daim ntawv luam theej tawm, muaj cai sau cov cai rau qhov kev teev tseg phiaj-dir catalog.

Datadir yog dab tsi?

yog data yog tus directory qhov twg database server MySQL khaws cov ntaub ntawv. Tag nrho cov databases, tag nrho cov rooj nyob ntawd. Ntawm feem ntau Linux kev faib tawm, cov ntawv teev npe ua ntej yog /var/lib/mysql.

Lub hom phiaj-dir directory yog dab tsi?

phiaj-dir - Nov yog daim ntawv teev npe uas cov thaub qab yuav raug cawm.

Tus neeg siv cov ntaub ntawv xav tau cov cai hauv qab no nkag mus rau cov ntxhuav thiab cov ntaub ntawv kom rov qab tau:

  • RELOAD thiab LOCK TABLE

  • REPLICATION Client

  • CREATE TABLESPACE

  • TXHEEJ TXHEEM

  • SUPER

  • tsim

  • NYIAJ

  • Xaiv

Configuration 

Configuration XtraBackup ua tiav siv cov kev xaiv uas coj zoo ib yam li cov qauv MySQL xaiv.

Qhov no txhais li cas?

Configuration parameters tuaj yeem teev nyob rau ntawm kab hais kom ua lossis hauv DBMS cov ntaub ntawv teeb tsa, piv txwv li hauv /etc/my.cnf.

XtraBackup qhov hluav taws xob nyeem cov partitions tom qab tso tawm [mysqld] ΠΈ [xtrabackup] los ntawm MySQL configuration files. Qhov no yog ua kom cov nqi hluav taws xob tuaj yeem siv qhov chaw ntawm koj DBMS yam tsis tas yuav tsum qhia cov kev txwv tsis pub dhau ntawm txhua qhov thaub qab.

Piv txwv li, tus nqi yog data thiab qee qhov parameter InnoDB Peb tau txais XtraBackup los ntawm kev teeb tsa ntawm koj DBMS.

Yog hais tias rau XtraBackup ua hauj lwm koj xav override cov tsis muaj nyob rau hauv seem [mysqld], ces cia li qhia lawv nyob rau hauv cov ntaub ntawv configuration nyob rau hauv seem [xtrabackup]. Txij li thaum lawv yuav tau nyeem tom qab, lawv qhov tseem ceeb yuav siab dua.

Koj tsis tas yuav ntxiv cov kev txwv rau kuv. cnf. Txhua yam uas yuav tsum tau muaj tuaj yeem teev rau ntawm kab hais kom ua. Feem ntau tsuas yog qhov uas tuaj yeem yooj yim muab tso rau hauv seem [xtrabackup] koj nws kuv. cnf yog parameter target_dir, uas los ntawm lub neej ntawd qhia txog cov npe uas cov ntaub ntawv yuav raug muab tso rau. Tab sis qhov no yog xaiv tau.

Ib qho piv txwv ntawm kev qhia txog txoj hauv kev mus rau cov npe nrog cov ntawv thaub qab hauv kuv. cnf:

[xtrabackup]
target_dir = /data/backups/mysql/

Cov ntawv thaub qab

Koj tuaj yeem siv cov ntawv hauv qab no los tsim ib qho thaub qab:

#!/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

Yuav ua li cas thaum sau ntawv?

Ua ntej tshaj plaws, peb tshem tawm (rho tawm) cov npe uas peb yuav khaws cov ntawv thaub qab:

rm -rf /mysql/backup.

Tom qab ntawd, siv lub tshuab hluav taws xob XtraBackup tsim ib daim ntawv luam thiab khaws cia rau hauv /mysql/ thaub qab/:

xtrabackup --user=xtrabackup --password=xxxxz1cYf95550Gc6xxxxxxxpE3rB03xxxx --backup --target-dir=/mysql/backup

Yav dhau los, peb tsim ib tus neeg siv hauv MySQL xtrabackup nrog rau cov cai tsim nyog. Siv cov parameter target-dir peb teev cov directory qhov twg cov thaub qab yuav tsum tau txais kev cawmdim.

Ib qho tseem ceeb!

Nco ntsoov kab ntawv:

xtrabackup --prepare --target-dir=/mysql/backup

Catalog cov ntaub ntawv /mysql/ thaub qab tsis sib xws kom txog thaum lawv siav. 

Qhov tseeb yog tias kev hloov pauv tuaj yeem tshwm sim thaum luam cov ntaub ntawv. Kev ua haujlwm xtrabackup --prepare --target-dir=/mysql/backup ua kom cov ntaub ntawv thaub qab zoo sib xws hauv lub sijhawm.

Koj tuaj yeem ua cov ntaub ntawv npaj ua haujlwm ntawm txhua lub tshuab. Tsis tas yuav ua qhov no ntawm lub server qhov chaw DBMS nyob. Koj tuaj yeem luam cov thaub qab mus rau lub hom phiaj server thiab npaj nws nyob ntawd.

Qhov kawg peb ua yog tsim ib qho archive uas peb tso peb cov thaub qab:

tar -zcvf /home/developer/dumps/xtrabackup-all-dbs-Β«$(date % F% H% M% S)Β».gz /mysql/backup

Restoreing ib tug thaub qab 

Ua ntej koj tuaj yeem rov qab tau cov ntaub ntawv thaub qab rau lub hom phiaj server, cov ntaub ntawv yuav tsum mus dhau theem kev npaj. Saib saum toj no seb yuav ua li cas.

Cov ntaub ntawv rov qab txheej txheem yog yooj yim heev. Koj yuav tsum rho tawm cov thaub qab los ntawm archive thiab hloov cov ntaub ntawv hauv yog data.

Yuav hloov cov ntaub ntawv hauv datadir li cas?

Cia peb xav txog ob txoj kev xaiv.

Qhov 1

Siv lub tshuab hluav taws xob XtraBackup. Koj yuav tsum tau qhia ib qho kev xaiv --copy-rov qab. 

Cov lus txib hauv qab no yuav hloov cov thaub qab mus rau yog data lub hom phiaj server:

xtrabackup --copy-back --target-dir=/mysql/backup

Qhov 2

Koj tuaj yeem ua nws txawv, ua yam tsis muaj kev siv hluav taws xob XtraBackup.

Txhua yam koj yuav tau ua yog luam cov thaub qab rau yog data. Koj tuaj yeem ua qhov no nrog cp los yog rsync.

Nws yog ib qho tseem ceeb kom nkag siab tias cov txheej txheem rau kev rov qab kho cov ntaub ntawv thaub qab tsuas yog hloov cov ntsiab lus ntawm cov npe. yog data.

Ua ntej koj pib kho qhov thaub qab ntawm lub hom phiaj server, koj yuav tsum:

  • Nres MySQL server.

  • Clear folder yog data los yog txav nws cov ntsiab lus mus rau lwm qhov chaw. Catalog yog data yuav tsum tau khoob.

Tom qab ua tiav cov ntaub ntawv hloov mus rau yog data MySQL server tuaj yeem pib.

Cov ntaub ntawv siv

Cov ntaub ntawv raug cai percona XtraBackup.

Tau qhov twg los: www.hab.com

Ntxiv ib saib