ITPub博客

Xtrabackup增量备份实战

原创 作者: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 )


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

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


  5. mysql > flush privileges ;

  6. 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


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

  4.            At the end of a successful backup run innobackupex

  5.            prints " completed OK! " .


  6. 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 ) .

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

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

  9. 180612 10:30:29  version_check Done .

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

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

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

  13. xtrabackup: uses posix_fadvise ( ) .

  14. xtrabackup: cd to / mysql / data

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

  16. xtrabackup: using the following InnoDB configuration:

  17. xtrabackup:   innodb_data_home_dir = .

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

  19. xtrabackup:   innodb_log_group_home_dir = . /

  20. xtrabackup:   innodb_log_files_in_group = 2

  21. xtrabackup:   innodb_log_file_size = 67108864

  22. xtrabackup: using O_DIRECT

  23. InnoDB: Number of pools: 1

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

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

  26. xtrabackup: Generating a list of tablespaces

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

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

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

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

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

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

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

  34. 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

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

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

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

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

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

  40. xtrabackup: Stopping log copying thread .

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


  42. 180612 10:30:40 Executing UNLOCK TABLES

  43. 180612 10:30:40 All tables unlocked

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

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

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

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

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

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

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

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

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

  53. 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 )


  3. mysql > select * from test ;

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

  5. | id | user_type | code |

  6. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  7. | 1 | 0 | 30 |

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

  9. 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


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

  4.            At the end of a successful backup run innobackupex

  5.            prints " completed OK! " .


  6. 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 ) .

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

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

  9. 180612 10:40:49  version_check Done .

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

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

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

  13. incremental backup from 1540210034 is enabled .

  14. xtrabackup: uses posix_fadvise ( ) .

  15. xtrabackup: cd to / mysql / data

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

  17. xtrabackup: using the following InnoDB configuration:

  18. xtrabackup:   innodb_data_home_dir = .

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

  20. xtrabackup:   innodb_log_group_home_dir = . /

  21. xtrabackup:   innodb_log_files_in_group = 2

  22. xtrabackup:   innodb_log_file_size = 67108864

  23. xtrabackup: using O_DIRECT

  24. InnoDB: Number of pools: 1

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

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

  27. xtrabackup: Generating a list of tablespaces

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

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

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

  31. 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

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

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

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

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

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

  37. xtrabackup: Stopping log copying thread .

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

  39. 180612 10:40:56 Executing UNLOCK TABLES

  40. 180612 10:40:56 All tables unlocked

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

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

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

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

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

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

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

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

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

  50. 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 )


  3. mysql > select * from test ;

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

  5. | id | user_type | code |

  6. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  7. | 1 | 0 | 30 |

  8. | 2 | 0 | 40 |

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

  10. 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


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

  4.            At the end of a successful backup run innobackupex

  5.            prints " completed OK! " .


  6. 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 ) .

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

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

  9. 180612 10:47:16  version_check Done .

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

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

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

  13. incremental backup from 1540210034 is enabled .

  14. xtrabackup: uses posix_fadvise ( ) .

  15. xtrabackup: cd to / mysql / data

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

  17. xtrabackup: using the following InnoDB configuration:

  18. xtrabackup:   innodb_data_home_dir = .

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

  20. xtrabackup:   innodb_log_group_home_dir = . /

  21. xtrabackup:   innodb_log_files_in_group = 2

  22. xtrabackup:   innodb_log_file_size = 67108864

  23. xtrabackup: using O_DIRECT

  24. InnoDB: Number of pools: 1

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

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

  27. xtrabackup: Generating a list of tablespaces

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

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

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

  31. 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

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

  33. 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

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

  35. 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

  36. 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

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

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

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

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

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

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

  43. xtrabackup: Stopping log copying thread .

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


  45. 180612 10:47:23 Executing UNLOCK TABLES

  46. 180612 10:47:23 All tables unlocked

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

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

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

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

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

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

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

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

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

  56. 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 )


  3. mysql > select * from test ;

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

  5. | id | user_type | code |

  6. + - - - - - - + - - - - - - - - - - - + - - - - - - +

  7. | 1 | 0 | 30 |

  8. | 2 | 0 | 40 |

  9. | 3 | 0 | 50 |

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

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


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


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

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

  8. xtrabackup: This target seems to be not prepared yet .

  9. InnoDB: Number of pools: 1

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

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

  12. xtrabackup:   innodb_data_home_dir = .

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

  14. xtrabackup:   innodb_log_group_home_dir = .

  15. xtrabackup:   innodb_log_files_in_group = 1

  16. xtrabackup:   innodb_log_file_size = 8388608

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

  18. xtrabackup:   innodb_data_home_dir = .

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

  20. xtrabackup:   innodb_log_group_home_dir = .

  21. xtrabackup:   innodb_log_files_in_group = 1

  22. xtrabackup:   innodb_log_file_size = 8388608

  23. xtrabackup: Starting InnoDB instance for recovery .

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

  25. InnoDB: PUNCH HOLE support available

  26. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  27. InnoDB: Uses event mutexes

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

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

  30. InnoDB: Number of pools: 1

  31. InnoDB: Using CPU crc32 instructions

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

  33. InnoDB: Completed initialization of buffer pool

  34. InnoDB: page_cleaner coordinator priority: -20

  35. InnoDB: Highest supported file format is Barracuda .

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

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

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

  39. InnoDB: Database was not shutdown normally!

  40. InnoDB: Starting crash recovery .

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

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

  43. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  44. InnoDB: Starting shutdown . . .

  45. InnoDB: Shutdown completed ; log sequence number 1540210052

  46. InnoDB: Number of pools: 1

  47. 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


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


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

  7. incremental backup from 1540210034 is enabled .

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

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

  10. InnoDB: Number of pools: 1

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

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

  13. xtrabackup:   innodb_data_home_dir = .

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

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

  16. xtrabackup:   innodb_log_files_in_group = 1

  17. xtrabackup:   innodb_log_file_size = 8388608

  18. xtrabackup: Generating a list of tablespaces

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

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

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

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

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

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

  25. 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


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

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

  4.            prints " completed OK! " .


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

  6. incremental backup from 1540210034 is enabled .

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

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

  9. InnoDB: Number of pools: 1

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

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

  12. xtrabackup:   innodb_data_home_dir = .

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

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

  15. xtrabackup:   innodb_log_files_in_group = 1

  16. xtrabackup:   innodb_log_file_size = 8388608

  17. xtrabackup: Generating a list of tablespaces

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  106. xtrabackup:   innodb_data_home_dir = .

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

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

  109. xtrabackup:   innodb_log_files_in_group = 1

  110. xtrabackup:   innodb_log_file_size = 8388608

  111. xtrabackup: Starting InnoDB instance for recovery .

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

  113. InnoDB: PUNCH HOLE support available

  114. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  115. InnoDB: Uses event mutexes

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

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

  118. InnoDB: Number of pools: 1

  119. InnoDB: Using CPU crc32 instructions

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

  121. InnoDB: Completed initialization of buffer pool

  122. InnoDB: page_cleaner coordinator priority: -20

  123. InnoDB: Highest supported file format is Barracuda .

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

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

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

  127. 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 .

  128. InnoDB: Database was not shutdown normally!

  129. InnoDB: Starting crash recovery .

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

  131. InnoDB: Creating shared tablespace for temporary tables

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

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

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

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

  136. InnoDB: Waiting for purge to start

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

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


  139. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  140. InnoDB: FTS optimize thread exiting .

  141. InnoDB: Starting shutdown . . .

  142. InnoDB: Shutdown completed ; log sequence number 1540210062

  143. InnoDB: Number of pools: 1

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

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

  146. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  147. InnoDB: FTS optimize thread exiting .

  148. InnoDB: Starting shutdown . . .

  149. InnoDB: Shutdown completed ; log sequence number 1540210216

  150. 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


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


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

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

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

  9. InnoDB: Number of pools: 1

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

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

  12. xtrabackup:   innodb_data_home_dir = .

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

  14. xtrabackup:   innodb_log_group_home_dir = .

  15. xtrabackup:   innodb_log_files_in_group = 2

  16. xtrabackup:   innodb_log_file_size = 67108864

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

  18. xtrabackup:   innodb_data_home_dir = .

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

  20. xtrabackup:   innodb_log_group_home_dir = .

  21. xtrabackup:   innodb_log_files_in_group = 2

  22. xtrabackup:   innodb_log_file_size = 67108864

  23. xtrabackup: Starting InnoDB instance for recovery .

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

  25. InnoDB: PUNCH HOLE support available

  26. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  27. InnoDB: Uses event mutexes

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

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

  30. InnoDB: Number of pools: 1

  31. InnoDB: Using CPU crc32 instructions

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

  33. InnoDB: Completed initialization of buffer pool

  34. InnoDB: page_cleaner coordinator priority: -20

  35. InnoDB: Highest supported file format is Barracuda .

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

  37. InnoDB: Creating shared tablespace for temporary tables

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

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

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

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

  42. InnoDB: Waiting for purge to start

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

  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: FTS optimize thread exiting .

  47. InnoDB: Starting shutdown . . .

  48. InnoDB: Shutdown completed ; log sequence number 1540210235

  49. InnoDB: Number of pools: 1

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

  51. xtrabackup:   innodb_data_home_dir = .

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

  53. xtrabackup:   innodb_log_group_home_dir = .

  54. xtrabackup:   innodb_log_files_in_group = 2

  55. xtrabackup:   innodb_log_file_size = 67108864

  56. InnoDB: PUNCH HOLE support available

  57. InnoDB: Mutexes and rw_locks use GCC atomic builtins

  58. InnoDB: Uses event mutexes

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

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

  61. InnoDB: Number of pools: 1

  62. InnoDB: Using CPU crc32 instructions

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

  64. InnoDB: Completed initialization of buffer pool

  65. InnoDB: page_cleaner coordinator priority: -20

  66. InnoDB: Highest supported file format is Barracuda .

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

  68. InnoDB: Creating shared tablespace for temporary tables

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

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

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

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

  73. InnoDB: Waiting for purge to start

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

  75. xtrabackup: starting shutdown with innodb_fast_shutdown = 1

  76. InnoDB: FTS optimize thread exiting .

  77. InnoDB: Starting shutdown . . .

  78. InnoDB: Shutdown completed ; log sequence number 1540210254

  79. 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


  3. IMPORTANT: Please check that the copy-back run completes successfully .

  4.            At the end of a successful copy-back run innobackupex

  5.            prints " completed OK! " .


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  36. 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. + - - - - - - + - - - - - - - - - - - + - - - - - - +






请登录后发表评论 登录
全部评论

注册时间:2015-02-10

  • 博文量
    167
  • 访问量
    175420