xtrabackup 备份和恢复mysql

安装

1. 安装xtrabackup
   注意xtrabackup8只适合mysql8 。

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-xtrabackup-80

2. 安装mysql8

这里docker-compose用于测试，生成环境不建议使用docker版

version: "3"
services:
  db:
    image: mysql:8
    container_name: mysql8
    restart: always
    security_opt:
      - seccomp:unconfined
    environment:
      MYSQL_ROOT_PASSWORD: root

    command:
      --max_connections=1000
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --default-authentication-plugin=mysql_native_password
    ports:
      - 3302:3306
    volumes:
      - /home/zhangz/mysql8/data:/var/lib/mysql
      - /home/zhangz/mysql8/conf:/etc/mysql/conf.d
      - /home/zhangz/mysql8/log:/logs
      - /home/zhangz/mysql8/sock:/run
      - /home/zhangz/mysql8/mysql-files:/var/lib/mysql-files/
      - /home/zhangz/mysql8/my.cnf:/etc/mysql/my.cnf
    entrypoint: bash -c "chown -R mysql:mysql /run && exec /entrypoint.sh mysqld"

3. 创建my.cnf
   这个文件要先在宿主机创建，再启动docker

[mysqld]
user=mysql
default_authentication_plugin=mysql_native_password
character-set-server=utf8
collation-server=utf8_unicode_ci

datadir = /var/lib/mysql
#socket=/home/mysql_data/mysql.sock
socket = /run/mysql.sock
#log-error=/logs/mysqld.log
pid-file=/run/mysqld.pid

server-id = 58
auto_increment_offset=1
auto_increment_increment=1
log-bin=mysql-bin
expire_logs_days=14
max_relay_log_size = 0
relay_log_purge = 1
relay_log_recovery = 1
sync_relay_log =0
sync_relay_log_info = 0
log-slave-updates=1
gtid_mode = on
enforce_gtid_consistency = on
skip_slave_start=0
slave-skip-errors=1032
[client]
#socket=/home/mysql_data/mysql.sock
#default_authentication_plugin=mysql_native_password
default-character-set=utf8

4. 创建mysql用户

CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT,RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

备份

每周三，周日全备份，其余增量备份

1. 所有数据库备份

import datetime
import pytz
import os

# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum install percona-xtrabackup-80
# CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
# GRANT BACKUP_ADMIN,SELECT,RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
# FLUSH PRIVILEGES;
tz = pytz.timezone('Asia/Shanghai')

back_dir = '/home/backup/mysql/all'
full_dir = '{}/full'.format(back_dir)
incr_dir = '{}/incr'.format(back_dir)
log_dir = '{}/log'.format(back_dir)

user = 'backup'
pwd = '123456'
db_conf = '/etc/my.cnf'
now = datetime.datetime.now(tz=tz)
week = now.isoweekday()

today = now.strftime('%Y%m%d')
last_day = (now + datetime.timedelta(days=-1)).strftime('%Y%m%d')
log = '{}/{}.log'.format(log_dir, today)
print(week, today, last_day)


def check_dir():
    if not os.path.exists(back_dir):
        os.makedirs(back_dir)
    if not os.path.exists(full_dir):
        os.makedirs(full_dir)
    if not os.path.exists(incr_dir):
        os.makedirs(incr_dir)
    if not os.path.exists(log_dir):
        os.makedirs(log_dir)


def task():
    if week in [3, 7]:
        task_full()
    else:
        if len(os.listdir(full_dir)) == 0:
            print('第一次增量备份')
            task_full()
            global last_day
            last_day = today
            task_incr(last_full=True)
        else:
            if week in [1, 4]:
                task_incr(last_full=True)
            else:
                task_incr()


def task_full():
    print('开始全备份')
    backup_path = '{}/full_{}'.format(full_dir, today)
    if os.path.exists(backup_path):
        print('今日备份已存在')
        return
    cmd = "xtrabackup  --defaults-file={} --backup --user={} --password={} --target-dir={} >> {} 2>&1".format(
        db_conf, user, pwd, backup_path, log)
    print(cmd)
    del_cmd = 'find {} -name "full_*" -mtime +6 -exec rm -rf {{}} \;'.format(full_dir)
    os.system(del_cmd)
    os.system(cmd)


def task_incr(last_full=False):
    print('开始增量备份')
    backup_path = '{}/incr_{}'.format(incr_dir, today)
    if os.path.exists(backup_path):
        print('今日备份已存在')
        return
    base_dir = '{}/incr_{}'.format(incr_dir, last_day)
    if last_full:
        base_dir = '{}/full_{}'.format(full_dir, last_day)
        print('以全备份为基础备份,路径:{}'.format(base_dir))
    else:
        print('以增量备份为基础备份,路径:{}'.format(base_dir))
    if not os.path.exists(base_dir):
        print('基础备份不存在,开始全备份')
        task_full()
        base_dir = '{}/full_{}'.format(full_dir, today)
    cmd = "xtrabackup --defaults-file={} --backup --user={} --password={}  --target-dir={}  " \
          "--incremental-basedir={} >> {} 2>&1".format(
        db_conf, user, pwd, backup_path, base_dir, log)
    print(cmd)
    os.system(cmd)


def task_del():
    cmd = "find {} -mtime +6 -type f -name '.*log' -exec rm -rf {{}} \;".format(log_dir)
    os.system(cmd)


def main():
    check_dir()
    task()
    task_del()


if __name__ == '__main__':
    main()

2. 指定数据库备份

import datetime
import pytz
import os

# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum install percona-xtrabackup-80
# CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
# GRANT BACKUP_ADMIN,SELECT,RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
# FLUSH PRIVILEGES;
tz = pytz.timezone('Asia/Shanghai')
database = 'health_card'
back_dir = '/home/backup/mysql/{}'.format(database)
full_dir = '{}/full'.format(back_dir)
incr_dir = '{}/incr'.format(back_dir)
log_dir = '{}/log'.format(back_dir)

user = 'backup'
pwd = '123456'
db_conf = '/etc/my.cnf'
now = datetime.datetime.now(tz=tz)
week = now.isoweekday()

today = now.strftime('%Y%m%d')
last_day = (now + datetime.timedelta(days=-1)).strftime('%Y%m%d')
log = '{}/{}.log'.format(log_dir, today)
print(week, today, last_day)


def check_dir():
    if not os.path.exists(back_dir):
        os.makedirs(back_dir)
    if not os.path.exists(full_dir):
        os.makedirs(full_dir)
    if not os.path.exists(incr_dir):
        os.makedirs(incr_dir)
    if not os.path.exists(log_dir):
        os.makedirs(log_dir)


def task():
    if week in [3, 7]:
        task_full()
    else:
        if len(os.listdir(full_dir)) == 0:
            print('第一次增量备份')
            task_full()
            global last_day
            last_day = today
            task_incr(last_full=True)
        else:
            if week in [1, 4]:
                task_incr(last_full=True)
            else:
                task_incr()


def task_full():
    print('开始全备份')
    backup_path = '{}/full_{}'.format(full_dir, today)
    if os.path.exists(backup_path):
        print('今日备份已存在')
        return
    cmd = "xtrabackup  --defaults-file={} --backup --user={} --password={} --databases={} --target-dir={} >> {} 2>&1".format(
        db_conf, user, pwd, database, backup_path, log)
    print(cmd)
    del_cmd = 'find {} -name "full_*" -mtime +6 -exec rm -rf {{}} \;'.format(full_dir)
    os.system(del_cmd)
    os.system(cmd)


def task_incr(last_full=False):
    print('开始增量备份')
    backup_path = '{}/incr_{}'.format(incr_dir, today)
    if os.path.exists(backup_path):
        print('今日备份已存在')
        return
    base_dir = '{}/incr_{}'.format(incr_dir, last_day)
    if last_full:
        base_dir = '{}/full_{}'.format(full_dir, last_day)
        print('以全备份为基础备份,路径:{}'.format(base_dir))
    else:
        print('以增量备份为基础备份,路径:{}'.format(base_dir))
    if not os.path.exists(base_dir):
        print('基础备份不存在,开始全备份')
        task_full()
        base_dir = '{}/full_{}'.format(full_dir, today)
    cmd = "xtrabackup --defaults-file={} --backup --user={} --password={} --databases={} --target-dir={}  " \
          "--incremental-basedir={} >> {} 2>&1".format(
        db_conf, user, pwd, database, backup_path, base_dir, log)
    print(cmd)
    os.system(cmd)


def task_del():
    cmd = "find {} -mtime +6 -type f -name '.*log' -exec rm -rf {{}} \;".format(log_dir)
    os.system(cmd)


def main():
    check_dir()
    task()
    task_del()


if __name__ == '__main__':
    main()