๐Ÿ“—
smiley book
  • Smiley Books
  • AI
    • Readme
    • openai-whisper
      • ์ƒ˜ํ”Œ ์‹คํ–‰ํ•ด๋ณด๊ธฐ
      • GPU ์„œ๋ฒ„ ์ค€๋น„ํ•˜๊ธฐ
      • API๋กœ whisper๋ฅผ ์™ธ๋ถ€์— ์˜คํ”ˆํ•˜๊ธฐ
      • ํ”„๋กฌํ”„ํŠธ ์ง€์›
      • ์‹ค์‹œ๊ฐ„ message chat
      • ํ™”๋ฉด ์ด์˜๊ฒŒ ๋งŒ๋“ค๊ธฐ์™€ ๋กœ๊ทธ์ธ
      • ํŒŒ์ด์ฌ ๊ฐ€์ƒํ™˜๊ฒฝ
      • ์‹ค์‹œ๊ฐ„ voice chat
      • fine tunning(๋ฏธ์„ธ ์กฐ์ •) ์œผ๋กœ ์„ฑ๋Šฅ ์˜ฌ๋ฆฌ๊ธฐ
      • app์—์„œ api๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ ์‹ค์‹œ๊ฐ„์œผ๋กœ ํ…์ŠคํŠธ๋กœ ๋ฐ”๊ฟ”๋ณด๊ธฐ
    • ollama - llm์„ ์‰ฝ๊ฒŒ ๋‚ด์ปด์—์„œ ์‹คํ–‰
      • ollama webui
      • ollama docker
    • stable diffusion
      • SDXL - text to image
      • SD-webui
    • ChatGPT
      • ๋‹ต๋ณ€์ด ๋Š๊ธธ๋•Œ
      • ์—ญํ• ์„ ์ •ํ•˜์ž
      • ๊ตฌ์ฒด์ ์ธ ์งˆ๋ฌธ
      • ๊ฒฐ๊ณผํ˜•ํƒœ๋ฅผ ์ง€์ •
      • ํ”„๋กฌํ”„ํŠธ๋ฅผ ์—ฌ๋Ÿฌ์ค„๋กœ ์‚ฌ์šฉํ•˜์ž.
      • ๋งˆํ‹ด ํŒŒ์šธ๋Ÿฌ ๊ธ€ ๋ฒˆ์—ญ๋ณธ
    • Prompt Engineering
    • Auto-GPT
    • Gemini
      • google ai studio
      • gemini-api
      • embedding guide
    • Huggingface
      • huggingface ์‚ฌ์šฉ๋ฒ•
      • huggingface nlp ๊ณต๋ถ€์ค‘
    • kaggle
      • download dataset
    • langchain
      • langchain์„ ๊ณต๋ถ€ํ•˜๋ฉฐ ์ •๋ฆฌ
      • basic
      • slackbot
      • rag
      • document-loader
      • website-loader
      • confluence
      • memory
      • function-call
      • langsmith
      • agent-toolkit
  • Ansible
    • templates vs files and jinja2
    • dynamic inventory
    • limit ์˜ต์…˜ ๊ฐ•์ œํ•˜๊ธฐ
    • limit ์‚ฌ์šฉํ›„ gather_fact ๋ฌธ์ œ
  • AWS
    • AWS CLI
    • EKS
      • cluster manage
      • ALB Controller
      • external-dns
      • fargate
    • ECR
    • S3
    • Certificate Manager
  • Azure
    • Azure AD OAuth Client Flow
  • Container
    • Registry
    • ๋นŒ๋“œ์‹œ์— env๊ฐ’ ์„ค์ •ํ•˜๊ธฐ
  • DB
    • PXC
      • Operator
      • PMM
      • ์‚ญ์ œ
      • GTID
      • Cross Site Replication
    • Mssql
    • Mysql
  • dotnet
    • Thread Pool
    • Connection Pool
    • Thread Pool2
  • Devops
    • Recommendation
  • GIT
    • Basic
    • Submodule
  • GitHub
    • Repository
    • GitHub Action
    • GitHub PR
    • Self Hosted Runner
    • GitHub Webhook
  • GitLab
    • CI/CD
    • CI/CD Advance
    • Ssl renew
    • CI/CD Pass env to other job
  • Go Lang
    • ๊ฐœ๋ฐœ ํ™˜๊ฒฝ ๊ตฌ์ถ•
    • multi os binary build
    • kubectl๊ฐ™์€ cli๋งŒ๋“ค๊ธฐ
    • azure ad cli
    • embed static file
    • go study
      • pointer
      • module and package
      • string
      • struct
      • goroutine
  • Kubernetes
    • Kubernetes๋Š” ๋ฌด์—‡์ธ๊ฐ€
    • Tools
    • Install with kubespray
    • Kubernetes hardening guidance
    • 11 ways not to get hacked
    • ArgoCD
      • Install
      • CLI
      • Repository
      • Apps
      • AWS ALB ์‚ฌ์šฉ
      • Notification slack
      • Backup / DR
      • Ingress
      • 2021-11-16 Github error
      • Server Config
      • auth0 ์ธ์ฆ ์ถ”๊ฐ€(oauth,OIDC)
    • Extension
      • Longhorn pvc
      • External dns
      • Ingress nginx
      • Cert Manager
      • Kube prometheus
    • Helm
      • Subchart
      • Tip
    • Loki
    • Persistent Volume
    • TIP
      • Job
      • Pod
      • Log
  • KAFKA
    • raft
  • KVM
    • kvm cpu model
  • Linux
    • DNS Bind9
      • Cert-Manager
      • Certbot
      • Dynamic Update
      • Log
    • Export and variable
    • Grep ์‚ฌ์šฉ๋ฒ•
  • Modeling
    • C4 model introduce
    • Mermaid
    • reference
  • Monitoring
    • Readme
    • 0. What is Monitoring
    • 1. install prometheus and grafana
    • 2. grafana provisioning
    • 3. grafana dashboard
    • 4. grafana portable dashboard
    • 5. prometheus ui
    • 6. prometheus oauth2
    • Prometheus
      • Metric type
      • basic
      • rate vs irate
      • k8s-prometheus
    • Grafana
      • Expolorer
    • Node Exporter
      • advance
      • textfile collector
  • Motivation
    • 3 Simple Rule
  • OPENNEBULA
    • Install(ansible)
    • Install
    • Tip
    • Windows vm
  • Reading
    • comfort zone
    • ๋ฐฐ๋ ค
    • elon musk 6 rule for insane productivity
    • Feynman Technique
    • how to interview - elon musk
    • ๊ฒฝ์ฒญ
    • Readme
  • Redis
    • Install
    • Master-slave Architecture
    • Sentinel
    • Redis Cluster
    • Client programming c#
  • SEO
    • Readme
  • Security
    • criminalip.io
      • criminalip.io
  • Stock
    • robinhood-python
  • Terraform
    • moved block
    • output
  • vault
    • Readme
  • VS Code
    • dev container
    • dev container on remote server
  • Old fashione trend
    • curity
    • MAAS
      • Install maas
      • Manage maas
      • Tip
Powered by GitBook
On this page
  • Global Transaction Identifier
  • ๋ฐฑ์—…ํ•˜๊ธฐ
  • ํ™•์ธ
  • ๋ณต๊ตฌํ•˜๊ธฐ
  • point-in-time restore

Was this helpful?

  1. DB
  2. PXC

GTID

Global Transaction Identifier

๊ฐ๊ฐ์˜ ํŠธ๋ Œ์ ์…˜๋“ค์€ ๊ณ ์œ ํ•œ ์ „์—ญ์‹๋ณ„์ž๋ฅผ ๊ฐ–๊ฒŒ ๋œ๋‹ค.

GTID = source_id:transaction_Id

transaction_id๋Š” ํ•ด๋‹น ์„œ๋ฒ„์—์„œ ์ปค๋ฐ‹๋œ ํŠธ๋žœ์žญ์…˜์˜ ์ˆœ์„œ์— ๋”ฐ๋ผ ์ˆœ์ฐจ์ ์ธ ์ˆซ์ž๋กœ ๊ฒฐ์ •๋œ๋‹ค. ์˜ˆ๋กœ ์ฒซ ๋ฒˆ์งธ ํŠธ๋žœ์žญ์…˜์€ transaction_id=1์ด ๋˜๊ณ , ๋™์ผํ•œ ์„œ๋ฒ„์—์„œ ์—ด ๋ฒˆ์งธ ํŠธ๋žœ์žญ์…˜์€ transaction_id=10์ด ๋œ๋‹ค. (GTID์—์„œ ํŠธ๋žœ์žญ์…˜์ด ์ˆœ์ฐจ์ ์ธ ์ˆซ์ž๋Š” 1๋ถ€ํ„ฐ ์‹œ์ž‘๋œ๋‹ค. 0์€ ๋  ์ˆ˜ ์—†๋‹ค.)

๋ฐฑ์—…ํ•˜๊ธฐ

cr.yaml
backup:
  pitr:
    enabled: true
    storageName: s3-us-west-binlog
    timeBetweenUploads: 60
  storages:
    s3-us-west-fullbackup:
      type: s3
      s3:
        bucket: pxc-fullbackup
        credentialsSecret: backup-aws-s3
        region: us-west-1
    s3-us-west-binlog:
      type: s3
      s3:
        bucket: pxc-binlog
        credentialsSecret: backup-aws-s3
        region: us-west-1

full backup๊ณผ ๊ฐ™์ด pitr๋ฅผ ํ™œ์„ฑํ™” ํ•˜๊ณ  ์ ์šฉํ•˜์ž.

s3์— ํ™•์ธ์„ ํ•ด๋ณด๋ฉด ํŒŒ์ผ๋“ค์ด ๋ฐฑ์—…์ด ๋˜๊ณ  ์ž‡๋Š”๊ฒƒ์„ ์•Œ์ˆ˜ ์žˆ๋‹ค.

ํ™•์ธ

select @@global.gtid_executed; -- ํ˜„์žฌ GTID ํ™•์ธ
create database test;
select @@global.gtid_executed; -- GTID๊ฐ€ ์ฆ๊ฐ€ํ•œ๊ฒƒ์„ ๋ณผ์ˆ˜ ์žˆ๋‹ค.

60์ดˆ ๋’ค์—

์ƒˆ๋กœ์šด ํŒŒ์ผ์ด ์˜ฌ๋ผ์˜จ๊ฒƒ์„ ํ™•์ธํ• ์ˆ˜ ์žˆ๋‹ค.

๋‹ค์šด๋ฐ›์•„์„œ ํ™•์ธํ•ด๋ณด์ž.

mkdir log
#download s3
cd log
docker run --rm -v $(pwd):/binlog mysql:8 mysqlbinlog -v /binlog/binlog_1629743530_397451a8b1770fbc4ec2a983dfff161d
  • Binary Log Format

    • STATEMENT

    • ROW

    • MIXED

์ด๋ ‡๊ฒŒ ์ž‡๋Š”๋ฐ pxc์—์„œ row๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค. ๋กœ๊ทธ๋ฅผ ๋ณด๋ ค๋ฉด -v ์˜ต์…˜์„ ์ค˜์„œ ๋ด์•ผํ•œ๋‹ค.

๋ณต๊ตฌํ•˜๊ธฐ

restore.yaml
kind: PerconaXtraDBClusterRestore
metadata:
  name: restore1
spec:
  pxcCluster: cluster01
  backupName: backup1
  pitr:
    type: latest
    date: '2021-08-23 16:00:00'
    # gtid: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:nnn"
    # gtid: "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:nnn-nnn,nnn-nnn"
    backupSource:
      storageName: 's3-us-west-binlog'
kubectl apply -f deploy/backup/restore.yaml
  • type

    • date - roll back to specific date,

    • transaction - roll back to specific transaction,

    • latest - recover to the latest possible transaction,

date๋ฅผ ์‚ฌ์šฉํ•ด์„œ ํŠน์ • ๋‚ ์งœ๋กœ ๋ณต๊ตฌ๊ฐ€ ๊ฐ€๋Šฅํ•˜๋‹ค.

gtid๋ฅผ ์ด์šฉํ•ด์„œ ํŠน์ • ์‹œํ€€์Šค๋„˜๋ฒ„ ๊นŒ์ง€ ๋ณต๊ตฌ๊ฐ€ ๊ฐ€๋Šฅํ•˜๋‹ค. ์˜ˆ๋ฅผ๋“ค๋ฉด '20' ๋งˆ์ง€๋ง‰ ๋ฒˆํ˜ธ ๋ฐ”๋กœ ์•ž๊นŒ์ง€๋งŒ ๋ณต๊ตฌ๋œ๋‹ค. 19๊นŒ์ง€ ๋ณต๊ตฌ๋œ๋‹ค.

gtid set๋ฅผ ์ด์šฉํ•ด์„œ '1-10,11-20' ์œผ๋กœ ๊ทธ๋ฃนํ™”ํ•˜์—ฌ ๋ณต๊ตฌ๊ฐ€ ๊ฐ€๋Šฅํ•˜๋‹ค. 9๋ฒˆ๊นŒ์ง€ ๋ณต๊ตฌํ•˜๊ณ  10๋ฒˆ์€ ๊ฑด๋„ˆ๋›ฐ๊ณ  11-20๋ฒˆ๊นŒ์ง€ ๋ณต๊ตฌ๋œ๋‹ค.

gtid๊ฐ’๋งŒ ์ฐพ์œผ๋ฉด ๋ณต๊ตฌ๊ฐ€ ์ด์ œ ๊ฐ€๋Šฅํ•˜๋‹ค.

create table movies(id int auto_increment primary key, name varchar(20) not null);
show tables;
insert into movies(name) values('hello1');
insert into movies(name) values('hello2');
insert into movies(name) values('hello3');
insert into movies(name) values('hello4');
insert into movies(name) values('hello5');
select * from movies;
delete from movies;
select * from movies;
insert into movies(name) values('new1');
insert into movies(name) values('new2');
insert into movies(name) values('new3');
insert into movies(name) values('new4');
insert into movies(name) values('new5');
select * from movies;

์ค‘๊ฐ„์— ๋‚ ๋ฆฐ delete๋งŒ ๋นผ๊ณ  ์‹ถ๋‹ค.

mkdir log
#download s3
cd log
docker run --rm -v $(pwd):/binlog -it mysql:8 mysqlbinlog -v /binlog/binlog_* | grep -i -e gtid_next -e delete

delete ์•ž์ชฝ์— ๋‚˜์˜ค๋Š” ๊ฐ’ 717์„ ์•Œ์•„๋‚ผ์ˆ˜ ์žˆ๋‹ค.

select @@global.gtid_executed; -- ํ˜„์žฌ GTID ํ™•์ธ 720

์ด์ œ ์ด๋ ‡๊ฒŒ ์ฒ˜๋ฆฌํ• ์ˆ˜ ์žˆ๋‹ค.

gtid: 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:1-717,718-720'

์ด๋ ‡๊ฒŒ ๋ณต๊ตฌํ•˜๋ฉด๋œ๋‹ค.

point-in-time restore

์ด๊ฑธ ์œ„ํ•ด์„œ๋Š” bin log ํŒŒ์ผ์„ ๋ฐฑ์—…์„ ๋ฐ›์•„์•ผํ•œ๋‹ค.

์ด๋ถ€๋ถ„ ์ฃผ์„ ํ•ด์ œ ์Šคํ† ๋ฆฌ์ง€ ์ด๋ฆ„์„ ์ ์–ด์ฃผ๋ฉด bin logfile ๋ฅผ ์—…๋กœ๋“œํ•œ๋‹ค.

๋ณต๊ตฌ๋ฅผ ํ•ด๋ณด์ž.

vi reststore.yaml

Previous์‚ญ์ œNextCross Site Replication

Last updated 1 year ago

Was this helpful?

s3 ํ˜„์žฌ

https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html