绵阳梓潼网站制作公司

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
标题来源：http://myzitong.com/article/jgeecp.html

其他资讯