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


  8. 查看数据库状态

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

  15. 应用日志

  16. [root@my01 db]# innobackupex --apply-log /data/db/xtbakup/2018-03-20_16-02-00/
  17. 180320 16:01:55 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!".

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

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

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

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

  15. mysql> select * from t1;
  16. +------+------+
  17. | id | name |
  18. +------+------+
  19. | 1 | aaa |
  20. | 2 | bbb |
  21. | 3 | ccc |
  22. | 4 | ddd |
  23. +------+------+
  24. 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

  5. [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/

  6. [root@my01 incdata]# ls
  7. 2018-03-20_16-04-16
  8. [root@my01 incdata]# cd 2018-03-20_16-04-16/
  9. [root@my01 2018-03-20_16-04-16]# ls
  10. backup-my.cnf ibdata1.delta mysql scott test xtrabackup_checkpoints xtrabackup_logfile
  11. ib_buffer_pool ibdata1.meta performance_schema sys xtrabackup_binlog_info xtrabackup_info
  12. [root@my01 2018-03-20_16-04-16]# more xtrabackup_checkpoints
  13. backup_type = incremental
  14. from_lsn = 9251193
  15. to_lsn = 9257319
  16. last_lsn = 9257328
  17. compact = 0
  18. 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

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

  18. 增量备份2

  19. [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)

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

点击(此处)折叠或打开

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

  2. [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

  3. 将第二次的增量备份添加到全备份(注意:不添加redo-only)
  4. *****************************注意******************************
  5. ***************************************************************
  6. 做增量备份还原时,最后一次的增量备份添加到全备中时不添加redo-only参数
  7. ***************************************************************

  8. [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

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

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

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

  15. innobackupex version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
  16. xtrabackup: cd to /data/db/xtbakup/2018-03-20_16-02-00/
  17. xtrabackup: This target seems to be already prepared.
  18. InnoDB: Number of pools: 1
  19. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
  20. xtrabackup: using the following InnoDB configuration for recovery:
  21. xtrabackup: innodb_data_home_dir = .
  22. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  23. xtrabackup: innodb_log_group_home_dir = .
  24. xtrabackup: innodb_log_files_in_group = 2
  25. xtrabackup: innodb_log_file_size = 50331648
  26. xtrabackup: using the following InnoDB configuration for recovery:
  27. xtrabackup: innodb_data_home_dir = .
  28. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
  29. xtrabackup: innodb_log_group_home_dir = .
  30. xtrabackup: innodb_log_files_in_group = 2
  31. xtrabackup: innodb_log_file_size = 50331648
  32. xtrabackup: Starting InnoDB instance for recovery.
  33. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  34. InnoDB: PUNCH HOLE support available
  35. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  36. InnoDB: Uses event mutexes
  37. InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
  38. InnoDB: Compressed tables use zlib 1.2.3
  39. InnoDB: Number of pools: 1
  40. InnoDB: Using CPU crc32 instructions
  41. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  42. InnoDB: Completed initialization of buffer pool
  43. InnoDB: page_cleaner coordinator priority: -20
  44. InnoDB: Highest supported file format is Barracuda.
  45. InnoDB: Removed temporary tablespace data file: "ibtmp1"
  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: 5.7.13 started; log sequence number 9262120
  52. InnoDB: xtrabackup: Last MySQL binlog file position 1842, file name mysql-bin.000001

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

  87. 恢复数据

  88. [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)

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

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

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

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

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

  37. Database changed
  38. mysql> show tables;
  39. +----------------+
  40. | Tables_in_test |
  41. +----------------+
  42. | t |
  43. | t1 |
  44. | test |
  45. +----------------+
  46. 3 rows in set (0.00 sec)

  47. mysql> select * from t1;
  48. +------+------+
  49. | id | name |
  50. +------+------+
  51. | 1 | aaa |
  52. | 2 | bbb |
  53. | 3 | ccc |
  54. | 4 | ddd |
  55. | 101 | aaa |
  56. | 102 | bbb |
  57. | 103 | ccc |
  58. | 104 | ddd |
  59. +------+------+
  60. 8 rows in set (0.00 sec)

网站标题:MySQL增量备份之xtrbackup
当前路径:http://pwwzsj.com/article/jgeecp.html