xtrabackup

环境

  • mysql 8.4
  • rockylinux 9.6

安装

创建备份用户和目录

  • 备份目录如果是 nfs，挂载时需指定 sync 选项
  • 创建备份用户 
    1
2
3
4
5

    CREATE USER xb@localhost IDENTIFIED BY 'Xtrabackup_1234';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO xb@localhost;
GRANT SELECT ON performance_schema.log_status TO xb@localhost;
GRANT SELECT ON performance_schema.keyring_component_status TO xb@localhost;
GRANT SELECT ON performance_schema.replication_group_members TO xb@localhost;

全量备份和恢复

  • 备份

    1
2

    /opt/pxb/bin/xtrabackup --backup --target-dir=/backup/ \
    --user=xb --password=Xtrabackup_1234

  • 准备

    1

    /opt/pxb/bin/xtrabackup --prepare --target-dir=/backup/

  • 恢复，提前停止 mysql

    1
2

    /opt/pxb/bin/xtrabackup --move-back --target-dir=/backup/
chown -R mysql:mysql /var/lib/mysql

增量备份和恢复

  • 先全量备份一次

    1
2

    /opt/pxb/bin/xtrabackup --backup --target-dir=/backup/full \
    --user=xb --password=Xtrabackup_1234

  • 创建增量备份

    1
2
3

    /opt/pxb/bin/xtrabackup --backup --target-dir=/backup/incre \
    --incremental-basedir=/backup/full \
    --user=xb --password=Xtrabackup_1234

  • 准备全量备份，注意这里指定"–apply-log-only"避免事务回滚

    1

    /opt/pxb/bin/xtrabackup --prepare --apply-log-only --target-dir=/backup/full

  • 准备增量备份，准备最后一个增量备份无需指定"–apply-log-only"

    1
2

    /opt/pxb/bin/xtrabackup --prepare --target-dir=/backup/full \
    --incremental-dir=/data/backups/incre

  • 恢复，提前停止 mysql

    1
2

    /opt/pxb/bin/xtrabackup --move-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql

mariabackup

环境

  • mariadb 10.3
  • rockylinux 8.10

安装

1

dnf install mariadb-backup

创建备份用户

1
2

CREATE USER backup@localhost IDENTIFIED BY 'Backup_1234';
GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO backup@localhost;

全量备份和恢复

  • 备份

    1
2

    mariabackup --backup --target-dir=/backup/ \
    --user=backup --password=Backup_1234

  • 准备

    1

    mariabackup --prepare --target-dir=/backup/

  • 恢复，提前停止 mariadb

    1
2

    mariabackup --move-back --target-dir=/backup/
chown -R mysql:mysql /var/lib/mysql

增量备份和恢复

  • 先全量备份一次

    1
2

    mariabackup --backup --target-dir=/backup/full \
    --user=backup --password=Backup_1234

  • 创建增量备份

    1
2
3

    mariabackup --backup --target-dir=/backup/incre \
    --incremental-basedir=/backup/full \
    --user=backup --password=Backup_1234

  • 准备全量备份

    1

    mariabackup --prepare --target-dir=/backup/full

  • 准备增量备份

    1
2

    mariabackup --prepare --target-dir=/backup/full \
    --incremental-dir=/data/backups/incre

  • 恢复，提前停止 mariadb

    1
2

    /opt/pxb/bin/xtrabackup --move-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql

mysqlsh

环境

  • mysql 8.4
  • rockylinux 9.6

安装

  • 下载最新的通用二进制包，解压 
    1
2

    tar zxf mysql-shell-8.4.9-linux-glibc2.28-x86-64bit.tar.gz
mv mysql-shell-8.4.9-linux-glibc2.28-x86-64git /opt/mysql-shell

创建备份用户

1
2

CREATE USER shell@'%' IDENTIFIED BY 'Shell_1234';
GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO shell@localhost;

备份命令

  • 简化 msyql-shell 命令

    1
2

    export MYSQLSH="/opt/mysql-shell -h 'mysql-ip' -P 3306 -u shell -p 'Shell_1234'"
# 替换其中的 'mysql-ip'

  • 备份实例

    1
2
3
4

    $MYSQLSH -e 'util.dumpInstance("/data/backup/full", {compression: "zstd"})'
# includeSchemas/excludeSchemas: ["db1", "db2"], 指定/忽略备份某些库
# includeTables/excludeTables: ["db1.tb1", "db2.tb2"], 指定/忽略备份某些库
# routines/users/triggers: true, 备份函数和存储过程/账号/触发器

  • 备份指定库

    1
2
3

    $MYSQLSH -e 'util.dumpInstance("/data/backup/dbs", {includeSchemas: ["db1", "db2"]})'
# 或
$MYSQLSH -e 'util.dumpSchemas(["db1", "db2"], "/data/backup/dbs")'

  • 备份指定表

    1
2
3

    $MYSQLSH -e 'util.dumpInstance("/data/backup/tbs", {includeTables: ["db1.tb1", "db2.tb2"]})'
# 或
$MYSQLSH -e 'util.dumpTables("db1", ["tb1", "tb2"], "/data/backup/db1_tbs")'

  • 导入数据，建议在服务器本地导入

    1
2
3
4
5
6

    # 开启 local_infile
mysql -S /tmp/mysql.sock -uroot -p -e "set global LOCLA_INFILE=ON"

/opt/mysql-shell/bin/mysqlsh -S /tmp/mysql.sock \
    -e 'util.loadDump("/data/backup/xxxx", {loadUsers: true})'
# loadUsers: true, 导入账号

参考