精华内容
下载资源
问答
  • MYSQL备份

    千次阅读 2018-11-02 17:01:00
    那么我们的MYSQL备份就相当的重要了! 话说备份有很多种,什么冷备,热备的;什么逻辑备份和物理备份的。还有什么单表备份,全库备份,全实列备份。估计学习并理解起来有点复杂和吃力,很多时候根本用不着,学了...

    数据库的备份是非常重要的事情,危机来的时候可用于恢复。如同旱情的时候的水塔,粮荒时候的粮仓!那么我们的MYSQL的备份就相当的重要了!

    话说备份有很多种,什么冷备,热备的;什么逻辑备份和物理备份的。还有什么单表备份,全库备份,全实列备份。估计学习并理解起来有点复杂和吃力,很多时候根本用不着,学了也白学了。

    MYSQL的备份跟ORACLE备份有点区别!

    ORACLE 备份使用RMAN工具,备份的时候直接采用物理BLOCK备份成文件,并且可以压缩。同时可以把参数文件,控制文件,日志文件打包进去。

    ORACLE 还有个工具叫EXPDP/IMDP 导入导出,主要是该时间点的数据导出来。

    那么MYSQL 有个工具叫MYSQLDUMP,该工具跟EXPDP一样属于导入导出的。属于逻辑导出工具,并且导出成类似于文本格式的SQL语句。包含建表,插入数据等。

     

    物理备份速度快,不影响数据库。逻辑就慢些,也影响数据库一些性能。

    MYSQL 没有物理备份工具,只好使用逻辑备份工具MYSQLDUMP

     

    在MYSQL 有两种主流引擎 MYISAM和INNODB。

    针对不同引擎要带上不同的参数,这一点比较ORACLE显得麻烦些。

    mysqldump -u$MYUSER -p$MYPASS -h$HOST --databases test mysql > backup.SQL

    -u 是连接用户

    -p 是密码

    -h 是IP地址

    --databases 是要导出的数据库 也可以使用  -B 简写

    $ 符合是SHELL的变量的取值符号.

    脚本如下:

    #!/bin/sh

    MYUSER=root

    MYPASS=2019

    HOST=192.168.1.207

    MYDUMP="mysqldump -u$MYUSER -p$MYPASS -h$HOST --databases test mysql > backup.SQL"

     

    假如是MYISAM引擎呢?  要加额外参数 --lock-all-tables  锁定该库的所有表,不能写操作,这个就很那个了,因为MYISAM引擎没有UNDO表空间。

    对于InnoDB将--lock-all-tables替换为--single-transaction

     

    --single-transaction
    该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。

     

    现在大家用的MYSQL基本上都是INNODB引擎了,这下有轻松了些,我们只关注INNODB的备份参数就行了。

     

    MYDUMP="mysqldump -u$MYUSER -p$MYPASS -h$HOST --databases test mysql --single-transaction > backup.SQL"

     

    全备就解决了!好像每天全备有点遗憾,要是上午9点备了,下午18点崩溃了,使用全备只能恢复到上午9点的数据,那么这白天的数据就无法恢复算丢失了。

    这样只能增加全备的频率,如果是大数据库的话又不合适! 

    怎么办呢?  那只有增量备份了

     

    不过MYSQL的增量备份跟ORACLE增量备份不是同一个概念。ORACLE的增量备份是基于全备后的增量备份,是根据物理BLOCK的变化,它有个参数记录BLOCK的变化。MYSQL的增量备份只是备份全备后的日志。就是保存日志就行了。

     

    这下就简单多了,不需要记太多,只要全备后,把日志也保存起来,手工物理文件方式就行了。

     

    那么就要开启MYSQL的BINLOG,BINLOG类似于ORACLE的ARCHIVE_LOG。反正都是数据库的所有变化都记录在BINLOG里面了。

     

    那就剩下个问题,就是我怎么知道要从哪个BINLOG文件开始呢?

    这就要我们在全备的时候增加两个参数来识别

    --flush-logs --master-data=2

     

    --flush-logs为结束当前日志,生成新日志文件;
    --master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称,

     

    这个--FLUSH-LOGS 类似于ORACLE 备份的时候切换日志味道!

     

    MYDUMP="mysqldump -u$MYUSER -p$MYPASS -h$HOST --flush-logs --master-data=2 --databases test mysql --single-transaction > backup.SQL"

     

    那么我就打开该备份文件的头部,就知道要从这个BINLOG文件开始备份。

     

    其他几个常用参数:

    --compress, -C

    在客户端和服务器之间启用压缩传递所有信息

    --default-character-set

    设置默认字符集,默认值为utf8

    --port, -P

    连接数据库端口号

    展开全文
  • mysql备份

    千次阅读 2021-03-24 16:51:30
    权限管理和备份 用户管理 -- 创建用户 create user 用户名 IDENTIFIED by 密码 CREATE USER admin IDENTIFIED BY '123456' -- 修改密码 (修改当前用户的密码) SET PASSWORD=PASSWORD('123456') -- 修改密码 (修改...

    权限管理和备份

    用户管理

    -- 创建用户 create user 用户名 IDENTIFIED by 密码
    CREATE USER admin IDENTIFIED BY '123456'
    
    -- 修改密码 (修改当前用户的密码)
    SET PASSWORD=PASSWORD('123456')
    
    
    -- 修改密码  (修改指定用户密码)
    SET PASSWORD FOR admin1 =PASSWORD('112233')
    
    
    -- 重命名  RENAME USER 旧名字 TO 新名字
    RENAME USER admina TO admin
    
    
    -- 用户授权  ALL PRIVILEGES 全部授权  库 表
    -- ALL PRIVILEGES 除了给别人授权 其他都能
    GRANT ALL PRIVILEGES ON *.* TO admin 
    
    -- 查询权限
    SHOW GRANT FOR admin@localhost  
    SHOW GRANT FOR root@localhost
    
    -- 撤销权限 
    REVOKE ALL PRIVILEGES ON *.* FROM admin
    
    

    mysql 备份

    为什么要备份:

    • 保证重要的数据不丢失
    • 数据转移

    mysql数据库备份的方式

    • 直接拷贝物理文件

    • 在sqlyag这种可视化工具中手动导出

      • 在想要导出的表或者库中,右键,选择备份或导出
    • 使用命令行导出 mysqldump 命令行导出

      • 使用命令行导出 mysqldump 命令行使用
      #mysqldump -h 主机  -u 用户名 -p 密码  数据库 表名 > 物理磁盘位置/文件名
      mysqldump -hlocalhost -uroot -p123456 school student >D:/aaa.sql
      
      
      #mysqldump -h 主机  -u 用户名 -p 密码  数据库 表1 表2 表3 > 物理磁盘位置/文件名
      mysqldump -hlocalhost -uroot -p123456 school student resl >D:/aaa.sql
      
      #mysqldump -h 主机  -u 用户名 -p 密码  数据库 > 物理磁盘位置/文件名
      mysqldump -hlocalhost -uroot -p123456 school >D:/aaa.sql
      
      # 导入
      # 登录情况下,切换到指定的数据库
      # source 备份文件
       首先登陆  mysql -uroot -p123456   #mysql -u用户名 -p密码 库名 <备份文件
      source d:a.sql
      
    展开全文
  • Mysql备份

    千次阅读 2016-05-09 18:55:41
    忙了一个月的毕设,终于有时间整理下博客~~1.冷备冷备份发生在数据库已经正常关闭的情况下,当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。... #mysql -u

    忙了一个月的毕设,终于有时间整理下博客~~

    1.冷备

    冷备份发生在数据库已经正常关闭的情况下,当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言,冷备份是最快和最安全的方法。

    只能提供到“某个时间点”的恢复;不能按表和用户恢复;工作是需要关闭数据库;恢复对版本有很高要求

    备份速度取决于存储引擎类型

    cp

    1.找到数据目录存放位置

    #mysql -uroot -pmypasswd
    mysql> show variables like ‘%datadir%’;

    2.把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入

    #mysql -uroot -pmypasswd
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> FLUSH LOGS;

    3.关闭mysql服务器

    #service mysqld stop

    4.备份

    #tar -zcvf mysql.tar.gz /var/lib/mysql
    #cp mysql.tar.gz mysql_bck

    注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复。

    2.热备

    热备份是在数据库运行的情况下,备份数据库操作的sql语句。

    mysqldump

    mysqldump 是采用SQL级别的备份机制(逻辑备份),它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法

    Mysqldump命令的工作原理很简单,它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换为一条INSTERT语句。这些CREATE语句和INSTERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据。它可以实现整个服务器备份,也可以实现单个或部分数据库、单个或部分表、表中的某些行、存储过程、存储函数、触发器的备份;并且能自动记录备份时刻的二进制日志文件及相应的位置。对于InnoDB存储引擎来讲支持基于单事务模式实现热备,对于MyISAM则最多支持温备。

    MySQL数据库压缩备份

    #mysqldump -hhostname -uusername -pmypasswd db_name | gzip > backupfile.sql.gz

    仅备份数据库结构

    #mysqldump –no-data –databases db_name1 db_name2 > backupfile.sql

    备份服务器上所有数据库

    #mysqldump –all-databases > allbackupfile.sql

    还原需要先手动创建一个数据库 db_name
    还原MySQL数据库的命令

    #mysql -hhostname -uusername -pmypasswd db_name < backupfile.sql

    还原压缩的MySQL数据库

    #gunzip < backupfile.sql.gz | mysql -uusername -pmypasswd db_name

    二进制(binlog)

    二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。

    启用binlog。

    #vim /etc/my.cnf

    server-id = 1  #标识数据库
    log-bin = binlog  #二进制文件存放路径
    log-bin-index = binlog.index #二进制索引文件的路径

    #service mysqld restart

    1.滚动日志复制文件
    终止对当前 binlog 的写入

    #mysql -uroot -pmypasswd
    mysql> FLUSH LOGS;

    复制导出二进制文件

    cp mysql-bin.000001 /mysql_bck/mysql-bin.000001

    2.mysqlbinlog导出二进制日志文件内容

    # mysqlbinlog mysql-bin.000001 > binlog_date +%F.sql

    3.恢复

    #mysqlbinlog /mysql_bck/binlog.000001 | mysql -uroot -pmypasswd db_name

    ps:如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。

    mysqldump全备 + binlog增备

    #mysqldump -uroot -pmypasswd –lock-all-tables –master-data=2 –events –single-transaction –routines–all-databases –flush-logs > /mysql_bck/database_date +%F.sql
    tips:–lock-all-tables表示为所有表施加读锁;–master-data=2表示在备份文件中记录当前二进制日志的位置;–single-transaction保证innodb的读一致性–events表示备份数据的同时备份时间调度器代码;–routines表示备份数据的同时备份存储过程和存储函数;–all-databases表示备份所有库;–flush-logs刷新log。
    # mysqlbinlog –start-position= –stop-position= mysql-bin.000001 > /mysql_bck/binlog_date +%F_%H.sql

    mysqlhotcopy

    mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运行在 类Unix 和 NetWare 系统上。

    安装

    #yum -y install perl perl-DBI
    #wget http://file.111cn.net/upload/2013/12/DBD-mysql-3.0002.tar.gz
    #tar zxvf DBD-mysql-3.0002.tar.gz
    #cd DBD-mysql-3.0002
    #perl Makefile.PL –mysql_config=/usr/local/mysql/bin/mysql_config
    #make
     #make install

    备份

    #mysqlhotcopy -uroot -pmypasswd db_name /mysql_bck

    参考文献:

    http://www.jb51.net/article/74613.htm
    http://www.jb51.net/article/22727.htm

    展开全文
  • MySQL备份恢复

    千次阅读 2018-09-11 15:45:55
    MySQL备份恢复 本文档主要围绕生产中经常使用的mysqldump、mydumper、xtrabackup等工具来进行备份恢复的学习。 1. MySQL 的备份方式 MySQL的备份方法,划分为如下三种: 冷备份 冷备是指在数据库关闭的情况...

    MySQL备份恢复

    本文档主要围绕生产中经常使用的mysqldump、mydumper、xtrabackup等工具来进行备份恢复的学习。

    1. MySQL 的备份方式

    MySQL的备份方法,划分为如下三种:

    • 冷备份
      冷备是指在数据库关闭的情况下进行备份,这种备份非常简单,只需关闭数据库,复制相关的物理文件即可。

    • 温备份
      温备份也是在数据库运行的过程中进行备份,但是备份会对数据库操作有所影响。

    • 热备份
      热备份是指在数据库运行的过程中进行备份,对生产环境中的数据库运行没有任何影响。常见的热备份方案是利用mysqldump、xtrabackup等工具进行备份。

    根据备份文件的类型,备份又可以划分如下两种:

    • 物理备份
      物理备份是指复制数据库的物理文件,既可以是在数据库运行中复制(如xtrabackup这类工具),也可以是在数据库停止运行时直接的数据文件复制。

    • 逻辑备份
      逻辑备份是指备份文件的内容是可读的,该文本一般是由一条条SQL语句或者表的实际数据组成。常见的逻辑备份方式有mysqldump、select * into outfile等方法。

    按照备份数据库的内容来分,备份又可以分为:

    • 完全备份
      完全备份是指对数据库进行一个完整的备份。

    • 增量备份
      增量备份是指在上次完全备份的基础是,对于更改的数据进行备份。

    • 日志备份
      日志备份主要针对MySQL数据库binlog的备份,通过对一个完全备份进行binlog的重做(replay)来完成数据库的point-in-time的恢复工作。MySQL数据库复制(replication)的原理就是异步实时地将二进制日志重做传送并应用到(slave)数据库。

    2. 冷备

    冷备就是在数据库处于关闭状态下的备份,好处可以保证数据库的晚自习,备份过程简单并且恢复速度相对快一些。

    冷备的备份与恢复过程:

    1) 停止MySQL服务

    mysqladmin -S /tmp/mysql3306.sock shutdown
    

    2) 备份数据目录

    cd /data/mysql/
    tar -cvjpf mysql3306.tar.bz2 mysql3306   #-c为创建一个打包文件,相应的-f后面接创建的文件的名称,使用了.tar.bz2后缀,-j标志使用bzip2压缩,最后面为具体的操作对象mysql3306目录
    
    # 查看
    tar -tvjf mysql3306.tar.bz2  #-t为查看操作,则-f对应所查看的文件的名称,文件后缀显示使用bzip2进行压缩,所以加入-j选项,-v会显示详细的权限信息
    
    

    3) 恢复数据

    cd /data/mysql/
    rm -rf mysql3306
    tar -xvjf mysql3306.tar.bz2    #-x为解压操作,则-f指定的是解压使用的文件,文件后缀显示使用bzip2进行压缩,所以加入-j选项,即使用bzip2解压
    

    3. 热备

    热备是在数据库处于运行状态下的备份,不影响现有业务的正常进行。

    热备又分为逻辑备份和物理备份。

    3.1 逻辑备份之 - mysqldump

    mysqldump客户端实用程序执行逻辑备份,生成一组SQL语句,可以执行这些语句来重现原始数据库对象定义和表数据。 它转储一个或多个MySQL数据库以备份或传输到另一个SQL服务器。 mysqldump命令还可以生成CSV,其他分隔文本或XML格式的输出。

    3.1.1 mysqldump语法如下

    Usage: mysqldump [OPTIONS] database [tables]
    OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
    
    

    3.1.2 mysqldump常见的参数

    mysqldump的参数有很多,可以通过使用mysqldump --help命令来查看所有参数,有些参数有缩写形式,如--all-databases的缩写形式为-A。这里列举一些常见的参数。

    • -u, --user=name
      指定连接的用户名

    • -p, --password[=name]
      指定用户的密码,这里可以指定参数名,若不指定参数值,mysqldump随后将提示输入密码,以保护账户口令的安全

    • -S, --socket=name
      指定socket文件连接

    • -h, --host=name
      指定连接的服务器名

    • -P, --port=#
      指定连接的服务器端口号

    • –tables
      导出指定的表对象,格式为 ‘dbname tablename’,如mysqldump -S /tmp/mysql3306.sock test t1,默认会覆盖-B, --databases参数

    • –single-transaction
      在备份开始前,先执行start transaction命令,以此来获得备份的一致性,当前该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时,确保没有其他任何的DDL语句执行(ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE),因为一致性读并不能隔离DDL操作。启用此参数时,参数--lock-tables将自动禁用。

    • -A, --all-databases
      备份所有数据库

    • -B, --databases
      备份指定的数据库,如mysqldump -S /tmp/mysql3306.sock --database db1 db2

    • –default-character-set=name
      设置字符集,默认为以服务器设置的字符集进行导出。

    • -l, --lock-tables
      以只读方式依次锁住每个库下的所有表,默认启用。使用--skip-lock-tables禁用。不能保证所有库下的表备份一致。

    • -x, --lock-all-tables
      在备份过程中,对所有库的所有表,同时锁定。若指定了本参数,则会自动禁用--single-transaction-l, --lock-tables参数。

    • –add-drop-database
      在任何创建库语句前,附加drop database语句。

    • –add-drop-table
      在任何建表语句钱,附加drop table语句。默认启用。如果不希望生成drop table语句,可以通过--skip-add-drop-table参数禁用。

    • –add-drop-trigger
      创建任何触发器前,附加drop trigger语句。

    • –add-locks
      在生成的insert语句钱附加lock语句,默认启用。使用 --skip-add-locks来禁用。

    • –allow-keywords
      允许创建使用关键字的列名。

    • –master-data[=#]
      该参数有1和2两个值,如果值等于1,就会在备份文件中添加一个change master语句。如果值为2,就会在备份文件中添加一个带有注释符号的change master语句。

    • –dump-slave[=#]
      该参数用于在从库备份数据,在线搭建新的从库时使用。此参数也有1和2两个值。值为1时,在备份文件中添加一个change master语句。值为2时,就会在备份文件中添加一个带有注释符号的change master语句。

    • -t, --no-create-info
      备份过程中,只备份表数据,不备份表结构

    • -d, --no-data
      备份过程中,只备份表结构,不备份表数据

    • -c, --complete-insert
      使用完整的insert语句会包含表中的列信息,这么做可以提高插入效率。

    • -q, --quick
      表示导出时不会先将数据加载至buffer中,而是直接输出。默认启用,使用 --skip-quick禁用。

    • -w, --where=name
      导出给定条件的数据。

    3.1.3 mysqldump备份流程

    开启general_log以观察备份流程。

    mysql> set global general_log = 1;
    
    shell> mysqldump -S /tmp/mysql3306.sock --master-data=2 --single-transaction test > /tmp/testdb.sql
    
    mysql> set global general_log = 0;
    
    mysql> show variables like '%gen%';
    +------------------+-----------------------------------------+
    | Variable_name    | Value                                   |
    +------------------+-----------------------------------------+
    | general_log      | OFF                                     |
    | general_log_file | /data/mysql/mysql3306/data/mysqldb1.log |
    +------------------+-----------------------------------------+
    
    
    1. FLUSH /*!40101 LOCAL */ TABLES
    2. FLUSH TABLES WITH READ LOCK
    3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
    4. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
    5. SHOW VARIABLES LIKE 'gtid\_mode'
    6. SHOW MASTER STATUS
    7. UNLOCK TABLES
    8. show databases;
    9. show create database if not exists 'dbname';
    10. SAVEPOINT sp
    11. show tables
    12. show table status like 'account'
    13. SET SQL_QUOTE_SHOW_CREATE=1
    14. SET SESSION character_set_results = 'binary'
    15. show create table `account`
    16. SET SESSION character_set_results = 'utf8'
    17. show fields from `account`
    18. SELECT /*!40001 SQL_NO_CACHE */ * FROM `account`
    19. SET SESSION character_set_results = 'binary'
    20. use `test`
    21. select @@collation_database
    22. SHOW TRIGGERS LIKE 'account'
    23. SHOW CREATE TRIGGER `ins_sum`
    24. SET SESSION character_set_results = 'utf8'
    25. ROLLBACK TO SAVEPOINT sp
    26. ...
    27. ROLLBACK TO SAVEPOINT sp
    28. RELEASE SAVEPOINT sp
    
    
    

    3.1.4 mysqldump常见用法

    • 1) 全库的备份恢复

    备份全库

    mysqldump -S /tmp/mysql3306.sock --master-data=2 --single-transaction -A >alldb-`date +%Y%M%d`.sql
    

    恢复全库

    mysql -S /tmp/mysql3306.sock < alldb-20180910.sql
    
    • 2) 指定库的备份恢复

    备份指定库

    mysqldump -S /tmp/mysql3306.sock --single-transaction -B test >db-`date +%Y%m%d`.sql
    
    

    恢复指定库

    mysql -S /tmp/mysql3306.sock < db-20180910.sql
    
    • 3)指定表的备份与恢复

    备份t1与b表

    mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b >db-`date +%Y%m%d`.sql
    
    

    恢复t1与b表

    mysql -S /tmp/mysql3306.sock  test < db-20180910.sql
    
    • 4)只备份表的表结构信息

    备份test库中t1与b表的表结构信息

    mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b -d >db-`date +%Y%m%d`.sql
    
    ...
    DROP TABLE IF EXISTS `b`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `b` (
      `b1` int(11) DEFAULT NULL,
      `name` varchar(10) NOT NULL DEFAULT 'w',
      KEY `idx_b1` (`b1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    /*!40101 SET character_set_client = @saved_cs_client */;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2018-09-10 16:27:50
    
    
    • 5)只备份表的数据信息

    备份test库中t1与b表的表结构信息

    mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b -t >db-`date +%Y%m%d`.sql
    
    ...
     LOCK TABLES `t1` WRITE;
    /*!40000 ALTER TABLE `t1` DISABLE KEYS */;
    INSERT INTO `t1` VALUES (1,'aaa'),(3,'aaa'),(4,'bbb'),(2,'ccc'),(5,'ccc'),(6,'zzz');
    /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
    UNLOCK TABLES;
    
    --
    -- Dumping data for table `b`
    --
    
    LOCK TABLES `b` WRITE;
    /*!40000 ALTER TABLE `b` DISABLE KEYS */;
    INSERT INTO `b` VALUES (1,'w'),(2,'w'),(3,'w'),(4,'w'),(5,'w');
    /*!40000 ALTER TABLE `b` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    
    • 6)备份指定条件的数据

    备份test库的t1表,并且大于5的数据。

    mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 --where='id>5'
    
    -- MySQL dump 10.13  Distrib 5.7.23, for linux-glibc2.12 (x86_64)
    --
    -- Host: localhost    Database: test
    -- ------------------------------------------------------
    -- Server version	5.7.23-log
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `t1`
    --
    
    DROP TABLE IF EXISTS `t1`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `t1` (
      `id` int(11) NOT NULL,
      `name` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `t1`
    --
    -- WHERE:  id>5
    
    LOCK TABLES `t1` WRITE;
    /*!40000 ALTER TABLE `t1` DISABLE KEYS */;
    INSERT INTO `t1` VALUES (6,'zzz');
    /*!40000 ALTER TABLE `t1` ENABLE KEYS */;
    UNLOCK TABLES;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2018-09-10 16:03:29
    
    

    3.2 逻辑备份之 - select ... into outfile

    SELECT … INTO 语句也是一种逻辑备份的方法,更准确地说是导出一张表中的数据。

    3.2.1 语法

    SELECT  [column 1],[column 2]... 
        INTO
        OUTFILE 'file_name'
        [{FIELDS | COLUMNS}
            [TERMINATED BY 'string']
            [[OPTIONALLY] ENCLOSED BY 'char']
            [ESCAPED BY 'char']
        ]
        [LINES
            [STARTING BY 'string']
            [TERMINATED BY 'string']
        ]
     FROM TABLE WHERE ...
    

    FIELDS TERMINATED BY 'string':表示每个列的分隔符。

    [OPTIONALLY] ENCLOSED BY 'char:表示对于字符串的包含符

    ESCAPED BY 'char': 表示转义符

    STARTING BY 'string': 表示每行的开始符号

    TERMINATED BY 'string': 表示每行的结束符号

    如果没有指定任何的FILEDS和LINES的选项,默认使用以下的设置:

    FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

    LINES STARTING BY '' TERMINATED BY '\n'

    3.2.2 参数

    要想支持select … into 语法,首先得设置参数secure-file-priv,此参数的值有以下:

    • empty string
      如果为空,则变量无效。这不是一个安全的设置。

    • null
      如果设置为NULL,则服务器禁用导入和导出操作。

    • dirname
      如果设置为目录名称,则服务器会将导入和导出操作限制为仅适用于该目录中的文件。目录必须存在;服务器不会创建它。

    3.3.3 导出导入示例

    select ... into outfile导出数据与LOAD DATA导入数据示例

    root@localhost [test] 09:41:38> select * from t1 into outfile '/tmp/t1.txt';
    ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
    
    将参数设置为:
    secure_file_priv = /data/mysql/mysql3306/outfile
    
    导出数据
    mysql> select * from t1 into outfile '/data/mysql/mysql3306/outfile/t1.txt';
    
    # cat t1.txt 
    1	aaa
    3	aaa
    4	bbb
    2	ccc
    5	ccc
    6	zzz
    
    恢复数据
    
    mysql> delete from t1;
    
    mysql> LOAD DATA INFILE '/data/mysql/mysql3306/outfile/t1.txt' into table t1;
    
    mysql> select * from t1;
    +----+------+
    | id | name |
    +----+------+
    |  1 | aaa  |
    |  3 | aaa  |
    |  4 | bbb  |
    |  2 | ccc  |
    |  5 | ccc  |
    |  6 | zzz  |
    +----+------+
    6 rows in set (0.00 sec)
    
    
    

    3.3.4 SELECT OUTFILE + awk

    导出数据
    mysql> select * from t1 into outfile '/data/mysql/mysql3306/outfile/t1.txt';
    
    mysql> delete from t1;
    
    利用awk生成导入sql
    shell> cat t1.txt |awk '{print "insert into test.t1 values("$1",'\''"$2"'\'');"}' > into_t1.sql
    
    # cat into_t1.sql
    insert into test.t1 values(1,'aaa');
    insert into test.t1 values(3,'aaa');
    insert into test.t1 values(4,'bbb');
    insert into test.t1 values(2,'ccc');
    insert into test.t1 values(5,'ccc');
    insert into test.t1 values(6,'zzz');
    
    导入数据
    shell> mysql -S /tmp/mysql3306.sock < into_t1.sql
    
    mysql> select * from test.t1;
    +----+------+
    | id | name |
    +----+------+
    |  1 | aaa  |
    |  3 | aaa  |
    |  4 | bbb  |
    |  2 | ccc  |
    |  5 | ccc  |
    |  6 | zzz  |
    +----+------+
    6 rows in set (0.00 sec)
    
    
    

    3.4 逻辑备份之 - mysqlpump

    mysqlpump和mysqldump一样,属于逻辑备份,备份以SQL形式的文本保存。逻辑备份相对物理备份的好处是不关心undo log的大小,直接备份数据即可。它最主要的特点是:

    • 并行备份数据库和数据库中的对象的,加快备份过程。

    • 更好的控制数据库和数据库对象(表,存储过程,用户帐户)的备份。

    • 备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到MySQL的系统数据库。

    • 备份出来直接生成压缩后的备份文件。

    • 备份进度指示(估计值)。

    • 重新加载(还原)备份文件,先建表后插入数据最后建立索引,减少了

    • 索引维护开销,加快了还原速度。

    • 备份可以排除或则指定数据库。

    #mysqlpumpy压缩备份 以lz4压缩格式备份employees数据库
    
    mysqlpump -S /tmp/mysql3306.sock --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B employees > /tmp/employees_db.sql.lz4
    
    #mysqldump备份压缩
    mysqldump -S /tmp/mysql3306.sock --single-transaction -B employees | gzip > /tmp/employees.sql.gz
    

    3.5 逻辑备份之 - mydumper

    MySQL在备份方面包含了自身的mysqldump工具,但其只支持单线程工作,这就使得它无法迅速的备份数据。而mydumper作为一个实用工具,能够良好支持多线程工作,这使得它在处理速度方面十倍于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定,因此如果我们需要在工作时段执行备份工作,那么会引起DML阻塞。但一般现在的MySQL都有主从,备份也大部分在从上进行,所以锁的问题可以不用考虑。这样,mydumper能更好的完成备份任务。

    3.5.1 安装mydumper

    yum install https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-1.el7.x86_64.rpm
    

    3.5.2 mydumper和myloader重点参数介绍

    mydumper参数介绍

    参数名 说明
    -B, --database 需要备份的数据库
    -T, --tables-list 需要备份的表,多表间用逗号空格
    -O, --omit-from-file 包含要跳过的database.table条目列表的文件,每行一个(在应用正则表达式选项之前跳过)
    -o, --outputdir 输出文件的目录
    -s, --statement-size 生成的insert语句的字节数,默认为 1000000
    -r, --rows 将表按行分块时,指定的块行数,指定这个选项时,会关闭--chunk-filesize
    -F, --chunk-filesize 将表按大小分块时,指定的块大小,单位是MB
    -c, --compress 压缩输出文件
    -e, --build-empty-files 即使表没有数据,还是会产生一个空文件
    -x, --regex 正则表达式匹配’db.table’
    -i, --ignore-engines 忽略的存储引擎,用逗号分隔
    -N, --insert-ignore 使用INSERT IGNORE 备份数据
    -m, --no-schemas 不导出表结构
    -d, --no-data 不导出表数据
    -G, --triggers 导出触发器
    -E, --events Dump events
    -R, --routines Dump stored procedures and functions
    -W, --no-views 不导出视图
    -k, --no-locks 不要执行临时共享读锁定. 警告:这将导致备份不一致
    --no-backup-locks 不要使用Percona备份锁
    --less-locking 最大限度地减少InnoDB表的锁定时间。
    -l, --long-query-guard 设置长查询时间, 默认60s
    -K, --kill-long-queries kill 长时间执行的查询
    -D, --daemon 启用守护进程模式
    -I, --snapshot-interval 每个转储快照之间的间隔(以分钟为单位),需要–daemon模式下,默认为60s
    -L, --logfile 要使用的日志文件名,默认情况下使用stdout
    --tz-utc 备份的时候允许备份Timestamp,这样会导致不同时区的备份还原会出问题,默认关闭,参数:–skip-tz-utc to disable.
    --skip-tz-utc
    --use-savepoints 使用保存点来减少元数据锁定问题,需要SUPER权限
    --success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn’t exist
    --lock-all-tables 使用LOCK TABLE代替FTWRL(FLUSH TABLE WITH READ LOCK)
    -U, --updated-since 使用Update_time仅转储在过去天中更新的表
    --trx-consistency-only 只包含一致性事务
    --complete-insert 使用包含列名的完整INSERT语句
    -h, --host The host to connect to
    -u, --user Username with the necessary privileges
    -p, --password User password
    -a, --ask-password Prompt For User password
    -P, --port TCP/IP port to connect to
    -S, --socket UNIX domain socket file to use for connection
    -t, --threads 使用的线程数,默认是4个
    -C, --compress-protocol Use compression on the MySQL connection
    -V, --version Show the program version and exit
    -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
    --defaults-file Use a specific defaults file
    --ssl Connect using SSL
    --key The path name to the key file
    --cert The path name to the certificate file
    --ca The path name to the certificate authority file
    --capath The path name to a directory that contains trusted SSL CA certificates in PEM format
    --cipher A list of permissible ciphers to use for SSL encryption

    myloader参数介绍

    参数名 注释
    -d, --directory 要导入的备份文件所在的目录
    -q, --queries-per-transaction 每次事务执行的查询数量, default 1000
    -o, --overwrite-tables 如果要恢复的表存在,则先drop表
    -B, --database 指定需要还原数据到哪个数据库中
    -s, --source-db 需要还原的数据库
    -e, --enable-binlog 启用二进制日志恢复数据
    -h, --host The host to connect to
    -u, --user Username with the necessary privileges
    -p, --password User password
    -a, --ask-password Prompt For User password
    -P, --port TCP/IP port to connect to
    -S, --socket UNIX domain socket file to use for connection
    -t, --threads 使用的线程数量, default 4
    -C, --compress-protocol 连接上使用的压缩协议
    -V, --version Show the program version and exit
    -v, --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
    --defaults-file Use a specific defaults file

    3.5.3 mydumper与myloader的用法

    1)以压缩备份备份所有数据库,还原指定的sbtest库

    shell> mydumper -S /tmp/mysql3306.sock  -c /data/mysql/mysql3306/outfile/
    
    shell> ls
    export-20180911-152023
    
    从备份文件中还原指定库sbtest
    shell> myloader -S /tmp/mysql3306.sock -s sbtest -B sbtest -d /data/mysql/mysql3306/outfile/export-20180911-152023/
    
    【注意】:如果不加-s,会把所有库的表,还原到sbtest库中。
    

    2)还原sbtest库中的sbtest1,sbtest2表

    mysql> drop table sbtest1;
    mysql> drop table sbtest2;
    
    shell> myloader -S /tmp/mysql3306.sock -s sbtest -B sbtest -o sbtest1,sbtest2 -d /data/mysql/mysql3306/outfile/export-20180911-152023/
    
    

    3.6 物理备份之 - XtraBackup

    XtraBackup是Percona公司的开源的MySQL热备工具,在备份的过程中不会被锁在数据库中。该工具能够备份InnoDB、MyISAM表,甚至可以备份Percona XtraDB Cluster。支持流方式、压缩、加密和增量备份等。

    XtraBackup的优点如下:

    • 无须停止数据库进行InnoDB热备

    • 增量备份MySQL

    • 流压缩传输到其他服务器

    • 在线移动表

    • 能够比较容易地创建主从同步

    • 备份MySQL时不会增大服务器负载

    3.6.1 XtraBackup备份原理

    XtraBackup是基于InnoDB自身的崩溃恢复机制完成备份的。它首先复制所有InnoDB表数据文本副本,这样导致了内部数据的不一致。但是,它后来执行崩溃恢复功能,使数据达到最终的一致,成为可用的数据库文件。

    Percona XtraBackup 备份原理说明【转】

    3.6.2 安装XtraBackup

    shell> tar xfvz percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz
    
    shell> cd /usr/local
    
    shell> ln -s /opt/percona-xtrabackup-2.4.12-Linux-x86_64 xtrabackup
    
    shell> echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile
    
    shell> source /etc/profile
    

    3.6.3 XtraBackup需要的权限

    • RELOAD,LOCK TABLES
      在备份时,需要执行 FLUSH TABLES WITH READ LOCKFLUSH ENGINE LOGS,然后开始复制数据。并且在使用 Backup Locks时,需要执行LOCK TABLES FOR BACKUPLOCK BINLOG FOR BACKUP。除非备份时,执行–no-lock选项,这样便不需要这两个权限了。

    • REPLICATION CLIENT
      在备份时需要获得二进制日志的位置信息,需要该权限

    • CREATE TABLESPACE
      使用 XtraBackup进行恢复独立表空间的时候,需要使用导入表空间,这时候需要该权限。

    • PROCESS
      XtraBackup在备份的时候,利用该权限可以查看所有正在服务器端运行的线程情况。

    • SUPER
      用在复制环境中开启和关闭 SLAVE线程。

    • CREATE
      CREATE权限用于创建 PERCONA_SCHEMA.xtrabackup_ history数据库和表。

    • INSERT
      INSERT权限用于将写入历史记录到 PERCONA_SCHEMA. xtrabackup_history数据库和表。

    • SELECT
      当 innobackupex使用--incremental-history-name--incremental-history-uuid选项时,可以使用 SELECT权限查询 PERCONA_SCHEMA. xtrabackup_history表的 innodb_to_lsn的值来满足该特性。

    创建备份用户

    mysql> create user 'backup'@'localhost' identified by 'mysql';
    
    mysql> grant reload,lock tables,replication client,CREATE TABLESPACE,PROCESS,SUPER,CREATE,INSERT,SELECT on *.* to 'backup'@'localhost';
    
    

    3.6.4 配置XtraBackup

    在my.cnf配置文件,增加以下参数

    [xtrabackup]
    target_dir = /data/backup/
    

    若没在my.cnf配置文件增加xtrabackup参数,可以在备份时指定备份目录,如:

    xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/
    
    

    3.6.5 xtrabackup 备份实践

    全备份

    1. xtrabackup备份
    xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/
    
    1. prepare备份
    xtrabackup --prepare --target-dir=/data/backup/
    
    
    1. 恢复
    #恢复时,需MySQL数据目录为空。
    
    shell> xtrabackup --copy-back --target-dir=/data/backup/
    
    
    #恢复完成后,更改MySQL数据目录权限属主
    
    shell> chown -R mysql:mysql data
    
    启动不了,错误日志提示
    
    2018-09-12T06:54:49.020755Z 0 [ERROR] InnoDB: Unable to open undo tablespace 'undolog/undo001'.
    2018-09-12T06:54:49.020815Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
    
    查看undolog目录,发现undolog并没有恢复。。。
    
    手动cp过来
    
    然后在重新启动,就行了。
    
    为什么undolog不还原呢?
    
    配置文件中my.cnf的innodb_undo_directory参数需要指定绝对路径
    
    innodb_undo_directory = /data/mysql/mysql3306/data/undolog
    
    这样恢复就行了
    

    增量备份

    1. 创建基础备份(全备份)
    shell> mkdir /data/backup/base
    shell> mkdir /data/backup/incre
    shell> mkdir /data/backup/incre2
    shell> chown mysql:mysql /data/backup/*
    
    xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/base/
    
    # more xtrabackup_checkpoints 
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 4105224763
    last_lsn = 4105224772
    compact = 0
    recover_binlog_info = 0
    
    
    1. 创建增量备份1
    mysql> create database incre;
    mysql> use incre;
    mysql> create table t1 (id int);
    
    shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/incre/ --incremental-basedir=/data/backup/base/
    
    shell> more xtrabackup_checkpoints
    backup_type = incremental
    from_lsn = 4105224763
    to_lsn = 4105229436
    last_lsn = 4105229445
    compact = 0
    recover_binlog_info = 0
    
    
    1. 创建增量备份2
    mysql> create table t2 (id int);
    mysql> insert into t2 select 2;
    
    shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/incre2/ --incremental-basedir=/data/backup/incre/
    
    shell> more xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 4105229436
    to_lsn = 4105233934
    last_lsn = 4105233943
    compact = 0
    recover_binlog_info = 0
    
    
    1. prepare 增量备份
    shell> xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
    
    #日志显示:
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 4105224781
    InnoDB: Number of pools: 1
    180912 15:42:08 completed OK!
    
    
    
    #应用第一个增量备份
    shell> xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/incre/
    
    
    #应用第二个增量备份
    shell> xtrabackup --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/incre2/
    
    
    #日志显示:
    
    InnoDB: Shutdown completed; log sequence number 4105234472
    180912 15:51:27 completed OK!
    
    shell> more /data/backup/base/xtrabackup_checkpoints 
    backup_type = full-prepared
    from_lsn = 0
    to_lsn = 4105233934
    last_lsn = 4105233943
    compact = 0
    recover_binlog_info = 0
    
    
    1. 恢复备份
    shell> mysqladmin -S /tmp/mysql3306.sock shutdown
    shell> mv data data_bak
    shell> mkdir data
    shell> chown mysql:mysql data
    
    shell> xtrabackup --copy-back --target-dir=/data/backup/base/
    
    shell> chown -R mysql:mysql /data/mysql/mysql3306/data
    
    shell> mysqld --defaults-file=/etc/my3306.cnf &
    
    mysql> use incre
    
    mysql> select * from t1;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.01 sec)
    
    mysql> select * from t2;
    +------+
    | id   |
    +------+
    |    2 |
    +------+
    1 row in set (0.01 sec)
    
    可以看到增量备份1与增量备份2的备份都恢复出来了。
    
    

    压缩备份

    使用--compress选项进行压缩备份

    1. 备份数据库
    shell> mkdir compress
    shell> chown mysql:mysql compress
    
    shell> xtrabackup --backup --compress -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/compress/
    
    #如果要加速压缩,可以使用并行压缩,可以使用xtrabackup --compress-threads选项启用。 以下示例将使用四个线程进行压缩:
    
    shell> xtrabackup --backup --compress  --compress-threads=4 -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/compress/
    
    1. prepare 备份
    # 解压备份
    shell> xtrabackup --decompress --target-dir=/data/backup/compress/
    
    # xtrabackup --parallel可与xtrabackup --decompress选项一起使用,以同时解压缩多个文件。如:
    
    shell> xtrabackup --decompress --parallel=4 --target-dir=/data/backup/compress/
    
    #注意:使用--parallel选项时,需要先安装qpress插件,否则解压时,报如下错误:
    sh: qpress: command not found
    sh: qpress: command not found
    sh: qpress: command not found
    cat: write error: Broken pipe
    sh: qpress: command not found
    cat: write error: Broken pipe
    cat: write error: Broken pipe
    Error: decrypt and decompress thread 0 failed.
    Error: decrypt and decompress thread 1 failed.
    Error: decrypt and decompress thread 2 failed.
    Error: decrypt and decompress thread 3 failed.
    
    #安装qpress
    shell> wget http://www.quicklz.com/qpress-11-linux-x64.tar
    shell> tar xvf qpress-11-linux-x64.tar
    shell> cp qpress /usr/bin
    
    #prepare备份
    shell> xtrabackup --prepare --target-dir=/data/backup/compress/
    
    1. 恢复备份
    shell> xtrabackup --copy-back --target-dir=/data/backup/compress/
    

    加密备份

    1. 创建密钥
    shell> openssl rand -base64 24
    SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8
    
    或者
    
    shell> openssl rand -base64 24
    A0fYOUej9Vq66JWqfRnvENjtHa7ipNmy 
    
    shell> echo -n "A0fYOUej9Vq66JWqfRnvENjtHa7ipNmy" > /data/backup/encry/keyfile
    
    1. 创建加密备份
    shell> xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/encry --encrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8"
    
    或者
    
    shell> xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/encry --encrypt=AES256 --encrypt-key-file=/data/backup/encry/keyfile
    
    
    1. prepare 备份
    #解密备份
    xtrabackup --decrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8" --target-dir=/data/backup/encry
    
    #加上--remove-original参数可以在解密备份文件后,删除加密文件。
    
    shell> xtrabackup --decrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8" --target-dir=/data/backup/encry --remove-original
    
    #也可以加上 --parallel参数来提高解密文件速度
    
    #prapare备份
    shell> xtrabackup --prepare --target-dir=/data/backup/encry
    
    1. 恢复备份
    shell> xtrabackup --copy-back --target-dir=/data/backup/encry
    

    3.6.6 innobackupex 备份实践

    全备份

    1. 备份
    #备份到 /data/backup/当前时间/ 目录下
    shell> innobackupex --defaults-file=/etc/my3306.cnf --user=backup --password=mysql /data/backup
    
    
    # 或者指定--no-timestamp参数,将备份到指定backup-dir目录
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql  /data/backup/backup-dir --no-timestamp
    
    
    1. prepare备份
    shell> innobackupex --apply-log /data/backup/2018-09-13_10-29-10/
    
    1. 恢复备份
    shell> innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/2018-09-13_10-29-10/
    
    shell> chown -R mysql:mysql /data/mysql/mysql3306/data
    
    

    增量备份

    1. 创建base备份
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql /data/backup/base
    
    shell> cat xtrabackup_checkpoints 
    
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 4105235515
    last_lsn = 4105235524
    compact = 0
    recover_binlog_info = 0
    
    
    1. 创建增量备份1
    mysql> use incre
    mysql> create table t3 (id int);
    mysql> insert into t3 select 3;
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-basedir=/data/backup/base/2018-09-13_10-51-46
    
    
    shell> cat xtrabackup_checkpoints 
    
    backup_type = incremental
    from_lsn = 4105235515
    to_lsn = 4105240026
    last_lsn = 4105240035
    compact = 0
    recover_binlog_info = 0
    
    
    1. 创建增量备份2
    mysql> use incre
    mysql> create table t4 (id int);
    mysql> insert into t4 select 4;
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-basedir=/data/backup/incre/2018-09-13_10-58-04/
    
    shell> cat xtrabackup_checkpoints 
    
    backup_type = incremental
    from_lsn = 4105240026
    to_lsn = 4105244515
    last_lsn = 4105244524
    compact = 0
    recover_binlog_info = 0
    
    #也可以使用参数--incremental-lsn进行增量备份,如
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-lsn=4105235515
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-lsn=4105244515
    
    
    1. prepare 增量备份
    #prepare base备份
    
    shell> innobackupex --apply-log --redo-only /data/backup/base/2018-09-13_10-51-46/
    
    #日志显示
    InnoDB: Shutdown completed; log sequence number 4105235533
    InnoDB: Number of pools: 1
    180913 11:14:03 completed OK!
    
    #prepare 增量备份1
    shell> innobackupex --apply-log --redo-only /data/backup/base/2018-09-13_10-51-46 --incremental-dir=/data/backup/incre/2018-09-13_10-58-04
    
    #prepare 增量备份2
    
    shell> innobackupex --apply-log /data/backup/base/2018-09-13_10-51-46 --incremental-dir=/data/backup/incre/2018-09-13_11-02-11
    
    shell> innobackupex --apply-log /data/backup/base/2018-09-13_10-51-46/
    
    
    

    5) 恢复备份

    shell> innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/base/2018-09-13_10-51-46/
    
    shell> chown -R mysql:mysql /data/mysql/mysql3306/data
    
    

    流式化备份

    1. 打包备份
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ > /data/backup/dbbackup`date +%Y%m%d_%H%M%S`.tar
    
    #或者
    
    shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar --target-dir=/data/backup/ > /data/backup/db.tar
    
    
    #解压到指定目录
    shell> mkdir db20180913_141603
    shell> tar -xvf db20180913_141603.tar -C ./db20180913_141603
    
    #peapare备份
    shell> innobackupex --apply-log /data/backup/db20180913_141603
    
    
    1. 带压缩的打包备份
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |gzip - > /data/backup/dbbackup`date +%Y%m%d_%H%M%S`.tar.gz
    
    #或者
    
    shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar --target-dir=/data/backup/ |gzip - > /data/backup/db.tar.gz
    
    
    shell> mkdir dbbackup20180913_150942
    shell> tar -xvzf dbbackup20180913_150942.tar.gz -C ./dbbackup20180913_150942
    
    #peapare备份
    shell> innobackupex --apply-log /data/backup/dbbackup20180913_150942
    
    1. 基于SSH远程
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |ssh root@mysqldb2 "cat - > /data/backup/dbback`date +%Y%m%d_%H%M%S`.tar"
    
    #或者
    
    shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar  |ssh root@mysqldb2 "cat - > /data/backup/db.tar"
    
    

    备份指定数据库

    #备份指定数据库 用--database参数,如下:备份employees 与sbtest数据库
    
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --database='employees sbtest' /data/backup
    
    #prepare备份
    shell> innobackupex --apply-log /data/backup/2018-09-13_16-13-47/
    
    
    #恢复备份
    #copy,因为是部分备份,不能直接用--copy-back,只能手动来复制需要的库,也要复制ibdata(数据字典)
    
    shell> cp -r sbtest /data/mysql/mysql3306/data/
    shell> cp -r employees /data/mysql/mysql3306/data/
    shell> cp ibdata* /data/mysql/mysql3306/data/
    shell> chown -R mysql:mysql /data/mysql/mysql3306/data
    
    #备份指定表,如备份employees.dept与sbtest.sbtest1表:
    shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --database='employees.dept sbtest.sbtest1' /data/backup
    
    
    展开全文
  • mysql备份
  • MySQL备份和还原操作

    万次阅读 2019-03-18 11:50:28
    MySQL备份和还原操作 目标  备份的概念  Mysqldump备份操作  MySQL还原操作  MySQL双机热备份 概述  在数据库表丢失或损坏的情况下,备份数据库是很重要的。如果发生系统崩溃,能够将表尽可能丢失最少的...
  • MySQL备份与恢复策略

    万次阅读 2018-07-27 14:12:28
    MySQL备份与恢复策略 逻辑备份与恢复 什么是数据库逻辑备份? 常用的逻辑备份 逻辑备份恢复方法 物理备份与恢复 什么是数据库物理备份 MySQL物理备份所需文件 各存储引擎常用物理备份方法 ...
  • MySQL备份与恢复之MySQL 延时备份

    千次阅读 2013-12-08 16:05:18
    在上一篇文章中,我们讲到MySQL备份与恢复之percona-xtrabackup实现增量备份及恢复,percona-xtrabackup是一个优秀的用于增量备份的工具。今天我们讲到的延时备份也是使用他们的产品……
  • mysql备份问题解决

    千次阅读 2019-12-09 13:40:02
    我之前一直mysql备份出错,后来用了下面这个语句成功了 mysqldump -uroot -p密码 -P3306 表名 [表名]…>备份地址 以下是成功案例贴图 这里要注意几点: 首先这个要在windows里的cmd命令行模式里输入(其他系统的...
  • MySQL备份工具xtrabackup

    2018-06-27 15:16:24
    MySQL备份工具xtrabackup官网:https://www.percona.com/software/mysql-database/percona-xtrabackup
  • 文章目录一、MySQL 完全备份1.1、数据库备份方式精讲1.1.1、数据库备份的重要性...MySQL 备份思路三、MySQL 增量备份3.1、增量备份的概念3.2、增量备份方法四、MySQL 增量恢复4.1、增量恢复方法4.2、企业恢复案例4.2
  • windows下的 MySQL备份

    千次阅读 2018-10-08 17:13:27
    windows下的 MySQL备份 一.思路: mysql备份采用全量备份+增量备份的方式进行数据实时备份, 恢复的时候采用先执行全量备份文件,然后再进行抽取增量sql进行执行 二.全量备份: 1.新建一个back_up.bat,内容如下:...
  • 理论+实验·MySQL备份与恢复 文章目录理论+实验·MySQL备份与恢复一、数据库备份的分类1.1 数据备份的重要性1.2 数据库备份的分类1.3 常见的备份方法二、MySQL完全备份与恢复2.1 MySQL完全备份2.2 数据库完全备份...
  • MySql 备份还原

    千次阅读 2014-09-28 10:59:18
    mysql备份和还原我们一般会执行这样的操作:在现场mysql数据库(一般是linux系统)上备份数据库(一般有几十G到上百G)到windows机器上,然后到公司后将windows机器上的文件还原到公司的linux系统的mysql数据库上。...
  • MySQL备份方法总结

    千次阅读 2014-12-08 14:55:51
    MySQL备份方法总结
  • mysql备份语句 备份单个数据库 mysqldump -u username -p dbname table1 table2 ...-&gt; BackupName.sql 其中: dbname参数表示数据库的名称; table1和table2参数表示需要备份的表的名称,为空则...
  • MySQL备份利器 innobackup 大数据备份还原大数据的备份和恢复,始终是个难点,当MySQL超过10个G,那么备份整个过程变得非常难熬,而且也不利于数据备份和恢复。这时候,刚好出现一款备份MySQL的工具 innobackup,...
  • MySql备份测试

    2013-08-07 16:07:26
    MySql备份测试, 1.如果想备份到指定目录,只需要 Mysqldump test >/路径/备份文件名.sql 2.如果想压缩备份可以 %mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz 3.如果想单独备份表,可以 ...
  • mysql备份与还原(一) 一、mysqldump备份结合binlog日志恢复 mysql备份一般采用全库加日志备份的方式,例如每天在执行一次全备份,每小时执行一次二进制日志备份,这样在mysql故障后可以使用全备份和日志备份将数据...
  • 理论+实验——MySQL备份与恢复

    千次阅读 2020-09-13 17:04:35
    文章目录一、数据库备份的分类1.1 备份的重要性1.2 数据库备份的分类1.3 常见的备份方法二、MySQL完全备份与恢复2.1 完全备份概述2.2 完全备份...MySQL 数据库增量恢复四、MySQL 备份恢复案例4.1 一般恢复(丢什么数据...
  • mysql备份

    千次阅读 2013-04-17 14:48:59
     mysql备份单个表,直接在终端输入: root@xxx:# mysqldump 数据库名 表名 > 文件路径 如: root@390:~# mysqldump mydatabase mytable > /root/mysql.sql 则mysql.sql中则有你创建表的语句还有表的数据; 2...
  • mysql备份的方法:

    千次阅读 2018-07-09 09:11:01
    MYSQL备份与恢复精华篇数据备份原理数据备份属于数据容灾保护中的内容,所有的数据备份系统设计都基于这五个元素,备份源、备份目标、传输网络、备份引擎和备份策略。用户按照需要制定备份策略,使用定时任务执行...
  • MySQL 备份/还原

    2015-06-23 20:17:19
    MySQL 备份/还原 基本概念和实际命令
  • MySQL备份和还原

    万次阅读 2014-10-08 21:22:28
    MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。 1.Win32下MySQL的备份与还原 1.1 备份 开始菜单 | 运行 | cmd |利用“cd /Program Files/MySQL/MySQL Server 5.0/bin”命令进入bin文件夹 |...
  • mysql备份xtrabackup

    千次阅读 2018-03-26 09:44:57
    xtrabackup是一种物理备份工具,通过协议连接到mysql服务端,然后读取并复制innodb底层的"数据块",完成所谓的"物理备份"。 支持对innodb进行热备、增量备份、差量备份。 支持对myisam进行温备,因为在备份myisam...
  • 数据库及时备份可以帮助我们在数据库出现异常宕机时及时的使用备份数据进行恢复工作,将因为数据库宕...如果还围观看过上一篇文章的可以先行查询上一篇文章关于使用xtrabackup进行数据备份与恢复:Mysql备份与恢复(...
  • Mysql备份方案分为以下三种1.mysqldump+binlog:(推荐)完全备份,通过备份二进制日志实现增量备份2.xtrabckup:对InnoDB:热备,支持完全备份和增量备份对MyISAM:温备,支持完全备份3.lvm2快照+binlog:几乎热备,物理...
  • MySQL备份脚本

    千次阅读 2019-01-24 11:39:17
    CentOS MySQL自动备份shell脚本 在数据库的日常维护工作中,除了保证业务的正常运行以外,就是要对数据库进行备份,以免造成数据库的丢失,从而给企业带来重大经济损失。 通常备份可以按照备份时数据库状态分为热备...
  • Linux 下MySQL备份

    万次阅读 2019-02-28 15:42:45
    Linux下MySQL数据库备份和恢复 Linux下MySQL数据库有逻辑备份和物理备份,也可以分为完全备份、部分备份。 ·完全备份是指备份整个数据集(即整个数据库) ·部分备份是指备份部分数据集(只备份一个表) 逻辑...
  • MySQL备份与恢复方案验证

    千次阅读 2018-04-13 11:10:38
    mysqlbackup+xtrabackup(RHEL6X86_64)之前针对mysql的备份做了个简单测试,与大家分享下 目前关于MySQL备份工具最流行的主要有三种1.xtrabackup -----Percona opensource2.mysqlbackup -----mysql Enterprise3....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 60,523
精华内容 24,209
关键字:

mysql备份

mysql 订阅