10.数据库备份恢复 1.MySQL逻辑备份与恢复 1.1数据库完整备份与恢复 1.2数据库增量备份与恢复 2.MySQL物理备份与恢复 2.1数据库完整备份与恢复 2.2数据库增量备份与恢复 2.3数据库差异备份与恢复 3.简单命令进⾏物理备份 4.⽣产备份思路与实战 通常数据库备份 database 数据⽂件 binlog ⽇志⽂件 my.cnf 配置⽂件,所有的数据库的备份数据都应放在⾮数据 库本地, ⽽且建议备份多份。 仅备份是没有任何意义, 需要在测试环境中做⽇常恢复演练, 测试备份的可⽤性, 恢复较⽐备份更加的重要。 备份: 能够有效防⽌设备故障以及⼈为误操作带来的数据丢失, 例如:将数据⽂件保存在远端。 冗余: 数据有多分冗余, 但不等于备份, 只能防⽌机械故障丢失的数据, 例如: 主备模式、数据库集群。 数据库备份必须考虑因素 1.数据的⼀致性 2.服务的可⽤性 数据库备份⽅式 1.逻辑备份: 备份 DDL DML DCL 语句, 适⽤于中⼩型数据库, 效率相对低下。 mysqldump、mydumper 2.物理备份: 直接复制数据库⽂件, 适⽤于⼤型数据库环境, 效率相对较⾼。 xtrabackup、inbackup、cp、 tar、lvm snapshot 在选择备份⽅式时，需要考虑数据库的规模、备份和恢复的效率、备份数据的⼀致性等因素。同时，为了保证备份 数据的安全性，备份数据应该保存在⾮本地的地⽅，并且建议备份多份。 数据库备份模式 完全备份 增量备份 差异备份 冷备份与热备份 冷备份 关闭数据库服务，完整拷⻉数据⽂件 热备份 在不影响数据库读写服务的情况下备份数据库 备份周期 考虑因素： 数据库⼤⼩(决定备份时间) 恢复速度要求(快速or慢速) 备份⽅式(全量or增量) 企业备份机制 如果你的数据库⼩于20G 每天公司0点进⾏全量备份 逻辑备份 物理备份可以的 如果数据库⼤于100G，周⼀晚上0点进⾏物理全备份，周⼆ 增量备份,周三 以此类推， 1.MySQL逻辑备份与恢复 mysql⾃带逻辑备份⼯具 mysqldump , 可以保证数据备份⼀致性, 以及服务可⽤性 1.1数据库完整备份与恢复 mysqldump命令使⽤⽅式 默认情况下， mysqldump 命令会锁定整个数据库，备份导出数据时会阻塞所有对数据库的写⼊操作，这对于⽣产 环境的数据库来说是不可接受的。使⽤ single-transaction 参数后， mysqldump 命令会在⼀个事务中导出数 据，这样可以保证导出的数据是⼀致的，⽽且不会阻塞数据库的写⼊操作。 single-transaction 参数的使⽤条件如下： 1. 数据库引擎必须⽀持事务，例如 InnoDB 引擎。 2. 导出的表必须使⽤事务，例如表类型为 InnoDB。 注意，使⽤ single-transaction 参数仅适⽤于导出数据，不适⽤于导⼊数据。如果需要导⼊数据，可以将导出 的 SQL ⽂件直接导⼊到 MySQL 数据库中。 注意: 不管物理备份还是逻辑备份, 必须开启 binlog ⽇志 >mysqldump -h 服务器 -u ⽤户名 -p 密码 数据库名 > 备份⽂件.sql //常⽤参数 -A, --all-databases #备份所有库 -B, --databases #备份多个库多个数据库 --single-transaction#InnoDB ⼀致性 服务可⽤性 --master-data=1|2 #记录 binlog⽇志位置与⽂件名,追加⾄备份⽂件中 --triggers #备份触发器 -F, --flush-logs #备份之前刷新⽇志 -E, --events #备份事件调度器代码 -R, --routines #备份存储过程和存储函数 默认情况下， mysqldump 命令会锁定整个数据库，备份导出数据时会阻塞所有对数据库的写⼊操作，这对于⽣产 环境的数据库来说是不可接受的。使⽤ single-transaction 参数后， mysqldump 命令会在⼀个事务中导出数 据，这样可以保证导出的数据是⼀致的，⽽且不会阻塞数据库的写⼊操作。 single-transaction 参数的使⽤条件如下： 1. 数据库引擎必须⽀持事务，例如 InnoDB 引擎。 2. 导出的表必须使⽤事务，例如表类型为 InnoDB。 注意，使⽤ single-transaction 参数仅适⽤于导出数据，不适⽤于导⼊数据。如果需要导⼊数据，可以将导出 的 SQL ⽂件直接导⼊到 MySQL 数据库中。 注意: 不管物理备份还是逻辑备份, 必须开启 binlog ⽇志 1.使⽤ mysqldump 完整备份 //本地备份 [root@sql ~]# mkdir /backup/mysql -p [root@sql ~]# mysqldump -uroot -p'Wing@123' -h 10.1.106.70 \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql 2.查看 binlog ⽇志信息 [root@sql ~]# sed -n "22p" /backup/mysql/`date +%F%H`-mysql-all.sql CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000003', MASTER_LOG_POS=154; 数据库完整恢复流程 1.停⽌数据库 2.删除破损数据库 3.重新初始化数据库 4.重置密码,否则⽆法恢复 5.恢复数据[新密码] 6.刷新授权[备份时密码] yum安装数据库操作 #1.模拟数据奔溃 [root@sql ~]# systemctl stop mysqld [root@sql ~]# rm -rf /var/lib/mysql #2.2.重新初始化数据库,启动后修改密码 [root@sql ~]# systemctl start mysqld [root@sql ~]# grep "password" /var/log/mysqld.log khy_=i512g=F [root@sql ~]# mysqladmin -uroot -p'khy_=i512g=F' password "Wing@123" ⼆进制安装操作 #1.模拟数据奔溃 [root@sql ~]#/etc/init.d/mysqld stop [root@sql ~]# rm -rf /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ [root@sql ~]# rm -rf /soft/mysql #2.基础环境准备 [root@sql ~]# systemctl stop firewalld [root@sql ~]# systemctl disable firewalld [root@sql ~]# setenforce 0 //建⽴⽤户与相应⽬录 [root@sql ~]# groupadd mysql [root@sql ~]# useradd -r -g mysql -s /sbin/nologin mysql [root@sql ~]# mkdir /soft/src -p && cd /soft/src #3.下载`MySQL`并安装 [root@sql src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux glibc2.12-x86_64.tar.gz [root@sql src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /soft [root@sql src]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql #4.进⾏`MySQL`初始化 //创建初始化⽬录以及数据库数据⽬录 [root@sql ~]# mkdir /soft/mysql/{mysql-file,data} [root@sql ~]# chown mysql.mysql /soft/mysql/ //初始化数据库 [root@sql ~]# /soft/mysql/bin/mysqld --initialize \ --user=mysql --basedir=/soft/mysql \ --datadir=/soft/mysql/data ------- //初始化数据库会告诉默认登陆账户与密码 [root@sql ~]# egrep 'temporary password is generated' /log/mysql/err.log 2023-05-21T08:17:39.165590Z 1 [Note] A temporary password is generated for root@localhost: 6N;u,POFLd8I //使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略] [root@sql ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \ --datadir=/soft/mysql/data/ [root@sql ~]# chown -R mysql.mysql /soft/mysql/ #使⽤(systemV)⽅式管理, [强烈推荐] [root@sql ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld [root@sql ~]# chkconfig --add mysqld [root@sql ~]# chkconfig mysqld on //启动数据库 [root@sql ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! //默认情况没有mysql命令, 如果有可能使⽤过yum安装, 这样容易连错数据库(PATH路径存在命令执⾏优先级问题) [root@sql ~]# mysql -bash: mysql: command not found //可以选择添加路径⾄PATH中, 或者直接使⽤绝对路径执⾏ [root@sql ~]# echo "export PATH=$PATH:/soft/mysql/bin" >> /etc/profile [root@sql ~]# source /etc/profile //登陆数据库 [root@sql ~]# mysql -uroot -p'6N;u,POFLd8I' -h 127.0.0.1 //默认系统配置数据库密码必须修改, 否则⽆法使⽤数据库 mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. //修改系统默认密码 mysql> alter user root@'localhost' identified by 'Wing@123'; Query OK, 0 rows affected (0.01 sec) mysql> exit; //退出后使⽤新密码重新登录数据库 [root@sql soft]# mysql -uroot -p"Wing@123" -e ' show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 3.恢复导⼊备份数据 [root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2816-mysql-all.sql //使⽤恢复后的密码进⾏登录mysql [root@sql ~]# mysql -uroot -p"Wing@123" -e ' show databases;' mysql> 4.建议在恢复备份时暂停 binlog ⽇志记录 //1.关闭binlog记录, 不然容易恢复增量失败 [root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql all.sql //2.在mysql控制台临时修改不记录binlog⽇志 mysql> set sql_log_bin=0; mysql> source /backup/mysql/2023-05-2816-mysql-all.sql //3. binlog打开 mysql> set sql_log_bin=1; 1.2数据库增量备份与恢复 实战案例1: 数据库完整备份+数据库增量备份 新建数据表, 进⾏了全量备份, 随着时间推移, 数据库突然奔溃 1.环境准备 //备份之前 mysql> set sql_log_bin=0; mysql> drop database wing; mysql> set sql_log_bin=1; mysql> create database wing; mysql> create table wing.t1 (id int, name varchar(20)); mysql> insert into wing.t1 values (1,"wing1"); mysql> insert into wing.t1 values (2,"wing2"); mysql> select * from wing.t1; +------+------+ | id | name | +------+------+ | 1 | wing1 | | 2 | wing2 | +------+------+ 2 rows in set (0.00 sec) //基于当前状态备份 [root@sql ~]# mysqldump -uroot -p'Wing@123' \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql //模拟数据插⼊操作 mysql> insert into wing.t1 values (3,"wing3"); mysql> insert into wing.t1 values (5,"tt"); mysql> select * from wing.t1; +------+------+ | id | name | +------+------+ | 1 | wing1 | | 2 | wing2 | | 3 | wing3 | | 5 | tt | +------+------+ 4 rows in set (0.00 sec) //模拟数据库故障 [root@sql ~]# /etc/init.d/mysqld stop [root@sql ~]# rm -rf /sort/mysql //重新启动会⾃动初始化操作 [root@sql ~]# systemctl start mysqld //注意修改默认密码 [root@sql ~]# mysqladmin -uroot -p'RA+:>Xu&.6K_' password "Wing@123" 2.恢复全备数据 //关闭binlog记录, 不然容易恢复增量失败 [root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2716-mysql all.sql [root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2716-mysql-all.sql [root@sql mysql]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;" +------+------+ | id | name | +------+------+ | 1 | wing1 | | 2 | wing2 | +------+------+ 3.恢复增量数据 //查看备份后binlog起始位置点 [root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000014', MASTER_LOG_POS=154; //查找结束位置点, 进⾏恢复 [root@sql bin]# mysqlbinlog --start-position=154 sql_106_70.000014|mysql -uroot - p'Wing@123' //验证增量恢复数据 [root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;" +------+-------+ | id | name | +------+-------+ | 1 | wing1 | | 2 | wing2 | | 3 | wing3 | | 5 | tt | +------+-------+ 1.3实战案例2: 数据库完整备份+数据库增量备份 例⼦：现在有⼀个运维⼈员误删除了数据库，或者是某⼀张表 1.删除所有数据库 2.删除所有binlog⽇志 3.重启mysqld服务 1.模拟环境准备 2.使⽤ mysqldump 进⾏全备 3.再次插⼊⼀些数据 mysql> create database wingdb; mysql> use wingdb; mysql> create table t1 (id int,name varchar(20)); mysql> insert into t1 values (1,"wing"); mysql> insert into t1 values (2,"wing1"); mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | wing | | 2 | wing1 | +------+-------+ 2 rows in set (0.00 sec) 2.使⽤ mysqldump 进⾏全备 [root@sql ~]# mysqldump -uroot -p'Wing@123' \ --all-databases \ --single-transaction \ --master-data=1 \ --flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql 3.再次插⼊⼀些数据 mysql> insert into t1 values (3,'trl'), (4,'zx'), (5,'wq'), (6,'tj'), (7,'gwt'); mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | wing | | 2 | wing1 | | 3 | trl | | 4 | zx | | 5 | wq | | 6 | tj | | 7 | gwt | +------+-------+ 7 rows in set (0.00 sec) 3.模拟故障 mysql> delete from t1 where id ='2'; mysql> drop database wingdb; 4.恢复全量备份的数据 [root@sql ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql-all.sql [root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2816-mysql-all.sql [root@sql ~]# mysql -uroot -p'Wing@123' -e "select * from wingdb.t1;" +------+-------+ | id | name | +------+-------+ | 1 | wing | | 2 | wing1 | +------+-------+ 5.通过 mysqlbinlog 命令导出关于数据库 wingdb 的 sql 语句 //查看备份后binlog起始位置点 [root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000015', MASTER_LOG_POS=154; //针对全量备份后仅产⽣1个binlog⽂件的⽅式 [root@sql bin]# mysqlbinlog --start-position=154 --base64-output="decode-rows" -v sql_106_70.000015 > db.sql 全量备份到出问题只产⽣了⼀个 sql_106_70.000017 ⽂件， 如果产⽣了多个binlog⽇志, 可是使⽤下⾯⽅法恢复 mysqlbinlog --base64-output="decode-rows" -v sql_106_70.000017 >> 1.sql 6.查看 sql ⽂件，找到误操作语句对应的位置,然后记录下来, 随后恢复时跳过即可 [root@sql bin]# vim db.sql # at 640 #230527 16:44:22 server id 70 end_log_pos 686 CRC32 0x5230fb50 Delete_rows: table id 176 flags: STMT_END_F ### DELETE FROM `wingdb`.`t1` ### WHERE ### @1=2 ### @2='wing1' # at 717 #230527 16:44:25 server id 70 end_log_pos 782 CRC32 0x6fad23ad Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 782 #230527 16:44:25 server id 70 end_log_pos 880 CRC32 0x4c5bd31f Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1685177065/*!*/; drop database wingdb 7.使⽤ mysqlbinlog 进⾏恢复跳过 drop 与 delete 语句 [root@sql bin]# mysqlbinlog --start-position=154 --stop-position=640 sql_106_70.000015 |mysql -uroot -p'Wing@123' [root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wingdb.t1;" +------+-------+ | id | name | +------+-------+ | 1 | wing | | 2 | wing1 | | 3 | trl | | 4 | zx | | 5 | wq | | 6 | tj | | 7 | gwt | +------+-------+ 2.MySQL物理备份与恢复 2.1 xtrabackup 物理备份 特点： 开源，在线备份InnoDB表 ⽀持限速备份，避免对业务造成影响 ⽀持流备 ⽀持增量备份 ⽀持备份⽂件压缩与加密 ⽀持并⾏备份与恢复，速度快 2.2 mysqldump 和 xtrabackup mysqldump不同，xtrabackup是⼀种物理备份⼯具，但是，它是需要通过协议连接到mysql服务端（这⼀点与 mysqldump相同，它们都是客户端⼯具，都需要连接到服务端），然后读取并复制innodb底层的”数据块”，完成 所谓的”物理备份”。 2.3 xtrabackup备份原理 基于InnoDB的crash-recovery功能 备份期间允许⽤户读写，写请求产⽣redo⽇志 从磁盘上拷⻉数据⽂件 从InnoDB redo log file实时拷⻉⾛备份期间产⽣的所有redo⽇志 恢复的时候 数据⽂件 + redo⽇志 = ⼀致性数据
innodb存储引擎的逻辑结构如上图所示，逻辑单元从⼤到⼩分别为:表空间(tablesapce)、段(segment)、区 (extent)、⻚(page)、⾏(row)，如上图所示，每个⼤的逻辑单元中都包含了N个⼩的逻辑单元。 2.4 xtrabackup怎样实现增量备份 每个Page都有⾃⼰的LSN号码，LSN是⼀个全局递增的号码，每次对page中的记录进⾏修改时，都会有对应的LSN 号码，因为LSN是全局递增的，所以，LSN只会越来越⼤，之后的修改产⽣的LSN肯定⽐之前的操作产⽣的LSN⼤， 每个page中的数据被更改时，都会在这个page中记录下本次的LSN号码，所以，如果这个page中记录的LSN越 ⼤，就证明这个Page中的数据越新（最近被修改）。 ⽽xtrabackup就是通过LSN，实现对innodb表的增量备份的，每次备份开始时，xtrabackup会记录下当前备份到 的LSN号码，当下次进⾏增量备份时，xtrabackup就只会拷⻉出LSN⼤于上次记录的page，那些LSN号码⼩于上次 记录的page则会被跳过，如果page中的LSN⼩于上次备份时记录的LSN号码，则证明这个page⾃从上次备份以来 并没有被修改过，同理，如果page中的LSN⼤于上次备份时记录的LSN号，则证明这个page在上次备份以后，已经 被修改了，所以，备份出这些page，即可实现增量备份的⽬的。 假设，我们第⼀次备份的数据如下，所有数据由如下6个page组成，下图中的⻩⾊⽅块代表page，⻩⾊⽅块右上⻆ 的号码代表当前page的LSN，从下图可以看出，⽬前最⼤的LSN号码为5。
安装 xtrabackup 官⽅下载地址 2.5 数据库完整备份与恢复 1.完整备份流程 mkdir -p /soft/xtrabackup && cd /soft/xtrabackup wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup- 2.4.6/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm yum -y install percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm 2.5 数据库完整备份与恢复 1.完整备份流程 //1.创建备份⽬录 [root@sql ~]# mkdir /xtrabackup/ && cd /xtrabackup/ //2.执⾏全备操作 [root@sql ~]# innobackupex --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root --password="Wing@123" /xtrabackup/ //3.检查备份结果 [root@sql ~]# ls /xtrabackup/ 2023-05-27_17-39-33 [root@lb-node1 xtrabackup]# cat /xtrabackup/2023-05-27_17-39-33/xtrabackup_binlog_info sql_106_70.000017 2394 2.完全备份恢复流程 #1.停⽌数据库 [root@sql ~]#/etc/init.d/mysqld stop #2.清理环境 [root@sql ~]# rm -rf /soft/mysql/data/* #3.重演回滚 [root@sql ~]# innobackupex --apply-log /xtrabackup/2023-05-28_16-33-25 #4.恢复数据 [root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-33-25 #5.修改权限 [root@sql ~]# chown -R mysql.mysql /soft/mysql/data/ #6.启动数据库 [root@sql ~]# /etc/init.d/mysqld start [root@sql ~]# mysql -uroot -pWing@123 mysql> 2.5数据库增量备份与恢复 增量备份每次需要基于前⼀次的备份 1.准备数据 #周⽇的数据 mysql> drop database wingdb; mysql> create database wingdb; mysql> use wingdb; mysql> create table t1(id int, name varchar(20)); mysql> insert into t1 values (7,'day7'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 7 | day7 | +------+------+ 1 row in set (0.00 sec) 2.使⽤物理全备 //清理之前的全量备份测试 [root@sql ~]# rm -rf /xtrabackup/* [root@sql ~]# innobackupex --username=root --password=Wing@123 -S /tmp/mysql.sock /xtrabackup/ 3.后续每天进⾏增量备份 //模拟周⼀备份,先关闭时间同步 [root@lb-node1 xtrabackup]# crontab -l #*/1 * * * * /usr/sbin/ntpdate ntp1.aliyun.com //假如周⼀,登陆mysql插⼊数据 [root@sql ~]# date -s '2023-05-29' [root@sql ~]# mysql -uroot -pWing@123 mysql> use wingdb; mysql> insert into wingdb.t1 values (1,'day1'); mysql> select * from t1; +------+------+ | id | name | +------+------+ | 7 | day7 | | 1 | day1 | +------+------+ //增量备份 [root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \ --incremental /xtrabackup/ \ --incremental-basedir=/xtrabackup/2023-05-28_16-38-33/ [root@sql ~]# cat /xtrabackup/2023-05-29_00-00-45/xtrabackup_binlog_info sql_106_70.000018 4322 //周⼆,登陆mysql插⼊数据 [root@sql ~]# date -s '2023-05-30' mysql> insert into wingdb.t1 values (2,'day2'); mysql> select * from wingdb.t1; +------+------+ | id | name | +------+------+ | 7 | day7 | | 1 | day1 | | 2 | day2 | +------+------+ //基于周⼀基础之上进⾏增备 [root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \ --incremental /xtrabackup/ \ --incremental-basedir=/xtrabackup/2023-05-29_00-00-49/ [root@sql ~]# cat /xtrabackup/2023-05-30_00-00-45/xtrabackup_binlog_info sql_106_70.000018 4587 4.模拟故障 //1.停⽌数据库 [root@sql ~]# /etc/init.d/mysqld stop //2.清理环境 [root@sql ~]# rm -rf /var/lib/mysql/* 5.依次重演 //恢复周⽇全备数据 [root@sql ~]# innobackupex --apply-log \ --redo-only /xtrabackup/2023-05-28_16-38-33/ //恢复周⼀的增量数据 [root@sql ~]# innobackupex --apply-log \ --redo-only /xtrabackup/2023-05-28_16-38-33/ \ --incremental-dir=/xtrabackup/2023-05-29_00-00-49 //恢复周⼆的增量数据 [root@sql ~]# innobackupex --apply-log \ --redo-only /xtrabackup/2023-05-28_16-38-33/ \ --incremental-dir=/xtrabackup/2023-05-30_00-00-27 5.执⾏回滚 //恢复对应数据 [root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-38-33/ //授权 [root@sql ~]# chown -R mysql.mysql /soft/mysql/ //启动数据库 [root@sql ~]# /etc/init.d/mysqld start //查询数据 [root@sql ~]# mysql -uroot -pWing@123 -e "select * from wingdb.t1;" +------+------+ | id | name | +------+------+ | 7 | day7 | 1 | day1 | | 2 | day2 | +------+------+ 2.6数据库差异备份与恢复 1.完整备份 //修改时间为周⽇ [root@sql ~]#/usr/sbin/ntpdate ntp1.aliyun.com //创建对应数据 mysql> drop database wingdb; mysql> create database wingdb; mysql> use wingdb; mysql> create table t2(id int); mysql> insert into t2 values(7); mysql> select * from t2; +------+ | id | +------+ | 7 | +------+ //清理之前的备份 ⽣产谨慎操作 [root@sql ~]# rm -rf /xtrabackup/* //使⽤物理全备 [root@sql ~]# /usr/sbin/ntpdate ntp1.aliyun.com [root@sql ~]# mkdir /xtrabackup [root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock /xtrabackup/ [root@sql ~]# cat /xtrabackup/2023-05-27_18-10-25/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 8559825 last_lsn = 8559834 compact = 0 recover_binlog_info = 0 2、差异备份:周⼀ -> 周三 //修改时间为周⼀, 新增数据 [root@sql ~]# date -s '2023-05-29' mysql> insert into wingdb.t2 values(1); //差异备份(以完整备份为准2023-05-28_16-52-1) [root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \ --incremental /xtrabackup/ \ --incremental-basedir=/xtrabackup/2023-05-28_16-52-14/ //修改时间为周⼆, 新增数据 [root@sql ~]# date -s '2023-05-30' mysql> insert into wingdb.t2 values(2); //差异备份(以完整备份为准2023-05-28_16-52-1) [root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \ --incremental /xtrabackup/ \ --incremental-basedir=/xtrabackup/2023-05-28_16-52-14/ //修改时间为周三, 新增数据 [root@sql ~]# date -s '2023-05-31' mysql> insert into wingdb.t2 values(3); //差异备份, (以完整备份为准2023-05-28_16-52-1) [root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \ --incremental /xtrabackup/ \ --incremental-basedir=/xtrabackup/2023-05-28_16-52-14/ 4.模拟备份后⼀次错误操作 mysql> create database WingSred; mysql> drop database WingSred; 3.差异备份恢复流程 //1.停⽌数据库 [root@sql ~]# /etc/init.d/mysqld stop //2.清理环境 [root@sql ~]# rm -rf /soft/mysql/data/* //3.重演回滚, 回滚全备 [root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/ //4.重演回滚, 将差异备份应⽤⾄完整备份上 [root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/ \ --incremental-dir=/xtrabackup/2023-05-31_00-00-30/ //5.应⽤数据 [root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-52-14/ //6.修改权限 [root@sql ~]# chown -R mysql.mysql /soft/mysql/ //7.启动数据库 [root@sql ~]# /etc/init.d/mysqld start [root@sql ~]# mysql -uroot -pWing@123 -e "select * from wingdb.t2;" +------+ | id | +------+ | 7 | | 1 | | 2 | | 3 | +------+ //6.binlog恢复删除掉的WingSred;库 [root@sql ~]# cat /xtrabackup/2023-05-31_00-00-25/xtrabackup_binlog_info sql_106_70.000018 1676 //导出对应的sql, 找到drop语句位置 [root@sql ~]# mysqlbinlog --start-position=1676 \ --base64-output="decode-rows" \ -v sql_106_70.000019 sql_106_70.000018 >db.sql //针对起始点与结束点位置恢复 [root@sql bin]# mysqlbinlog --start-position=1676 \ --stop-position=1847 sql_106_70.000018 |mysql -uroot -p'Wing@123'
3.简单命令进⾏物理备份 使⽤ tar 命令打包来进⾏物理备份数据库系统 备份期间，服务不可⽤ 1.备份操作过程 //1.停⽌数据库 [root@sql ~]# /etc/init.d/mysqld stop //2. [root@sql ~]# mkdir /backup && cd /backup //3.tar备份数据 [root@sql ~]# cp -rd /soft/mysql/ ./ [root@sql ~]# tar -czf `date +%F`-mysql-all.tar mysql 注：备份⽂件应该复制其它服务器或存储上 2.恢复操作过程 //1.停⽌数据库 [root@sql ~]#/etc/init.d/mysqld stop //2.清理环境 [root@sql ~]# rm -rf /soft/mysql //3.导⼊备份数据 [root@sql ~]# tar -xf /backup/2023-05-31-mysql-all.tar -C /soft/ //4.权限 [root@sql ~]# chown mysql.mysql -R /soft/mysql //4.启动数据库 [root@sql ~]#/etc/init.d/mysqld start //5.binlog 恢复 4.⽣产备份思路与实战 逻辑备份 保证⼀致性，服务可⽤性 适合少量的数据1G-10G 只能全备->指定某⼀个数据库备份 效率不是很⾼, 将所有的数据转成sql语句 ddl dml dcl 物理备份xtrabackup 保证⼀致性，服务可⽤性 适合⼤量的数据20G 100G 500G ⽀持增量、差异、全备 备份怎么做 物理备份 周天全备 周⼀到周六增量 /xtrabackup/1⽉/1周/xxx /xtrabackup/1⽉/2周/xx /xtrabackup/1⽉/3周 /xtrabackup/1⽉/4周 /xtrabackup/2⽉/1周 注意： binlog⽇志⾮常的重要 备份时，不要记录binlog⽇志 备份不单单只是数据⽂件，还有binlog、my.cnf 标签：10,16,--,备份,sql,xtrabackup,2023.6,mysql,root From： https://www.cnblogs.com/BXXY5961/p/17486699.html