Mysql----备份与还原xtrabackup工具

一、安装

1、Ubuntu安装Percona
2、封装xtrabackup到mysql镜像
Dockerfile如下

FROM mysql:5.7

RUN sed -i "s/deb.debian.org/mirrors.tuna.tsinghua.edu.cn/g" /etc/apt/sources.list \
 && sed -i "s/security.debian.org/mirrors.tuna.tsinghua.edu.cn/g" /etc/apt/sources.list \
 && apt-get update  && apt-get install wget lsb-release -y \
 && wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb \
 && dpkg -i percona-release_latest.generic_all.deb \
 && apt-get update \
 && apt-get install percona-xtrabackup-24 -y \
 && rm -rf percona-release_latest.generic_all.deb \
 && apt-get clean \
 && rm -rf /var/lib/apt/lists/*

MySQL8.0:添加：&& apt-get update -y && apt-get install curl -y \

docker build -t xqkang/mysql:5.7 .

3.启动此mysql之前启动一个不加配置的MySQL将配置文件拷出来：

docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

将拷贝出来的mysql文件夹修改为config:

4.启动MySQL：

docker run -d --name mysql -p 3306:3306  -e MYSQL_ROOT_PASSWORD=123456  -v /qj/data/test_rds/config/:/etc/mysql  -v /qj/data/test_rds/data:/var/lib/mysql  -v /qj/data/test_rds/backup:/data/backup  xqkang/mysql:5.7

5.创建备份：

docker exec -it mysql bash

xtrabackup --backup --target-dir=/data/backup  -S /var/run/mysqld/mysqld.sock -H 127.0.0.1 -uroot -p123456 -P3306

6.数据恢复

xtrabackup --prepare --target-dir=/data/backup -S /var/run/mysqld/mysqld.sock -H 127.0.0.1 -uroot -p123456 -P3306

xtrabackup --copy-back --target-dir=/data/backup -S /var/run/mysqld/mysqld.sock -H 127.0.0.1 -uroot -p123456 -P3306

遇到错误：

还原数据时，数据目录必须为空：

root@3187e6cd91fb:/data/backup# xtrabackup --copy-back --target-dir=/data/backup -S /var/run/mysqld/mysqld.sock -H 127.0.0.1 -uroot -p123456 -P3306
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --open_files_limit=65535 --log_bin=mysql-bin --server-id=1 
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/data/backup/xfull --socket=/var/run/mysqld/mysqld.sock --host=127.0.0.1 --user=root --password=* --port=3306 
xtrabackup version 2.4.15 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 544842a)
Original data directory /var/lib/mysql is not empty!

解决：

rm -rf /var/lib/mysql/*