🥇Tsim ib qho thaub qab MySQL siv XtraBackup utility

Percona XtraBackup — yog ib qho cuab yeej siv rau kev thaub qab kub ntawm cov ntaub ntawv MySQL.

Thaum tsim cov ntaub ntawv thaub qab, tsis muaj lub rooj twg raug xauv, thiab koj lub kaw lus tseem ua haujlwm yam tsis muaj kev txwv.

XtraBackup 2.4 tuaj yeem tsim cov ntawv theej ntawm cov rooj InnoDB, XtraDB и MyISAM ntawm cov servers MySQL 5.11, 5.5, 5.6, thiab 5.7thiab, thiab ntawm lub server percona rau MySQL с XtraDB.

Ua haujlwm nrog MySQL 8.x yuav tsum siv cov version XtraBackup 8.x. Tsab xov xwm no tsuas yog yuav tham txog XtraBackup 2.4.

Lub ntsiab kom zoo dua XtraBackup yog tias qhov kev pab cuam no yog qhov tsim nyog rau kev tsim cov ntawv theej ntawm cov khoom siv uas tau thauj khoom ntau heev servers, nrog rau rau cov kab ke uas muaj tus lej tsawg ntawm kev lag luam.

Yog tias tag nrho qhov loj ntawm koj cov ntaub ntawv MySQL yog qhov tseem ceeb (kaum tawm gigabytes), ces tus qauv siv mysqldump yuav tsis pub koj sai sai tsim ib daim qauv thaub qab, thiab kev rov qab kho qhov chaw pov tseg yuav siv sijhawm ntev.

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

2Tom qab kev teeb tsa, khiav cov lus txib xtrabackup -v. Nws yog ib qho tseem ceeb kom paub tseeb tias cov cuab yeej siv tau khiav kom raug ntawm lub server. Yog li ntawd, ib yam dab tsi zoo li no yuav tshwm sim ntawm qhov screen:

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

Cov cai nkag mus, kev tso cai thiab cov cai tshwj xeeb

XtraBackup yuav tsum muaj peev xwm ua tau:

Txuas rau koj lub MySQL server.
Muaj cai nkag mus rau hauv daim nplaub tshev datadir.
Thaum tsim ib daim qauv thaub qab, muaj cai sau rau cov parameter teev tseg lub hom phiaj-dir daim ntawv teev khoom.

Datadir yog dab tsi?

datadir - Qhov no yog daim nplaub tshev uas lub database server nyob. MySQL khaws cov ntaub ntawv. Txhua lub database, txhua lub rooj nyob ntawd. Hauv feem ntau ntawm cov kev faib tawm Linux Los ntawm lub neej ntawd, daim ntawv qhia no yog /var/lib/mysql.

Target-dir directory yog dab tsi?

lub hom phiaj-dir - qhov no yog daim nplaub tshev uas cov ntaub ntawv thaub qab yuav raug khaws cia.

Tus neeg siv database xav tau cov cai nkag mus rau cov rooj thiab databases kom tau thaub qab:

RELOAD thiab XAUV COV ROOJ
TUS NEEG SIV KHOOM ROV QAB
TSIM QHOV ROOJ
TXHEEJ TXHEEM
SUPER
tsim
NYIAJ
Xaiv

Configuration

Configuration XtraBackup ua tiav los ntawm kev siv cov kev xaiv uas ua tib yam li cov qauv MySQL.

Qhov no txhais li cas?

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

Lub XtraBackup utility nyeem cov partitions tom qab pib [mysqld] и [xtrabackup] los ntawm cov ntaub ntawv teeb tsa MySQL. Qhov no ua tiav kom cov cuab yeej siv tau koj cov chaw teeb tsa DBMS yam tsis tas yuav tsum tau teev cia rau txhua qhov thaub qab.

Piv txwv li, lub ntsiab lus datadir thiab qee cov parameter InnoDB Peb tau txais XtraBackup los ntawm koj qhov kev teeb tsa DBMS.

Yog tias koj xav hloov cov chaw teeb tsa hauv ntu XtraBackup [mysqld], ces tsuas yog teev lawv hauv cov ntaub ntawv teeb tsa hauv seem [xtrabackup]Vim tias lawv yuav raug nyeem tom qab, lawv qhov tseem ceeb yuav siab dua.

Koj tsis tas yuav ntxiv cov parameter rau kuv. cnfTxhua yam kev teeb tsa uas xav tau tuaj yeem teev tseg rau ntawm kab hais kom ua. Feem ntau, tsuas yog ib yam uas tuaj yeem muab tso rau hauv seem [xtrabackup] koj nws kuv. cnf - qhov no yog ib qho parameter lub hom phiaj_dir, uas los ntawm lub neej ntawd qhia txog daim nplaub tshev uas cov thaub qab yuav raug tso. Txawm li cas los xij, qhov no tsis tas yuav tsum muaj.

Ib qho piv txwv ntawm kev teev txoj kev mus rau daim nplaub tshev nrog daim qauv thaub qab hauv kuv. cnf:

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

Tsab ntawv sau txog kev tsim cov ntaub ntawv thaub qab

Yuav tsim ib qho thaub qab, koj siv tau cov ntawv sau hauv qab no:

#!/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 ib daim ntawv sau tiav lawm?

Thawj qhov peb ua yog ntxuav (rho tawm) daim nplaub tshev uas peb yuav txuag cov ntaub ntawv thaub qab:

rm -rf /mysql/backup.

Tom qab ntawd, siv lub cuab yeej XtraBackup tsim ib daim qauv thaub qab thiab txuag nws rau hauv /mysql/thaub qab/:

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

Peb tau tsim ib tus neeg siv ua ntej hauv MySQL. xtrabackup nrog rau cov cai tsim nyog. Siv cov parameter target-dir Peb teev cov directory uas cov backup yuav tsum tau khaws cia.

Ib qho tseem ceeb!

Thov nco ntsoov kab ntawv sau:

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

Cov ntaub ntawv hauv daim ntawv teev khoom /mysql/thaub qab tsis sib xws kom txog thaum lawv npaj tau.

Qhov teeb meem yog tias kev hloov pauv yuav tshwm sim thaum cov ntaub ntawv raug theej. xtrabackup --prepare --target-dir=/mysql/backup ua rau cov ntaub ntawv thaub qab zoo sib xws dhau sijhawm.

Koj tuaj yeem ua qhov kev npaj cov ntaub ntawv ntawm txhua lub tshuab. Tsis tas yuav ua nws ntawm lub server uas tuav lub DBMS qhov chaw. Koj tuaj yeem theej cov ntaub ntawv thaub qab mus rau lub server uas koj xav tau thiab npaj nws rau ntawd.

Qhov kawg uas peb ua yog tsim ib qho archive uas peb muab peb daim ntawv theej thaub qab tso rau hauv:

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

Rov qab kho qhov thaub qab

Ua ntej yuav rov qab muab cov ntaub ntawv thaub qab rov qab rau lub server uas koj xav tau, cov ntaub ntawv yuav tsum tau npaj ua ntej. Saib saum toj no kom paub cov lus qhia txog yuav ua li cas.

Cov txheej txheem rov qab cov ntaub ntawv yooj yim heev. Koj tsuas yog yuav tsum rho tawm cov ntaub ntawv thaub qab los ntawm cov ntaub ntawv khaws cia thiab hloov cov ntaub ntawv rau hauv datadir.

Yuav ua li cas hloov cov ntaub ntawv hauv datadir?

Cia peb xav txog ob txoj kev xaiv.

Qhov 1

Siv lub cuab yeej XtraBackupKoj yuav tsum qhia meej qhov kev xaiv —copy-baсk.

Cov lus txib hauv qab no yuav tsiv qhov thaub qab mus rau datadir lub hom phiaj neeg rau zaub mov:

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

Qhov 2

Koj tuaj yeem ua nws txawv thiab ua yam tsis muaj qhov chaw pabcuam. XtraBackup.

Txhua yam koj yuav tsum ua yog theej daim ntawv backup mus rau datadirKoj tuaj yeem ua qhov no nrog kev pab los ntawm cp los yog rsync.

Nws yog ib qho tseem ceeb kom nkag siab tias cov txheej txheem rov qab los ntawm kev thaub qab tsuas yog ib qho teeb meem ntawm kev hloov cov ntsiab lus ntawm daim nplaub tshev. datadir.

Ua ntej koj pib rov qab kho qhov thaub qab rau lub server uas koj xav tau, koj yuav tsum: