首页 > 数据库 >MYSQL 备份与恢复

MYSQL 备份与恢复

时间：2023-12-27 09:56:27浏览次数：29  
标签：opt -- 恢复 备份 MYSQL kgc mysql 日志

备份与恢复

日志文件默认保存位置 .../mysql/data   ...指安装的路径 /usr/local/mysql/data

永久修改日志文件

vim /etc/my.cnf #配置文件

[mysqld]
#错误日志，用来记录dangmysql启动、停止或运行时发生的错误信息，默认开启
log-error=/usr/local/mysql/data/mysql_error.log
#指定日志的保存位置和文件名



general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#通用查询日志，用来记录MySOL的所有连接和语句，默认是关闭的




log-bin=mysql-bin #也可以 log bin=mysql-bin
#二进制日志(binlg)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启




slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
#慢查询日志，用来记录所有执行时间超过long_gquery_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的




long_query_time=5
#设置超过5秒执行的语句被记录，缺省时为10秒


 





 #修改配置文件




 #重启mysql服务，登录MySQL




 #查看通用查询日志是否开启




 #查看二进制日志是否开启




 #查看慢寻日志功能是否开启




 #查看慢寻查询时间设置，2秒




 #开启慢查询


 


数据库备份



#### 物理与逻辑角度

##### 物理备份

对数据库操作系统的物理文件(如数据文件
日志文件等)的备份

物理备份方法

冷备份 (脱机备份) : 是在关闭数据库的时候进行的

热备份 (联机备份) : 数据库处于运行状态，依赖于数据库的日志文件

温备份  数据库锁定表格 (不可写入但可读) 的状态下进行备份操作



##### 逻辑备份

对数据库逻辑组件 (如: 表等数据库对象)的备份






备份策略


完全备份 ：每次备份都备份完整的库或者表数据




差异备份 ：只备份上一次完全备份后的更新数据




增量备份 :每次备份只备份上一次完全备份或增量备份后的更新数据



 





完全备份：每次对数据库进行完整备份
#恢复起来容易
差异备份：备份自从上次完全备份之后被修改过的文件

增量备份：只有在上次完全备份或增量备份后被修改的文件才会被备份。
#只备份更新数据，没有重复数据。 恢复起来需要按照顺序来恢复，比较麻烦


#生产环境中完全备份和增量备份组合用
#如在每周三凌晨通过计划性crontab任务做个完全备份，通过计划性crontab任务每天做增量备份，从而完成公司的备份操作



 


mysqldump备份数据库



mysqldump -uroot -p123 --databases kgc > kgc.sql  
#不加--databases只会备份表不会备份库
mysqldump -uroot -p123 -d kgc  ky34 ky35 > kgc_ky45.sql #只备份表结构不备份表数据 


mysqldump  -uxxx -pxxx --databases 库1 [库2...]  > xxx.sql  #备份一个或多个库及库中所有的表数据

mysqldump  -uxxx -pxxx --all-databases > xxx.sql 
#备份所有的库
mysqldump  -uxxx -pxxx 库名 > XXX.sql  
#备份指定库中的所有表数据，不包含库对象

mysqldump  -uxxx -pxxx 库名 表1 [表2...] > xxx.SQL   
#备份指定库中一个或多个或所有表数据，不包含库对象 



 


增量备份



#开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED                
#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

可每周对数据库或表进行完全备份
mysqldump -u root -p kgc info1 > /opt/kgc_info1_$(date +%F).sql
mysqldump -u root -p --databases kgc > /opt/kgc_$(date +%F).sql

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs

4．插入新数据，以模拟数据的增加或变更
use kgc;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6．查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容



 


 


数据冷迁移




 #备份库取名为kgc.sql，并导出备份文件到29服务器上




 #29服务器以收到




 #完全恢复库






 #登录数据库，切换kgc的数据库。查看库里的表，再看其中一个表的数据。


 


物理冷备份与恢复



systemctl stop mysqld

mkdir /backup

#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

#解压恢复
mv /usr/local/mysql/data/ ~
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz
mv /opt/usr/local/mysql/data/ /usr/local/mysql/
 
systemctl restart mysqld



 


 


MySQLdump 备份与恢复



1）完全备份一个或多个完整的库（包括其中所有的表）
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql    #导出的就是数据库脚本文件
例：
mysqldump -u root -p --databases kgc > /opt/kgc.sql
mysqldump -u root -p --databases mysql kgc > /opt/mysql-kgc.sql

（2）完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例：
mysqldump -u root -p --all-databases > /opt/all.sql

（3）完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
例：
mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_info1.sql
#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

（4）查看备份文件
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"






1）恢复数据库
mysql -u root -p -e 'drop database kgc;'
#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'SHOW DATABASES;'




mysql -u root -p < /opt/kgc.sql
mysql -u root -p -e 'SHOW DATABASES;'




（2）恢复数据表
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
mysqldump -u root -p kgc info1 > /opt/kgc_info1.sql




mysql -u root -p -e 'drop table kgc.info1;'
mysql -u root -p -e 'show tables from kgc;'




mysql -u root -p kgc < /opt/kgc_info1.sql
mysql -u root -p -e 'show tables from kgc;'






先登录到数据库，再执行 source xxx.sql （如果sql文件是只备份了表数据的，需要先创建库并use切换库后再执行）




mysql -uxxx -pxxx < xxx.sql #恢复库
mysql -uxxx -pxxx 库名 < xxx.sql  #恢复表




cat xxx.sql | mysql -uxxx -pxxx #恢复库
cat xxx.sql  |mysql -uxxx  -pxxx 库名  #恢复表





优 #简单
缺 #有很多重复数据，会大量占用磁盘空间，恢复的时间长







 




 #指定部份表备份




 #删除ky35表




 #用完全恢复表命令恢复ky35 登录MySQL数据库




 #ky35表恢复




 #查看备份文件


 


增量备份



开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED                #可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)
ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

2．可每周对数据库或表进行完全备份
mysqldump -u root -p kgc info1 > /opt/kgc_info1_$(date +%F).sql
mysqldump -u root -p --databases kgc > /opt/kgc_$(date +%F).sql

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs

4．插入新数据，以模拟数据的增加或变更
use kgc;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6．查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容



 




 #默认模式为STATEMENT(基于SQL语句)




 


 






 #进行数据库完全备份，以时间为后缀


 




 #对数据库进行表备份


 


 


查看二进制日志文件内容




 




 


 # --base64-output=decode-rows：使用64位编码机制去解码并按行读取     -v：显示详细内容


增量恢复







（1）模拟丢失更改的数据的恢复步骤
use kgc;
delete from info1 where id=3;
delete from info1 where id=4;

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

（2）模拟丢失所有数据的恢复步骤
use kgc;
drop table info1;

mysql -u root -p kgc < /opt/kgc_info1_2020-11-22.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p



 


 


断点恢复



mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例：
# at 302
#201122 16:41:16
插入了“user3”的用户数据

# at 623
#201122 16:41:24
插入了“user4”的用户数据

（1）基于位置恢复
#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

（2）基于时间点恢复
#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p




基于位置点恢复
mysqlbinlog --no-defaults --start-position='开始位置点' --stop-position='结束位置点' <BINLOG> | mysql -uxxx -pxxx
基于时间点恢复
mysqlbinlog --no-defaults --start-datetime='开始时间点' --stop-position='结束时间点' <BINLOG> mysql -uxxx -pxxx





如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start



 


基于位置点恢复




 #先生成一个新的二进制日志文件




 #000006就是新生成的二进制日志文件，后面的操作都在这个新生成的二进制日志文件里






 #去数据库新插入三个表数据




 #先看下生成的二进制日志文件里是否有我创建的表数据






 #有创建的数据




 #将二进制日志文件备份到opt下




 #删除新插入的表数据




 #只恢复7和8




 #位置结束点为746




 #7行8行恢复


 


基于时间点恢复




 #恢复id=9，开始时间与结束时间




 #基于时间点恢复，确定开始时间和结束时间




 #id=9数据恢复


 


增量备份的脚本



#!/bin/bash
#增量备份mysql二进制日志
TODAY=$(date +%Y%m%d)
YSETERDAY=$(date -d day +%Y%m%d)
DATA_HOME=/usr/local/mysql/data
BACKUP_UID=/opt/backup
USERNAME=root
PASSWORD=123

\cp -f $DATA_HOME/mysql_bin.index  $BACKUP_UID/mysql_bin.index-$TODAY
#备份二进制日志索引文件 mysql_bin.index

if [ -f $BACKUP_UID/mysql_bin.index-$YESTERDAY ];
then
BINLOGS=$(cat $BACKUP_UID/mysql_bin.index-$TADAY | grep -v "$(cat mysql_bin.index-$YESTERDAY)" | awk -F/ '{print $2}')
else
BINLOGS=$(cat $BACKUP_UID/mysql_bin.index-$TADAY |awk -F/ '{print $2}')
fi
#判断是都存在前一天的日志索引文件，如果没有则说明是第一次备份，那么就可以直接提取二进制日志索引文件里的binlog文件名
#如果有则拿今天的日志索引文件比对前一天的日志索引文件，进行反向过滤获取今天生成的binog文件名

mysqladmin -u "$USERNAME" -p"$PASSWORD" flush-logs &> /dev/null
#使用flush-logs刷新二进制日志
for i in $BINLOGS
do
mv -f "$DATA_HOME/$I" "$BACKUP_DIR/$i-$TODAY"
done
#使用for循环遍历备份今天生成的binlog日志



 



标签：opt,--,恢复,备份,MYSQL,kgc,mysql,日志	

From： https://www.cnblogs.com/yanrui07/p/17929270.html
      

      




      

      

        
相关文章

        

          
    
 
  • 虚拟机中安装mysql 完整教程（ CentOS7 版本）
    一、检查是否安装了MysqlYum检查yumlistinstalled|grepmysql安装则直接删除yumremovemysql-community-client.x86_64yumremovemysqlmysql-servermysql-libscompat-mysqlyumremovemysql-community-releaserpm检查：rpm-qa|grep-imysql//有则直......
     
  • mysql5.7安装
    #!/bin/bash###############################################################FileName:install_redis.sh#Version:V1.0#Author:junwang#Organization:#CreatedTime:2021-04-1417:12:54#Description:###############################################......
     
  • mysql查询中返回一列序号
    可以设一个变量，然后赋值。select(@i:=@i+1)asaid,signup.*fromsignup,(select@i:=0)asitWHERE1=1andid=12注：signup是表名，aid是返回的自增长的序号 前端：aid*(page-1)*pageSize（根据情况） page：当前页码从1开始pageSize：每页显示的记录......
     
  • 0x13.mysql注入
    基本判断定义：后台服务器在接收相关参数时未做好过滤直接带入到数据库中查询，导致可以拼接执行构造的SQL语句常见组合：asp+access/sqlserveraspx+sqlserverphp+mysqljsp+mysql/sqlserver/oracle静态与动态：判断方式：document.lastModified，输出现在的时间，则为伪静态。......
     
  • iTunes备份文件转换----iTunesBackupTransfer
    iTunesBackupTransfer我的github被标记了，还在和客服对线，现在是访问不了的，包括之前的面板一把梭。目前还不支持加密备份转换，手头没有素材，后面再说吧。。。找软件是一个麻烦事，一搜大把的都是商业软件，要么就去找破解版，要么就硬搞试用，还是写一个吧。着急想用的可以直接找我要，不着......
     
  • kafka 自定义开发Sink Connector组件（兼容mysql和oracle）
    1.情景展示目前，市场上已有不少能从kafka消费数据的插件，如：io.confluent.connect.jdbc.JdbcSinkConnector，但这个组件有个致命的问题是，只能同步字符串类型。具体意思是：源库源表的日期类型字段，往目标库目标表插入数据的时候，只能是字符串类型，无法自动将其转成日期类型。这样一来的......
     
  • MySQL 8.0.12 深入理解bit类型
     背景：在阿里巴巴推荐的MySQL建表规范里要求如下：表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsignedtinyint（1表示是，0表示否）。解释：在MySQL里表示是和否的概念可以使用如下三种方案：1.使用bit(1)类型，此时bit允许存储的是ASCII中的0和1.0表示否1表示是的概念。2......
     
  • .NET8操作Mysql，Pomelo.EntityFrameworkCore.MySql版本目前最高只有7.0解决办法
    Pomelo.EntityFrameworkCore.MySql7.0是不支持.NET8的，但现在Pomelo.EntityFrameworkCore.MySql的最新版本只有7.0因为 Pomelo.EntityFrameworkCore.MySql8.0还在研发中，还没有正式的版本，只有公测版本，可以在控制台中直接输入命令安装即可Install-PackagePomelo.EntityFrameworkCor......
     
  • MySQL安装
    安装tar-zxvfmysql-5.7.43-el7-x86_64.tar.gz-C/usr/local/cd/usr/localmvmysql-5.7.43-el7-x86_64mysqlcd/usr/lcoal/mysqlgroupaddmysqluseradd-r-M-gmysqlmysqlchown-Rmysql:mysql./创建目录mkdir/datamkdir/data/mysql备份并修改配置文件cp......
     
  • [升级] MySQL升级步骤及注意事项
    升级步骤a.备份数据库（包括schema，data，routines(function,triggersetc)b.修复util.checkForZSzerverUpgrade()发现的问题c.添加innodb_fast_shutdown=0来彻底的关闭mysqld.更新mysqlbinaries，可以使用tarball，RMP，mis等注意事项a.从5.7升级到8.0，或者在8.0.x之间升级可以......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99