经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库运维 » MySQL » 查看文章
Mysql备份 -----innobackupex
来源:cnblogs  作者:浅雨凉  时间:2018/10/31 8:54:10  对本文有异议

一,原理及介绍
〇 xtrabackup能做哪些
    对InnoDB引擎的表做热备
    增量备份
    流压缩传输到另外的服务器上
    在线移动表
    更简单的创建从库
    备份时不增加服务器负载

 〇 原理
     备份及恢复大致涉及三个步骤:备份 -> prepare -> 恢复
     备份运行时,工具会记住当时的LSN号,并打开xtrabackup_logfile,然后开始对datafile进行copy,即ibdata1及ibd文件。
     复制需要一定的时间,在复制期间,如果文件被修改,工具将监视redo log file并将每一次更变记录下来,保存在xtrabackup_logfile中。
     接下来处理非事务表如MyISAM的备份操作,innobackupex通过FLUSH TABLES WITH READ LOCK来阻塞DML。
     并在此时获取binlog的position[和GTID](此处我理解为和mysqldump --single-transaction处理方式类似)
     在做完非事务表的copy之后,执行UNLOCK TABLES,完成备份,并停止记录xtrabackup_logfile。
     接下来就是需要做prepare的过程,该过程类似InnoDB的crash-recovery。
     对redo log进行前滚(到数据文件),并将没提交的事务进行回滚操作(rollback),这样便可以保证数据的一致性,所以对于事务表,整个过程是不会影响写操作的。
 
     注:InnoDB、XtraDB、MyISAM是肯定支持的,其他的存储引擎没有测过。
 
 〇 权限需求
         对datadir需要有rwx的权限。
     MySQL:
         最小所需要的权限有:
         RELOAD
         LOCK TABLES(如果加上--no-lock的话可以不要)
         REPLICATION CLIENT(为了获得binary log的position)
         PROCESS(为了执行show engine innodb status,并且需要查看所有运行的线程)
         其他可能需要用到的权限:
         CREATE TABLESPACE(如果需要通过5.6+ 的TTS恢复/迁移单个表的话)
         SUPER(可能需要在复制环境里启动或者停止slave线程)
         CREATE\INSERT\SELECT(对PERCONA_SCHEMA.xtrabackup_history进行操作)

二、下载安装
官方地址:https://www.percona.com/downloads/XtraBackup/LATEST/

安装:
tar xf Percona-XtraBackup-2.4.12-r170eb8c-el7-x86_64-bundle.tar
rpm -vih percona-xtrabackup*.rpm 或者yum install -y percona-xtrabackup-*

三,全量备份与恢复

?备份数据
语法:innobackupex [--defaults-file=/etc/my.cnf] --user=DBUSER --password=DBUSERPASS [--socket=/var/lib/mysql/mysql.sock --port=3306] /path/to/BACKUP-DIR/

  1. [root@Server data]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=939879412 /data/
  2. xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=server-bin.log --server-id=1
  3. xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --log_bin=server-bin.log --server-id=1
  4. 181029 14:41:49 innobackupex: Starting the backup operation
  5. IMPORTANT: Please check that the backup run completes successfully.
  6. At the end of a successful backup run innobackupex
  7. prints "completed OK!".
  8. 181029 14:41:50 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using passw
  9. ord: YES).181029 14:41:50 version_check Connected to MySQL server
  10. 181029 14:41:50 version_check Executing a version check against the server...
  11. 181029 14:41:50 version_check Done.
  12. 181029 14:41:50 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
  13. Using server version 5.6.41
  14. innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
  15. xtrabackup: uses posix_fadvise().
  16. xtrabackup: cd to /var/lib/mysql
  17. xtrabackup: open files limit requested 0, set to 65535
  18. xtrabackup: using the following InnoDB configuration:
  19. xtrabackup: innodb_data_home_dir = .
  20. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  21. xtrabackup: innodb_log_group_home_dir = ./
  22. xtrabackup: innodb_log_files_in_group = 2
  23. xtrabackup: innodb_log_file_size = 50331648
  24. InnoDB: Number of pools: 1
  25. 181029 14:41:50 >> log scanned up to (2048699761)
  26. xtrabackup: Generating a list of tablespaces
  27. InnoDB: Allocated tablespace ID 4 for mysql/slave_master_info, old maximum was 0
  28. 181029 14:41:51 [01] Copying ./ibdata1 to /data/2018-10-29_14-41-49/ibdata1
  29. 181029 14:41:51 >> log scanned up to (2048699761)
  30. 181029 14:41:52 >> log scanned up to (2048699952)
  31. 181029 14:41:53 >> log scanned up to (2048700648)
  32. 181029 14:41:54 >> log scanned up to (2048700949)
  33. 181029 14:41:55 >> log scanned up to (2048701233)
  34. 181029 14:41:56 >> log scanned up to (2048701233)
  35. 181029 14:41:57 >> log scanned up to (2048701533)
  36. 181029 14:41:58 >> log scanned up to (2048701928)
  37. .........
  38. 181029 14:42:09 Finished backing up non-InnoDB tables and files
  39. 181029 14:42:09 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
  40. xtrabackup: The latest check point (for incremental): '2048706153'
  41. xtrabackup: Stopping log copying thread.
  42. .181029 14:42:09 >> log scanned up to (2048706153)
  43. 181029 14:42:09 Executing UNLOCK TABLES
  44. 181029 14:42:09 All tables unlocked
  45. 181029 14:42:09 Backup created in directory '/data/2018-10-29_14-41-49/'
  46. 181029 14:42:09 [00] Writing /data/2018-10-29_14-41-49/backup-my.cnf
  47. 181029 14:42:09 [00] ...done
  48. 181029 14:42:09 [00] Writing /data/2018-10-29_14-41-49/xtrabackup_info
  49. 181029 14:42:09 [00] ...done
  50. xtrabackup: Transaction log of lsn (2048670711) to (2048706153) was copied.
  51. 181029 14:42:10 completed OK!
View Code

当出现innobackupex: completed OK!
出现上面的信息,表示备份已经ok。

查看备份的结果

  1. [root@Server data]# ll
  2. total 4
  3. drwxr-x--- 7 root root 4096 Oct 29 14:42 2018-10-29_14-41-49
  4. [root@Server data]# cd 2018-10-29_14-41-49/
  5. [root@Server 2018-10-29_14-41-49]# ls
  6. backup-my.cnf ibdata1 mysql performance_schema test test1 xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile zabbix

将数据库备份到远程目录
前提条件:
a.远程机器的目录要存在,同时保证是空目录。远程机器目录非空,备份时报以下错误"xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)"
b.ssh要能连接:使用ssh秘钥免密码输入
innobackupex --user=USER --password=PASSWD  --socket=/var/lib/mysql/mysql.sock --port=3306 --stream=tar /data/temp/  | gzip | ssh root@远程主机host "cat -> /data/temp/mysql_full_backup_2018-10-29.tar.gz"

 在备份的文件夹中,有几个文件值得注意:
     xtrabackup_binlog_pos_innodb记录了binlog的position,若开启了GTID,也会将GTID取出。
     在用于备份+binlog恢复或建立slave的场景里十分有用。
     xtrabackup_checkpoints记录了此次备份的类型和lsn号的起始值,是否压缩等
     xtrabackup_info则记录了备份工具的信息,时间,备份对象(是针对全实例还是某库表),是否是增量,binlog位置等
backup-my.cnf文件,则记录了备份时可能涉及到的选项参数,比如系统表空间信息,独立undo表空间信息,redo-log信息等

? 恢复数据
注意:恢复之前
1)要先关闭数据库
2)要删除数据文件和日志文件(也可以mv移到别的地方,只要确保清空mysql数据存放目录就行)
apply-log 类似innodb的crash recovery

  1. [root@Server data]# innobackupex --apply-log /data/2018-10-29_14-41-49/ #prepare过程
  2. xtrabackup: recognized server arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=
  3. ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=innodb --innodb_log_checksum_algorithm=innodb --innodb_data_file_path=
  4. ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=. --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=0 181029 14:50:05 innobackupex: Starting the apply-log operation
  5. IMPORTANT: Please check that the apply-log run completes successfully.
  6. At the end of a successful apply-log run innobackupex
  7. prints "completed OK!".
  8. innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
  9. xtrabackup: cd to /data/2018-10-29_14-41-49/
  10. xtrabackup: This target seems to be not prepared yet.
  11. InnoDB: Number of pools: 1
  12. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2048670711)
  13. xtrabackup: using the following InnoDB configuration for recovery:
  14. xtrabackup: innodb_data_home_dir = .
  15. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  16. xtrabackup: innodb_log_group_home_dir = .
  17. xtrabackup: innodb_log_files_in_group = 1
  18. xtrabackup: innodb_log_file_size = 8388608
  19. xtrabackup: using the following InnoDB configuration for recovery:
  20. xtrabackup: innodb_data_home_dir = .
  21. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  22. xtrabackup: innodb_log_group_home_dir = .
  23. xtrabackup: innodb_log_files_in_group = 1
  24. xtrabackup: innodb_log_file_size = 8388608
  25. xtrabackup: Starting InnoDB instance for recovery.
  26. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  27. InnoDB: PUNCH HOLE support available
  28. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  29. InnoDB: Uses event mutexes
  30. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  31. InnoDB: Compressed tables use zlib 1.2.3
  32. InnoDB: Number of pools: 1
  33. InnoDB: Using CPU crc32 instructions
  34. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  35. InnoDB: Completed initialization of buffer pool
  36. InnoDB: page_cleaner coordinator priority: -20
  37. InnoDB: Highest supported file format is Barracuda.
  38. InnoDB: Log scan progressed past the checkpoint lsn 2048670711
  39. InnoDB: Doing recovery: scanned up to log sequence number 2048706153 (0%)
  40. InnoDB: Database was not shutdown normally!
  41. InnoDB: Starting crash recovery.
  42. InnoDB: Starting an apply batch of log records to the database...
  43. InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
  44. 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed
  45. InnoDB: xtrabackup: Last MySQL binlog file position 11989, file name ./server-bin.000044
  46. InnoDB: Creating shared tablespace for temporary tables
  47. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  48. InnoDB: File './ibtmp1' size is now 12 MB.
  49. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  50. InnoDB: 32 non-redo rollback segment(s) are active.
  51. InnoDB: Waiting for purge to start
  52. InnoDB: 5.7.19 started; log sequence number 2048706153
  53. InnoDB: xtrabackup: Last MySQL binlog file position 11989, file name ./server-bin.000044
  54. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  55. InnoDB: FTS optimize thread exiting.
  56. InnoDB: Starting shutdown...
  57. InnoDB: Shutdown completed; log sequence number 2048707915
  58. InnoDB: Number of pools: 1
  59. xtrabackup: using the following InnoDB configuration for recovery:
  60. xtrabackup: innodb_data_home_dir = .
  61. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  62. xtrabackup: innodb_log_group_home_dir = .
  63. xtrabackup: innodb_log_files_in_group = 2
  64. xtrabackup: innodb_log_file_size = 50331648
  65. InnoDB: PUNCH HOLE support available
  66. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  67. InnoDB: Uses event mutexes
  68. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  69. InnoDB: Compressed tables use zlib 1.2.3
  70. InnoDB: Number of pools: 1
  71. InnoDB: Using CPU crc32 instructions
  72. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  73. InnoDB: Completed initialization of buffer pool
  74. InnoDB: page_cleaner coordinator priority: -20
  75. InnoDB: Setting log file ./ib_logfile101 size to 48 MB
  76. InnoDB: Setting log file ./ib_logfile1 size to 48 MB
  77. InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  78. InnoDB: New log files created, LSN=2048707915
  79. InnoDB: Highest supported file format is Barracuda.
  80. InnoDB: Log scan progressed past the checkpoint lsn 2048708108
  81. InnoDB: Doing recovery: scanned up to log sequence number 2048708117 (0%)
  82. InnoDB: Database was not shutdown normally!
  83. InnoDB: Starting crash recovery.
  84. InnoDB: xtrabackup: Last MySQL binlog file position 11989, file name ./server-bin.000044
  85. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  86. InnoDB: Creating shared tablespace for temporary tables
  87. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  88. InnoDB: File './ibtmp1' size is now 12 MB.
  89. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  90. InnoDB: 32 non-redo rollback segment(s) are active.
  91. InnoDB: Waiting for purge to start
  92. InnoDB: 5.7.19 started; log sequence number 2048708117
  93. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  94. InnoDB: FTS optimize thread exiting.
  95. InnoDB: Starting shutdown...
  96. InnoDB: Shutdown completed; log sequence number 2048713467
  97. 181029 14:50:09 completed OK!
View Code

当出现innobackupex: completed OK!
出现上面的信息,表示恢复已经ok。

# cp -r 2018-10-29_14-41-49/*  /var/lib/mysql/      将数据拷贝到数据库的数据目录下
也可以用此命令拷贝  innobackupex --copy-back /var/lib/mysql/
# chown mysql:mysql /var/lib/mysql/ -R
重启数据库

四,增量备份与恢复
①增备方法与全备不一样:
     innobackupex --user= --password= --incremental $new_dir --incremental-basedir=$basedir
 
    其中--incremental是本次增量备份存放目录
     $new_dir是表示将增量备份出来的东西放在哪个目录
     --incremental-basedir则表示,针对哪一次备份做增量备份
 
     备份的差异在目录的xtrabackup_checkpoints中查看:
     比如:
     $basedir中内容: 
     backup_type = full-prepared
     from_lsn = 0
     to_lsn = 2048706153
     last_lsn = 2048706153
     compact = 0
     recover_binlog_info = 0 
 
     $new_bkdir中内容:
     backup_type = incremental
     from_lsn = 2048706153
     to_lsn = 2048876543
     last_lsn = 2048876543
     compact = 0
     recover_binlog_info = 0
  
     可以注意一下增备的from_lsn号
     大于这个LSN号的页都是被变更过的,这些偏移量,也就是需要被增量备份出去的
 
 ②prepare:
     prepare过程:
     从第一个备份开始(也就是全量)做prepare,再将往后的增量备份依次添加到全量备份中。
     注意,此处多了一个参数即--redo-only,该参数是指将已提交的事务应用,未提交的事务回滚。
     此外,--incremental-dir也是在之前没有用到过的,这个参数代表需要被合并进去的增量备份目录。
     注意,此处多次的增量备份是指:针对上次的增量备份做的增量。
    
     也就是可以理解为:
         全备:500GB
         第一次增量备份:2GB
         第二次增量备份:1GB(针对第一次增量备份的增量数据)
         ……
         第n次
 
     按照备份顺序做prepare,也就是prepare的顺序为:
     第一次全备 -> 增量备份1 -> 增量备份2 -> ... -> 增量备份n
     第一次全备的prepare:innobackup --apply-log --redo-only $basedir
     第二次prepare:innobackup --apply-log --redo-only $basedir --incremental-dir=$new_dir_1(此处的$new_dir_1也就是第一次增量备份)
     ......
     第n次prepare:innobackup --apply-log $basedir --incremental-dir=$new_dir_n(此处的$new_dir_n也就是最近也就是最后一次的增量备份
     最后一次增量备份的prepare,不需要指定--redo-only
 
     最后将增量备份和全备进行合并,将未提交的事务回滚,这个操作和全量prepare无异:
     innobackup --apply-log $basedir

③恢复到datadir:
     和全量无异,直接copyback就行了
     innobackupex --copy-back $basedir
 
  增量备份的prepare有点特殊,还是小结一下:
      ① prepare完备(加上--redo-only)
      ② prepare每一次增量备份到完备中,需要加上--redo-only,最后一次增量备份的prepare不需要加--redo-only
      ③ 对生成的最终完备做--apply-log


执行第一次增量备份之后,可以再做一次增量备份
此时有两种增量备份方法:
     第一种,总是针对basedir做增量,这个方式恢复起来就特别简单了,只需要将最后一次的增量备份合并到全量备份里,就可以恢复了。
     第二种,总是针对上一次的增量,做增量备份。这个方式的恢复,就要逐一合并了,也就是我上述所说看起来有点复杂的增备思路。

图示:
第一种:
     总是将1月1日的全备作为basedir,所以FROM_LSN号总是5000。

 


第二种:
     总是把上一次(最近一次)的备份作为basedir。

 

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号