devbox
羊村懒王
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

xtrabackup 全量备份、恢复数据

作者：羊村懒王 | 2024-05-02 04:58:27

赞

踩

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

1、全量备份

  1. [root@localhost lib]##innobackupex --defaults-file=$defaults_file --user=$mysql_username --password=$mysql_password --stream=tar $mysql_backup_dir | gzip - > $timeStart.bz.tar.gz 2> "$logfile"
  2.  
  3. 备份目录/var/lib/mysqlsource，
  4. 如果下面要恢复，解压bz文件至该目录下面。

 2、恢复准备

[root@localhost lib]#mkdir mysql
  1. [root@localhost lib]# innobackupex --apply-log /var/lib/mysqlsource/
  2. 180712 15:22:51 innobackupex: Starting the apply-log operation
  3.  
  4. IMPORTANT: Please check that the apply-log run completes successfully.
  5.            At the end of a successful apply-log run innobackupex
  6.            prints "completed OK!".
  7.  
  8. innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
  9. xtrabackup: cd to /var/lib/mysqlsource/
  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=(117069890525)
  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 __atomic_thread_fence() is used for memory barrier
  31. InnoDB: Compressed tables use zlib 1.2.7
  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 117069890525
  39. InnoDB: Doing recovery: scanned up to log sequence number 117070116070 (3%)
  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: 0 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 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 
  44. InnoDB: Apply batch completed
  45. InnoDB: xtrabackup: Last MySQL binlog file position 466896958, file name mysql-bin.000360
  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: page_cleaner: 1000ms intended loop took 9422ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
  52. InnoDB: 5.7.13 started; log sequence number 117070116070
  53. InnoDB: xtrabackup: Last MySQL binlog file position 466896958, file name mysql-bin.000360
  54.  
  55. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  56. InnoDB: FTS optimize thread exiting.
  57. InnoDB: Starting shutdown...
  58. InnoDB: Shutdown completed; log sequence number 117070116783
  59. InnoDB: Number of pools: 1
  60. xtrabackup: using the following InnoDB configuration for recovery:
  61. xtrabackup:   innodb_data_home_dir = .
  62. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  63. xtrabackup:   innodb_log_group_home_dir = .
  64. xtrabackup:   innodb_log_files_in_group = 2
  65. xtrabackup:   innodb_log_file_size = 268435456
  66. InnoDB: PUNCH HOLE support available
  67. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  68. InnoDB: Uses event mutexes
  69. InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
  70. InnoDB: Compressed tables use zlib 1.2.7
  71. InnoDB: Number of pools: 1
  72. InnoDB: Using CPU crc32 instructions
  73. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  74. InnoDB: Completed initialization of buffer pool
  75. InnoDB: page_cleaner coordinator priority: -20
  76. InnoDB: Setting log file ./ib_logfile101 size to 256 MB
  77. InnoDB: Progress in MB:
  78.  100 200
  79. InnoDB: Setting log file ./ib_logfile1 size to 256 MB
  80. InnoDB: Progress in MB:
  81.  100 200
  82. InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  83. InnoDB: New log files created, LSN=117070116783
  84. InnoDB: Highest supported file format is Barracuda.
  85. InnoDB: Log scan progressed past the checkpoint lsn 117070116876
  86. InnoDB: Doing recovery: scanned up to log sequence number 117070116885 (0%)
  87. InnoDB: Doing recovery: scanned up to log sequence number 117070116885 (0%)
  88. InnoDB: Database was not shutdown normally!
  89. InnoDB: Starting crash recovery.
  90. InnoDB: xtrabackup: Last MySQL binlog file position 466896958, file name mysql-bin.000360
  91. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  92. InnoDB: Creating shared tablespace for temporary tables
  93. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  94. InnoDB: File './ibtmp1' size is now 12 MB.
  95. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  96. InnoDB: 32 non-redo rollback segment(s) are active.
  97. InnoDB: page_cleaner: 1000ms intended loop took 6645ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
  98. InnoDB: 5.7.13 started; log sequence number 117070116885
  99. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  100. InnoDB: FTS optimize thread exiting.
  101. InnoDB: Starting shutdown...
  102. InnoDB: Shutdown completed; log sequence number 117070117601
  103. 180712 15:23:12 completed OK!

 3、开始恢复

  1. [root@localhost lib]# innobackupex  --defaults-file=/etc/my.cnf --copy-back --rsync /var/lib/mysqlsource
  2. 180712 15:23:50 innobackupex: Starting the copy-back operation
  3.  
  4. IMPORTANT: Please check that the copy-back run completes successfully.
  5.            At the end of a successful copy-back run innobackupex
  6.            prints "completed OK!".
  7.  
  8. innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
  9. 180712 15:23:50 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
  10. 180712 15:23:52 [01]        ...done
  11. 180712 15:23:53 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1
  12. 180712 15:23:55 [01]        ...done
  13. 180712 15:23:55 [01] Copying ibdata1 to /var/lib/mysql/ibdata1
  14. 180712 15:23:59 [01]        ...done
  15. 180712 15:23:59 [01] Copying ./2018712013001.bz.tar.gz to /var/lib/mysql/2018712013001.bz.tar.gz
  16. 180712 15:25:02 [01]        ...done
  17. 180712 15:25:03 [01] Copying ./vehicle_gps/tb_bas_gps_vendor.ibd to /var/lib/mysql/vehicle_gps/tb_bas_gps_vendor.ibd
  18. 180712 15:25:03 [01]        ...done
  19. 180712 15:25:03 [01] Copying ./vehicle_gps/tb_gps_info.ibd to /var/lib/mysql/vehicle_gps/tb_gps_info.ibd
  20. 180712 15:25:03 [01]        ...done
  21. 180712 15:25:03 [01] Copying ./vehicle_gps/tb_gps_alarm_info.ibd to /var/lib/mysql/vehicle_gps/tb_gps_alarm_info.ibd
  22. 180712 15:25:03 [01]        ...done
  23. 180712 15:40:55 [01]        ...done
  24. 180712 15:40:55 [01] Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
  25. 180712 15:40:55 [01]        ...done
  26. 180712 15:40:55 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
  27. 180712 15:40:55 [01]        ...done
  28. 180712 15:40:55 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
  29. 180712 15:40:55 [01]        ...done
  30. 180712 15:40:55 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
  31. 180712 15:40:55 [01]        ...done
  32. 180712 15:40:55 completed OK!

 

4、更改mysql目录属性

[root@localhost lib]# chown -R mysql.mysql mysql

 5、启动mysql

  1. [root@localhost mysql]# service mysqld start
  2. Redirecting to /bin/systemctl start  mysqld.service
  3. [root@localhost mysql]# service mysqld status
  4. Redirecting to /bin/systemctl status  mysqld.service
  5. ● mysqld.service - MySQL Server
  6.    Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  7.    Active: active (running) since Thu 2018-07-12 15:42:17 CST; 10s ago
  8.      Docs: man:mysqld(8)
  9.            http://dev.mysql.com/doc/refman/en/using-systemd.html
  10.   Process: 127987 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  11.   Process: 127908 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  12.  Main PID: 127995 (mysqld)
  13.    CGroup: /system.slice/mysqld.service
  14.            └─127995 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
  15.  
  16. Jul 12 15:41:40 localhost.localdomain systemd[1]: Starting MySQL Server...
  17. Jul 12 15:42:17 localhost.localdomain systemd[1]: Started MySQL Server.

 扩展阅读：

转载于:https://www.cnblogs.com/growithus/p/11012170.html

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/522292
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号