ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > Xtrabackup增量备份实战

Xtrabackup增量备份实战

原创 MySQL 作者：StevenBeijing 时间：2018-06-12 11:25:59 0 删除 编辑

1、创建MySQL备份用户

点击( 此处 )折叠或打开

  1. mysql > create user 'backup' @ '%' identified by 'mysql' ;

  2. Query OK , 0 rows affected ( . 00 sec )


  4. mysql > grant reload , lock tables , replication client , create tablespace , process , super , create , insert , select on * . * to 'backup' @ '%' ;

  5. Query OK , 0 rows affected ( . 00 sec )


  7. mysql > flush privileges ;

  8. Query OK , 0 rows affected ( . 00 sec )

2、查看测试表

点击( 此处 )折叠或打开

  1. mysql > select * from test ;

  2. Empty set ( . 00 sec )

3、对数据库进行全备

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf -- user = backup -- password = mysql --port = 3306 / backup / full_backup /

  2. 180612 10:30:29 innobackupex: Starting the backup operation


  4. IMPORTANT: Please check that the backup run completes successfully .

  5.            At the end of a successful backup run innobackupex

  6.            prints " completed OK! " .


  8. 180612 10:30:29  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysqld.sock' as 'backup' ( using password : YES ) .

  9. 180612 10:30:29  version_check Connected to MySQL server

  10. 180612 10:30:29  version_check Executing a version check against the server . . .

  11. 180612 10:30:29  version_check Done .

  12. 180612 10:30:29 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock

  13. Using server version 5 . 7 . 19- log

  14. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  15. xtrabackup: uses posix_fadvise ( ) .

  16. xtrabackup: cd to / mysql / data

  17. xtrabackup: open files limit requested 65535 , set to 65535

  18. xtrabackup: using the following InnoDB configuration:

  19. xtrabackup:   innodb_data_home_dir = .

  20. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

  21. xtrabackup:   innodb_log_group_home_dir = . /

  22. xtrabackup:   innodb_log_files_in_group = 2

  23. xtrabackup:   innodb_log_file_size = 67108864

  24. xtrabackup: using O_DIRECT

  25. InnoDB: Number of pools: 1

  26. Warning: " test . test " uses engine " MEMORY " and will not be backed up .

  27. 180612 10:30:30 > > log scanned up to ( 1540210043 )

  28. xtrabackup: Generating a list of tablespaces

  29. InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0

  30. 180612 10:30:30 [ 01 ] Copying . / ibdata1 to / backup / full_backup / 2018-06-12_10-30-29 / ibdata1

  31. 180612 10:30:31 > > log scanned up to ( 1540210043 )

  32. 180612 10:30:32 > > log scanned up to ( 1540210043 )

  33. 180612 10:30:33 > > log scanned up to ( 1540210043 )

  34. 180612 10:30:34 > > log scanned up to ( 1540210043 )

  35. 180612 10:30:34 [ 01 ] . . . done

  36. 180612 10:30:34 [ 01 ] Copying . / test / t_user_info . ibd to / backup / full_backup / 2018-06-12_10-30-29 / test / t_user_info . ibd

  37. 180612 10:30:39 Finished backing up non-InnoDB tables and files

  38. 180612 10:30:39 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / xtrabackup_binlog_info

  39. 180612 10:30:39 [ 00 ] . . . done

  40. 180612 10:30:39 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .

  41. xtrabackup: The latest check point ( for incremental ) : '1540210034'

  42. xtrabackup: Stopping log copying thread .

  43. . 180612 10:30:40 > > log scanned up to ( 1540210043 )


  45. 180612 10:30:40 Executing UNLOCK TABLES

  46. 180612 10:30:40 All tables unlocked

  47. 180612 10:30:40 [ 00 ] Copying ib_buffer_pool to / backup / full_backup / 2018-06-12_10-30-29 / ib_buffer_pool

  48. 180612 10:30:40 [ 00 ] . . . done

  49. 180612 10:30:40 Backup created in directory '/backup/full_backup/2018-06-12_10-30-29/'

  50. MySQL binlog position : filename 'mysql-bin.000012' , position '1645'

  51. 180612 10:30:40 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / backup-my . cnf

  52. 180612 10:30:40 [ 00 ] . . . done

  53. 180612 10:30:40 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / xtrabackup_info

  54. 180612 10:30:40 [ 00 ] . . . done

  55. xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .

  56. 180612 10:30:40 completed OK!

查看全量备份信息：

点击( 此处 )折叠或打开

  1. [ root @ mysql5 2018-06-12_10-30-29 ] # cat xtrabackup_info

  2. uuid = 99098d6e-6de8-11e8-9520-080027240aa4

  3. name =

  4. tool_name = innobackupex

  5. tool_command = --defaults-file = / etc / my . cnf -- user = backup -- password = . . . --port = 3306 / backup / full_backup /

  6. tool_version = 2 . 4 . 9

  7. ibbackup_version = 2 . 4 . 9

  8. server_version = 5 . 7 . 19- log

  9. start_time = 2018-06-12 10:30:29

  10. end_time = 2018-06-12 10:30:40

  11. lock_time = 0

  12. binlog_pos = filename 'mysql-bin.000012' , position '1645'

  13. innodb_from_lsn = 0

  14. innodb_to_lsn = 1540210034

  15. partial = N

  16. incremental = N

  17. format = file

  18. compact = N

  19. compressed = N

  20. encrypted = N

4、对测试表插入数据

点击( 此处 )折叠或打开

  1. mysql > insert into test values ( 1 , '0' , 30 ) ;

  2. Query OK , 1 row affected ( . 05 sec )


  4. mysql > select * from test ;

  5. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  6. | id | user_type | code |

  7. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  8. | 1 | 0 | 30 |

  9. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  10. 1 row in set ( . 00 sec )

5、以之前的全备做基础，做第一次增量备份

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf -- user = backup -- password = mysql --port = 3306 --incremental / backup / incr_backup / --incremental-basedir = / backup / full_backup / 2018-06-12_10-30-29 /

  2. 180612 10:40:49 innobackupex: Starting the backup operation


  4. IMPORTANT: Please check that the backup run completes successfully .

  5.            At the end of a successful backup run innobackupex

  6.            prints " completed OK! " .


  8. 180612 10:40:49  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysqld.sock' as 'backup' ( using password : YES ) .

  9. 180612 10:40:49  version_check Connected to MySQL server

  10. 180612 10:40:49  version_check Executing a version check against the server . . .

  11. 180612 10:40:49  version_check Done .

  12. 180612 10:40:49 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock

  13. Using server version 5 . 7 . 19- log

  14. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  15. incremental backup from 1540210034 is enabled .

  16. xtrabackup: uses posix_fadvise ( ) .

  17. xtrabackup: cd to / mysql / data

  18. xtrabackup: open files limit requested 65535 , set to 65535

  19. xtrabackup: using the following InnoDB configuration:

  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 = 2

  24. xtrabackup:   innodb_log_file_size = 67108864

  25. xtrabackup: using O_DIRECT

  26. InnoDB: Number of pools: 1

  27. Warning: " test . test " uses engine " MEMORY " and will not be backed up .

  28. 180612 10:40:49 > > log scanned up to ( 1540210043 )

  29. xtrabackup: Generating a list of tablespaces

  30. InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0

  31. 180612 10:40:49 [ 01 ] Copying . / ibdata1 to / backup / incr_backup / 2018-06-12_10-40-49 / ibdata1 . delta

  32. 180612 10:40:49 [ 01 ] . . . done

  33. 180612 10:40:49 [ 01 ] Copying . / test / t_user_info . ibd to / backup / incr_backup / 2018-06-12_10-40-49 / test / t_user_info . ibd . delta

  34. 180612 10:40:56 Finished backing up non-InnoDB tables and files

  35. 180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / xtrabackup_binlog_info

  36. 180612 10:40:56 [ 00 ] . . . done

  37. 180612 10:40:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .

  38. xtrabackup: The latest check point ( for incremental ) : '1540210034'

  39. xtrabackup: Stopping log copying thread .

  40. . 180612 10:40:56 > > log scanned up to ( 1540210043 )

  41. 180612 10:40:56 Executing UNLOCK TABLES

  42. 180612 10:40:56 All tables unlocked

  43. 180612 10:40:56 [ 00 ] Copying ib_buffer_pool to / backup / incr_backup / 2018-06-12_10-40-49 / ib_buffer_pool

  44. 180612 10:40:56 [ 00 ] . . . done

  45. 180612 10:40:56 Backup created in directory '/backup/incr_backup/2018-06-12_10-40-49/'

  46. MySQL binlog position : filename 'mysql-bin.000012' , position '1952'

  47. 180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / backup-my . cnf

  48. 180612 10:40:56 [ 00 ] . . . done

  49. 180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / xtrabackup_info

  50. 180612 10:40:56 [ 00 ] . . . done

  51. xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .

  52. 180612 10:40:56 completed OK!

查看第一次增量备份信息：

点击( 此处 )折叠或打开

  1. [ root @ mysql5 2018-06-12_10-40-49 ] # cat xtrabackup_info

  2. uuid = 08769a08-6dea-11e8-9520-080027240aa4

  3. name =

  4. tool_name = innobackupex

  5. tool_command = --defaults-file = / etc / my . cnf -- user = backup -- password = . . . --port = 3306 --incremental / backup / incr_backup / --incremental-basedir = / backup / full_backup / 2018-06-12_10-30-29 /

  6. tool_version = 2 . 4 . 9

  7. ibbackup_version = 2 . 4 . 9

  8. server_version = 5 . 7 . 19- log

  9. start_time = 2018-06-12 10:40:49

  10. end_time = 2018-06-12 10:40:56

  11. lock_time = 0

  12. binlog_pos = filename 'mysql-bin.000012' , position '1952'

  13. innodb_from_lsn = 1540210034

  14. innodb_to_lsn = 1540210034

  15. partial = N

  16. incremental = Y

  17. format = file

  18. compact = N

  19. compressed = N

  20. encrypted = N

6、向测试表插入数据

点击( 此处 )折叠或打开

  1. mysql > insert into test values ( 2 , '0' , 40 ) ;

  2. Query OK , 1 row affected ( . 02 sec )


  4. mysql > select * from test ;

  5. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  6. | id | user_type | code |

  7. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  8. | 1 | 0 | 30 |

  9. | 2 | 0 | 40 |

  10. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  11. 2 rows in set ( . 00 sec )

7、以第一次增量备份作为基础，做第二次增量备份

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf -- user = backup -- password = mysql --port = 3306 --incremental / backup / incr_backup / --incremental-basedir = / backup / incr_backup / 2018-06-12_10-40-49 /

  2. 180612 10:47:16 innobackupex: Starting the backup operation


  4. IMPORTANT: Please check that the backup run completes successfully .

  5.            At the end of a successful backup run innobackupex

  6.            prints " completed OK! " .


  8. 180612 10:47:16  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysqld.sock' as 'backup' ( using password : YES ) .

  9. 180612 10:47:16  version_check Connected to MySQL server

  10. 180612 10:47:16  version_check Executing a version check against the server . . .

  11. 180612 10:47:16  version_check Done .

  12. 180612 10:47:16 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock

  13. Using server version 5 . 7 . 19- log

  14. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  15. incremental backup from 1540210034 is enabled .

  16. xtrabackup: uses posix_fadvise ( ) .

  17. xtrabackup: cd to / mysql / data

  18. xtrabackup: open files limit requested 65535 , set to 65535

  19. xtrabackup: using the following InnoDB configuration:

  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 = 2

  24. xtrabackup:   innodb_log_file_size = 67108864

  25. xtrabackup: using O_DIRECT

  26. InnoDB: Number of pools: 1

  27. Warning: " test . test " uses engine " MEMORY " and will not be backed up .

  28. 180612 10:47:16 > > log scanned up to ( 1540210043 )

  29. xtrabackup: Generating a list of tablespaces

  30. InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0

  31. 180612 10:47:16 [ 01 ] Copying . / ibdata1 to / backup / incr_backup / 2018-06-12_10-47-16 / ibdata1 . delta

  32. 180612 10:47:16 [ 01 ] . . . done

  33. 180612 10:47:16 [ 01 ] Copying . / test / t_user_info . ibd to / backup / incr_backup / 2018-06-12_10-47-16 / test / t_user_info . ibd . delta

  34. 180612 10:47:16 [ 01 ] . . . done

  35. 180612 10:47:16 [ 01 ] Copying . / xcredit / tb_openapi . ibd to / backup / incr_backup / 2018-06-12_10-47-16 / xcredit / tb_openapi . ibd . delta

  36. 180612 10:47:16 [ 01 ] . . . done

  37. 180612 10:47:16 [ 01 ] Copying . / xcredit / tb_system_param . ibd to / backup / incr_backup / 2018-06-12_10-47-16 / xcredit / tb_system_param . ibd . delta

  38. 180612 10:47:23 [ 01 ] Copying . / performance_schema / replication_applier_configuration . frm to / backup / incr_backup / 2018-06-12_10-47-16 / performance_schema / replication_applier_configuration . frm

  39. 180612 10:47:23 [ 01 ] . . . done

  40. 180612 10:47:23 Finished backing up non-InnoDB tables and files

  41. 180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / xtrabackup_binlog_info

  42. 180612 10:47:23 [ 00 ] . . . done

  43. 180612 10:47:23 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .

  44. xtrabackup: The latest check point ( for incremental ) : '1540210034'

  45. xtrabackup: Stopping log copying thread .

  46. . 180612 10:47:23 > > log scanned up to ( 1540210043 )


  48. 180612 10:47:23 Executing UNLOCK TABLES

  49. 180612 10:47:23 All tables unlocked

  50. 180612 10:47:23 [ 00 ] Copying ib_buffer_pool to / backup / incr_backup / 2018-06-12_10-47-16 / ib_buffer_pool

  51. 180612 10:47:23 [ 00 ] . . . done

  52. 180612 10:47:23 Backup created in directory '/backup/incr_backup/2018-06-12_10-47-16/'

  53. MySQL binlog position : filename 'mysql-bin.000012' , position '2259'

  54. 180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / backup-my . cnf

  55. 180612 10:47:23 [ 00 ] . . . done

  56. 180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / xtrabackup_info

  57. 180612 10:47:23 [ 00 ] . . . done

  58. xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .

  59. 180612 10:47:23 completed OK!

查看第二次增量备份信息：

点击( 此处 )折叠或打开

  1. [ root @ mysql5 2018-06-12_10-47-16 ] # cat xtrabackup_info

  2. uuid = eefb0a37-6dea-11e8-9520-080027240aa4

  3. name =

  4. tool_name = innobackupex

  5. tool_command = --defaults-file = / etc / my . cnf -- user = backup -- password = . . . --port = 3306 --incremental / backup / incr_backup / --incremental-basedir = / backup / incr_backup / 2018-06-12_10-40-49 /

  6. tool_version = 2 . 4 . 9

  7. ibbackup_version = 2 . 4 . 9

  8. server_version = 5 . 7 . 19- log

  9. start_time = 2018-06-12 10:47:16

  10. end_time = 2018-06-12 10:47:23

  11. lock_time = 0

  12. binlog_pos = filename 'mysql-bin.000012' , position '2259'

  13. innodb_from_lsn = 1540210034

  14. innodb_to_lsn = 1540210034

  15. partial = N

  16. incremental = Y

  17. format = file

  18. compact = N

  19. compressed = N

  20. encrypted = N

8、再次向测试表插入数据

点击( 此处 )折叠或打开

  1. mysql > insert into test values ( 3 , '0' , 50 ) ;

  2. Query OK , 1 row affected ( . 02 sec )


  4. mysql > select * from test ;

  5. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  6. | id | user_type | code |

  7. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  8. | 1 | 0 | 30 |

  9. | 2 | 0 | 40 |

  10. | 3 | 0 | 50 |

  11. + - - - - - - + - - - - - - - - - - - + - - - - - - +

9、现在模式故障，删除data目录下所有文件
10、恢复前kill掉mysql进程
11、恢复基础备份（这里一定要加--redo-only参数，该参数的意思是只应用xrabackup日志中已经提交的事务数据，不回滚还未提交的数据。）

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --apply- log --redo-only / backup / full_backup / 2018-06-12_10-30-29 /

  2. 180612 11:01:18 innobackupex: Starting the apply- log operation


  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! " .


  8. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  9. xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /

  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 = ( 1540210034 )

  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 __sync_synchronize ( ) is used for memory barrier

  31. InnoDB: Compressed tables use zlib 1 . 2 . 3

  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 1540210034

  39. InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )

  40. InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )

  41. InnoDB: Database was not shutdown normally!

  42. InnoDB: Starting crash recovery .

  43. InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001

  44. InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001

  45. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  46. InnoDB: Starting shutdown . . .

  47. InnoDB: Shutdown completed ; log sequence number 1540210052

  48. InnoDB: Number of pools: 1

  49. 180612 11:01:20 completed OK!

12、将第一次增量备份恢复到全备：

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --apply- log --redo-only / backup / full_backup / 2018-06-12_10-30-29 / --incremental-dir = / backup / incr_backup / 2018-06-12_10-40-49 /

  2. 180612 11:04:06 innobackupex: Starting the apply- log operation


  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! " .


  8. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  9. incremental backup from 1540210034 is enabled .

  10. xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /

  11. xtrabackup: This target seems to be already prepared with --apply- log - only .

  12. InnoDB: Number of pools: 1

  13. xtrabackup: xtrabackup_logfile detected: size = 8388608 , start_lsn = ( 1540210034 )

  14. xtrabackup: using the following InnoDB configuration for recovery:

  15. xtrabackup:   innodb_data_home_dir = .

  16. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

  17. xtrabackup:   innodb_log_group_home_dir = / backup / incr_backup / 2018-06-12_10-40-49 /

  18. xtrabackup:   innodb_log_files_in_group = 1

  19. xtrabackup:   innodb_log_file_size = 8388608

  20. xtrabackup: Generating a list of tablespaces

  21. InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0

  22. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-40-49 / / ibdata1 . delta is 16384 bytes

  23. Applying / backup / incr_backup / 2018-06-12_10-40-49 / / ibdata1 . delta to . / ibdata1 . . .

  24. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-40-49 / / test / t_user_info . ibd . delta is 16384 bytes

  25. 180612 11:04:12 [ 00 ] Copying / backup / incr_backup / 2018-06-12_10-40-49 / / xtrabackup_info to . / xtrabackup_info

  26. 180612 11:04:12 [ 00 ] . . . done

  27. 180612 11:04:12 completed OK!

13、将第二次增量备份应用到全备上（恢复最后一个增量备份时，需要去掉--redo-only参数，回滚xtrabackup日志中那些还未提交的参数）

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --apply- log / backup / full_backup / 2018-06-12_10-30-29 / --incremental-dir = / backup / incr_backup / 2018-06-12_10-47-16 / 180612 11:07:12 innobackupex: Starting the apply- log operation


  3. IMPORTANT: Please check that the apply- log run completes successfully .

  4.            At the end of a successful apply- log run innobackupex

  5.            prints " completed OK! " .


  7. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  8. incremental backup from 1540210034 is enabled .

  9. xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /

  10. xtrabackup: This target seems to be already prepared with --apply- log - only .

  11. InnoDB: Number of pools: 1

  12. xtrabackup: xtrabackup_logfile detected: size = 8388608 , start_lsn = ( 1540210034 )

  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 = / backup / incr_backup / 2018-06-12_10-47-16 /

  17. xtrabackup:   innodb_log_files_in_group = 1

  18. xtrabackup:   innodb_log_file_size = 8388608

  19. xtrabackup: Generating a list of tablespaces

  20. InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0

  21. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / ibdata1 . delta is 16384 bytes

  22. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / ibdata1 . delta to . / ibdata1 . . .

  23. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / test / t_user_info . ibd . delta is 16384 bytes

  24. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / test / t_user_info . ibd . delta to . / test / t_user_info . ibd . . .

  25. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_generic_data . ibd . delta is 16384 bytes

  26. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_generic_data . ibd . delta to . / xcredit / tb_generic_data . ibd . . .

  27. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_resource . ibd . delta is 16384 bytes

  28. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_resource . ibd . delta to . / xcredit / tb_resource . ibd . . .

  29. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index . ibd . delta is 16384 bytes

  30. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index . ibd . delta to . / xcredit / tb_index . ibd . . .

  31. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role . ibd . delta is 16384 bytes

  32. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role . ibd . delta to . / xcredit / tb_role . ibd . . .

  33. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_offer_log . ibd . delta is 16384 bytes

  34. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_offer_log . ibd . delta to . / xcredit / tb_openapi_offer_log . ibd . . .

  35. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_acquire_log . ibd . delta is 16384 bytes

  36. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_acquire_log . ibd . delta to . / xcredit / tb_query_acquire_log . ibd . . .

  37. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_cert . ibd . delta is 16384 bytes

  38. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_cert . ibd . delta to . / xcredit / tb_cert . ibd . . .

  39. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_acquire_log . ibd . delta is 16384 bytes

  40. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_acquire_log . ibd . delta to . / xcredit / tb_openapi_acquire_log . ibd . . .

  41. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_system_param . ibd . delta is 16384 bytes

  42. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_system_param . ibd . delta to . / xcredit / tb_system_param . ibd . . .

  43. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_sync_job . ibd . delta is 16384 bytes

  44. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_sync_job . ibd . delta to . / xcredit / tb_sync_job . ibd . . .

  45. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_analyze_index . ibd . delta is 16384 bytes

  46. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_analyze_index . ibd . delta to . / xcredit / tb_analyze_index . ibd . . .

  47. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_data_history . ibd . delta is 16384 bytes

  48. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_data_history . ibd . delta to . / xcredit / tb_query_data_history . ibd . . .

  49. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_data_complaint . ibd . delta is 16384 bytes

  50. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_data_complaint . ibd . delta to . / xcredit / tb_data_complaint . ibd . . .

  51. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi . ibd . delta is 16384 bytes

  52. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi . ibd . delta to . / xcredit / tb_openapi . ibd . . .

  53. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role_resource . ibd . delta is 16384 bytes

  54. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role_resource . ibd . delta to . / xcredit / tb_role_resource . ibd . . .

  55. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_index_log . ibd . delta is 16384 bytes

  56. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_index_log . ibd . delta to . / xcredit / tb_query_index_log . ibd . . .

  57. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_message . ibd . delta is 16384 bytes

  58. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_message . ibd . delta to . / xcredit / tb_message . ibd . . .

  59. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_admin . ibd . delta is 16384 bytes

  60. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_admin . ibd . delta to . / xcredit / tb_admin . ibd . . .

  61. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_offer_log . ibd . delta is 16384 bytes

  62. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_offer_log . ibd . delta to . / xcredit / tb_query_offer_log . ibd . . .

  63. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index_log . ibd . delta is 16384 bytes

  64. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index_log . ibd . delta to . / xcredit / tb_index_log . ibd . . .

  65. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / sys / sys_config . ibd . delta is 16384 bytes

  66. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / sys / sys_config . ibd . delta to . / sys / sys_config . ibd . . .

  67. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / plugin . ibd . delta is 16384 bytes

  68. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / plugin . ibd . delta to . / mysql / plugin . ibd . . .

  69. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_topic . ibd . delta is 16384 bytes

  70. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_topic . ibd . delta to . / mysql / help_topic . ibd . . .

  71. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / engine_cost . ibd . delta is 16384 bytes

  72. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / engine_cost . ibd . delta to . / mysql / engine_cost . ibd . . .

  73. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / user_bak . ibd . delta is 16384 bytes

  74. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / user_bak . ibd . delta to . / mysql / user_bak . ibd . . .

  75. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone . ibd . delta is 16384 bytes

  76. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone . ibd . delta to . / mysql / time_zone . ibd . . .

  77. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_name . ibd . delta is 16384 bytes

  78. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_name . ibd . delta to . / mysql / time_zone_name . ibd . . .

  79. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_index_stats . ibd . delta is 16384 bytes

  80. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_index_stats . ibd . delta to . / mysql / innodb_index_stats . ibd . . .

  81. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_worker_info . ibd . delta is 16384 bytes

  82. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_worker_info . ibd . delta to . / mysql / slave_worker_info . ibd . . .

  83. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / servers . ibd . delta is 16384 bytes

  84. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / servers . ibd . delta to . / mysql / servers . ibd . . .

  85. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition . ibd . delta is 16384 bytes

  86. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition . ibd . delta to . / mysql / time_zone_transition . ibd . . .

  87. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_master_info . ibd . delta is 16384 bytes

  88. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_master_info . ibd . delta to . / mysql / slave_master_info . ibd . . .

  89. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_leap_second . ibd . delta is 16384 bytes

  90. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_leap_second . ibd . delta to . / mysql / time_zone_leap_second . ibd . . .

  91. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition_type . ibd . delta is 16384 bytes

  92. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition_type . ibd . delta to . / mysql / time_zone_transition_type . ibd . . .

  93. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_relation . ibd . delta is 16384 bytes

  94. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_relation . ibd . delta to . / mysql / help_relation . ibd . . .

  95. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_relay_log_info . ibd . delta is 16384 bytes

  96. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_relay_log_info . ibd . delta to . / mysql / slave_relay_log_info . ibd . . .

  97. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / gtid_executed . ibd . delta is 16384 bytes

  98. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / gtid_executed . ibd . delta to . / mysql / gtid_executed . ibd . . .

  99. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / server_cost . ibd . delta is 16384 bytes

  100. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / server_cost . ibd . delta to . / mysql / server_cost . ibd . . .

  101. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_category . ibd . delta is 16384 bytes

  102. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_category . ibd . delta to . / mysql / help_category . ibd . . .

  103. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_keyword . ibd . delta is 16384 bytes

  104. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_keyword . ibd . delta to . / mysql / help_keyword . ibd . . .

  105. xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_table_stats . ibd . delta is 16384 bytes

  106. Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_table_stats . ibd . delta to . / mysql / innodb_table_stats . ibd . . .

  107. xtrabackup: using the following InnoDB configuration for recovery:

  108. xtrabackup:   innodb_data_home_dir = .

  109. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

  110. xtrabackup:   innodb_log_group_home_dir = / backup / incr_backup / 2018-06-12_10-47-16 /

  111. xtrabackup:   innodb_log_files_in_group = 1

  112. xtrabackup:   innodb_log_file_size = 8388608

  113. xtrabackup: Starting InnoDB instance for recovery .

  114. xtrabackup: Using 104857600 bytes for buffer pool ( set by --use-memory parameter )

  115. InnoDB: PUNCH HOLE support available

  116. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  117. InnoDB: Uses event mutexes

  118. InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier

  119. InnoDB: Compressed tables use zlib 1 . 2 . 3

  120. InnoDB: Number of pools: 1

  121. InnoDB: Using CPU crc32 instructions

  122. InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M

  123. InnoDB: Completed initialization of buffer pool

  124. InnoDB: page_cleaner coordinator priority: -20

  125. InnoDB: Highest supported file format is Barracuda .

  126. InnoDB: Log scan progressed past the checkpoint lsn 1540210034

  127. InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )

  128. InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )

  129. InnoDB:  Are you sure you are using the right ib_logfiles to start up the database ? Log sequence number in the ib_logfiles is 1540210034 , less than the log sequence number in the first system tablespace file header , 1540210052 .

  130. InnoDB: Database was not shutdown normally!

  131. InnoDB: Starting crash recovery .

  132. InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001

  133. InnoDB: Creating shared tablespace for temporary tables

  134. InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .

  135. InnoDB: File './ibtmp1' size is now 12 MB .

  136. InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .

  137. InnoDB: 32 non-redo rollback segment ( s ) are active .

  138. InnoDB: Waiting for purge to start

  139. InnoDB: 5 . 7 . 13 started ; log sequence number 1540210043

  140. InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001


  142. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  143. InnoDB: FTS optimize thread exiting .

  144. InnoDB: Starting shutdown . . .

  145. InnoDB: Shutdown completed ; log sequence number 1540210062

  146. InnoDB: Number of pools: 1

  147. 180612 11:07:15 [ 01 ] Copying / backup / incr_backup / 2018-06-12_10-47-16 / test / test . frm to . / test / test . frm

  148. InnoDB: 5 . 7 . 13 started ; log sequence number 1540210197

  149. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  150. InnoDB: FTS optimize thread exiting .

  151. InnoDB: Starting shutdown . . .

  152. InnoDB: Shutdown completed ; log sequence number 1540210216

  153. 180612 11:07:20 completed OK!

14、把所有合在一起的数据进行一次apply，回滚未提交的数据

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --apply- log / backup / full_backup / 2018-06-12_10-30-29 /

  2. 180612 11:09:59 innobackupex: Starting the apply- log operation


  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! " .


  8. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  9. xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /

  10. xtrabackup: This target seems to be already prepared .

  11. InnoDB: Number of pools: 1

  12. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare' .

  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 = 2

  18. xtrabackup:   innodb_log_file_size = 67108864

  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 = 2

  24. xtrabackup:   innodb_log_file_size = 67108864

  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 __sync_synchronize ( ) is used for memory barrier

  31. InnoDB: Compressed tables use zlib 1 . 2 . 3

  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: Removed temporary tablespace data file: " ibtmp1 "

  39. InnoDB: Creating shared tablespace for temporary tables

  40. InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .

  41. InnoDB: File './ibtmp1' size is now 12 MB .

  42. InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .

  43. InnoDB: 32 non-redo rollback segment ( s ) are active .

  44. InnoDB: Waiting for purge to start

  45. InnoDB: 5 . 7 . 13 started ; log sequence number 1540210216

  46. InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001


  48. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  49. InnoDB: FTS optimize thread exiting .

  50. InnoDB: Starting shutdown . . .

  51. InnoDB: Shutdown completed ; log sequence number 1540210235

  52. InnoDB: Number of pools: 1

  53. xtrabackup: using the following InnoDB configuration for recovery:

  54. xtrabackup:   innodb_data_home_dir = .

  55. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

  56. xtrabackup:   innodb_log_group_home_dir = .

  57. xtrabackup:   innodb_log_files_in_group = 2

  58. xtrabackup:   innodb_log_file_size = 67108864

  59. InnoDB: PUNCH HOLE support available

  60. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  61. InnoDB: Uses event mutexes

  62. InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier

  63. InnoDB: Compressed tables use zlib 1 . 2 . 3

  64. InnoDB: Number of pools: 1

  65. InnoDB: Using CPU crc32 instructions

  66. InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M

  67. InnoDB: Completed initialization of buffer pool

  68. InnoDB: page_cleaner coordinator priority: -20

  69. InnoDB: Highest supported file format is Barracuda .

  70. InnoDB: Removed temporary tablespace data file: " ibtmp1 "

  71. InnoDB: Creating shared tablespace for temporary tables

  72. InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .

  73. InnoDB: File './ibtmp1' size is now 12 MB .

  74. InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .

  75. InnoDB: 32 non-redo rollback segment ( s ) are active .

  76. InnoDB: Waiting for purge to start

  77. InnoDB: 5 . 7 . 13 started ; log sequence number 1540210235

  78. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  79. InnoDB: FTS optimize thread exiting .

  80. InnoDB: Starting shutdown . . .

  81. InnoDB: Shutdown completed ; log sequence number 1540210254

  82. 180612 11:10:02 completed OK!

15、进行restore操作，将准备好的数据copy到data目录

点击( 此处 )折叠或打开

  1. [ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf --copy-back / backup / full_backup / 2018-06-12_10-30-29 /

  2. 180612 11:14:02 innobackupex: Starting the copy-back operation


  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! " .


  8. . / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )

  9. 180612 11:14:02 [ 01 ] Copying ib_logfile0 to / mysql / data / ib_logfile0

  10. 180612 11:14:02 [ 01 ] . . . done

  11. 180612 11:14:03 [ 01 ] Copying ib_logfile1 to / mysql / data / ib_logfile1

  12. 180612 11:14:03 [ 01 ] . . . done

  13. 180612 11:14:03 [ 01 ] Copying ibdata1 to / mysql / data / ibdata1

  14. 180612 11:14:07 [ 01 ] . . . done

  15. 180612 11:14:07 [ 01 ] Copying . / ib_buffer_pool to / mysql / data / ib_buffer_pool

  16. 180612 11:14:07 [ 01 ] . . . done

  17. 180612 11:14:07 [ 01 ] Copying . / test / test . frm to / mysql / data / test / test . frm

  18. 180612 11:14:07 [ 01 ] . . . done

  19. 180612 11:14:07 [ 01 ] Copying . / test / t_user_info . frm to / mysql / data / test / t_user_info . frm

  20. 180612 11:14:07 [ 01 ] . . . done

  21. 180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . frm to / mysql / data / test / t_tou_info . frm

  22. 180612 11:14:07 [ 01 ] . . . done

  23. 180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . MYI to / mysql / data / test / t_tou_info . MYI

  24. 180612 11:14:07 [ 01 ] . . . done

  25. 180612 11:14:07 [ 01 ] Copying . / test / t_user_info . ibd to / mysql / data / test / t_user_info . ibd

  26. 180612 11:14:07 [ 01 ] . . . done

  27. 180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . MYD to / mysql / data / test / t_tou_info . MYD

  28. 180612 11:14:07 [ 01 ] . . . done

  29. 180612 11:14:07 [ 01 ] Copying . / test / db . opt to / mysql / data / test / db . opt

  30. 180612 11:14:07 [ 01 ] . . . done

  31. 180612 11:14:07 [ 01 ] Copying . / xcredit / tb_generic_data . frm to / mysql / data / xcredit / tb_generic_data . frm

  32. 180612 11:14:07 [ 01 ] . . . done

  33. 180612 11:14:07 [ 01 ] Copying . / xcredit / tb_openapi . ibd to / mysql / data / xcredit / tb_openapi . ibd

  34. 180612 11:14:07 [ 01 ] . . . done

  35. 180612 11:14:12 [ 01 ] . . . done

  36. 180612 11:14:12 [ 01 ] Copying . / performance_schema / replication_applier_configuration . frm to / mysql / data / performance_schema / replication_applier_configuration . frm

  37. 180612 11:14:12 [ 01 ] . . . done

  38. 180612 11:14:12 completed OK!

16、修改data目录权限，重启MySQL服务

点击( 此处 )折叠或打开

  1. [ root @ mysql5 mysql ] # ll

  2. total 8

  3. drwxrwxr-x . 7 mysql mysql 4096 Jun 12 11:14 data

  4. drwxr-xr-x  6 mysql mysql 4096 Mar 30 10:08 data_bak

  5. [ root @ mysql5 mysql ] #

  6. [ root @ mysql5 mysql ] #

  7. [ root @ mysql5 mysql ] # cd data

  8. [ root @ mysql5 data ] # ll

  9. total 286760

  10. -rw-r----- 1 root root       509 Jun 12 11:14 ib_buffer_pool

  11. -rw-r----- 1 root root 146800640 Jun 12 11:14 ibdata1

  12. -rw-r----- 1 root root  67108864 Jun 12 11:14 ib_logfile0

  13. -rw-r----- 1 root root  67108864 Jun 12 11:14 ib_logfile1

  14. -rw-r----- 1 root root  12582912 Jun 12 11:14 ibtmp1

  15. drwxr-x--- 2 root root      4096 Jun 12 11:14 mysql

  16. drwxr-x--- 2 root root      4096 Jun 12 11:14 performance_schema

  17. drwxr-x--- 2 root root     12288 Jun 12 11:14 sys

  18. drwxr-x--- 2 root root      4096 Jun 12 11:14 test

  19. drwxr-x--- 2 root root      4096 Jun 12 11:14 xcredit

  20. -rw-r----- 1 root root        22 Jun 12 11:14 xtrabackup_binlog_pos_innodb

  21. -rw-r----- 1 root root       608 Jun 12 11:14 xtrabackup_info

  22. [ root @ mysql5 data ] # cd . .

  23. [ root @ mysql5 mysql ] # chown -R mysql:mysql data

  24. [ root @ mysql5 mysql ] # cd data

  25. [ root @ mysql5 data ] # ll

  26. total 286760

  27. -rw-r----- 1 mysql mysql       509 Jun 12 11:14 ib_buffer_pool

  28. -rw-r----- 1 mysql mysql 146800640 Jun 12 11:14 ibdata1

  29. -rw-r----- 1 mysql mysql  67108864 Jun 12 11:14 ib_logfile0

  30. -rw-r----- 1 mysql mysql  67108864 Jun 12 11:14 ib_logfile1

  31. -rw-r----- 1 mysql mysql  12582912 Jun 12 11:14 ibtmp1

  32. drwxr-x--- 2 mysql mysql      4096 Jun 12 11:14 mysql

  33. drwxr-x--- 2 mysql mysql      4096 Jun 12 11:14 performance_schema

  34. drwxr-x--- 2 mysql mysql     12288 Jun 12 11:14 sys

  35. drwxr-x--- 2 mysql mysql      4096 Jun 12 11:14 test

  36. drwxr-x--- 2 mysql mysql      4096 Jun 12 11:14 xcredit

  37. -rw-r----- 1 mysql mysql        22 Jun 12 11:14 xtrabackup_binlog_pos_innodb

  38. -rw-r----- 1 mysql mysql       608 Jun 12 11:14 xtrabackup_info

  39. [ root @ mysql5 data ] # service mysql start

  40. Starting MySQL . Logging to '/mysql/data/mysql-err.log' .

  41. . [ OK ]

17、对test表数据进行验证

点击( 此处 )折叠或打开

  1. mysql > select * from test ;

  2. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  3. | id | user_type | code |

  4. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  5. | 1 | 0 | 30 |

  6. | 2 | 0 | 40 |

  7. | 3 | 0 | 50 |

  8. + - - - - - - + - - - - - - - - - - - + - - - - - - +






来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30135314/viewspace-2156006/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL5.7主从复制-半同步复制搭建
下一篇： MySQL本地小版本升级（5.6.36到5.6.40）
请登录后发表评论 登录
全部评论
StevenBeijing

注册时间：2015-02-10

  • 博文量
    244
  • 访问量
    325272

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593