精华内容
下载资源
问答
  • MySQL 备份与恢复

    2021-04-03 11:30:07
    MySQL 备份与恢复

    MySQL 提供的最简单方便的备份和恢复方式为 mysqldump备份工具。

    1、备份数据库

    使用cmd命令行窗口,切换到mysql安装目录的bin路径下,使用命令如下:

    mysqldump -u username -p databasename >backup_file_name.sql

    -u参数指定用户名,-p会提示输入密码

    -p 后面指定数据库名,如果不指定,则备份所有数据库,

    >后面指定备份文件的名称

    例如输入如下命令,会将springcloud数据库备份,名称为springcloud0411.sql。

    mysqldump -u root -p springcloud >springcloud0411.sql

     

    2、切换到mysql命令行

    接着在cmd命令行窗口输入mysql -u root -p,回车,在输入密码提示后输入密码,进入mysql命令行

     

    3、通过导入备份文件恢复数据库

    use springcloud

    提示 database changed 

    然后使用source命令,

    source springcloud0411.sql

    观察窗口提示信息

     

     

     

     

    展开全文
  • MySQL备份与恢复

    2020-12-24 08:33:35
    MySQL备份与恢复

    MySQL备份与恢复

    一: 数据库备份的分类

    1.1: 数据备份重要性

    • 在生产环境中,数据的安全性至关重要
    • 任何数据的丢失都可能产生严重的后果
      造成数据丢失的原因
    1. 程序错误
    2. 人为操作失误
    3. 运算错误
    4. 磁盘故障
    5. 灾难(如火灾、地震)和盗窃

    1.2 分类

    • 从物理与逻辑的角度,备份可分为
    1. 物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
    • 物理备份方法
      1.1 冷备份(脱机备份) :是在关闭数据库的时候进行的,依赖于数据文件
      1.2 热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件
      1.3 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
    1. 逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的备份
    • 按照数据库的备份策略角度,备份可分为
    1. 完全备份:每次对数据进行完整的备份
    2. 差异备份:备份那些自从上次完全备份之后被修改过的文件
    3. 增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

    1.3 常见的备份

    1. 物理冷备

    备份时数据库处于关闭状态,直接打包数据库文件
    备份速度快,恢复时也是最简单的
    专用备份工具mydump或mysqlhotcopy
    mysqldump常用的逻辑备份工具
    mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

    1. 启用二进制日志进行增量备份

    进行增量备份,需要刷新二进制日志

    1. 第三方工具备份

    免费的MySQL热备份软件Percona XtraBackup

          mysql数据库备份常见命令

    1.4 MySQL物理冷备份与恢复

    1. 关闭数据库,打包备份
    systemctl stop mysqld
    mkdir /backup
    tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
    
    1. 恢复数据库,将备份数据mv成线上库文件夹
    mkdir /bak
    mv /usr/local/mysql/data/ /bak/
    mkdir restore
    tar zxvf /backup/mysql_all-2020-12-24.tar.gz -C restore/
    mv restore/usr/local/mysql/data/ /usr/local/mysql/
    systemctl start mysqld
    

    二:MySQL的完全备份

    2.1:概念

    • 完全备份是对整个数据库的备份、数据库结构和文件结构的备份

    • 完全备份保存的是备份完成时刻的数据库

    • 完全备份是增量备份的基础

    2.2 优缺点

    优点:

    • 安全性高
    • 备份与恢复操作简单方便

    缺点:

    • 数据存在大量的重复
    • 占用大量的备份空间,空间利用率低
    • 备份与恢复时间长

    2.3 mysqldump备份库

    2.3.1 完全备份MySQL数据库分类

    物理冷备份与恢复

    关闭MySQL数据库
    使用tar命令直接打包数据库文件夹
    直接替换现有MySQL目录即可
    

    mysqldump备份与恢复

    MySQL自带的备份工具,可方便实现对MySQL的备份
    可以将指定的库、表导出为SQL脚本
    使用命令mysql导入备份的数据
    
    • mysqldump介绍

    mysqldump命令是MySQLI自带的备份工具,相当方便对MySQL进行备份
    通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本,在需要恢复时可进行数据恢复

    2.3.2 mysqldump命令备份数据库

    向数据库中添加一些数据

    mysql> create database school;
    mysql> use school;
    mysql> create table cd(id int(4) auto_increment primary key, name varchar(16), age char(2));
    mysql> insert into cd(name,age) values('zhangsan','18'),('lisi','19');
    

    在这里插入图片描述

    1. 备份单个库

    语法:

    mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名
    
    mysqldump -u root -p cd > /backup/cd.sql
    

    示例

    mysqldump -uroot -pabc123 school > /opt/school.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    

    在这里插入图片描述

    1. 备份多个库

    语法:

    mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ...  > /备份路径/备份文件名
    

    如:

    mysqldump -u root -p --databases yiku erku > /backup/yiku-erku.sql
    

    示例:

    mysqldump -uroot -pabc123 --databases school name > /opt/school-name.sql     //同时备份school和name库
    
    1. 对所有库备份

    语法:

    mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
    

    如:

    mysqldump -u root -p --opt --all-databases > /backup/all.sql
    

    2.3.3 mysqldump命令备份表

    在生产环境中,存在对某个特定表的维护操作,此时 mysqldump同样发挥重大作用

    • 使用 mysqldump备份表的操作

    语法

    mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
    

    如:

    mysqldump -uroot -pabc123 school cd > /backup/school-cd.sql
    

    在这里插入图片描述

    • 使用 mysqldump备份表的结构
    mysqldump -u 用户名 -p [密码] [选项] -d 数据库名 表名 > /备份路径/备份文件名
    

    如:

    mysqldump -uroot -pabc123 -d school cd > /backup/school-cd_structure.sql
    

    在这里插入图片描述

    三、MySQL数据恢复

    mysqldump命令导出的SQL备份脚本,在进行数据恢复时可使用以下方法导入:

    1. source命令
    2. mysql命令

    3.1 source命令恢复数据库

    1. 登录到 MySQL数据库
    mysql> source /backup/school-cd.sql;     //使用绝对路径
    '//source命令在mysql库中使用'
    
    mysql> drop database school;
    mysql> source /backup/school-cd.sql;
    

    在这里插入图片描述
    注意: 备份文件里没有判断库的语句,所以要先创建库,再导入表,但是备份多个库的时候,备份的sql语句包含了库的判断,所以不用提前创建库’

    mysql> create database school;
    mysql> use school
    mysql> source /backup/school-cd.sql;
    mysql> select * from cd;
    

    在这里插入图片描述

    3.2 mysql命令恢复数据库

    语法:

    mysql -u 用户名 -p [密码] < 库备份脚本的路径
    

    如:

    mysql -u root -pabc123 < /backup/school.sql
    

    3.3 恢复数据表

    • 恢复表时同样可以使用 source或者mysql命令
    • source恢复表的操作与恢复库的操作相同
    • 当备份文件中只包含表的备份,而不包括创建库的语句时,必须指定库名,且目标库必须存在

    语法

    1. mysql命令
    mysql -u 用户名 -p [密码] < 表备份脚本的路径
    

    如:

    mysql -u root -p mysql < /backup/school-cd.sql
    
    1. source命令
    mysql> source /backup/school-cd.sql;
    

    在生产环境中,可以使用shell脚本自动实现定期备份

    四: MySQL增量备份与恢复

    4.1 概述

    只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
    增量备份就是备份自上一次备份之后增加或变化的文件或者内容

    • mysqldump命令完全备份存在的问题

    备份数据中有重复数据
    备份时间与恢复时间长

    4.2 优缺点

    • 优点:

    没有重复数据,效率高,空间利用率最大化

    备份量不大,时间短

    • 缺点:

    恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复

    安全性较低

    4.3 MySQL二进制日志

    • 二进制日志保存了所有更新或者可能更新数据库的操作

    • 二进制日志在启动 MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的大小或者接收到 flush-logs命令后重新创建新的日志文件

    • 只需定时执行 flush-logs方法重新创建新的日志,生成二进制文件序列,并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

    4.4 MySQL 增量备份

    MySQL没有提供直接的增量备份方法

    • 可以通过 MySQL提供的二进制日志( binary logs)间接实现增量备份

    • MySQL的配置文件的[mysqld]项中加入log-bin=filepath项(filepath是二进制文件的路径),如log-bin=mysql-bin,然后重启mysqld服务。

    • 二进制日志文件的默认路径为/usr/local/mysql/data

    [root@localhost ~]# vim /etc/my.cnf
    '//在[mysqld]项中加入配置 log-bin=mysql-bin'
    ...
    server-id = 1
    log-bin=mysql-bin	        '//添加此句'
    
    systemctl restart mysqld	'//重启服务'
    

    二进制日志文件的默认路径为/usr/local/mysql/data

    • 语法
      在这里插入图片描述
    mysqladmin -u 用户名 -p [密码] flush-logs
    

    只需定时执行 flush-logs方法重新创建新的日志,生成二进制文件序列,并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份。
    在这里插入图片描述
    可以看出:多了一个文件mysql-bin.000002。

    4.5 MySQL 增量备份恢复

    4.5.1 查看到日志操作语句

    mysqlbinlog --no-defaults --base64-output=decode-rows -v 日志文件名称 /opt/mysql.log.txt	
      '//使用64位解码器按行输出日志文件放到/opt/mysql.log.txt中'
    cat /opt/mysql.log.txt  '//查看日志文件的详细信息'
    
    mysql> show master status;                       '查看限制正在使用哪个日志''
    

    在这里插入图片描述

    4.5.2 一般恢复

    语法

    mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p
    

    基于二进制日志恢复

    mysql> use school;
    mysql> drop table cd;
    mysql> source /backup/school-cd.sql
    

    或者

    [root@mysql data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -pabc123
    

    4.5.3 断点恢复

    1. 基于位置恢复

    就是将某个起始时间的二进制日志导入数据库中,从而跳过某个发生错误的时间点实现数据的恢复

    • 恢复数据到指定位置
    mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
    
    • 从指定的位置开始恢复数据
    mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
    
    1. 基于时间点恢复

    使用基于时间点的恢复,可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作,所以需要一种更为精确的恢复方式

    • 从日志开头截止到某个时间点的恢复
    mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    
    • 从某个时间点到日志结尾的恢复
    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    
    • 从某个时间点到某个时间点的恢复
    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    

    4.6 增量恢复步骤

    开启二进制日志–>添加数据–>进行完全备份–>录入新的数据–>进行增量备份(刷新二进制日志)–>模拟故障––恢复操作

    1. 开启二进制日志
    [root@mysql ~]# vim /etc/my.cnf
    ...
    server-id = 1
    log-bin=mysql-bin                 '//添加此行开启二进制日志,名称为mysql-bin开头'
    
    [root@mysql ~]# cd /usr/local/mysql/data
    [root@mysql data]# ls
    apple           ibdata1      ibtmp1  performance_schema
    auto.cnf        ib_logfile0  kgc     school
    ib_buffer_pool  ib_logfile1  mysql   sys
    
    [root@mysql data]# systemctl restart mysqld       //开启二进制日志后重启服务
    [root@mysql data]# ls
    apple           ibdata1      ibtmp1  mysql-bin.000001    school
    auto.cnf        ib_logfile0  kgc     mysql-bin.index     sys
    ib_buffer_pool  ib_logfile1  mysql   performance_schema
                                          //生成二进制日志文件mysql-bin.000001
    
    1. 进行完全备份
    mysql> use school;
    
    mysql> select * from cd;
    +----+----------+------+
    | id | name     | age  |
    +----+----------+------+
    |  1 | zhangsan | 18   |
    |  2 | lisi     | 19   |
    +----+----------+------+
    2 rows in set (0.00 sec)
    
    [root@mysql ~]# mysqldump -uroot -pabc123 school cd > /opt/school-cd2.sql
    

    在这里插入图片描述

    1. 进行正常操作和误操作,进行增量备份
    mysql> use school;
    mysql> insert into cd values(3,'wangwu',20);     //正常操作
    
    mysql> delete from cd where name='zhangsan';           //误操作
    
    mysql> insert into cd values(5,'zhangliu',21);     //正常操作
    
    mysql> select * from cd;
    

    在这里插入图片描述

    [root@mysql data]# mysqladmin -uroot -pabc123 flush-logs   //刷新日志增量备份
    [root@mysql data]# ls -lh                                   
    -rw-r-----. 1 mysql mysql  201 12 24 18:51 mysql-bin.000001
    -rw-r-----. 1 mysql mysql 1.0K 12 24 19:42 mysql-bin.000002
    -rw-r-----. 1 mysql mysql  154 12 24 19:42 mysql-bin.000003
    
     //刷新日志后生成000002文件,原来的正常操作和误操作保存在日志000001里面
    
    1. 查看增量备份二进制文件
    [root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bak.txt
    [root@mysql data]# vim /opt/bak.txt
    
    ......
    BEGIN
    /*!*/;
    # at 293
    #201224 19:38:38 server id 1  end_log_pos 346 CRC32 0xf7359359  Table_map: `school`.`cd` mapped to number 222
    # at 346
    #201224 19:38:38 server id 1  end_log_pos 396 CRC32 0xc34a1362  Write_rows: table id 222 flags: STMT_END_F
    ### INSERT INTO `school`.`cd`
    ### SET
    ###   @1=3
    ###   @2='wangwu'
    ###   @3='20'
    # at 396
    #201224 19:38:38 server id 1  end_log_pos 427 CRC32 0xa5b658aa  Xid = 173
    COMMIT/*!*/;
    # at 427
    #201224 19:39:32 server id 1  end_log_pos 492 CRC32 0x812226d5  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
    /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 492
    #201224 19:39:32 server id 1  end_log_pos 566 CRC32 0x61231674  Query   thread_id=17    exec_time=0     error_code=0
    SET TIMESTAMP=1608809972/*!*/;
    BEGIN
    /*!*/;
    # at 566
    #201224 19:39:32 server id 1  end_log_pos 619 CRC32 0x7a6e973e  Table_map: `school`.`cd` mapped to number 222
    # at 619
    #201224 19:39:32 server id 1  end_log_pos 671 CRC32 0x24435a14  Delete_rows: table id 222 flags: STMT_END_F
    ### DELETE FROM `school`.`cd`
    ### WHERE
    ###   @1=1
    ###   @2='zhangsan'
    ###   @3='18'
    # at 671
    #201224 19:39:32 server id 1  end_log_pos 702 CRC32 0xc9db2a9b  Xid = 174
    COMMIT/*!*/;
    # at 702
    #201224 19:40:19 server id 1  end_log_pos 767 CRC32 0x4ae2a49c  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
    /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 767
    #201224 19:40:19 server id 1  end_log_pos 841 CRC32 0xa41b163a  Query   thread_id=17    exec_time=0     error_code=0
    SET TIMESTAMP=1608810019/*!*/;
    BEGIN
    /*!*/;
    # at 841
    #201224 19:40:19 server id 1  end_log_pos 894 CRC32 0x2c22cb13  Table_map: `school`.`cd` mapped to number 222
    # at 894
    #201224 19:40:19 server id 1  end_log_pos 946 CRC32 0xfe387271  Write_rows: table id 222 flags: STMT_END_F
    ### INSERT INTO `school`.`cd`
    ### SET
    ###   @1=5
    ###   @2='zhangliu'
    ###   @3='21'
    # at 946
    #201224 19:40:19 server id 1  end_log_pos 977 CRC32 0x40cd330a  Xid = 175
    COMMIT/*!*/;
    # at 977
    #201224 19:42:30 server id 1  end_log_pos 1024 CRC32 0x7096e131         Rotate to mysql-bin.000003  pos: 4
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    ......
    '注意时间201224 19:38:38 对应的是 2020-12-24 19:38:38'
    '时间200821 19:40:19  对应的格式是 2020-12-24 19:40:19'
    
    1. 使用基于时间点的断点恢复
    mysqlbinlog --no-defaults --stop-datetime='2020-12-24 19:38:38' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
    '//第一个正常操作的结尾时间是'2020-12-24 19:38:38',所以此处用此时间,表示恢复到此时间的操作'
    
    mysqlbinlog --no-defaults --start-datetime='2020-12-24 19:40:19' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -pabc123
    '//跳过中间失误操作失误操作后的正常操作的开始时间是'2020-12-24 19:40:19',所以此处用此时间,表示从此时间恢复到结尾的操作'
    
    mysql> select * from kgc;          //看表已经跳过误删除名为王五的操作继续恢复后面插入张六的操作
    

    在这里插入图片描述

    1. 使用基于位置点的断点恢复

    由于已经基于时间点恢复,所以把数据删除,再用位置点进行恢复

    mysql> drop table cd;                            //删除表
    mysql> source /opt/school-cd.sql;                       
    
    mysqlbinlog --no-defaults --stop-position='612' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
    mysql: [Warning] Using a password on the command line interface can be insecure. 
    //这里612对应误操作664的上一个位置点,这里千万不要写664,写了664这个位置,就说明把这个位置的语句执行一遍;
    恢复的时候没有成功跳过断点,记住断点的上一个位置点
    
    mysql> select * from school.cd;
    
    
    mysqlbinlog --no-defaults --start-position='806' /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -pabc123
    mysql: [Warning] Using a password on the command line interface can be insecure.
    //806位置点为误操作的下一个位置点
    mysql> select * from school.cd;        //查看表已经跳过误删除名为王五的操作继续恢复后面插入张六的操作
    
    
    
    展开全文
  • mysql备份与恢复

    2020-12-28 09:30:50
    mysql备份与恢复数据备份数据备份得重要性造成数据丢失得原因备份的分类从物理与逻辑角度从数据库的备份策略角度常见的备份方法物理冷备:专用备份工具mydump或mysqlhotcopy启用二进制日志进行增量备份第三方工具...

    数据备份

    数据备份得重要性

    在生成环境中,数据安全性至关重要
    任何数据得丢失都可能产生严重后果

    造成数据丢失得原因

    程序错误
    人为操作事务
    运算错误
    磁盘故障
    灾难(火灾、地震)和盗窃

    备份的分类

    从物理与逻辑角度

    物理备份:对数据库操作系统得物理文件(如数据文件、日志文件)的备份
    物理备份方法:
    冷备份(脱机备份):实在关闭数据库的时候进行
    热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
    温备份:数据库锁定表格(不可写入但可读)的状态进行备份操作

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

    从数据库的备份策略角度

    完全备份:每次对数据库进行完整的备份
    差异备份:备份自上次完全备份之后被修改过的文件
    增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

    完全备份在每次备份时都会将所有数据备份一次,差异备份会备份完全备份之后的所有数据,二者都无法避免大量的数据重复,增量备份每次都是在上次备份的基础上对被修改的文件进行备份,节省磁盘空间

    常见的备份方法

    物理冷备:

    备份时数据库处于关闭状态,直接打包数据库文件,备份速度块,恢复时也是最简单的

    专用备份工具mydump或mysqlhotcopy

    mysqldump常用的逻辑备份工具
    mysqlhotcopy仅拥有备份myisam和archive表

    启用二进制日志进行增量备份

    进行增量备份,需要刷新二进制日志(因为每次启动都会产生新的二进制日志文件)
    日志的生成条件:服务重启,刷新日志,超过储存容量

    第三方工具备份

    免费mysql热备份软件percona xtrabackup

    mysql完全备份

    是对整个数据库,数据库结构和文件结构的备份
    保存的是备份完成时刻的数据库
    是差异备份与增量备份的基础

    优点:备份恢复操作简单

    缺点:数据存在大量的数据重复,占用大量的备份空间,备份和恢复时间过长

    分类

    物理冷备份与恢复

    关闭MySQL数据库
    使用tar命令直接打包数据库文件夹
    直接替换现有MySQL目录即可

    systemctl stop mysqld #关闭mysql数据库 
    mkdir /backup #创建一个备份目录
    tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #打包数据库所有数据,并标名日期
    

    查看备份文件
    在这里插入图片描述
    登录数据库,删除表,模拟数据丢失

    systemctl start mysqld  #开启数据库
    mysql -uroot -p #进入数据库
    drop database a; #删除数据库a
    show databases; #查看数据库
    exit; #退出mysql

    在这里插入图片描述
    恢复数据

    systemctl stop mysqld #停止数据库
    tar zxf /backup/mysql_all-$(date +%F).tar.gz #解压备份压缩包
    mkdir mysql_old #创建出问题的数据库备份目录
    mv /usr/local/mysql/data/ mysql_old #将当前的数据库数据移动到此处
    mv usr/local/mysql/data/ /usr/local/mysql/ #将解压的备份数据,移到mysql目录下
    systemctl start mysqld #启动mysql
    mysql -uroot -p # 登录mysql
    show databases; #查看数据库

    在这里插入图片描述
    在这里插入图片描述

    mysqldump备份与恢复

    mysqldump备份

    MySQL自带的备份工具,可方便实现对MySQL的备份
    可以将指定的库,表导出为SQL脚本(后缀为.sql)
    使用命令mysql导入备份的数据
    mysqldump命令进行完全备份
    单库备份

    mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
    mysqldump -uroot -p123123 a > /backup/a-$(date +%F).sql

    多库备份

    mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2]...
    mysqldump -uroot -p123123 --databases a b > /backup/ab-$(date +%F).sql

    所有库备份

    mysqldump -u 用户名 -p [密码] [选项]  --all-databases > /备份路径/备份文件名
    mysqldump -uroot -p123123 --all-databases > /backup/all-$(date +%F).sql

    查看备份文件
    在这里插入图片描述

    恢复数据库使用

    mysqldump导出
    source命令
    mysql命令

    使用source恢复数据库
    登录到mysql数据库
    执行source备份sql脚本的路径

    登录mysql,删除数据库,模拟数据库故障,然后恢复
    在这里插入图片描述
    恢复数据库

    source []< /备份路径/备份文件名
    source /backup/ab-2020-12-25.sql

    在这里插入图片描述

    mysql -u 用户名 -p 【密码】 < 库备份脚本路径
    mysql -uroot -p123123 < /backup/all-2020-12-25.sql

    在这里插入图片描述

    表的恢复

    恢复表时同样可以使用source或者mysql命令
    source恢复表的操作与恢复库的操作相同
    当备份文件中只包含表的备份,而不包括创建库的语句时,必须指定库名,且目标库必须存在

    mysqldump -uroot -p123123 a b > /backup/a-b.sql #创建表的备份文件
    mysql -uroot -p #登录数据库
    drop table a.b; #删除数据库a的表b
    source /backup/a-b.sql #恢复数据库a里的表b

    在这里插入图片描述

    增量备份与恢复

    增量备份

    使用mysqldump进行完全备份存在问题
    备份数据中由重复数据
    备份时间与恢复时间长

    是自上次备份后增加/变化的文件或者内容

    特点
    没有重复数据,备份量不大,时间短
    恢复需要上次完全备份以及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复(对同一个日志文件)

    mysql没有提供直接的增量备份方法

    可通过mysql提供的二进制日志间接实现增量备份

    mysql二进制日志对备份的意义
    二进制日志保存了所有更新或者可能更新数据库的操作
    二进制日志在启动MySQL服务器后开始记录,并在文件达到max_ binlog_ size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
    只需定时执行flush logs方法重新创建新的日志,生成二进制文件序列,并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

    恢复

    一般恢复:将所有备份的二进制日志内容全部恢复
    基于位置恢复:数据库在某一时间点可能既有错误操作也有正确操作可以基于精准的位置跳过错误的操作
    基于时间点:恢复跳过某个发生错误的时间点实现数据恢复(停止在发生错误的时间点)
    在错误的时间点开始,在下一个正确的时间点开始

    基于时间点恢复

    基本步骤

    删除原先坏掉的那张表
    还原完全备份的那个数据库
    停止在错误的时间点
    开始在正确的时间点

    流程

    开启二进制日志文件

    vi /etc/my.cnf
    log_bin=/usr/local/mysql/data/mysql_bin #添加
    #选项                           日志名称
    
    
    systemctl restart mysqld #重启服务

    在这里插入图片描述
    查看二进制日志文件

    mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001 
    --no-defaults:解决utf-8报错
    --base64-output=decode-rows:解决乱码问题
    -v:输出文件指向无
    

    无新增的信息
    在这里插入图片描述

    mysql -uroot -p123123 #进入mysql
    create database a;
    use a;
    create table b(name varchar(128), score int(4)); #创建表b
    insert into b values('p',80);
    insert into b values('h',70);#插入数据
    select * from b;  #查看表b数据

    在这里插入图片描述

    exit #退出mysql
    mysqldump -uroot -p123123 --all-databases  > /backup/a.sql #建立完全备份
    cd  /backup/
    ls -lh #查看

    在这里插入图片描述

    mysqladmin -uroot -p flush-logs  #刷新日志 #生成02日志
    mysql -uroot -p123123 #进入数据库
    delete from b where name='p'; #进入mysql删除表b的name为h的数据,作为错误操作,此步骤保存在02日志中
    insert into b values('ph',90); #进入数据库在表b增加数据,此步骤保存在02日志
    mysqladmin -uroot -p flush-logs  #退出mysql,刷新日志 生成03日志
    select * from b; #查看表b

    在这里插入图片描述
    查看二进制日志文件

    cd /usr/local/mysql/data/
    ls -lh

    在这里插入图片描述

    mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 #查看02操作日志

    在这里插入图片描述
    数据损坏

    进入数据库后删除数据库a
    drop database a; 
    建立新的数据库a
    create database a;
    use a;
    source /backup/a.sql #恢复完全备份的数据库
    退出mysql后进行恢复
    mysqlbinlog --no-defaults --stop-datetime='2020-12-27   23:54:03' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p #停止在错误的时间点
    mysqlbinlog --no-defaults --start-datetime='2020-12-27   23:54:11 ' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p #恢复在正确的时间点
    

    进入数据库后查看表b
    在这里插入图片描述

    基于位置点恢复

    基本步骤

    位置点恢复
    删除原先坏掉的那张表
    任意删除两个数据字段
    利用日志,备份恢复
    查询该二进制日志内容
    上一次正确操作的位置点停止
    下一次正确操作的位置点开始

    流程

    mysql -uroot -p123123use a;
    delete from b where name='p';
    delete from b where name='h';
    select * from a.b;

    在这里插入图片描述
    退出mysql后恢复数据

    mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000004 #查看04操作日志

    在这里插入图片描述

    mysqlbinlog --no-defaults --stop-position='1776' /usr/local/mysql/data/mysql_bin.000004 | mysql -uroot -p #停止于错误位置点
    mysqlbinlog --no-defaults --start-position='2028' /usr/local/mysql/data/mysql_bin.000004 | mysql -uroot -p #开始于下次正确位置点

    进入mysql查看表b
    在这里插入图片描述

    展开全文
  • MYSQL备份与恢复

    2020-08-23 09:57:06
    文章目录MYSQL备份与恢复一、数据库备份的分类1.1数据备份的重要性1.2从物理与逻辑的角度,备份可分为1.3从数据库的备份策略角度,备份可分为1.4常见备份方法1.5MYSQL物理冷备份与恢复二、MYSQL的完全备份2.1完全...

    MYSQL备份与恢复

    一、数据库备份的分类

    1.1数据备份的重要性

    • 在生产环境中,数据的安全性至关重要
    • 任何数据的丢失都可能产生严重的后果
    • 造成数据丢失的原因
      程序错误
      人为操作错误
      运算错误
      磁盘故障
      灾难(如火灾、地震)和盗窃

    1.2从物理与逻辑的角度,备份可分为

    • 物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份物理备份方法 冷备份(脱机备份):是在关闭数据库的时候进行的
      热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
    • 逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

    1.3从数据库的备份策略角度,备份可分为

    • 完全备份:每次对数据库进行完整的备份
    • 差异备份:备份自从上次完全备份之后被修改过的文件
    • 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

    1.4常见备份方法

    • 物理冷备
      备份时数据库处于关闭状态,直接打包数据库文件
      备份速度快,恢复时也是最简单的
    • 专用备份工具mydump或mysqlhotcopy
      mysqldump常用的逻辑备份工具
      mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
    • 启用二进制日志进行增量备份
      进行增量备份,需要刷新二进制日志
    • 第三方工具备份
      免费的MySQL热备份软件Percona XtraBackup

    1.5MYSQL物理冷备份与恢复

    先关闭数据库,再打包备份

    [root@localhost ~]# systemctl stop mysqld
    [root@localhost ~]# mkdir /backup
    [root@localhost ~]# tar zcf /backup/mysql_all $(date + %F).tar.gz /usr/local/mysq/data/
    

    二、MYSQL的完全备份

    2.1完全备份概念

    • 完全备份是对整个数据库的备份、数据库结构和文件结构的备份
    • 完全备份保存的是备份完成时刻的数据库
    • 完全备份是增量备份的基础
    • 完全备份是对整个数据库的备份、数据库结构和文件结构的备份
    • 完全备份保存的是备份完成时刻的数据库
    • 完全备份是增量备份的基础

    2.2完全备份的优缺点

    优点 缺点
    安全性高 数据存在大量的重复
    完全备份概念 占用大量的备份空间,空间利用率低
    备份恢复时间长

    2.3 mysqldump备份数据库

    • mysqldump命令是MySQLI自带的备份工具,相当方便对MySQL进行备份
    • 通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本,在需要恢复时可进行数据恢复

    2.3.1备份数据库

    1备份单个库
    语法:
    mysqldump -u 用户名 -p [密码] [选项] [库名] > /备份路径/备份文件名
    例如:
    [root@localhost ~]# mysqldump -u root -pabc123 school > /opt/school.sql
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    
    2备份多个库
    语法:
    mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] ...  > /备份路径/备份文件名
    例如:
    mysqldump -u root -pABC23 --databases school mysql > /opt/school_mysql.sql
    
    3对所有库备份
    语法:
    mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
    例如:
    mysqldump -u root -p --opt --all-databases > /backup/ku.sql
    

    2.4mysqldump命令备份表

    • 在实际生产环境中,存在对某个特定表的维护操作,此时 mysqldump同样发挥重大作用
    • 使用 mysqldump备份表的操作
    语法
    mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
    例如
    mysqldump -u root -pabc123 school info > /opt/school_info.sql
    

    使用 mysqldump备份表的结构

    mysqldump -u 用户名 -p [密码] [选项] -d 数据库名 表名 > /备份路径/备份文件名
    例如
    mysqldump -u root -pabc123 -d school info > /opt/school-info.sql
    

    三、MYSQL数据恢复

    3.1数据恢复方法

    使用 mysqldump命令导出的SQL备份脚本,在进行数据恢复时可使用以下方法导入

    • source命令 在数据库中使用绝对路径
    • mysql命令 在字符界面使用

    3.2 使用source命令恢复数据库

    登录到数据库

    执行source 备份的sql脚本绝对路径

    示例

    mysql> use school; 
    Database changed
     mysql> source /opt/school.sql
     Query OK, 0 rows affected (0.00 sec)
    

    注:这里创建单个库的时候sql语句里没有判断库的语句,所以要先创建库,再导入表

    ​ 备份多个库的时候,备份的sql语句包含了库的判断,所以不用提前创建库

    3.3使用mysql命令恢复数据库

    语法:
    mysql -u 用户名 -p [密码] < 库备份脚本的路径
    '//此处用了导入<符号,而不是导出>符号'
    例如
    mysql -u root -pabc23 < /opt/school-info.sql
    

    3.4恢复数据表

    • 恢复表时同样可以使用 source或者mysql命令进行
    • source恢复表的操作与恢复库的操作相同
    • 当备份文件中只包含表的备份,而不包括创建库的语句时,必须指定库名,且目标库必须存在
    语法
    mysql -u 用户名 -p [密码] < 表备份脚本的路径
    

    四、MYSQL增量备份与恢复

    4.1MYSQL增量备份概述

    • 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
    • 增量备份就是备份自上一次备份之后增加或变化的文件或者内容

    4.2使用mysqldump命令完全备份的缺点

    • 备份数据中有重复数据
    • 备份时间与恢复时间长

    4.3增量备份的优缺点

    优点 缺点
    备份数据中有重复数据 备份时间与恢复时间长 恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
    备份量不大,时间短 安全性较低

    4.4MYSQL增量备份方法

    • MySQL没有提供直接的增量备份方法
    • 可以通过 MySQL提供的二进制日志( binary logs)间接实现增量备份
    • MySQL二进制日志对备份的意义
      1. 二进制日志保存了所有更新或者可能更新数据库的操作
      2. 二进制日志在启动 MySQL服务器后开始记录,并在文件达到max_binlog_size所设置的大小或者接收到 flush-logs命令后重新创建新的日志文件
    • 只需定时执行 flush-logs方法重新创建新的日志,生成二进制文件序列,并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

    4.5MYSQL增量备份

    MySQL的配置文件的[mysqld]项中加入log-bin=filepath项(filepath是二进制文件的路径),如log-bin=mysql-bin,然后重启mysqld服务。
    
    二进制日志文件的默认路径为/usr/local/mysql/data
    
    [root@localhost ~]# vim /etc/my.cnf
    '//在[mysqld]项中加入配置 log-bin=mysql-bin'
    ...省略内容
    [mysqld]
    user = mysql
    basedir = /usr/local/mysql
    datadir=/usr/local/mysql/data
    port = 3306
    character_set_server=utf8
    pid-file = /usr/local/mysql/mysqld.pid
    socket = /usr/local/mysql/mysql.sock
    server-id = 1
    log-bin=mysql-bin	'//添加此句'
    ...省略内容
    [root@localhost ~]# systemctl restart mysqld	'//重启服务'
    [root@localhost ~]# ls /usr/local/mysql/data/
    ...省略内容
    mysql-bin.000001	'//发现已经生成了二进制文件,设置成功'
    ...省略内容
    

    二进制日志备份

    语法
    mysqladmin -u 用户名 -p [密码] flush-logs
    

    定时执行 flush-logs方法重新创建新的日志,生成二进制文件序列,并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

    4.6MYSQL增量备份恢复

    64位解码

    [root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bf01.txt

    4.6.1一般恢复

    语法
    mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p
    

    4.6.2断点恢复

    基于位置恢复

    • 就是将某个起始时间的二进制日志导入数据库中,从而跳过某个发生错误的时间点实现数据的恢复
    恢复数据到指定位置
    mysqlbinlog --stop-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
    
    从指定的位置开始恢复数据
    mysqlbinlog --start-position='操作id' 二进制日志 |mysql -u 用户名 -p 密码
    

    基于时间点恢复

    • 使用基于时间点的恢复,可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作,所以我们需要一种更为精确的恢复方式
    从日志开头截止到某个时间点的恢复
    mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    
    从某个时间点到某个时间点的恢复
    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    

    要一种更为精确的恢复方式

    从日志开头截止到某个时间点的恢复
    mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    
    从某个时间点到某个时间点的恢复
    mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 |mysql -u 用户名 -p 密码
    
    展开全文
  • Mysql备份与恢复

    2018-05-29 13:14:23
    生产中Mysql备份恢复的形式,基于mysqldump备份及原理,基于Xtrabcackup备份及原理,表空间传输,binlog备份(增量),基于mysql全备+增备的恢复Tips
  • MySQL备份与恢复

    2019-03-05 11:05:22
    MySQL备份与恢复 1.首先查看服务器是否安装crontab crontab 执行crontab命令如果报command not found,就表明没有安装 2安装crontab yum install -y vixie-cron 3.查看是否设置了开机自启动 chkconfig --list crond...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,693
精华内容 2,677
关键字:

mysql备份与恢复

mysql 订阅