备份 二进制备份MySQLdump 增量备份 xtrabackupex

备份的原因是因为数据丢失和删除，当想要找回的时候就可以去备份恢复。备份具有唯一性和服务的可用性；备份有两种方式，一种是热备份（逻辑备份），还有一种是冷备份（物理备份）；备份分为三类，完全备份，增量备份，差异备份。
percona-xtrabackup:它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份；
为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载。
percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql的分支版本–percona Server。并围绕
percona Server还发布了一系列的mysql工具。
首先我们备份时需要软件支持的，所以第一步是获得安装包，下载地址：https://www.percona.com/




当然也可以用这种方法：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

安装percona需要的mysql包
mysql官方源

yum install -y https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
yum工具
yum install -y yum-utils
查看mysql可用版本
yum repolist all | grep mysql
禁用80
yum-config-manager --disable mysql80-community
启用57
yum-config-manager --enable mysql57-community
yum install mysql-community-libs-compat -y
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

1-完全备份

这里我的机器遇到了不可查的问题 ：

xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments:
200828 17:52:46 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints “completed OK!”.

200828 17:52:47 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup’ as ‘root’ (using password: YES).
200828 17:52:47 version_check Connected to MySQL server
200828 17:52:47 version_check Executing a version check against the server…
200828 17:52:47 version_check Done.
200828 17:52:47 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.29
innobackupex version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
200828 17:52:47 >> log scanned up to (2630134)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
200828 17:52:47 [01] Copying ./ibdata1 to /xtrabackup/full/2020-08-28_17-52-46/ibdata1
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
200828 17:52:48 >> log scanned up to (2630134)
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Database page corruption detected at page 322, retrying…
[01] xtrabackup: Error: failed to read page after 10 retries. File ./ibdata1 seems to be corrupted.
[01] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[01] xtrabackup: Error: failed to copy datafile.
200828 17:52:49 >> log scanned up to (2630134)

下面是解决方法：

rm -rf /xtrabackup/
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqld.log //修改密码
innobackupex --user=root --password=‘QianFeng@123’ /xtrabackup/full //开始备份

备份完成之后一定要查看查看

ls /xtrabackup/full/

2-完全恢复
现在是准备模拟环境 ！！！！！！！模拟环境！！！！模拟环境！！！！！工作可不能这样做

显示成功，
接下来恢复：




3-增量备份
准备工作：











还有另外一种备份方式：mysqldump +binlog
这种方式备份的好处就是能够自动的记录日志position位置，还具有可用性，锁表机制。
下面我们操作一下：

这就是mysqldump的备份，进入我们备分的文件，找到这样的一句话： CHANGE MASTER TO MASTER_LOG_FILE=‘localhost-bin.000002’, MASTER_LOG_POS=154; 大概也就是22行上下就能够找到，








这是二进制会后的。