资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

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://cdkjz.cn/article/jgeecp.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
重庆教育培训网站建设凯亚自动化控制成都网站设计成都网络推广成都微信小程序开发南京护栏钻孔机普格博源建站做网站成都网站设计内江智速网站建设

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图