1、创建MySQL备份用户
点击( 此处 )折叠或打开
-
mysql > create user 'backup' @ '%' identified by 'mysql' ;
-
Query OK , 0 rows affected ( . 00 sec )
-
-
mysql > grant reload , lock tables , replication client , create tablespace , process , super , create , insert , select on * . * to 'backup' @ '%' ;
-
Query OK , 0 rows affected ( . 00 sec )
-
-
mysql > flush privileges ;
-
Query OK , 0 rows affected ( . 00 sec )
2、查看测试表
点击( 此处 )折叠或打开
-
mysql > select * from test ;
-
Empty set ( . 00 sec )
3、对数据库进行全备
点击( 此处 )折叠或打开
-
[ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf -- user = backup -- password = mysql --port = 3306 / backup / full_backup /
-
180612 10:30:29 innobackupex: Starting the backup operation
-
-
IMPORTANT: Please check that the backup run completes successfully .
-
At the end of a successful backup run innobackupex
-
prints " completed OK! " .
-
-
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 ) .
-
180612 10:30:29 version_check Connected to MySQL server
-
180612 10:30:29 version_check Executing a version check against the server . . .
-
180612 10:30:29 version_check Done .
-
180612 10:30:29 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock
-
Using server version 5 . 7 . 19- log
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
xtrabackup: uses posix_fadvise ( ) .
-
xtrabackup: cd to / mysql / data
-
xtrabackup: open files limit requested 65535 , set to 65535
-
xtrabackup: using the following InnoDB configuration:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = . /
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
xtrabackup: using O_DIRECT
-
InnoDB: Number of pools: 1
-
Warning: " test . test " uses engine " MEMORY " and will not be backed up .
-
180612 10:30:30 > > log scanned up to ( 1540210043 )
-
xtrabackup: Generating a list of tablespaces
-
InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0
-
180612 10:30:30 [ 01 ] Copying . / ibdata1 to / backup / full_backup / 2018-06-12_10-30-29 / ibdata1
-
180612 10:30:31 > > log scanned up to ( 1540210043 )
-
180612 10:30:32 > > log scanned up to ( 1540210043 )
-
180612 10:30:33 > > log scanned up to ( 1540210043 )
-
180612 10:30:34 > > log scanned up to ( 1540210043 )
-
180612 10:30:34 [ 01 ] . . . done
-
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
-
180612 10:30:39 Finished backing up non-InnoDB tables and files
-
180612 10:30:39 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / xtrabackup_binlog_info
-
180612 10:30:39 [ 00 ] . . . done
-
180612 10:30:39 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .
-
xtrabackup: The latest check point ( for incremental ) : '1540210034'
-
xtrabackup: Stopping log copying thread .
-
. 180612 10:30:40 > > log scanned up to ( 1540210043 )
-
-
180612 10:30:40 Executing UNLOCK TABLES
-
180612 10:30:40 All tables unlocked
-
180612 10:30:40 [ 00 ] Copying ib_buffer_pool to / backup / full_backup / 2018-06-12_10-30-29 / ib_buffer_pool
-
180612 10:30:40 [ 00 ] . . . done
-
180612 10:30:40 Backup created in directory '/backup/full_backup/2018-06-12_10-30-29/'
-
MySQL binlog position : filename 'mysql-bin.000012' , position '1645'
-
180612 10:30:40 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / backup-my . cnf
-
180612 10:30:40 [ 00 ] . . . done
-
180612 10:30:40 [ 00 ] Writing / backup / full_backup / 2018-06-12_10-30-29 / xtrabackup_info
-
180612 10:30:40 [ 00 ] . . . done
-
xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .
-
180612 10:30:40 completed OK!
查看全量备份信息:
点击( 此处 )折叠或打开
-
[ root @ mysql5 2018-06-12_10-30-29 ] # cat xtrabackup_info
-
uuid = 99098d6e-6de8-11e8-9520-080027240aa4
-
name =
-
tool_name = innobackupex
-
tool_command = --defaults-file = / etc / my . cnf -- user = backup -- password = . . . --port = 3306 / backup / full_backup /
-
tool_version = 2 . 4 . 9
-
ibbackup_version = 2 . 4 . 9
-
server_version = 5 . 7 . 19- log
-
start_time = 2018-06-12 10:30:29
-
end_time = 2018-06-12 10:30:40
-
lock_time = 0
-
binlog_pos = filename 'mysql-bin.000012' , position '1645'
-
innodb_from_lsn = 0
-
innodb_to_lsn = 1540210034
-
partial = N
-
incremental = N
-
format = file
-
compact = N
-
compressed = N
-
encrypted = N
4、对测试表插入数据
点击( 此处 )折叠或打开
-
mysql > insert into test values ( 1 , '0' , 30 ) ;
-
Query OK , 1 row affected ( . 05 sec )
-
-
mysql > select * from test ;
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| id | user_type | code |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| 1 | 0 | 30 |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
1 row in set ( . 00 sec )
5、以之前的全备做基础,做第一次增量备份
点击( 此处 )折叠或打开
-
[ 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 /
-
180612 10:40:49 innobackupex: Starting the backup operation
-
-
IMPORTANT: Please check that the backup run completes successfully .
-
At the end of a successful backup run innobackupex
-
prints " completed OK! " .
-
-
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 ) .
-
180612 10:40:49 version_check Connected to MySQL server
-
180612 10:40:49 version_check Executing a version check against the server . . .
-
180612 10:40:49 version_check Done .
-
180612 10:40:49 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock
-
Using server version 5 . 7 . 19- log
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
incremental backup from 1540210034 is enabled .
-
xtrabackup: uses posix_fadvise ( ) .
-
xtrabackup: cd to / mysql / data
-
xtrabackup: open files limit requested 65535 , set to 65535
-
xtrabackup: using the following InnoDB configuration:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = . /
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
xtrabackup: using O_DIRECT
-
InnoDB: Number of pools: 1
-
Warning: " test . test " uses engine " MEMORY " and will not be backed up .
-
180612 10:40:49 > > log scanned up to ( 1540210043 )
-
xtrabackup: Generating a list of tablespaces
-
InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0
-
180612 10:40:49 [ 01 ] Copying . / ibdata1 to / backup / incr_backup / 2018-06-12_10-40-49 / ibdata1 . delta
-
180612 10:40:49 [ 01 ] . . . done
-
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
-
180612 10:40:56 Finished backing up non-InnoDB tables and files
-
180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / xtrabackup_binlog_info
-
180612 10:40:56 [ 00 ] . . . done
-
180612 10:40:56 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .
-
xtrabackup: The latest check point ( for incremental ) : '1540210034'
-
xtrabackup: Stopping log copying thread .
-
. 180612 10:40:56 > > log scanned up to ( 1540210043 )
-
180612 10:40:56 Executing UNLOCK TABLES
-
180612 10:40:56 All tables unlocked
-
180612 10:40:56 [ 00 ] Copying ib_buffer_pool to / backup / incr_backup / 2018-06-12_10-40-49 / ib_buffer_pool
-
180612 10:40:56 [ 00 ] . . . done
-
180612 10:40:56 Backup created in directory '/backup/incr_backup/2018-06-12_10-40-49/'
-
MySQL binlog position : filename 'mysql-bin.000012' , position '1952'
-
180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / backup-my . cnf
-
180612 10:40:56 [ 00 ] . . . done
-
180612 10:40:56 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-40-49 / xtrabackup_info
-
180612 10:40:56 [ 00 ] . . . done
-
xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .
-
180612 10:40:56 completed OK!
查看第一次增量备份信息:
点击( 此处 )折叠或打开
-
[ root @ mysql5 2018-06-12_10-40-49 ] # cat xtrabackup_info
-
uuid = 08769a08-6dea-11e8-9520-080027240aa4
-
name =
-
tool_name = innobackupex
-
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 /
-
tool_version = 2 . 4 . 9
-
ibbackup_version = 2 . 4 . 9
-
server_version = 5 . 7 . 19- log
-
start_time = 2018-06-12 10:40:49
-
end_time = 2018-06-12 10:40:56
-
lock_time = 0
-
binlog_pos = filename 'mysql-bin.000012' , position '1952'
-
innodb_from_lsn = 1540210034
-
innodb_to_lsn = 1540210034
-
partial = N
-
incremental = Y
-
format = file
-
compact = N
-
compressed = N
-
encrypted = N
6、向测试表插入数据
点击( 此处 )折叠或打开
-
mysql > insert into test values ( 2 , '0' , 40 ) ;
-
Query OK , 1 row affected ( . 02 sec )
-
-
mysql > select * from test ;
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| id | user_type | code |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| 1 | 0 | 30 |
-
| 2 | 0 | 40 |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
2 rows in set ( . 00 sec )
7、以第一次增量备份作为基础,做第二次增量备份
点击( 此处 )折叠或打开
-
[ 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 /
-
180612 10:47:16 innobackupex: Starting the backup operation
-
-
IMPORTANT: Please check that the backup run completes successfully .
-
At the end of a successful backup run innobackupex
-
prints " completed OK! " .
-
-
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 ) .
-
180612 10:47:16 version_check Connected to MySQL server
-
180612 10:47:16 version_check Executing a version check against the server . . .
-
180612 10:47:16 version_check Done .
-
180612 10:47:16 Connecting to MySQL server host: localhost , user : backup , password : set , port: 3306 , socket: / tmp / mysqld . sock
-
Using server version 5 . 7 . 19- log
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
incremental backup from 1540210034 is enabled .
-
xtrabackup: uses posix_fadvise ( ) .
-
xtrabackup: cd to / mysql / data
-
xtrabackup: open files limit requested 65535 , set to 65535
-
xtrabackup: using the following InnoDB configuration:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = . /
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
xtrabackup: using O_DIRECT
-
InnoDB: Number of pools: 1
-
Warning: " test . test " uses engine " MEMORY " and will not be backed up .
-
180612 10:47:16 > > log scanned up to ( 1540210043 )
-
xtrabackup: Generating a list of tablespaces
-
InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0
-
180612 10:47:16 [ 01 ] Copying . / ibdata1 to / backup / incr_backup / 2018-06-12_10-47-16 / ibdata1 . delta
-
180612 10:47:16 [ 01 ] . . . done
-
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
-
180612 10:47:16 [ 01 ] . . . done
-
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
-
180612 10:47:16 [ 01 ] . . . done
-
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
-
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
-
180612 10:47:23 [ 01 ] . . . done
-
180612 10:47:23 Finished backing up non-InnoDB tables and files
-
180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / xtrabackup_binlog_info
-
180612 10:47:23 [ 00 ] . . . done
-
180612 10:47:23 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS . . .
-
xtrabackup: The latest check point ( for incremental ) : '1540210034'
-
xtrabackup: Stopping log copying thread .
-
. 180612 10:47:23 > > log scanned up to ( 1540210043 )
-
-
180612 10:47:23 Executing UNLOCK TABLES
-
180612 10:47:23 All tables unlocked
-
180612 10:47:23 [ 00 ] Copying ib_buffer_pool to / backup / incr_backup / 2018-06-12_10-47-16 / ib_buffer_pool
-
180612 10:47:23 [ 00 ] . . . done
-
180612 10:47:23 Backup created in directory '/backup/incr_backup/2018-06-12_10-47-16/'
-
MySQL binlog position : filename 'mysql-bin.000012' , position '2259'
-
180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / backup-my . cnf
-
180612 10:47:23 [ 00 ] . . . done
-
180612 10:47:23 [ 00 ] Writing / backup / incr_backup / 2018-06-12_10-47-16 / xtrabackup_info
-
180612 10:47:23 [ 00 ] . . . done
-
xtrabackup: Transaction log of lsn ( 1540210034 ) to ( 1540210043 ) was copied .
-
180612 10:47:23 completed OK!
查看第二次增量备份信息:
点击( 此处 )折叠或打开
-
[ root @ mysql5 2018-06-12_10-47-16 ] # cat xtrabackup_info
-
uuid = eefb0a37-6dea-11e8-9520-080027240aa4
-
name =
-
tool_name = innobackupex
-
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 /
-
tool_version = 2 . 4 . 9
-
ibbackup_version = 2 . 4 . 9
-
server_version = 5 . 7 . 19- log
-
start_time = 2018-06-12 10:47:16
-
end_time = 2018-06-12 10:47:23
-
lock_time = 0
-
binlog_pos = filename 'mysql-bin.000012' , position '2259'
-
innodb_from_lsn = 1540210034
-
innodb_to_lsn = 1540210034
-
partial = N
-
incremental = Y
-
format = file
-
compact = N
-
compressed = N
-
encrypted = N
8、再次向测试表插入数据
点击( 此处 )折叠或打开
-
mysql > insert into test values ( 3 , '0' , 50 ) ;
-
Query OK , 1 row affected ( . 02 sec )
-
-
mysql > select * from test ;
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| id | user_type | code |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| 1 | 0 | 30 |
-
| 2 | 0 | 40 |
-
| 3 | 0 | 50 |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
9、现在模式故障,删除data目录下所有文件
10、恢复前kill掉mysql进程
11、恢复基础备份(这里一定要加--redo-only参数,该参数的意思是只应用xrabackup日志中已经提交的事务数据,不回滚还未提交的数据。)
点击( 此处 )折叠或打开
-
[ root @ mysql5 bin ] # . / innobackupex --apply- log --redo-only / backup / full_backup / 2018-06-12_10-30-29 /
-
180612 11:01:18 innobackupex: Starting the apply- log operation
-
-
IMPORTANT: Please check that the apply- log run completes successfully .
-
At the end of a successful apply- log run innobackupex
-
prints " completed OK! " .
-
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /
-
xtrabackup: This target seems to be not prepared yet .
-
InnoDB: Number of pools: 1
-
xtrabackup: xtrabackup_logfile detected: size = 8388608 , start_lsn = ( 1540210034 )
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = .
-
xtrabackup: innodb_log_files_in_group = 1
-
xtrabackup: innodb_log_file_size = 8388608
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = .
-
xtrabackup: innodb_log_files_in_group = 1
-
xtrabackup: innodb_log_file_size = 8388608
-
xtrabackup: Starting InnoDB instance for recovery .
-
xtrabackup: Using 104857600 bytes for buffer pool ( set by --use-memory parameter )
-
InnoDB: PUNCH HOLE support available
-
InnoDB: Mutexes and rw_locks use GCC atomic builtins
-
InnoDB: Uses event mutexes
-
InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier
-
InnoDB: Compressed tables use zlib 1 . 2 . 3
-
InnoDB: Number of pools: 1
-
InnoDB: Using CPU crc32 instructions
-
InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M
-
InnoDB: Completed initialization of buffer pool
-
InnoDB: page_cleaner coordinator priority: -20
-
InnoDB: Highest supported file format is Barracuda .
-
InnoDB: Log scan progressed past the checkpoint lsn 1540210034
-
InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )
-
InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )
-
InnoDB: Database was not shutdown normally!
-
InnoDB: Starting crash recovery .
-
InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001
-
InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001
-
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
-
InnoDB: Starting shutdown . . .
-
InnoDB: Shutdown completed ; log sequence number 1540210052
-
InnoDB: Number of pools: 1
-
180612 11:01:20 completed OK!
12、将第一次增量备份恢复到全备:
点击( 此处 )折叠或打开
-
[ 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 /
-
180612 11:04:06 innobackupex: Starting the apply- log operation
-
-
IMPORTANT: Please check that the apply- log run completes successfully .
-
At the end of a successful apply- log run innobackupex
-
prints " completed OK! " .
-
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
incremental backup from 1540210034 is enabled .
-
xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /
-
xtrabackup: This target seems to be already prepared with --apply- log - only .
-
InnoDB: Number of pools: 1
-
xtrabackup: xtrabackup_logfile detected: size = 8388608 , start_lsn = ( 1540210034 )
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = / backup / incr_backup / 2018-06-12_10-40-49 /
-
xtrabackup: innodb_log_files_in_group = 1
-
xtrabackup: innodb_log_file_size = 8388608
-
xtrabackup: Generating a list of tablespaces
-
InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-40-49 / / ibdata1 . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-40-49 / / ibdata1 . delta to . / ibdata1 . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-40-49 / / test / t_user_info . ibd . delta is 16384 bytes
-
180612 11:04:12 [ 00 ] Copying / backup / incr_backup / 2018-06-12_10-40-49 / / xtrabackup_info to . / xtrabackup_info
-
180612 11:04:12 [ 00 ] . . . done
-
180612 11:04:12 completed OK!
13、将第二次增量备份应用到全备上(恢复最后一个增量备份时,需要去掉--redo-only参数,回滚xtrabackup日志中那些还未提交的参数)
点击( 此处 )折叠或打开
-
[ 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
-
-
IMPORTANT: Please check that the apply- log run completes successfully .
-
At the end of a successful apply- log run innobackupex
-
prints " completed OK! " .
-
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
incremental backup from 1540210034 is enabled .
-
xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /
-
xtrabackup: This target seems to be already prepared with --apply- log - only .
-
InnoDB: Number of pools: 1
-
xtrabackup: xtrabackup_logfile detected: size = 8388608 , start_lsn = ( 1540210034 )
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = / backup / incr_backup / 2018-06-12_10-47-16 /
-
xtrabackup: innodb_log_files_in_group = 1
-
xtrabackup: innodb_log_file_size = 8388608
-
xtrabackup: Generating a list of tablespaces
-
InnoDB: Allocated tablespace ID 64 for test / t_user_info , old maximum was 0
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / ibdata1 . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / ibdata1 . delta to . / ibdata1 . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / test / t_user_info . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / test / t_user_info . ibd . delta to . / test / t_user_info . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_generic_data . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_generic_data . ibd . delta to . / xcredit / tb_generic_data . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_resource . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_resource . ibd . delta to . / xcredit / tb_resource . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index . ibd . delta to . / xcredit / tb_index . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role . ibd . delta to . / xcredit / tb_role . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_offer_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_offer_log . ibd . delta to . / xcredit / tb_openapi_offer_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_acquire_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_acquire_log . ibd . delta to . / xcredit / tb_query_acquire_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_cert . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_cert . ibd . delta to . / xcredit / tb_cert . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_acquire_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi_acquire_log . ibd . delta to . / xcredit / tb_openapi_acquire_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_system_param . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_system_param . ibd . delta to . / xcredit / tb_system_param . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_sync_job . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_sync_job . ibd . delta to . / xcredit / tb_sync_job . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_analyze_index . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_analyze_index . ibd . delta to . / xcredit / tb_analyze_index . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_data_history . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_data_history . ibd . delta to . / xcredit / tb_query_data_history . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_data_complaint . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_data_complaint . ibd . delta to . / xcredit / tb_data_complaint . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_openapi . ibd . delta to . / xcredit / tb_openapi . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role_resource . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_role_resource . ibd . delta to . / xcredit / tb_role_resource . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_index_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_index_log . ibd . delta to . / xcredit / tb_query_index_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_message . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_message . ibd . delta to . / xcredit / tb_message . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_admin . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_admin . ibd . delta to . / xcredit / tb_admin . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_offer_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_query_offer_log . ibd . delta to . / xcredit / tb_query_offer_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index_log . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / xcredit / tb_index_log . ibd . delta to . / xcredit / tb_index_log . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / sys / sys_config . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / sys / sys_config . ibd . delta to . / sys / sys_config . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / plugin . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / plugin . ibd . delta to . / mysql / plugin . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_topic . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_topic . ibd . delta to . / mysql / help_topic . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / engine_cost . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / engine_cost . ibd . delta to . / mysql / engine_cost . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / user_bak . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / user_bak . ibd . delta to . / mysql / user_bak . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone . ibd . delta to . / mysql / time_zone . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_name . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_name . ibd . delta to . / mysql / time_zone_name . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_index_stats . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_index_stats . ibd . delta to . / mysql / innodb_index_stats . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_worker_info . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_worker_info . ibd . delta to . / mysql / slave_worker_info . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / servers . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / servers . ibd . delta to . / mysql / servers . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition . ibd . delta to . / mysql / time_zone_transition . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_master_info . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_master_info . ibd . delta to . / mysql / slave_master_info . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_leap_second . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_leap_second . ibd . delta to . / mysql / time_zone_leap_second . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition_type . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / time_zone_transition_type . ibd . delta to . / mysql / time_zone_transition_type . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_relation . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_relation . ibd . delta to . / mysql / help_relation . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_relay_log_info . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / slave_relay_log_info . ibd . delta to . / mysql / slave_relay_log_info . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / gtid_executed . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / gtid_executed . ibd . delta to . / mysql / gtid_executed . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / server_cost . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / server_cost . ibd . delta to . / mysql / server_cost . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_category . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_category . ibd . delta to . / mysql / help_category . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_keyword . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / help_keyword . ibd . delta to . / mysql / help_keyword . ibd . . .
-
xtrabackup: page size for / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_table_stats . ibd . delta is 16384 bytes
-
Applying / backup / incr_backup / 2018-06-12_10-47-16 / / mysql / innodb_table_stats . ibd . delta to . / mysql / innodb_table_stats . ibd . . .
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = / backup / incr_backup / 2018-06-12_10-47-16 /
-
xtrabackup: innodb_log_files_in_group = 1
-
xtrabackup: innodb_log_file_size = 8388608
-
xtrabackup: Starting InnoDB instance for recovery .
-
xtrabackup: Using 104857600 bytes for buffer pool ( set by --use-memory parameter )
-
InnoDB: PUNCH HOLE support available
-
InnoDB: Mutexes and rw_locks use GCC atomic builtins
-
InnoDB: Uses event mutexes
-
InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier
-
InnoDB: Compressed tables use zlib 1 . 2 . 3
-
InnoDB: Number of pools: 1
-
InnoDB: Using CPU crc32 instructions
-
InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M
-
InnoDB: Completed initialization of buffer pool
-
InnoDB: page_cleaner coordinator priority: -20
-
InnoDB: Highest supported file format is Barracuda .
-
InnoDB: Log scan progressed past the checkpoint lsn 1540210034
-
InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )
-
InnoDB: Doing recovery: scanned up to log sequence number 1540210043 ( % )
-
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 .
-
InnoDB: Database was not shutdown normally!
-
InnoDB: Starting crash recovery .
-
InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001
-
InnoDB: Creating shared tablespace for temporary tables
-
InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .
-
InnoDB: File './ibtmp1' size is now 12 MB .
-
InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .
-
InnoDB: 32 non-redo rollback segment ( s ) are active .
-
InnoDB: Waiting for purge to start
-
InnoDB: 5 . 7 . 13 started ; log sequence number 1540210043
-
InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001
-
-
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
-
InnoDB: FTS optimize thread exiting .
-
InnoDB: Starting shutdown . . .
-
InnoDB: Shutdown completed ; log sequence number 1540210062
-
InnoDB: Number of pools: 1
-
180612 11:07:15 [ 01 ] Copying / backup / incr_backup / 2018-06-12_10-47-16 / test / test . frm to . / test / test . frm
-
InnoDB: 5 . 7 . 13 started ; log sequence number 1540210197
-
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
-
InnoDB: FTS optimize thread exiting .
-
InnoDB: Starting shutdown . . .
-
InnoDB: Shutdown completed ; log sequence number 1540210216
-
180612 11:07:20 completed OK!
14、把所有合在一起的数据进行一次apply,回滚未提交的数据
点击( 此处 )折叠或打开
-
[ root @ mysql5 bin ] # . / innobackupex --apply- log / backup / full_backup / 2018-06-12_10-30-29 /
-
180612 11:09:59 innobackupex: Starting the apply- log operation
-
-
IMPORTANT: Please check that the apply- log run completes successfully .
-
At the end of a successful apply- log run innobackupex
-
prints " completed OK! " .
-
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
xtrabackup: cd to / backup / full_backup / 2018-06-12_10-30-29 /
-
xtrabackup: This target seems to be already prepared .
-
InnoDB: Number of pools: 1
-
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare' .
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = .
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = .
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
xtrabackup: Starting InnoDB instance for recovery .
-
xtrabackup: Using 104857600 bytes for buffer pool ( set by --use-memory parameter )
-
InnoDB: PUNCH HOLE support available
-
InnoDB: Mutexes and rw_locks use GCC atomic builtins
-
InnoDB: Uses event mutexes
-
InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier
-
InnoDB: Compressed tables use zlib 1 . 2 . 3
-
InnoDB: Number of pools: 1
-
InnoDB: Using CPU crc32 instructions
-
InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M
-
InnoDB: Completed initialization of buffer pool
-
InnoDB: page_cleaner coordinator priority: -20
-
InnoDB: Highest supported file format is Barracuda .
-
InnoDB: Removed temporary tablespace data file: " ibtmp1 "
-
InnoDB: Creating shared tablespace for temporary tables
-
InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .
-
InnoDB: File './ibtmp1' size is now 12 MB .
-
InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .
-
InnoDB: 32 non-redo rollback segment ( s ) are active .
-
InnoDB: Waiting for purge to start
-
InnoDB: 5 . 7 . 13 started ; log sequence number 1540210216
-
InnoDB: xtrabackup: Last MySQL binlog file position 3349 , file name mysql- bin . 000001
-
-
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
-
InnoDB: FTS optimize thread exiting .
-
InnoDB: Starting shutdown . . .
-
InnoDB: Shutdown completed ; log sequence number 1540210235
-
InnoDB: Number of pools: 1
-
xtrabackup: using the following InnoDB configuration for recovery:
-
xtrabackup: innodb_data_home_dir = .
-
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
-
xtrabackup: innodb_log_group_home_dir = .
-
xtrabackup: innodb_log_files_in_group = 2
-
xtrabackup: innodb_log_file_size = 67108864
-
InnoDB: PUNCH HOLE support available
-
InnoDB: Mutexes and rw_locks use GCC atomic builtins
-
InnoDB: Uses event mutexes
-
InnoDB: GCC builtin __sync_synchronize ( ) is used for memory barrier
-
InnoDB: Compressed tables use zlib 1 . 2 . 3
-
InnoDB: Number of pools: 1
-
InnoDB: Using CPU crc32 instructions
-
InnoDB: Initializing buffer pool , total size = 100M , instances = 1 , chunk size = 100M
-
InnoDB: Completed initialization of buffer pool
-
InnoDB: page_cleaner coordinator priority: -20
-
InnoDB: Highest supported file format is Barracuda .
-
InnoDB: Removed temporary tablespace data file: " ibtmp1 "
-
InnoDB: Creating shared tablespace for temporary tables
-
InnoDB: Setting file './ibtmp1' size to 12 MB . Physically writing the file full ; Please wait . . .
-
InnoDB: File './ibtmp1' size is now 12 MB .
-
InnoDB: 96 redo rollback segment ( s ) found . 1 redo rollback segment ( s ) are active .
-
InnoDB: 32 non-redo rollback segment ( s ) are active .
-
InnoDB: Waiting for purge to start
-
InnoDB: 5 . 7 . 13 started ; log sequence number 1540210235
-
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
-
InnoDB: FTS optimize thread exiting .
-
InnoDB: Starting shutdown . . .
-
InnoDB: Shutdown completed ; log sequence number 1540210254
-
180612 11:10:02 completed OK!
15、进行restore操作,将准备好的数据copy到data目录
点击( 此处 )折叠或打开
-
[ root @ mysql5 bin ] # . / innobackupex --defaults-file = / etc / my . cnf --copy-back / backup / full_backup / 2018-06-12_10-30-29 /
-
180612 11:14:02 innobackupex: Starting the copy-back operation
-
-
IMPORTANT: Please check that the copy-back run completes successfully .
-
At the end of a successful copy-back run innobackupex
-
prints " completed OK! " .
-
-
. / innobackupex version 2 . 4 . 9 based on MySQL server 5 . 7 . 13 Linux ( x86_64 ) ( revision id: a467167cdd4 )
-
180612 11:14:02 [ 01 ] Copying ib_logfile0 to / mysql / data / ib_logfile0
-
180612 11:14:02 [ 01 ] . . . done
-
180612 11:14:03 [ 01 ] Copying ib_logfile1 to / mysql / data / ib_logfile1
-
180612 11:14:03 [ 01 ] . . . done
-
180612 11:14:03 [ 01 ] Copying ibdata1 to / mysql / data / ibdata1
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / ib_buffer_pool to / mysql / data / ib_buffer_pool
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / test . frm to / mysql / data / test / test . frm
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / t_user_info . frm to / mysql / data / test / t_user_info . frm
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . frm to / mysql / data / test / t_tou_info . frm
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . MYI to / mysql / data / test / t_tou_info . MYI
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / t_user_info . ibd to / mysql / data / test / t_user_info . ibd
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / t_tou_info . MYD to / mysql / data / test / t_tou_info . MYD
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / test / db . opt to / mysql / data / test / db . opt
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / xcredit / tb_generic_data . frm to / mysql / data / xcredit / tb_generic_data . frm
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:07 [ 01 ] Copying . / xcredit / tb_openapi . ibd to / mysql / data / xcredit / tb_openapi . ibd
-
180612 11:14:07 [ 01 ] . . . done
-
180612 11:14:12 [ 01 ] . . . done
-
180612 11:14:12 [ 01 ] Copying . / performance_schema / replication_applier_configuration . frm to / mysql / data / performance_schema / replication_applier_configuration . frm
-
180612 11:14:12 [ 01 ] . . . done
-
180612 11:14:12 completed OK!
16、修改data目录权限,重启MySQL服务
点击( 此处 )折叠或打开
-
[ root @ mysql5 mysql ] # ll
-
total 8
-
drwxrwxr-x . 7 mysql mysql 4096 Jun 12 11:14 data
-
drwxr-xr-x 6 mysql mysql 4096 Mar 30 10:08 data_bak
-
[ root @ mysql5 mysql ] #
-
[ root @ mysql5 mysql ] #
-
[ root @ mysql5 mysql ] # cd data
-
[ root @ mysql5 data ] # ll
-
total 286760
-
-rw-r----- 1 root root 509 Jun 12 11:14 ib_buffer_pool
-
-rw-r----- 1 root root 146800640 Jun 12 11:14 ibdata1
-
-rw-r----- 1 root root 67108864 Jun 12 11:14 ib_logfile0
-
-rw-r----- 1 root root 67108864 Jun 12 11:14 ib_logfile1
-
-rw-r----- 1 root root 12582912 Jun 12 11:14 ibtmp1
-
drwxr-x--- 2 root root 4096 Jun 12 11:14 mysql
-
drwxr-x--- 2 root root 4096 Jun 12 11:14 performance_schema
-
drwxr-x--- 2 root root 12288 Jun 12 11:14 sys
-
drwxr-x--- 2 root root 4096 Jun 12 11:14 test
-
drwxr-x--- 2 root root 4096 Jun 12 11:14 xcredit
-
-rw-r----- 1 root root 22 Jun 12 11:14 xtrabackup_binlog_pos_innodb
-
-rw-r----- 1 root root 608 Jun 12 11:14 xtrabackup_info
-
[ root @ mysql5 data ] # cd . .
-
[ root @ mysql5 mysql ] # chown -R mysql:mysql data
-
[ root @ mysql5 mysql ] # cd data
-
[ root @ mysql5 data ] # ll
-
total 286760
-
-rw-r----- 1 mysql mysql 509 Jun 12 11:14 ib_buffer_pool
-
-rw-r----- 1 mysql mysql 146800640 Jun 12 11:14 ibdata1
-
-rw-r----- 1 mysql mysql 67108864 Jun 12 11:14 ib_logfile0
-
-rw-r----- 1 mysql mysql 67108864 Jun 12 11:14 ib_logfile1
-
-rw-r----- 1 mysql mysql 12582912 Jun 12 11:14 ibtmp1
-
drwxr-x--- 2 mysql mysql 4096 Jun 12 11:14 mysql
-
drwxr-x--- 2 mysql mysql 4096 Jun 12 11:14 performance_schema
-
drwxr-x--- 2 mysql mysql 12288 Jun 12 11:14 sys
-
drwxr-x--- 2 mysql mysql 4096 Jun 12 11:14 test
-
drwxr-x--- 2 mysql mysql 4096 Jun 12 11:14 xcredit
-
-rw-r----- 1 mysql mysql 22 Jun 12 11:14 xtrabackup_binlog_pos_innodb
-
-rw-r----- 1 mysql mysql 608 Jun 12 11:14 xtrabackup_info
-
[ root @ mysql5 data ] # service mysql start
-
Starting MySQL . Logging to '/mysql/data/mysql-err.log' .
-
. [ OK ]
17、对test表数据进行验证
点击( 此处 )折叠或打开
-
mysql > select * from test ;
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| id | user_type | code |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
-
| 1 | 0 | 30 |
-
| 2 | 0 | 40 |
-
| 3 | 0 | 50 |
-
+ - - - - - - + - - - - - - - - - - - + - - - - - - +
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2156006/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30135314/viewspace-2156006/