咨询热线 ：
028-86922220
×
01
关于我们

公司简介

发展历程

02
服务项目

高端网站建设

微信开发

APP开发

网络营销服务

电商网站定制

生物医药网站建设

外贸网站建设

教育培训网站建设

03
小程序开发

小程序开发

功能应用

客户案例

04
案例展示

网站建设案例

小程序案例

电商平台案例

APP案例

系统平台案例

05
建站动态

网站建设

网站设计

常见问题

小程序

06
联系我们

公司地址

人才招聘

地址：成都市太升南路288号锦天国际A幢1002号

电话：028-86922220

028-86922220

重庆分公司，新征程启航

为企业提供网站建设、域名注册、服务器等服务

MySQL增量备份之xtrbackup

一、软件版本

点击(此处)折叠或打开

成都创新互联成立与2013年，先为青阳等服务建站，青阳等地企业，进行企业商务咨询服务。为青阳企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

  1. 平台：Centos 7
  2. 数据库版本：MySQL 5.7
  3. xtrabackup 版本：xtrabackup version 2.4.8
二、安装方式：二进制解压安装

点击(此处)折叠或打开

  1. [root@my01 xtrabackup]# tar zxvf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz
  2. [root@my01 xtrabackup]# mv percona-xtrabackup-2.4.8-Linux-x86_64 /usr/local/xtrabackup
  3. 添加环境变量
  4. export PATH=$PATH:/usr/local/xtrabackup/bin
三、创建备份用户

点击(此处)折叠或打开

  1. mysql> create user xtbakup@'localhost' identified by 'oracle';
  2. mysql> grant reload,process,lock tables,replication client on *.* to xtbakup@localhost;
四、执行全库备份

点击(此处)折叠或打开

  1. [root@my01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock /data/db/xtbakup
五、模拟删库并进行恢复

点击(此处)折叠或打开

  1. [root@my01 ~]# service mysql stop
  2. Shutting down MySQL.. 
  3. [root@my01 db]# ls
  4. mysql xtbakup
  5. [root@my01 db]# mv mysql/ mysql_bak/
  6. [root@my01 db]# ls
  7. mysql_bak xtbakup


  10. 查看数据库状态

  12. [root@my01 ~]# service mysql status
  13.   MySQL is not running
  14. [root@my01 ~]# service mysql start
  15. Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
  16. 2018-03-20T03:26:56.919210Z mysqld_safe Directory '/data/db/mysql/1221' for UNIX socket file don't exists.
  17.  ERROR! The server quit without updating PID file (/data/db/mysql/1221/my01.pid).

  19. 应用日志

  21. [root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
  22. 180320 16:01:55 innobackupex: Starting the apply-log operation

  24. IMPORTANT: Please check that the apply-log run completes successfully.
  25.            At the end of a successful apply-log run innobackupex
  26.            prints "completed OK!".

  28. innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
  29. xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
  30. xtrabackup: This target seems to be not prepared yet.
  31. InnoDB: Number of pools: 1
  32. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9239084)
  33. xtrabackup: using the following InnoDB configuration for recovery:
  34. xtrabackup: innodb_data_home_dir = .
  35. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  36. xtrabackup: innodb_log_group_home_dir = .
  37. xtrabackup: innodb_log_files_in_group = 1
  38. xtrabackup: innodb_log_file_size = 8388608
  39. xtrabackup: using the following InnoDB configuration for recovery:
  40. xtrabackup: innodb_data_home_dir = .
  41. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  42. xtrabackup: innodb_log_group_home_dir = .
  43. xtrabackup: innodb_log_files_in_group = 1
  44. xtrabackup: innodb_log_file_size = 8388608
  45. xtrabackup: Starting InnoDB instance for recovery.
  46. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  47. InnoDB: PUNCH HOLE support available
  48. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  49. InnoDB: Uses event mutexes
  50. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  51. InnoDB: Compressed tables use zlib 1.2.3
  52. InnoDB: Number of pools: 1
  53. InnoDB: Using CPU crc32 instructions
  54. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  55. InnoDB: Completed initialization of buffer pool
  56. InnoDB: page_cleaner coordinator priority: -20
  57. InnoDB: Highest supported file format is Barracuda.
  58. InnoDB: Log scan progressed past the checkpoint lsn 9239084
  59. InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
  60. InnoDB: Doing recovery: scanned up to log sequence number 9239093 (0%)
  61. InnoDB: Database was not shutdown normally!
  62. InnoDB: Starting crash recovery.
  63. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
  64. InnoDB: Creating shared tablespace for temporary tables
  65. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  66. InnoDB: File './ibtmp1' size is now 12 MB.
  67. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  68. InnoDB: 32 non-redo rollback segment(s) are active.
  69. InnoDB: 5.7.13 started; log sequence number 9239093
  70. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008

  72. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  73. InnoDB: FTS optimize thread exiting.
  74. InnoDB: Starting shutdown...
  75. InnoDB: Shutdown completed; log sequence number 9239112
  76. InnoDB: Number of pools: 1
  77. xtrabackup: using the following InnoDB configuration for recovery:
  78. xtrabackup: innodb_data_home_dir = .
  79. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  80. xtrabackup: innodb_log_group_home_dir = .
  81. xtrabackup: innodb_log_files_in_group = 2
  82. xtrabackup: innodb_log_file_size = 50331648
  83. InnoDB: PUNCH HOLE support available
  84. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  85. InnoDB: Uses event mutexes
  86. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  87. InnoDB: Compressed tables use zlib 1.2.3
  88. InnoDB: Number of pools: 1
  89. InnoDB: Using CPU crc32 instructions
  90. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  91. InnoDB: Completed initialization of buffer pool
  92. InnoDB: page_cleaner coordinator priority: -20
  93. InnoDB: Setting log file ./ib_logfile101 size to 48 MB
  94. InnoDB: Setting log file ./ib_logfile1 size to 48 MB
  95. InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  96. InnoDB: New log files created, LSN=9239112
  97. InnoDB: Highest supported file format is Barracuda.
  98. InnoDB: Log scan progressed past the checkpoint lsn 9239564
  99. InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
  100. InnoDB: Doing recovery: scanned up to log sequence number 9239573 (0%)
  101. InnoDB: Database was not shutdown normally!
  102. InnoDB: Starting crash recovery.
  103. InnoDB: xtrabackup: Last MySQL binlog file position 434, file name mysql-bin.000008
  104. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  105. InnoDB: Creating shared tablespace for temporary tables
  106. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  107. InnoDB: File './ibtmp1

恢复


[root@my01 2018-03-20_16-02-00]# innobackupex  --defaults-file=/etc/my.cnf --copy-back --rsync /data/db/xtbakup/2018-03-20_16-02-00/



[root@my01 ~]# cd /data/
[root@my01 data]# ls
db  kafka-logs  zookeeper
[root@my01 data]# cd db/
[root@my01 db]# ls
mysql  mysql_bak  xtbakup
[root@my01 db]# chown -R mysql.mysql mysql
[root@my01 db]# ll
total 0
drwxr-x---. 3 mysql mysql 18 Mar 20 11:29 mysql
drwxr-xr-x. 3 mysql mysql 18 Aug 18  2017 mysql_bak
drwxr-xr-x. 3 root  root  33 Mar 20 11:03 xtbakup
[root@my01 db]# service mysql start
Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
 SUCCESS! 

[root@my01 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)


Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
| test           |
+----------------+
2 rows in set (0.00 sec)


mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|    81920 |
+----------+
1 row in set (0.11 sec)


六、创建表插入数据

点击(此处)折叠或打开

  1. mysql> create table t1 (id int,name varchar(40));
  2. Query OK, 0 rows affected (0.04 sec)

  4. mysql> show tables;
  5. +----------------+
  6. | Tables_in_test |
  7. +----------------+
  8. | t |
  9. | t1 |
  10. | test |
  11. +----------------+
  12. 3 rows in set (0.00 sec)

  14. mysql> insert into t1 values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
  15. Query OK, 4 rows affected (0.01 sec)
  16. Records: 4 Duplicates: 0 Warnings: 0

  18. mysql> select * from t1;
  19. +------+------+
  20. | id | name |
  21. +------+------+
  22. | 1 | aaa |
  23. | 2 | bbb |
  24. | 3 | ccc |
  25. | 4 | ddd |
  26. +------+------+
  27. 4 rows in set (0.00 sec)

七、第一次增量备份

点击(此处)折叠或打开

  1. [root@my01 db]# ls
  2. incdata mysql mysql_bak xtbakup
  3. [root@my01 incdata]# pwd
  4. /data/db/incdata

  6. [root@my01 incdata]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/xtbakup/2018-03-20_16-02-00/

  8. [root@my01 incdata]# ls
  9. 2018-03-20_16-04-16
  10. [root@my01 incdata]# cd 2018-03-20_16-04-16/
  11. [root@my01 2018-03-20_16-04-16]# ls
  12. backup-my.cnf ibdata1.delta mysql scott test xtrabackup_checkpoints xtrabackup_logfile
  13. ib_buffer_pool ibdata1.meta performance_schema sys xtrabackup_binlog_info xtrabackup_info
  14. [root@my01 2018-03-20_16-04-16]# more xtrabackup_checkpoints
  15. backup_type = incremental
  16. from_lsn = 9251193
  17. to_lsn = 9257319
  18. last_lsn = 9257328
  19. compact = 0
  20. recover_binlog_info = 0
八、第二次增量备份

点击(此处)折叠或打开

  1. mysql> insert into t1 values (101,'aaa'),(102,'bbb'),(103,'ccc'),(104,'ddd');
  2. Query OK, 4 rows affected (0.02 sec)
  3. Records: 4 Duplicates: 0 Warnings: 0

  5. mysql> select * from t1;
  6. +------+------+
  7. | id | name |
  8. +------+------+
  9. | 1 | aaa |
  10. | 2 | bbb |
  11. | 3 | ccc |
  12. | 4 | ddd |
  13. | 101 | aaa |
  14. | 102 | bbb |
  15. | 103 | ccc |
  16. | 104 | ddd |
  17. +------+------+
  18. 8 rows in set (0.00 sec)

  20. 增量备份2

  22. [root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --user=xtbakup --password=oracle --socket=/data/db/mysql/1221/mysql.sock --incremental /data/db/incdata --incremental-basedir=/data/db/incdata/2018-03-20_16-04-16
九、模拟数据丢失

点击(此处)折叠或打开

  1. mysql> drop table t1;
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> show tables;
  5. +----------------+
  6. | Tables_in_test |
  7. +----------------+
  8. | t |
  9. | test |
  10. +----------------+
  11. 2 rows in set (0.00 sec)
十、恢复数据

点击(此处)折叠或打开

  1. 将第一次的增量备份添加到全备份

  3. [root@my01 db]# innobackupex --apply-log --redo-only /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-04-16

  5. 将第二次的增量备份添加到全备份（注意：不添加redo-only）
  6. *****************************注意******************************
  7. ***************************************************************
  8. 做增量备份还原时，最后一次的增量备份添加到全备中时不添加redo-only参数
  9. ***************************************************************

  11. [root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/ --incremental-dir=/data/db/incdata/2018-03-20_16-09-04

  13. 把所有的备份和到一起进行一次apply-log

  15. [root@my01 xtbakup]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
  16. 180320 16:17:37 innobackupex: Starting the apply-log operation

  18. IMPORTANT: Please check that the apply-log run completes successfully.
  19.            At the end of a successful apply-log run innobackupex
  20.            prints "completed OK!".

  22. innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
  23. xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
  24. xtrabackup: This target seems to be already prepared.
  25. InnoDB: Number of pools: 1
  26. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
  27. xtrabackup: using the following InnoDB configuration for recovery:
  28. xtrabackup: innodb_data_home_dir = .
  29. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  30. xtrabackup: innodb_log_group_home_dir = .
  31. xtrabackup: innodb_log_files_in_group = 2
  32. xtrabackup: innodb_log_file_size = 50331648
  33. xtrabackup: using the following InnoDB configuration for recovery:
  34. xtrabackup: innodb_data_home_dir = .
  35. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  36. xtrabackup: innodb_log_group_home_dir = .
  37. xtrabackup: innodb_log_files_in_group = 2
  38. xtrabackup: innodb_log_file_size = 50331648
  39. xtrabackup: Starting InnoDB instance for recovery.
  40. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  41. InnoDB: PUNCH HOLE support available
  42. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  43. InnoDB: Uses event mutexes
  44. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  45. InnoDB: Compressed tables use zlib 1.2.3
  46. InnoDB: Number of pools: 1
  47. InnoDB: Using CPU crc32 instructions
  48. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  49. InnoDB: Completed initialization of buffer pool
  50. InnoDB: page_cleaner coordinator priority: -20
  51. InnoDB: Highest supported file format is Barracuda.
  52. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  53. InnoDB: Creating shared tablespace for temporary tables
  54. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  55. InnoDB: File './ibtmp1' size is now 12 MB.
  56. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  57. InnoDB: 32 non-redo rollback segment(s) are active.
  58. InnoDB: 5.7.13 started; log sequence number 9262120
  59. InnoDB: xtrabackup: Last MySQL binlog file position 1842, file name mysql-bin.000001

  61. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  62. InnoDB: FTS optimize thread exiting.
  63. InnoDB: Starting shutdown...
  64. InnoDB: Shutdown completed; log sequence number 9262139
  65. InnoDB: Number of pools: 1
  66. xtrabackup: using the following InnoDB configuration for recovery:
  67. xtrabackup: innodb_data_home_dir = .
  68. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  69. xtrabackup: innodb_log_group_home_dir = .
  70. xtrabackup: innodb_log_files_in_group = 2
  71. xtrabackup: innodb_log_file_size = 50331648
  72. InnoDB: PUNCH HOLE support available
  73. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  74. InnoDB: Uses event mutexes
  75. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  76. InnoDB: Compressed tables use zlib 1.2.3
  77. InnoDB: Number of pools: 1
  78. InnoDB: Using CPU crc32 instructions
  79. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  80. InnoDB: Completed initialization of buffer pool
  81. InnoDB: page_cleaner coordinator priority: -20
  82. InnoDB: Highest supported file format is Barracuda.
  83. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  84. InnoDB: Creating shared tablespace for temporary tables
  85. InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
  86. InnoDB: File './ibtmp1' size is now 12 MB.
  87. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
  88. InnoDB: 32 non-redo rollback segment(s) are active.
  89. InnoDB: 5.7.13 started; log sequence number 9262139
  90. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  91. InnoDB: FTS optimize thread exiting.
  92. InnoDB: Starting shutdown...
  93. InnoDB: Shutdown completed; log sequence number 9262158
  94. 180320 16:17:40 completed

  96. 恢复数据

  98. [root@my01 db]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/db/xtbakup/2018-03-20_16-02-00/

十一、验证数据

点击(此处)折叠或打开

  1. [root@my01 ~]# service mysql status
  2.  ERROR! MySQL is not running
  3. [root@my01 ~]# service mysql start
  4. Starting MySQL.Logging to '/data/db/mysql/1221/mariadb.log'.
  5.  SUCCESS!
  6. [root@my01 ~]#
  7. [root@my01 ~]#
  8. [root@my01 ~]#
  9. [root@my01 ~]# mysql -u root -p
  10. Enter password:
  11. Welcome to the MySQL monitor. Commands end with ; or \g.
  12. Your MySQL connection id is 3
  13. Server version: 5.7.19-log MySQL Community Server (GPL)

  15. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

  17. Oracle is a registered trademark of Oracle Corporation and/or its
  18. affiliates. Other names may be trademarks of their respective
  19. owners.

  21. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  23. mysql>
  24. mysql>
  25. mysql>
  26. mysql> show databases;
  27. +--------------------+
  28. | Database |
  29. +--------------------+
  30. | information_schema |
  31. | mysql |
  32. | performance_schema |
  33. | scott |
  34. | sys |
  35. | test |
  36. +--------------------+
  37. 6 rows in set (0.01 sec)

  39. mysql> use test;
  40. Reading table information for completion of table and column names
  41. You can turn off this feature to get a quicker startup with -A

  43. Database changed
  44. mysql> show tables;
  45. +----------------+
  46. | Tables_in_test |
  47. +----------------+
  48. | t |
  49. | t1 |
  50. | test |
  51. +----------------+
  52. 3 rows in set (0.00 sec)

  54. mysql> select * from t1;
  55. +------+------+
  56. | id | name |
  57. +------+------+
  58. | 1 | aaa |
  59. | 2 | bbb |
  60. | 3 | ccc |
  61. | 4 | ddd |
  62. | 101 | aaa |
  63. | 102 | bbb |
  64. | 103 | ccc |
  65. | 104 | ddd |
  66. +------+------+
  67. 8 rows in set (0.00 sec)

网站题目：MySQL增量备份之xtrbackup
网站URL：http://cqcxhl.cn/article/jgeecp.html

其他资讯

分享至：
关于我们
公司简介 发展历程
服务项目
高端网站建设 小程序开发 APP开发 网络营销
建站知识
网站建设 网站设计 网站制作 小程序
联系我们
公司地址 人才招聘
成都 重庆
电话：028-86922220
地址：成都市太升南路288号锦天国际A幢1002号
电话：028-86922220
地址：重庆市南岸区弹子石腾龙大道58号2栋21-6
版权所有：成都创新互联科技有限公司重庆分公司 渝ICP备2021005571号-4
在线咨询
服务热线
服务热线：028-86922220
TOP