企业网站后台管理,如何查看网站的建设者,东莞城乡住建局,wordpress最好最全的教程数据备份及恢复
1.概述
所有备份数据都应放在非数据库本地#xff0c;而且建议有多份副本
备份#xff1a; 能够防止由于机械故障以及人为误操作带来的数据丢失#xff0c;例如将数据库文件保存在了其它地方
冗余#xff1a; 数据有多份冗余#xff0c;但不等备份而且建议有多份副本
备份 能够防止由于机械故障以及人为误操作带来的数据丢失例如将数据库文件保存在了其它地方
冗余 数据有多份冗余但不等备份只能防止机械故障还来的数据丢失例如主备模式、数据库集群
备份考虑的因素
数据的一致性
服务的可用性
分类
逻辑备份
备份的是建表、建库、插入等操作所执行SQL语句适用于中小型数据库效率相对较低mysqldumpbinlog日志
物理备份
直接复制数据库文件适用于大型数据库环境不受存储引擎的限制但不能恢复到不同的MySQL版本tar、xtrabackup
备份方式分类
完全备份
备份所有数据
增量备份
每次备份上一次备份到现在产生的新数据 差异备份
只备份跟完整备份不一样的
备份方案
①完全备份增量备份
设定一个周期一周或一月每周一对数据完整备份周二到周天做增量备份定期清理旧的数据只保留最新的若干份
②完全备份差异备份
设定一个周期一周或一月每周一对数据完整备份周二到周天做差异备份以周一的完整备份为基准定期清理旧的数据只保留最新的若干份
2.tar备份
注意备份期间服务不可用
备份过程完全物理备份
停止数据库
[rootxingdian ~]# systemctl stop mysqld tar备份数据
[rootxingdian ~]# mkdir /backup
[rootxingdian ~]# cd /var/lib/mysql
[rootxingdian ~]# tar -zcvf /backup/date %F-mysql-all.tar.gz ./* 启动数据库备份完成后启动数据库继续为其他服务提供服务
[rootxingdian ~]# systemctl start mysqld恢复过程模拟数据丢失恢复数据
停止数据库
[rootxingdian ~]# systemctl stop mysqld 清理环境
[rootxingdian ~]# rm -rf /var/lib/mysql/* 导入备份数据
[rootxingdian ~]# tar -xvf /backup/2019-08-20-mysql-all.tar.gz -C /usr/lib/mysql
[rootxingdian ~]# chown mysql.mysql /var/lib/mysql/* -R 启动数据库恢复后验证数据是否恢复成功
[rootxingdian ~]# systemctl start mysqld3.xtrabackup备份
简介
percona-xtrabackup是开源免费的支持MySQL 数据库热备份的软件能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份它不暂停服务创建Innodb热备份为mysql做增量备份在mysql服务器之间做在线表迁移使创建replication更加容易备份mysql而不增加服务器的负载 安装软件
[rootxingdian ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
[rootxingdian ~]# yum install percona-xtrabackup-24 -y完整备份
创建备份目录
[rootxingdian ~]# mkdir -p /xtrabackup/full/ 备份
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 /xtrabackup/full/ 查看备份数据
[rootxingdian ~]# ls /xtrabackup/full/
2022-09-25_19-40-47 模拟数据丢失数据恢复以下操作模拟数据丢失
丢失前数据库中的数据
[rootxingdian ~]# mysql -u root -pQianFeng123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.39 MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
--------------------
5 rows in set (0.00 sec)数据丢失
[rootxingdian ~]# systemctl stop mysqld
[rootxingdian ~]# rm -rf /var/lib/mysql/*
[rootxingdian ~]# rm -rf /var/log/mysqld.log
[rootxingdian ~]# rm -rf /var/log/mysql-slow/slow.log (有则删除无则不需要操作)恢复前的验证
该步骤主要是验证备份的数据是否可用
[rootxingdian ~]# innobackupex --apply-log /xtrabackup/full/2022-09-25_19-40-47/恢复之前需要确认配置文件内有数据库目录指定不然xtrabackup不知道恢复到哪里
[rootxingdian ~]# cat /etc/my.cnf
datadir/var/lib/mysql 恢复数据
[rootxingdian ~]# innobackupex --copy-back /xtrabackup/full/2022-09-25_19-40-47/ 修改权限
[rootxingdian ~]# chown mysql.mysql /var/lib/mysql -R 启动服务
[rootxingdian ~]# systemctl start mysqld 验证
[rootxingdian ~]# mysql -u root -pQianFeng123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39 MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
--------------------
5 rows in set (0.00 sec)增量备份
原理每次备份上一次备份到现在产生的新数据
注意在进行增量备份前先进行完整备份
案例周一进行全备周二到周天进行增量备份
完整备份周一
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 /xtrabackup/full 创建增量备份存放数据目录
[rootxingdian ~]# mkdir /xtrabackup/zeng -p 模拟数据增加略
第一次增量备份周二
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 --incremental /xtrabackup/zeng/ --incremental-basedir/xtrabackup/full/2022-09-25_19-40-47/第一次增量备份的数据
[rootxingdian ~]# ls /xtrabackup/zeng/
2022-09-25_19-56-00 模拟数据增加略
第二次增量备份周三
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 --incremental /xtrabackup/zeng/ --incremental-basedir/xtrabackup/zeng/2022-09-25_19-56-00/第二次增量备份的数据
[rootxingdian ~]# ls /xtrabackup/zeng/
2022-09-25_19-56-00 2022-09-25_19-58-12 后面的增量备份重复上面的操作略
增量备份数据恢复流程需要模拟数据的丢失
停止数据库
[rootxingdian ~]# systemctl stop mysqld 删除数据
[rootxingdian ~]# rm -rf /var/lib/mysql/*
[rootxingdian ~]# rm -rf /var/log/mysqld.log其他数据根据实际情况删除 依次重演回滚
全备回滚
[rootxingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/第一次增量回滚
[rootxingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/ --incremental-dir/xtrabackup/zeng/2022-09-25_19-56-00/第二次增量回滚
[rootxingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_19-40-47/ --incremental-dir/xtrabackup/zeng/2022-09-25_19-58-12/根据实际增量备份的次数回滚可以想恢复到那个时间节点就回滚到那个时间节点所有的回滚都给全备 恢复数据
[rootxingdian ~]# innobackupex --copy-back /xtrabackup/full/2022-09-25_19-40-47/ 修改权限
[rootxingdian ~]# chown mysql.mysql /var/lib/mysql -R 启动数据库
[rootxingdian ~]# systemctl start mysqld 验证
[rootxingdian ~]# mysql -u root -pQianFeng123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39 MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
| t2 |
| t3 |
--------------------差异备份
原理只备份跟完整备份不一样的
注意在进行增量备份前先进行完整备份
案例周一进行全备周二到周天进行差异备份
完整备份周一
[rootxingdian ~]# mkdir -p /xtrabackup/full
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 /xtrabackup/full 模拟数据增加略
第一次差异备份周二
[rootxingdian ~]# mkdir -p /xtrabackup/jian
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 --incremental /xtrabackup/jian --incremental-basedir/xtrabackup/full/2022-09-25_20-10-52/查看第一次差异备份的数据
[rootxingdian ~]# ls /xtrabackup/jian/
2022-09-25_20-12-55 模拟数据增加略
第二次差异备份周三
[rootxingdian ~]# innobackupex --userroot --passwordQianFeng123 --incremental /xtrabackup/jian --incremental-basedir/xtrabackup/full/2022-09-25_20-10-52/查看第二次差异备份的数据
[rootxingdian ~]# ls /xtrabackup/jian/
2022-09-25_20-12-55 2022-09-25_20-14-32注意后面的差异备份跟之前一样根据需求可以继续差异备份差异备份恢复流程模拟数据丢失
停止数据库
[rootxingdian ~]# systemctl stop mysqld 删除数据
[rootxingdian ~]# rm -rf /var/lib/mysql/*
[rootxingdian ~]# rm -rf /var/log/mysqld.log 重演数据回滚
完整备份回滚
[rootxingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_20-10-52/差异备份回滚根据差异备份的原理如果恢复所有数据只需要将最后依次差异回滚
[rootxingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-09-25_20-10-52/ --incremental-dir/xtrabackup/jian/2022-09-25_20-14-32/ 恢复数据
[rootxingdian ~]# innobackupex --copy-back /xtrabackup/full/2022-09-25_20-10-52/ 修改权限
[rootxingdian ~]# chown mysql.mysql /var/lib/mysql -R 启动数据库
[rootxingdian ~]# systemctl start mysqld 数据验证
[rootxingdian ~]# mysql -u root -pQianFeng123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39 MySQL Community Server (GPL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| k1 |
| k2 |
| mysql |
| performance_schema |
| sys |
--------------------
9 rows in set (0.00 sec)4.mysqldump备份
备份表前提有库有表
[rootxingdian ~]# mysqldump -u root -pQianFeng123 k1 t1 /t1.sql恢复表恢复之前模拟数据丢失
[rootxingdian ~]# mysql -u root -pQianFeng123 k1 /t1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.验证
[rootxingdian ~]# mysql -u root -pQianFeng123 -e use k1;show tables
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
| Tables_in_k1 |
--------------
| t1 |
--------------备份一个库
[rootxingdian ~]# mysqldump -u root -pQianFeng123 k1 /k1.sql-B 备份多个库
[rootxingdian ~]# mysqldump -u root -pQianFeng123 -B k1 k2 /kall.sql-A 备份所有库
[rootxingdian ~]# mysqldump -u root -pQianFeng123 -A /all.sql数据恢复
为保证数据一致性应在恢复数据之前停止数据库对外的服务,停止binlog日志
binlog使用binlog日志恢复数据时也会产生binlog日志如果开启的话需要关闭
mysql set sql_log_bin0;
Query OK, 0 rows affected (0.00 sec) 模拟数据丢失略
[rootxingdian ~]# mysql -u root -pQianFeng123 -D k1 /k1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database k1
出现该错误是因为在恢复的时候需要有库的存在[rootxingdian ~]# mysql -u root -pQianFeng123 -e create database k1
[rootxingdian ~]# mysql -u root -pQianFeng123 -D k1 /k1.sql [rootxingdian ~]# mysql -u root -pQianFeng123 -e create database k1
[rootxingdian ~]# mysql -u root -pQianFeng123 -e create database k2
[rootxingdian ~]# mysql -u root -pQianFeng123 -D k1 k2 /kall.sql或者
mysql source /k1.sql验证
[rootxingdian ~]# mysql -u root -pQianFeng123 -e use k1; show tables;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
| Tables_in_k1 |
--------------
| t1 |
--------------
[rootxingdian ~]# mysql -u root -pQianFeng123 -e use k2; show tables;
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
| Tables_in_k2 |
--------------
| t1 |
--------------5.binlog日志备份
原理日志方法备份恢复数据
日志默认存储位置
rpm /var/lib/mysql
编译: 安装目录的var下
产生日志
方式一编译安装
[rootxingdian ~]# mysqld_safe --log-bin --usermysql --server-id1 查看binlog日志
[rootxingdian ~]# mysqlbinlog slave2-bin.000001 -v --base64-outputdecode-rows时间点 141126 14:04:49位置点 : at 106 方式二rpm安装永久
[rootxingdian ~]# vim /etc/my.cnf
log-binmylog
server-id1 //做主从复制使用[rootxingdian ~]# systemctl restart mysqld查看
[rootxingdian ~]# ls /var/lib/mysql
auto.cnf client-key.pem ib_logfile1 mysql private_key.pem sys
ca-key.pem ib_buffer_pool ibtmp1 mysql.sock public_key.pem xingdian-bin.index
ca.pem ibdata1 mylog.000001 mysql.sock.lock server-cert.pem xtrabackup_info
client-cert.pem ib_logfile0 mylog.index[rootxingdian ~]# mysqlbinlog /var/lib/mysql/mylog.000001 -v --base64-outputdecode-rows
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#220925 21:12:47 server id 1 end_log_pos 123 CRC32 0x52358645 Start: binlog v 4, server v 5.7.39-log created 220925 21:12:47 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#220925 21:12:47 server id 1 end_log_pos 154 CRC32 0xa84d8536 Previous-GTIDs
# [empty]
# at 154
#220925 21:13:38 server id 1 end_log_pos 219 CRC32 0xc2b00431 Anonymous_GTID last_committed0 sequence_number1 rbr_onlyno
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 219
#220925 21:13:38 server id 1 end_log_pos 307 CRC32 0x635401a5 Query thread_id2 exec_time0 error_code0
SET TIMESTAMP1664111618/*!*/;
SET session.pseudo_thread_id2/*!*/;
SET session.foreign_key_checks1, session.sql_auto_is_null0, session.unique_checks1, session.autocommit1/*!*/;
SET session.sql_mode1436549152/*!*/;
SET session.auto_increment_increment1, session.auto_increment_offset1/*!*/;
/*!\C utf8 *//*!*/;
SET session.character_set_client33,session.collation_connection33,session.collation_server8/*!*/;
SET session.lc_time_names0/*!*/;
SET session.collation_databaseDEFAULT/*!*/;
create database t1
/*!*/;
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;方法二
mysql show binlog events in mylog.000001;
--------------------------------------------------------------------------------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
--------------------------------------------------------------------------------------------------
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
| mylog.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 219 | Query | 1 | 307 | create database t1 |
--------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)默认查看第一个
mysql show binlog events;
--------------------------------------------------------------------------------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
--------------------------------------------------------------------------------------------------
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
| mylog.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 219 | Query | 1 | 307 | create database t1 |
--------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)数据恢复
根据时间点恢复数据
[rootxingdian ~]# mysqlbinlog --start-datetime2022-9-25 21:12:47 --stop-datetime2022-9-25 21:16:55 /var/lib/mysql/mylog.000001 | mysql -u root -pQianFeng123 根据位置点恢复数据
mysql show binlog events;
--------------------------------------------------------------------------------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
--------------------------------------------------------------------------------------------------
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
| mylog.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 219 | Query | 1 | 307 | create database t1 |
| mylog.000001 | 307 | Anonymous_Gtid | 1 | 372 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 372 | Query | 1 | 453 | drop database t1 |
| mylog.000001 | 453 | Anonymous_Gtid | 1 | 518 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 518 | Query | 1 | 606 | create database t1 |
| mylog.000001 | 606 | Anonymous_Gtid | 1 | 671 | SET SESSION.GTID_NEXT ANONYMOUS |
| mylog.000001 | 671 | Query | 1 | 752 | drop database t1 |
--------------------------------------------------------------------------------------------------
[rootxingdian ~]# mysqlbinlog --start-position 219 --stop-position 307 /var/lib/mysql/mylog.000001 | mysql -u root -pQianFeng123
