网站建设需要服务器,电子商务交易平台,做网站流量优化都是什么,公司域名不变网站做变动前言#xff1a;本博客仅作记录学习使用#xff0c;部分图片出自网络#xff0c;如有侵犯您的权益#xff0c;请联系删除 一、为什么要备份
备份#xff1a;能够防止由于机械故障以及人为误操作带来的数据丢失#xff0c;例如将数据库文件保存在了其它地方。 冗余#… 前言本博客仅作记录学习使用部分图片出自网络如有侵犯您的权益请联系删除 一、为什么要备份
备份能够防止由于机械故障以及人为误操作带来的数据丢失例如将数据库文件保存在了其它地方。 冗余 数据有多份冗余但不等备份只能防止机械故障带来的数据丢失例如主备模式、数据库 集群。
二、MySQL数据备份需要重视的内容
备份内容databases、 Binlog、 my.cnf所有备份数据都应放在非数据库本地而且建议有多份副本。测试环境中做日常恢复演练恢复较备份更为重要。
备份过程必须考虑因素 1. 数据的一致性2. 服务的可用性Mysql A (读写) 压力比较大|Mysql B (读) 压力比较小 从节点做备份
三、MySQL备份类型
1、物理备份
直接复制数据库文件适用于大型数据库环境不受存储引擎的限制但不能恢复到不同的MySQL版本。
1热备hot backup
在线备份数据库处于运行状态这种备份方法依赖于数据库的日志文件对应用基本无影响但是性能还是会有下降,所以尽量不要在主库上做备份,在从库上做
2冷备cold backup
备份数据文件需要停机是在关闭数据库的时候进行的 备份 datadir 目录下的所有文件
3温备warm backup
针对myisam的备份(myisam不支持热备),备份时候实例只读不可写数据库锁定表格不可写入但可 读的状态下进行的对应用影响很大通常加一个读锁
2、逻辑备份
备份的是建表、建库、插入等操作所执行SQL语句DDL DML DCL适用于中小型数据库效率相对较低。
3、物理和逻辑备份的区别
逻辑备份物理备份备份方式备份数据库建表、建库、插入sql备份数据库物理文件优点语句备份文件相对较小,只备份表中的数据与结构恢复速度比较快缺点恢复速度较慢(需要重建索引,存储过程等)备份文件相对较大备份表空间包含数据与索引对业务影响I/0负载加大I/O负载加大代表工具mysqldumpibbackup、xtrabackup
四、MySQL备份工具 1、ibbackup官方备份工具收费物理备份2、xtrabackup开源社区备份工具开源免费物理备份3、mysqldump官方自带备份工具开源免费逻辑备份速度慢4、mysqlbackupmysql 官方备份工具innodb 引擎的表mysqlbackup 可以进行热备非innodb 表mysqlbackup 就只能温备物理备份备份还原速度快适合大规模数据使用
五、物理备份的方式
1、完全备份——完整备份 每次对数据进行完整的备份即对整个数据库的备份、数据库结构和文件结构的备份保存的是备份完成时刻的数据库是差异备份与增量备份的基础。
优点备份与恢复操作简单方便恢复时一次恢复到位恢复速度快
缺点占用空间大备份速度慢
2、增量备份 每次备份上一次备份到现在产生的新数据只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点仅备份这之间的数据变化
特点因而备份的数据量小占用空间小备份速度快。但恢复时需要从上一次的完整备份起按备份时间顺序逐个备份版本进行恢复恢复时间长如中间某次的备份数据损坏将导致数据的丢失。
3、差异备份 只备份跟完整备份不一样的备份那些自从第一次次完全备份之后被修改过的所有文件备份的时间起点是从第一次的完整备份起且以 后每次备份都是和第一次完整备份进行比较注意是第一次不是上一次备份自第一次完整备份以来所有的修改过的文件备份数据量会越来越大
特点占用空间比增量备份大比完整备份小恢复时仅需要恢复第一个完整版本和最后一次的差异版本恢复速度介于完整备份和增量备份之间。
六、percona工具
Xtrabackup 是开源免费的支持MySQL 数据库热备份的软件在 Xtrabackup 包中主要有Xtrabackup 和innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup同时增加了备份MyISAM引擎的功能。它不暂停服务创建Innodb热备份
Software Downloads - Percona 1、安装xtrackbackup
百度自行搜索
1上传peronaRPM包 # yum -y install percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm
2、完全备份流程 1、systemctl stop mysqld # 关闭数据库2、mkdir /xtrabackup/ # 创建备份目录3、innobackupex --userroot --password123 /xtrabackup/ # 备份4、innobackupex --apply-log /xtrabackup/要恢复的目录 # 准备恢复数据5、innobackupex --copy-back /xtrabackup/要恢复的目录 # 恢复数据6、chown mysql.mysql /data -R # 添加权限
具体示例演示 # 数据库备份[rootcentos /]# innobackupex --userroot --password123 /xtrabackup/# 备份完成在/xtrabackup/目录下能看到备份好的目录[rootcentos /]# cd /xtrabackup/[rootcentos xtrabackup]# ls2024-07-26_15-41-02--------------------------------------------------------# 实验删除数据库中的数据[rootcentos /]# rm -rf /data/mysql/data/ # 删除完数据可进入数据库查看库进行验证开始恢复数据[rootcentos /]# innobackupex --apply-log /xtrabackup/2024-07-26_15-41-02[rootcentos /]# innobackupex --copy-back /xtrabackup/2024-07-26_15-41-02[rootcentos /]# chown mysql.mysql /data -R # 恢复完成[rootcentos /]# systemctl restart mysqld[rootcentos /]# mysqlmysql show databases;
3、增量备份流程
基本语法 1、systemctl stop mysqld2、innobackupex --userroot --password123 /xtrabackup/ # 完全备份3、innobackupex --userroot --password123 --incremental /xtrabackup/ --incremental-basedir/xtrabackup/完全备份的目录4、innobackupex --userroot --password123 --incremental /xtrabackup/ --incremental-basedir/xtrabackup/上一次增量的备份目录5、innobackupex --apply-log --redo-only /xtrabackup/全备目录6、innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir/xtrabackup/要恢复的上一次增量目录7、innobackupex --copy-back /xtrabackup/全备
具体示例演示 [rootcentos /]# innobackupex --userroot --password123 /xtrabackup/ #全备 [rootcentos /]# cd /xtrabackup # 检查第一次全备目录[rootcentos xtrabackup]# ls2024-07-26_16-54-18# 开始实验创建周一sunday库mysql create database sunday;# 第一次备份选用上个备份目录这里即全备目录[rootcentos /]# innobackupex --userroot --password123 --incremental /xtrabackup/ --incremental-basedir/xtrabackup/2024-07-26_16-54-18 # 查看目录发现多了带有sunday库的目录[rootlocalhost xtrabackup]# ls 2024-07-26_16-54-18 2024-07-26_17-01-01# 再创建一个周二tuesday库mysql create database tuesday;# 第二次增量备份选用上次目录即从sunday库备份的目录[rootcentos /]# innobackupex --userroot --password123 --incremental /xtrabackup/ --incremental-basedir/xtrabackup/2024-07-26_17-01-01 [rootlocalhost xtrabackup]# ls # 查看多出的目录即带有tuesday的备份目录2024-07-26_16-54-18 2024-07-26_17-01-01 2024-07-26_17-03-34# 增加权限并重启mysqld服务[rootcentos /]# rm -rf /data/mysql/data[rootcentos /]# chown -R mysql.mysql /data[rootcentos /]# systemctl restart mysqldmysql show databases; # 检查数据库发现无数据库;开始恢复[rootcentos /]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-26_16-54-18[rootcentos /]# innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir/xtrabackup/2024-07-26_17-03-34 # 这里选择恢复tuesday备份目录mysql show databases; # 检查数据库发现数据库中连带monday库一起恢复--------------------| Database |--------------------| information_schema || monday || mysql || performance_schema || sys || tuesday |--------------------
4、差异备份流程 七、mysqldump逻辑备份
推荐优先使用
1、常用备份选项 -A, --all-databases #备份所有库-B, --databases #备份多个数据库-F, --flush-logs #备份之前刷新binlog日志--default-character-set #指定导出数据时采用何种字符集如果数据表不是采用默认的latin1字符集的话那么导出时必须指定该选项否则再次导入数据后将产生乱码问题。--no-data-d #不导出任何数据只导出数据库表结构。--lock-tables #备份前锁定所有数据库表--single-transaction #保证数据的一致性和服务的可用性-f, --force #即使在一个表导出期间得到一个SQL错误继续。
使用 mysqldump 备份数据库时避免锁表:
对一个正在运行的数据库进行备份请慎重 如果一定要在服务运行期间备份可以选择添加 --single-transaction选项 类似执行 mysqldump --single-transaction -u root -p123456 dbname mysql.sql
2、备份表 语法: # mysqldump -u root -p1 db1 t1 /db1.t1.bak[rootmysql-server ~]# mkdir /home/back # 创建备份目录[rootmysql-server ~]# mysqldump -uroot -pqf123 company employee5 /home/back/company.employee5.bak# 备份多个表语法: mysqldump -u root -p1 db1 t1 t2 /db1.t1_t2.bak[rootmysql-server ~]# mysqldump -uroot -pqf123 company new_t1 new_t2 /home/back/company.new_t1_t2.bak
3、备份库 备份一个库相当于将这个库里面的所有表全部备份。语法: # mysqldump -u root -p1 db1 /db1.bak[rootmysql-server ~]# mysqldump -uroot -pqf123 company /home/back/company.bak备份多个库语法mysqldump -u root -p1 -B db1 db2 db3 /db123.bak[rootmysql-server ~]# mysqldump -uroot -pqf123 -B company testdb /home/back/company_testdb.bak备份所有的库语法# mysqldump -u root -p1 -A /alldb.bak[rootmysql-server ~]# mysqldump -uroot -pqf123 -A /home/back/allbase.bak
4、恢复数据库和表
为保证数据一致性应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
为实验效果先将刚才备份的数据库和表删除了。登陆数据库 [rootmysql-server ~]# mysql -uroot -pqf123mysql show databases;mysql drop database company;mysql \q
恢复库 登陆mysql创建一个库mysql create database company;恢复[rootmysql-server ~]# mysql -uroot -pqf123 company /home/back/company.bak
恢复表 登陆到刚才恢复的库中将其中的一个表删除掉mysql show databases;mysql use companymysql show tables;-------------------| Tables_in_company |-------------------| employee5 || new_t1 || new_t2 |-------------------mysql drop table employee5;开始恢复:mysql set sql_log_bin0; #停止binlog日志Query OK, 0 rows affected (0.00 sec)mysql use company;mysql source /home/back/company.employee5.bak; -------加路径和备份的文件恢复方式二# mysql -u root -p1 db1 db1.t1.bak库名 备份的文件路径[rootmysql-server ~]# mysql -uroot -pqf123 company /home/back/company.employee5.bak
5、备份及恢复表结构
备份 linux中执行 mysqldump -uroot -p123 库 备份路径 mysqldump -uroot -p123 库 表1 表2 备份路径 # 备份一个库里的多张表mysqldump -uroot -p123 -B 库1 库2 备份路径 # 备份多个库mysqldump -uroot -p123 -A 备份路径 # 备份所有数据
还原 mysql -uroot -p 库 备份数据 # 恢复单个库需要手动创建库mysql -uroot -p 备份数据 # 如果还原多个库或者所有数据无需手动创建库
seuce还原
在数据库中执行 source 备份文件
备份恢复表结构 1.备份表结构语法mysqldump -uroot -p123456 -d database table dump.sql[rootmysql-server ~]# mysqldump -uroot -pqf123 -d company employee5 /home/back/emp.bak恢复表结构登陆数据库创建一个库mysql create database t1;语法# mysql -u root -p1 -D db1 db1.t1.bak[rootmysql-server ~]# mysql -uroot -pqf123 -D t1 /home/back/emp.bak
通过binlog日志恢复数据
先开启binl 重启服务查看正在使用的binlog找到binlog日志 /data/mysql/data /var/lib/mysql /usr/local/mysql找到想要恢复数据的偏移量linux下mysqlbinlog --start-position 844899 --stop-position 844994 137.000003 |mysql -uroot在数据库中查看数据是否恢复
致谢
在此我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者如果文章中有任何错误欢迎留言指正。 学习永无止境让我们共同进步
