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

    2018-05-31 14:30:42
    同学们有没有在工作中遇到mysql 备份恢复,不是感觉到头痛,本人详细总结,亲手实战mysql 常用的备份恢复 导入导出,本人已验证
  • MySQL备份恢复

    2018-01-27 11:38:41
    MySQL备份恢复

    备份单个库的表:

    mysqldump -uroot -pvincent -hmaridb01 mysql user>/tmp/bak1.sql
    # 备份mysql库的user表,
    # 不包含建库语句和use语句,
    # 包含表存在删除语句。
    
    mysqldump -uroot -pvincent -hmaridb01 mysql user host>/tmp/bak1.sql
    # 备份mysql库的user表和host表,
    # 不包含建库语句和use语句,
    # 包含表存在删除语句。

    表结构和数据拆开备份:

    # 只备份表结构或只导出数据的insert语句
    mysqldump -uroot -pvincent -d vincent
    mysqldump -uroot -pvincent -t vincent
    
    # 将表结构和表数据分别保存到不同文件中
    # 表数据每列以tab分割,生成非insert语句
    mkdir /tmp/test
    chown -R mariadb: /tmp/test
    mysqldump -uroot -pvincent vincent -T /tmp/test
    # 方便MySQL到Hive的转换

    备份库:

    mysqldump -uroot -pvincent -hmaridb01 vincent>/tmp/bak1.sql
    # 备份库vincent中的所有表,
    # 不包含建库语句和use语句,
    # 包含表存在删除语句。
    
    mysqldump -uroot -pvincent -hmaridb01 --databases vincent vincent1>/tmp/bak1.sql
    # 备份多个库时使用参数--databases,
    # 包含建库语句和use语句,
    # 包含表存在删除语句。
    
    mysqldump -uroot -pvincent -hmaridb01 --all-databases>/tmp/bak1.sql
    # 备份包括系统库在内的所有库,
    # 参数--all-databases等同于-A,
    # 包含建库语句和use语句,
    # 包含表存在删除语句。

    压缩备份:

    mysqldump -uroot -pvincent -hmaridb01 --all-databases|gzip >/tmp/bak1.sql.gz
    # 将备份的数据使用gzip压缩,备份表和备份库都可以使用gzip压缩存储。
    # 一个压缩的小技巧而已

    备份时获取binlog信息:

    # 使用参数--master-data在备份时定位binlog信息
    mysqldump -uroot -pvincent vincent --master-data=2|grep MASTER_LOG_FILE
    
    # 使用--delete-master-logs备份后删除恢复时不需要的binlog,要慎重
    mysqldump -uroot -pvincent -A --master-data=2 --delete-master-logs

    基于表的备份还原:

    create database test;
    mysql -uroot -pvincent test<bak1.sql
    # 备份是某个库的表,则将备份直接导入到一个库中即可,
    # 如果该库包含同名的表,则表会被备份中的数据替换。

    基于单个库的备份还原:

    create database test;
    mysql -uroot -pvincent test<bak1.sql
    # 备份是单个库的备份,
    # 可以将备份导入到一个存在的库中,但是库中的同名表会被覆盖,
    # 也可以导入到原库中,同样会覆盖同名表,备份操作之后的新建表不受影响,
    # 被导入的库必须存在,否则报错。

    基于多个库的备份还原:

    drop database vincent;
    drop database vincent1;
    mysql -uroot -pvincent<bak1.sql
    # 备份是多个库的备份,
    # 导入时不必指定库名,
    # 导入时库不必存在,
    # 如果库存在,则同名表会被替换。
    
    create database vincent1;
    mysql -uroot -pvincent --one-database vincent1<bak.sql
    # 备份是多个库的备份,
    # 可以使用参数--one-database只导入其中的一个库,
    # 该库需要提前创建,否则报错。

    基于压缩备份还原数据库:

    gunzip <bak1.sql.gz |mysql -uroot -pvincent vincent
    # 将gzip压缩备份包传递给gunzip解压,
    # 管道给mysql恢复数据库vincent。
    # 还是一个关于压缩解压的小技巧

    跨主机还原数据库:

    mysql -uroot -pvincent -h192.168.1.251 test<bak1.sql
    # 将本地备份,通过指定主机IP地址导入到其他主机之上。
    # 当然要保持网络畅通

    基于网络不落地转储库:

    mysqldump -uroot -pvincent vincent|mysql -uroot -pvincent -h192.168.1.251 test
    
    mysqldump -uroot -pecpss@123 -h192.168.1.251 test|mysql -uroot -pvincent test
    
    # 在线数据迁移,
    # 跨网络,该方法技巧性比较强,需要比较稳定的网络环境

    [TOC]

    展开全文
  • mysql备份恢复

    2019-09-20 10:06:58
    备份方式: 数据库备份的分类; 物理备份: 指对数据库操作系统的物理文件(数据文件、日志文件)等的备份; 冷备份: 必须在数据库关闭的状态下进行备份,能够更好的保证数据库的完整性; 热备份: 能够在数据库...

    备份方式:

    数据库备份的分类;

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

    冷备份: 必须在数据库关闭的状态下进行备份,能够更好的保证数据库的完整性;

    热备份: 能够在数据库处于正常运行的情况下备份,能够更高的保证服务的可用性;

    逻辑备份: 指对数据库的逻辑组件(数据库、表、数据对象)进行备份;

    完全备份: 将数据进行完整的备份,包含完整的库、表、索引、视图等,需要花费更长的时间;
    特点:占用空间大,备份速度慢,但恢复时一次恢复到位,恢复速度快。

    差异备份: 备份自上次完全备份到现在发生改变的数据库内容,备份的文件比完整备份的文件小,备份的速度更快;在第一次完整备份之后,第二次开始每次都将所有文件与第一次完整备份的文件做比较,把自第一次完整备份以来所有修改过的文件进行备份,且以后每次备份都是和第一次完整备份进行比较(注意是第一次,不是上一次),备份自第一次完整备份以来所有的修改过的文件。因此,差异备份在备份完毕之后不需要清除文件的存档属性,因为这些文件和下一次备份没有什么关系,它仅仅和第一次完整备份的数据进行比较(第一次完整备份之后是清除存档属性的)。

    特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。

    增量备份: 备份至上次完全备份或增量备份后被修改的数据库内容;在第一次完整备份之后,第二次开始每次都将添加了存档属性的文件进行备份,并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢?这就是为了下一次备份的时候判断是否有文件变化,因为用户在每次备份以后修改这些被清除存档属性的文件,存档属性就会自动加上,相当于用户告诉系统,这些文件有变化,你下一次就备份这些文件,其他没有存档属性的就不需要备份。
    特点:因每次仅备份自上一次备份(注意是上一次,不是第一次)以来有变化的文件,所 以备份体积小,备份速度快,但是恢复的时候,需要按备份时间顺序,逐个备份版本进行恢复,恢复时间长。

    生产Mysql数据库备份策略(转载)

    备份方案:

    1.在每周末的某个时段使用mysqldump进行重要数据库的全量备份(最好备份单个数据库,而不是all);

    2.在平时每天的晚上时段将二进制日志文件终止(前提需要根据数据记录的生成数量合理指定单个二进制文件的大小),这样每隔24小时会生成一个二进制日志文件;

    3.每周的全量备份文件和每天的二进制日志文件相加,就是这一周内数据库的整体内容;

    4.出现故障时,首先恢复全量备份,可以执行mysqlbinlog命令结合实际情况根据position值或者时间点进行恢复,确保万无一失;

    备份脚本:
    vi /opt/mysql_quan_backup.sh ##全量备份
    #!/bin/bash
    #############quan liang beifen ##########
    mkdir /opt/quanliang
    mkdir /opt/quanliang/backup-(date+/usr/local/mysql/bin/mysqldumpurootp123123linuxfan>/opt/quanliang/backup(date +%F)/ /usr/local/mysql/bin/mysqldump -uroot -p123123 linuxfan>/opt/quanliang/backup-(date +%F)/linuxfan.sql
    if [ -f /opt/quanliang/backup-(date+thenecho"mysqlisquanbackupsuccessontime(date +%F)/linuxfan.sql ]; then echo "mysql is quan backup success on time-(date +%F)" >>/usr/local/mysql/logs/mysql_quan_backup.log
    else
    echo “mysql is quan backup fail on time-$(date +%F)” >>/usr/local/mysql/logs/mysql_quan_backup.log
    fi
    然后:chmod +x /opt/mysql_quan_backup.sh
    echo “0 0 * * 7 /opt/mysql_quan_backup.sh” >>/var/spool/cron/root
    systemctl restart crond

    echo -e "log-bin = /usr/local/mysql/logs/mysql_bin \nserver_id = 1\n max_binlog_size = 100M " >>/etc/my.cnf ##增量备份
    vi /opt/mysql_zeng_backup.sh

    #!/bin/bash

    #############zeng liang beifen ##########
    mkdir /usr/local/mysql/logs/log-(date+myadminurootp123123flushlogs>/dev/nullsleep5find/usr/local/mysql/logs/mmin1execcp/usr/local/mysql/logs/log(date + %F) myadmin -uroot -p123123 flush-logs >/dev/null sleep 5 find /usr/local/mysql/logs/ -mmin 1 -exec cp {} /usr/local/mysql/logs/log-(date + %F)/ ;
    if [ -f /usr/local/mysql/logs/log-(date+echo"mysqliszengbackupsuccessontime(date + %F)/mysql_bin.* ];then echo "mysql is zeng backup success on time-(date +%F)" >>/usr/local/mysql/logs/mysql_zeng_backup.log
    else
    echo “mysql is zeng backup fail on time-$(date +%F)” >>/usr/local/mysql/logs/mysql_zeng_backup.log
    fi
    然后: chmod +x /opt/mysql_zeng_backup.sh
    echo “0 23 * * * /opt/mysql_zeng_backup.sh” >>/var/spool/cron/root
    systemctl restart crond

    MySQL备份:

    1)物理备份(冷备份),即备份MySQL程序文件:

    备份:systemctl stop mysqld ##停止MySQL,防止数据变化,保障数据一致性
    tar zcvf MySQL程序备份目录/mysql-backup-$(date +%F).tar.gz /usr/local/mysql/data/(视mysql实际安装目录) ##备份程序目录data数据文件
    恢复:systemctl stop mysql ##停掉数据库服务
    tar zxvf 备份目录/mysql-backup-2019-09-18.tar.gz -C /临时目录 ##先检查解压后的文件,后再覆盖原文件
    cp -rfp /临时目录/usr/local/mysql/data/mysql/ /usr/local/mysql/data/
    chown mysql:mysql -R /usr/local/mysql/data/mysql/ ##如果权限一致的话可省略
    systemctl start mysqld ##登录进去验证

    2)Mysqldump命令(热备份):一条命令搞定,速度快

    备份所有数据库:
    mysqldump -uroot -p --all-databases >/备份目录/all_$(date +%F).sql
    备份数据库中的单个表的结构:
    mysqldump -uroot -p -d 要备份的数据库.表名>/opt/ljiegou.sql
    备份数据库中的单个表的内容:
    mysqldump -uroot -p 要备份的数据库.表名>/opt/neirong.sql
    压缩备份:
    mysqldump -uroot -p --databases 要备份的数据库>/dev/null |gzip >/opt/数据库.sql.gz
    还原:
    还原压缩后的数据库文件:
    gunzip -c /备份目录/数据库备份.sql.gz | mysql -uroot -p 数据库
    mysql -uroot -p -f 数据库 < /备份目录/数据库备份neirong.sql ##-f忽略在还原中的sql错误
    登录数据库后用内部命令导入备份的数据来完成数据恢复:
    mysql> source /备份目录/数据库备份_neirong.sql ##导入数据
    mysql> select * from 表名;
    3)数据库增量备份:
    (1)二进制日志文件增量备份
    首先需要开启日志功能:
    echo -e "log-bin = /usr/local/mysql/logs/mysql_bin \nserver_id = 1\n max_binlog_size = 100M " >>/etc/my.cnf ##添加到[mysql区域内
    验证:
    tail -3 /etc/my.cnf

    log-bin = /usr/local/mysql/logs/mysql_bin ##开启二进制日志文件
    
    server_id = 1
    
    max_binlog_size = 100M  ##指定单个二进制日志文件的最大大小
    

    二进制文件查看确认:
    ls /usr/local/mysql/logs/ ###二进制文件、索引文件
    mysql_bin.000001 mysql_bin.index mysqld.log mysqld.pid
    登录mysql确认其二进制日志情况:
    mysql> show master status;
    常用二进制操作指令:
    mysql> flush logs; ##重新建立二进制文件日志
    mysql> reset master; ##清空所有二进制文件日志
    mysql> show binlog events in ‘数据库上述命令查询到的二进制文件’; ##查看二进制文件的变化
    恢复:
    恢复数据库全量备份:
    mysql>source /备份目录/数据库备份_neirong.sql ##进行全量恢复
    mysql> show tables; ##验证
    数据库增量备份恢复:
    mysqlbinlog --start-position=1 --stop-position=795 /usr/local/mysql/logs/二进制日志文件 | mysql -uroot -p
    mysqlbinlog --start-date=“2019-09-12 20:00:00” --stop-date=“2019-09-15 23:00:00” -d text -h 127.0.0.1 /usr/local/mysql/logs/二进制日志文件 |mysql -uroot -p ###其中,-d指定数据库,选项-h指定主机。
    将二进制日志文件中所有的数据记录全部恢复:
    mysqlbinlog /usr/local/mysql/logs/二进制日志文件 |mysql -uroot -p
    验证:mysql> show master logs;

    展开全文
  • Mysql 备份恢复

    千次阅读 2018-08-15 22:19:09
    Mysql 数据备份 关于备份 原因、目标 数据的一致性 服务的可用性 方式 备份:能防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在其他地方。 冗余:数据有多分冗余,但不等备份...

    Mysql 备份恢复

    关于备份

    原因、目标

    • 数据的一致性
    • 服务的可用性

    方式

    备份:能防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在其他地方。

    冗余:数据有多分冗余,但不等备份,只能防止机械故障带来得数据丢失,例如主备模式、数据库集群。

    对象

    • databases
    • binlog
    • my.cnf

    经验

    • 所有备份数据都应放在非数据库本地,而且建议由多份副本。

    • 在测试环境中做日常恢复演练(备份恢复更为重要)

    技术

    物理备份

    tar,cp,scp

    直接复制数据库文件,适用于大型数据库环境,不受储存引擎 的限制,但不能恢复到不同的mysql版本。

    优点:快

    缺点:服务停止

    逻辑备份

    mysqldump , mydumper

    备份的是建表,建库,插入等操作所执行的sql语句(DDL DML DCL),适用于中小型数据库。

    缺点是效率相对较低

    种类

    完全备份(略)
    增量备份

    连续恢复

    这里写图片描述

    每次仅备份上次备份的文件,备份体积小,速度快,但是恢复的时候需要按备份时间顺序,逐个备份版本进行恢复,恢复时间长。

    差异备份

    跳跃恢复

    这里写图片描述

    占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。

    物理备份

    tar方式

    物理备份数据库,备份期间,服务不可用

    过程

    备份
    1.停止数据库
    systemctl stop mysqld
    2.准备目录放置备份数据
    mkdir  /backup  
    3.tar备份数据
     tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql

    注:备份文件应复制到其他服务器或存储上

    4.启动数据库
    systemctl start mysqld
    还原
    1.停止数据库
    systemctl stop mysqld

    2.清理环境(模拟损坏)

    注:生产环境中不要作死

    rm -rf /var/lib/mysql/*

    3.导入备份数据

    tar -xf /backup/2058-01-01-mysql-all.tar -C /
    4.启动数据库
    systemctl start mysqld
    5.使用binlog恢复未备份的数据

    lvm快照+binlog

    lvm快照实现物理备份 + binlog(物理备份)数据一致,服务可用

    注意

    MySQL数据lv 和 将要创建的snapshot 必须在同一VG,因此VG必须要有一定的剩于空间

    优缺点
    优点
    • 几乎等于热备(创建快照前先把表上锁,创建完成后立即释放)
    • 支持所有存储引擎
    • 备份速度快
    • 无需使用商业软件
    缺点
    • ​ 可能需要跨部门协调(使用操作系统级别的命令,DBA一般没权限)
    • ​ 无法预计服务停止时间
    • ​ 数据如果分布在多个卷上比较麻烦(针对存储级别而言)
    前提

    mysql运行一段时间,数据并没有存储lvm,将现在的数据迁移到lvm

    1.准备lvm及文件系统
    # lvcreate -n lv-mysql -L  2G  datavg
    # mkfs.xfs   /dev/datavg/lv-mysql
    2.将数据迁移到lvm
    # systemctl stop mysqld
    # mount /dev/datavg/lv-mysql /mnt/       //临时挂载点
    # cp -a /var/lib/mysql/*  /mnt           //将MySQL原数据镜像到临时挂载点
    # umount /mnt/
    # vim /etc/fstab                    //加入fstab开机挂载
    /dev/datavg/lv-mysql    /var/lib/mysql     xfs     defaults        0 0
    # mount -a
    # chown -R mysql.mysql /var/lib/mysql
    # systemctl start mysqld
    备份
    1.加全局读锁
    mysql> flush tables with read lock;
    2.创建快照
    # lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql
    # mysql -p'密码'  -e 'show master status' > /backup/`date +%F`_position.txt
    3.释放锁
    mysql> unlock tables;
    4.从快照中拷贝
    # mount -o ro /dev/datavg/lv-mysql-snap /mnt/
    # cd /mnt/
    # tar -cf /backup/`date +%F`-mysql-all.tar ./*
    5.移除快照
    # cd; umount /mnt/
    # lvremove -f   /dev/datavg/lv-mysql-snap
    恢复
    1.停止数据库
    # systemctl stop mysqld
    2.清理环境
    # rm -rf  /var/lib/mysql/* 
    3.导入数据
    # tar -xf /backup/2028-01-01-mysql-all.tar -C /var/lib/mysql/
    4.修改权限
    # chown -R  mysqlmysql  /var/lib/mysql/
    5.启动数据库
    # systemctl start mysqld
    6.binlog恢复
    总结
    1、flush table with read lock;       全局读锁
    2、create snapshot            创建快照
    3show master status; show slave status; [可选]
    查询二进制日志的位置,下次使用bin 作为依据。
    4、unlock tables;   释放锁
    5、Copy files from the snapshot  从快照卷,复制数据。
    6、Unmount the snapshot.    卸载并删除快照。
    7、Remove snapshot
    脚本 + Cron
    脚本
    #!/bin/bash
    #LVM backmysql...
    back_dir=/backup/`date +%F`
    
    [ -d $back_dir ] || mkdir -p $back_dir
    
    echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql; \
    UNLOCK TABLES;" | mysql -p'密码'
    
    mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/
    
    rsync -a /mnt/ $back_dir
    
    if [ $? -eq 0 ];then
             umount /mnt/
             lvremove -f /dev/datavg/lv-mysql-snap 
    fi
    cron
    crontab -e
    0 2 * * *    /root/mysql.sh

    percona-xtrabackup

    简介

    它是开源免费的支持MySQL 数据库热备份的软件,它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份;
    为mysql做增量备份;在mysql服务器之间做在线表迁移;使创建replication更加容易;备份mysql而不增加服务器的负载。

    官网 https://www.percona.com/

    使用略

    逻辑备份

    使用mysqldump实现逻辑完全备份 + binlog

    mysqldump

    优势
    • 自动记录position位置 (show master status\G;)
    • 可用性,一致性(锁表机制)
    语法
    mysqldump -h 服务器  -u 用户名 -p密码  数据库名 >备份文件.sql
    常用数据库选项
    -A, --all-databases 所有库
    database    数据库名
    database stu_info t1    是指database数据库的表stu_info、t1
    -B, --databases bbs test mysql  多个数据库
    -F, --flush-logs   备份之前刷新binlog日志(截断日志。备份之后新binlog。) 
    --master-data=1|2 
    该选项将会记录备份时binlog的日志位置与文件名并追加到文件中,内容如下:
    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', 
    MASTER_LOG_POS=107;
    表示备份的是日志文件16里位置为107之前的数据    
    --quick,-q         
    该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
    --no-data,-d    
    不导出任何数据,只导出数据库表结构。 
    -x, --lock-all-tables                   
    在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭
    --lock-tables          
    它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表
    -R, --routines        备份存储过程和存储函数
    --triggers            备份触发器
    帮助
    mysqldump --help
    举个栗子
    备份
    1.备份库
    # mysqldump -uroot -p'密码' -A >all.sql
    2.备份二进制日志文件
    # cp /var/lib/mysql/*bin*-
    恢复数据
    恢复流程:
    1. 停止数据库
    2. 清理环境
    3. 启动数据库        【初始密码 /var/log/mysqld.log】
    4. 重置密码           【新密码 】
    5. mysql恢复数据   【新密码 】
    6. 刷新授权           【备份时密码 】
     mysql -p'密码' < /backup/2028-01-01-01-mysql-all.sql
    二进制日志恢复
    1.观察二进制截取记录
    vim /backup/2028-01-01-01-mysql-all.sql
    CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;
    注意后续有多少日志,要跟多少日志名字。
    2.恢复
    mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-position=154  | mysql -p'密码'
    备份脚本
    #!/bin/bash
    fullday=`date +%w`
    backfile=`find /var/lib/mysql/ -name mylog.00* | head -1`
    
    if [ $fullday -eq 0 ]
    then
        mysqldump -A > /mysql_backup/full_`date +%Y%m%d%H%M`.sql
    else
        if [ -z $backfile ]
        then
            echo 'file not found,create new binlog!'
            mysqladmin flush-logs
        else
        mysqladmin flush-logs
        tar cvzf  /mysql_backup/`date +%Y%m%d%H%M`.tar.gz $backfile
        rm -rf $backfile
        fi
    fi

    记录的导出和导入

    SELECT… INTO OUTFILE 导出
    select * from db1.t1  into outfile  '/backup/t1.txt'
    mysql命令导出文本文件
    # mysql -uroot -p'password' -e 'select * from db1.t1' > /backup/testdb1.t2.txt
    # mysql -u root -p'password' --xml -e 'select * from db1.t1' > /backup/testdb1.t3.txt
    # mysql -u root -p'password' --html -e 'select * from db1.t1' > /backup/index.html
    LOAD DATA INFILE 导入文本文件
    目录权限
    vim /etc/my.cnf
    secure-file-priv=/backup     mysql不信任该目录
    chown mysql.mysql /backup    mysql用户没有权限
    导入

    标的导入和导出只会备份表记录,不会备份表结构,因此需要mysqldump备份表结构,恢复时先恢复表结构,再导入数据。

    load data infile '/backup/db1.t1.txt'  into table db1.t1; 
    #读取备份文件
    into table db1.t1 
    #输入表中
    select * from  db1.t1
    展开全文

空空如也

空空如也

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

mysql备份恢复

mysql 订阅