首页 > > 网络编程 > Mysql >

利用Xtrabackup搭建GTID主从复制（一主一从）

2018-07-25 13:13:49来源：博客园阅读 ()

Preface

I've been demonstrated how to implement a master-slave structure using mysqldump in my previous blog.I'm gonna use Xtrabackup to do it again now.

Framework

Hostname	IP/Port	Identity	OS Version	MySQL Version	GTID Mode	Binlog Format
zlm2	192.168.1.101/3306	master	CentOS 7.0	5.7.21	on	row
zlm3	192.168.1.102/3306	slave	CentOS 7.0	5.7.21	on	row

Procedure

Generate a physical backup on master.

  1 [root@zlm2 03:59:24 ~]
  2 #innobackupex --defaults-file-/data/mysql/mysql3306/my3306.cnf /data/backup
  3 xtrabackup: recognized server arguments: 
  4 xtrabackup: recognized client arguments: 
  5 180725 04:00:42 innobackupex: Starting the backup operation
  6 
  7 IMPORTANT: Please check that the backup run completes successfully.
  8            At the end of a successful backup run innobackupex
  9            prints "completed OK!".
 10 
 11 180725 04:00:42  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=192.168.1.101;mysql_socket=/tmp/mysql3306.sock' as 'zlm'  (using password: YES).
 12 180725 04:00:42  version_check Connected to MySQL server
 13 180725 04:00:42  version_check Executing a version check against the server...
 14 180725 04:00:42  version_check Done.
 15 180725 04:00:42 Connecting to MySQL server host: 192.168.1.101, user: zlm, password: set, port: not set, socket: /tmp/mysql3306.sock
 16 Using server version 5.7.21-log
 17 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
 18 xtrabackup: uses posix_fadvise().
 19 xtrabackup: cd to /data/mysql/mysql3306/data/ //Change directory to the datadir of current instance.
 20 xtrabackup: open files limit requested 0, set to 1024
 21 xtrabackup: using the following InnoDB configuration:
 22 xtrabackup:   innodb_data_home_dir = .
 23 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 24 xtrabackup:   innodb_log_group_home_dir = ./
 25 xtrabackup:   innodb_log_files_in_group = 3
 26 xtrabackup:   innodb_log_file_size = 104857600
 27 InnoDB: Number of pools: 1
 28 180725 04:00:43 >> log scanned up to (10055645489)
 29 xtrabackup: Generating a list of tablespaces
 30 InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
 31 180725 04:00:43 [01] Copying ./ibdata1 to /data/backup/2018-07-25_04-00-42/ibdata1 //Copy shared tablespace file to the target of backup directory.
 32 180725 04:00:44 >> log scanned up to (10055645489) //The redo logfiles are continuously written before copy .ibd files.
 33 180725 04:00:45 >> log scanned up to (10055645489)
 34 180725 04:00:45 [01]        ...done
 35 180725 04:00:46 >> log scanned up to (10055645489)
 36 180725 04:00:47 >> log scanned up to (10055645489)
 37 180725 04:00:47 [01] Copying ./mysql/plugin.ibd to /data/backup/2018-07-25_04-00-42/mysql/plugin.ibd //begins to copy data of innodb table(.ibd).
 38 180725 04:00:47 [01]        ...done
 39 180725 04:00:47 [01] Copying ./mysql/servers.ibd to /data/backup/2018-07-25_04-00-42/mysql/servers.ibd
 40 180725 04:00:47 [01]        ...done
 41 180725 04:00:47 [01] Copying ./mysql/help_topic.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_topic.ibd
 42 180725 04:00:47 [01]        ...done
 43 180725 04:00:48 >> log scanned up to (10055645489)
 44 180725 04:00:48 [01] Copying ./mysql/help_category.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_category.ibd
 45 180725 04:00:48 [01]        ...done
 46 180725 04:00:48 [01] Copying ./mysql/help_relation.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_relation.ibd
 47 180725 04:00:48 [01]        ...done
 48 180725 04:00:48 [01] Copying ./mysql/help_keyword.ibd to /data/backup/2018-07-25_04-00-42/mysql/help_keyword.ibd
 49 180725 04:00:48 [01]        ...done
 50 180725 04:00:48 [01] Copying ./mysql/time_zone_name.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_name.ibd
 51 180725 04:00:48 [01]        ...done
 52 180725 04:00:48 [01] Copying ./mysql/time_zone.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone.ibd
 53 180725 04:00:48 [01]        ...done
 54 180725 04:00:48 [01] Copying ./mysql/time_zone_transition.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_transition.ibd
 55 180725 04:00:48 [01]        ...done
 56 180725 04:00:48 [01] Copying ./mysql/time_zone_transition_type.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_transition_type.ibd
 57 180725 04:00:48 [01]        ...done
 58 180725 04:00:48 [01] Copying ./mysql/time_zone_leap_second.ibd to /data/backup/2018-07-25_04-00-42/mysql/time_zone_leap_second.ibd
 59 180725 04:00:48 [01]        ...done
 60 180725 04:00:48 [01] Copying ./mysql/innodb_table_stats.ibd to /data/backup/2018-07-25_04-00-42/mysql/innodb_table_stats.ibd
 61 180725 04:00:48 [01]        ...done
 62 180725 04:00:48 [01] Copying ./mysql/innodb_index_stats.ibd to /data/backup/2018-07-25_04-00-42/mysql/innodb_index_stats.ibd
 63 180725 04:00:48 [01]        ...done
 64 180725 04:00:48 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_relay_log_info.ibd
 65 180725 04:00:48 [01]        ...done
 66 180725 04:00:48 [01] Copying ./mysql/slave_master_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_master_info.ibd
 67 180725 04:00:48 [01]        ...done
 68 180725 04:00:48 [01] Copying ./mysql/slave_worker_info.ibd to /data/backup/2018-07-25_04-00-42/mysql/slave_worker_info.ibd
 69 180725 04:00:48 [01]        ...done
 70 180725 04:00:48 [01] Copying ./mysql/gtid_executed.ibd to /data/backup/2018-07-25_04-00-42/mysql/gtid_executed.ibd
 71 180725 04:00:48 [01]        ...done
 72 180725 04:00:48 [01] Copying ./mysql/server_cost.ibd to /data/backup/2018-07-25_04-00-42/mysql/server_cost.ibd
 73 180725 04:00:48 [01]        ...done
 74 180725 04:00:48 [01] Copying ./mysql/engine_cost.ibd to /data/backup/2018-07-25_04-00-42/mysql/engine_cost.ibd
 75 180725 04:00:48 [01]        ...done
 76 180725 04:00:48 [01] Copying ./sys/sys_config.ibd to /data/backup/2018-07-25_04-00-42/sys/sys_config.ibd
 77 180725 04:00:48 [01]        ...done
 78 180725 04:00:48 [01] Copying ./zlm/test_innodb.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_innodb.ibd
 79 180725 04:00:48 [01]        ...done
 80 180725 04:00:48 [01] Copying ./zlm/test_ddl.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_ddl.ibd
 81 180725 04:00:48 [01]        ...done
 82 180725 04:00:48 [01] Copying ./zlm/test_ddl_no_pk.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_ddl_no_pk.ibd
 83 180725 04:00:48 [01]        ...done
 84 180725 04:00:48 [01] Copying ./zlm/test_index_usage.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_index_usage.ibd
 85 180725 04:00:48 [01]        ...done
 86 180725 04:00:49 >> log scanned up to (10055645489)
 87 180725 04:00:49 [01] Copying ./zlm/test_charset.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_charset.ibd
 88 180725 04:00:49 [01]        ...done
 89 180725 04:00:49 [01] Copying ./zlm/test_null.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_null.ibd
 90 180725 04:00:49 [01]        ...done
 91 180725 04:00:49 [01] Copying ./zlm/test_convert.ibd to /data/backup/2018-07-25_04-00-42/zlm/test_convert.ibd
 92 180725 04:00:49 [01]        ...done
 93 180725 04:00:50 >> log scanned up to (10055645489)
 94 180725 04:00:50 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... //In order to avoid writting binlog,new version will use "NO_WRITE_TO_BINLOG" option.
 95 180725 04:00:50 Executing FLUSH TABLES WITH READ LOCK... //Generate a FTWRL.
 96 180725 04:00:50 Starting to backup non-InnoDB tables and files
 97 180725 04:00:50 [01] Copying ./mysql/db.opt to /data/backup/2018-07-25_04-00-42/mysql/db.opt
 98 180725 04:00:50 [01]        ...done
 99 180725 04:00:50 [01] Copying ./mysql/db.frm to /data/backup/2018-07-25_04-00-42/mysql/db.frm
100 180725 04:00:50 [01]        ...done
101 180725 04:00:50 [01] Copying ./mysql/db.MYI to /data/backup/2018-07-25_04-00-42/mysql/db.MYI
102 180725 04:00:50 [01]        ...done
103 180725 04:00:50 [01] Copying ./mysql/db.MYD to /data/backup/2018-07-25_04-00-42/mysql/db.MYD
104 180725 04:00:50 [01]        ...done
105 180725 04:00:50 [01] Copying ./mysql/user.frm to /data/backup/2018-07-25_04-00-42/mysql/user.frm
106 180725 04:00:50 [01]        ...done
107 180725 04:00:50 [01] Copying ./mysql/user.MYI to /data/backup/2018-07-25_04-00-42/mysql/user.MYI
108 180725 04:00:50 [01]        ...done
109 180725 04:00:50 [01] Copying ./mysql/user.MYD to /data/backup/2018-07-25_04-00-42/mysql/user.MYD
110 
111 ... //A bundle of MyISAM tables(.MYI,.MYD,.frm) and innodb table structure(.frm) copied here,omitted.
112 
113 180725 04:00:51 [01] Copying ./zlm/db.opt to /data/backup/2018-07-25_04-00-42/zlm/db.opt
114 180725 04:00:51 [01]        ...done
115 180725 04:00:51 [01] Copying ./zlm/test_innodb.frm to /data/backup/2018-07-25_04-00-42/zlm/test_innodb.frm
116 180725 04:00:51 [01]        ...done
117 180725 04:00:51 [01] Copying ./zlm/test_myisam.frm to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.frm
118 180725 04:00:51 [01]        ...done
119 180725 04:00:51 [01] Copying ./zlm/test_myisam.MYI to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.MYI
120 180725 04:00:51 [01]        ...done
121 180725 04:00:51 [01] Copying ./zlm/test_myisam.MYD to /data/backup/2018-07-25_04-00-42/zlm/test_myisam.MYD
122 180725 04:00:51 [01]        ...done
123 180725 04:00:51 [01] Copying ./zlm/test_ddl.frm to /data/backup/2018-07-25_04-00-42/zlm/test_ddl.frm
124 180725 04:00:51 [01]        ...done
125 180725 04:00:51 [01] Copying ./zlm/test_ddl_no_pk.frm to /data/backup/2018-07-25_04-00-42/zlm/test_ddl_no_pk.frm
126 180725 04:00:51 [01]        ...done
127 180725 04:00:51 [01] Copying ./zlm/test_index_usage.frm to /data/backup/2018-07-25_04-00-42/zlm/test_index_usage.frm
128 180725 04:00:51 [01]        ...done
129 180725 04:00:51 [01] Copying ./zlm/test_charset.frm to /data/backup/2018-07-25_04-00-42/zlm/test_charset.frm
130 180725 04:00:51 [01]        ...done
131 180725 04:00:51 [01] Copying ./zlm/test_null.frm to /data/backup/2018-07-25_04-00-42/zlm/test_null.frm
132 180725 04:00:51 [01]        ...done
133 180725 04:00:51 [01] Copying ./zlm/test_convert.frm to /data/backup/2018-07-25_04-00-42/zlm/test_convert.frm
134 180725 04:00:51 [01]        ...done
135 180725 04:00:51 Finished backing up non-InnoDB tables and files
136 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/xtrabackup_binlog_info
137 180725 04:00:51 [00]        ...done
138 180725 04:00:51 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... //Flushes redo logfiles whith writting them into binlog.
139 xtrabackup: The latest check point (for incremental): '10055645480' //Prints the last checkpoint.
140 xtrabackup: Stopping log copying thread.
141 .180725 04:00:51 >> log scanned up to (10055645489)
142 
143 180725 04:00:51 Executing UNLOCK TABLES
144 180725 04:00:51 All tables unlocked
145 180725 04:00:51 [00] Copying ib_buffer_pool to /data/backup/2018-07-25_04-00-42/ib_buffer_pool
146 180725 04:00:51 [00]        ...done
147 180725 04:00:51 Backup created in directory '/data/backup/2018-07-25_04-00-42/'
148 MySQL binlog position: filename 'mysql-bin.000027', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021'
149 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/backup-my.cnf
150 180725 04:00:51 [00]        ...done
151 180725 04:00:51 [00] Writing /data/backup/2018-07-25_04-00-42/xtrabackup_info 
152 180725 04:00:51 [00]        ...done
153 xtrabackup: Transaction log of lsn (10055645480) to (10055645489) was copied. //Prints the backuped lsn information.
154 180725 04:00:51 completed OK!

Check the outputfile of Xtrabackup.

 1 [root@zlm2 04:00:51 /data/backup]
 2 #cd /data/backup/
 3 
 4 [root@zlm2 04:03:53 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 6 root root 4096 Jul 25 04:00 2018-07-25_04-00-42
 8 
 9 [root@zlm2 04:03:57 /data/backup]
10 #cd 2018-07-25_04-00-42/
11 
12 [root@zlm2 04:04:04 /data/backup/2018-07-25_04-00-42]
13 #ls -l
14 total 102456
15 -rw-r----- 1 root root       489 Jul 25 04:00 backup-my.cnf
16 -rw-r----- 1 root root      1078 Jul 25 04:00 ib_buffer_pool
17 -rw-r----- 1 root root 104857600 Jul 25 04:00 ibdata1
18 drwxr-x--- 2 root root      4096 Jul 25 04:00 mysql
19 drwxr-x--- 2 root root      8192 Jul 25 04:00 performance_schema
20 drwxr-x--- 2 root root      8192 Jul 25 04:00 sys
21 -rw-r----- 1 root root        68 Jul 25 04:00 xtrabackup_binlog_info
22 -rw-r----- 1 root root       121 Jul 25 04:00 xtrabackup_checkpoints
23 -rw-r----- 1 root root       570 Jul 25 04:00 xtrabackup_info
24 -rw-r----- 1 root root      2560 Jul 25 04:00 xtrabackup_logfile
25 drwxr-x--- 2 root root      4096 Jul 25 04:00 zlm
26 
27 [root@zlm2 04:04:07 /data/backup/2018-07-25_04-00-42]
28 #cat xtrabackup_binlog_info
29 mysql-bin.000027    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021
30 
31 [root@zlm2 04:04:22 /data/backup/2018-07-25_04-00-42]
32 #cat xtrabackup_checkpoints
33 backup_type = full-backuped
34 from_lsn = 0
35 to_lsn = 10055645480
36 last_lsn = 10055645489
37 compact = 0
38 recover_binlog_info = 0
39 
40 [root@zlm2 04:04:27 /data/backup/2018-07-25_04-00-42]
41 #cat xtrabackup_info
42 uuid = 8e8d59a6-8fae-11e8-87d6-080027de0e0e
43 name = 
44 tool_name = innobackupex
45 tool_command = --defaults-file-/data/mysql/mysql3306/my3306.cnf /data/backup
46 tool_version = 2.4.12
47 ibbackup_version = 2.4.12
48 server_version = 5.7.21-log
49 start_time = 2018-07-25 04:00:42
50 end_time = 2018-07-25 04:00:51
51 lock_time = 0
52 binlog_pos = filename 'mysql-bin.000027', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021'
53 innodb_from_lsn = 0
54 innodb_to_lsn = 10055645480
55 partial = N
56 incremental = N
57 format = file
58 compact = N
59 compressed = N
60 encrypted = N

Package the backup set and transfer it to slave server.

 1 [root@zlm2 04:06:06 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 6 root root 4096 Jul 25 04:00 2018-07-25_04-00-42
 5 
 6 [root@zlm2 04:06:10 /data/backup]
 7 #tar cf Xtrabk_full.tar 2018-07-25_04-00-42/
 8 
 9 [root@zlm2 04:06:27 /data/backup]
10 #ls -l
11 total 140856
12 drwxr-x--- 6 root root      4096 Jul 25 04:00 2018-07-25_04-00-42
13 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
14 
15 [root@zlm2 04:06:28 /data/backup]
16 #scp Xtrabk_full.tar zlm3:/data/backup
17 root@zlm3's password: 
18 Xtrabk_full.tar

Unpack the backup set.

 1 [root@zlm3 04:07:33 ~]
 2 #cd /data/backup
 3 
 4 [root@zlm3 04:07:40 /data/backup]
 5 #ls -l
 6 total 140852
 7 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
 8 
 9 [root@zlm3 04:07:41 /data/backup]
10 #tar xf Xtrabk_full.tar 
11 
12 [root@zlm3 04:07:49 /data/backup]
13 #ls -l
14 total 140856
15 drwxr-x--- 6 root root      4096 Jul 25 04:00 2018-07-25_04-00-42
16 -rw-r--r-- 1 root root 144230400 Jul 25 04:06 Xtrabk_full.tar
17 
18 [root@zlm3 04:07:51 /data/backup]
19 #chown -R mysql.mysql 2018-07-25_04-00-42/
20 
21 [root@zlm3 04:08:16 /data/backup]
22 #ls -l
23 total 140856
24 drwxr-x--- 6 mysql mysql      4096 Jul 25 04:00 2018-07-25_04-00-42
25 -rw-r--r-- 1 root  root  144230400 Jul 25 04:06 Xtrabk_full.tar

Apply the redo logfiles,make the datafiles on slave be consistent with those on master(combine the redo into datafiles).

  1 [root@zlm3 04:09:03 /data/backup]
  2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.conf --apply-log /data/backup/2018-07-25_04-00-42/
  3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1 
  4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1 
  5 180725 04:09:34 innobackupex: Starting the apply-log operation
  6 
  7 IMPORTANT: Please check that the apply-log run completes successfully.
  8            At the end of a successful apply-log run innobackupex
  9            prints "completed OK!".
 10 
 11 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
 12 xtrabackup: cd to /data/backup/2018-07-25_04-00-42/
 13 xtrabackup: This target seems to be not prepared yet.
 14 InnoDB: Number of pools: 1
 15 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(10055645480)
 16 xtrabackup: using the following InnoDB configuration for recovery:
 17 xtrabackup:   innodb_data_home_dir = .
 18 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 19 xtrabackup:   innodb_log_group_home_dir = .
 20 xtrabackup:   innodb_log_files_in_group = 1
 21 xtrabackup:   innodb_log_file_size = 8388608
 22 xtrabackup: using the following InnoDB configuration for recovery:
 23 xtrabackup:   innodb_data_home_dir = .
 24 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 25 xtrabackup:   innodb_log_group_home_dir = .
 26 xtrabackup:   innodb_log_files_in_group = 1
 27 xtrabackup:   innodb_log_file_size = 8388608
 28 xtrabackup: Starting InnoDB instance for recovery.
 29 xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
 30 InnoDB: PUNCH HOLE support available
 31 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 32 InnoDB: Uses event mutexes
 33 InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 34 InnoDB: Compressed tables use zlib 1.2.7
 35 InnoDB: Number of pools: 1
 36 InnoDB: Using CPU crc32 instructions
 37 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
 38 InnoDB: Completed initialization of buffer pool
 39 InnoDB: page_cleaner coordinator priority: -20
 40 InnoDB: Highest supported file format is Barracuda.
 41 InnoDB: Log scan progressed past the checkpoint lsn 10055645480
 42 InnoDB: Doing recovery: scanned up to log sequence number 10055645489 (0%)
 43 InnoDB: Database was not shutdown normally!
 44 InnoDB: Starting crash recovery.
 45 InnoDB: xtrabackup: Last MySQL binlog file position 155764470, file name mysql-bin.000019
 46 InnoDB: Creating shared tablespace for temporary tables
 47 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 48 InnoDB: File './ibtmp1' size is now 12 MB.
 49 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
 50 InnoDB: 32 non-redo rollback segment(s) are active.
 51 InnoDB: Waiting for purge to start
 52 InnoDB: 5.7.19 started; log sequence number 10055645489
 53 InnoDB: xtrabackup: Last MySQL binlog file position 155764470, file name mysql-bin.000019
 54 
 55 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
 56 InnoDB: FTS optimize thread exiting.
 57 InnoDB: Starting shutdown...
 58 InnoDB: Shutdown completed; log sequence number 10055645664
 59 InnoDB: Number of pools: 1
 60 xtrabackup: using the following InnoDB configuration for recovery:
 61 xtrabackup:   innodb_data_home_dir = .
 62 xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
 63 xtrabackup:   innodb_log_group_home_dir = .
 64 xtrabackup:   innodb_log_files_in_group = 3
 65 xtrabackup:   innodb_log_file_size = 104857600
 66 InnoDB: PUNCH HOLE support available
 67 InnoDB: Mutexes and rw_locks use GCC atomic builtins
 68 InnoDB: Uses event mutexes
 69 InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 70 InnoDB: Compressed tables use zlib 1.2.7
 71 InnoDB: Number of pools: 1
 72 InnoDB: Using CPU crc32 instructions
 73 InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
 74 InnoDB: Completed initialization of buffer pool
 75 InnoDB: page_cleaner coordinator priority: -20
 76 InnoDB: Setting log file ./ib_logfile101 size to 100 MB
 77 InnoDB: Progress in MB:
 78  100
 79 InnoDB: Setting log file ./ib_logfile1 size to 100 MB
 80 InnoDB: Progress in MB:
 81  100
 82 InnoDB: Setting log file ./ib_logfile2 size to 100 MB
 83 InnoDB: Progress in MB:
 84  100
 85 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
 86 InnoDB: New log files created, LSN=10055645664
 87 InnoDB: Highest supported file format is Barracuda.
 88 InnoDB: Log scan progressed past the checkpoint lsn 10055645708
 89 InnoDB: Doing recovery: scanned up to log sequence number 10055645717 (0%)
 90 InnoDB: Database was not shutdown normally!
 91 InnoDB: Starting crash recovery.
 92 InnoDB: xtrabackup: Last MySQL binlog file position 155764470, file name mysql-bin.000019
 93 InnoDB: Removed temporary tablespace data file: "ibtmp1"
 94 InnoDB: Creating shared tablespace for temporary tables
 95 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 96 InnoDB: File './ibtmp1' size is now 12 MB.
 97 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
 98 InnoDB: 32 non-redo rollback segment(s) are active.
 99 InnoDB: Waiting for purge to start
100 InnoDB: page_cleaner: 1000ms intended loop took 15837ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
101 InnoDB: 5.7.19 started; log sequence number 10055645717
102 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
103 InnoDB: FTS optimize thread exiting.
104 InnoDB: Starting shutdown...
105 InnoDB: Shutdown completed; log sequence number 10055645736
106 180725 04:09:55 completed OK!
107 
108 [root@zlm3 04:11:11 /data/backup/2018-07-25_04-00-42]
109 #cat xtrabackup_binlog_info
110 mysql-bin.000027    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021
111 
112 [root@zlm3 04:11:27 /data/backup/2018-07-25_04-00-42]
113 #cat xtrabackup_binlog_pos_innodb //After applied logs,the binlog infomation is different with it in "xtrabackup_binlog_info".It's acceptable.
114 mysql-bin.000019    155764470

Copy back the datafiles to the target instance on slave(shutdown the instance first).

  1 [root@zlm3 04:11:37 /data/backup/2018-07-25_04-00-42]
  2 #mysqladmin -S /tmp/mysql3306.sock shutdown
  3 
  4 [root@zlm3 04:14:21 /data/backup/2018-07-25_04-00-42]
  5 #ps aux|grep mysqld
  6 root      4103  0.0  0.0 112640   960 pts/1    R+   04:14   0:00 grep --color=auto mysqld
  7 
  8 [root@zlm3 04:18:52 /data/backup/2018-07-25_04-00-42]
  9 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-25_04-00-42/
 10 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
 11 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
 12 180725 04:19:15 innobackupex: Starting the copy-back operation
 13 
 14 IMPORTANT: Please check that the copy-back run completes successfully.
 15            At the end of a successful copy-back run innobackupex
 16            prints "completed OK!".
 17 
 18 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
 19 Original data directory /data/mysql/mysql3306/data is not empty! //It indicates the directory should be cleaned.
 20 
 21 [root@zlm3 04:19:15 /data/backup/2018-07-25_04-00-42]
 22 #cd /data/mysql/mysql3306/data
 23 
 24 [root@zlm3 04:22:05 /data/mysql/mysql3306/data]
 25 #ls -l
 26 total 410992
 27 -rw-r----- 1 mysql mysql        56 Apr 28 14:12 auto.cnf
 28 -rw-r----- 1 mysql mysql    140490 Jul 25 04:14 error.log
 29 -rw-r----- 1 mysql mysql       502 Jul 25 04:14 ib_buffer_pool
 30 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:14 ibdata1
 31 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:14 ib_logfile0
 32 -rw-r----- 1 mysql mysql 104857600 Jul 19 04:31 ib_logfile1
 33 -rw-r----- 1 mysql mysql 104857600 Jul 19 04:32 ib_logfile2
 34 -rw-r----- 1 mysql mysql      4311 Jul 19 08:56 innodb_status.3754
 35 -rw-r----- 1 mysql mysql      4345 Jul 13 08:46 innodb_status.3913
 36 -rw-r----- 1 mysql mysql         0 Jul 19 08:56 innodb_status.4135
 37 -rw-r----- 1 mysql mysql         0 Jul 19 08:57 innodb_status.4222
 38 -rw-r----- 1 mysql mysql         0 Jul 13 08:46 innodb_status.4398
 39 -rw-r----- 1 mysql mysql         0 Jul 13 08:47 innodb_status.4460
 40 -rw-r----- 1 mysql mysql      3830 Jun  4 07:57 innodb_status.7328
 41 drwxr-x--- 2 mysql mysql      4096 Jul 13 10:56 mysql
 42 drwxr-x--- 2 mysql mysql      8192 Apr 28 14:12 performance_schema
 43 -rw-r----- 1 mysql mysql       398 Jul 20 11:59 relay-bin.000074
 44 -rw-r----- 1 mysql mysql       288 Jul 20 11:59 relay-bin.000075
 45 -rw-r----- 1 mysql mysql       169 Jul 25 04:14 relay-bin.000076
 46 -rw-r----- 1 mysql mysql       219 Jun 15 07:19 relay-bin-group_replication_applier.000003
 47 -rw-r----- 1 mysql mysql      2588 Jun 15 11:28 relay-bin-group_replication_applier.000004
 48 -rw-r----- 1 mysql mysql       169 Jun 21 11:41 relay-bin-group_replication_applier.000005
 49 -rw-r----- 1 mysql mysql       169 Jun 22 11:21 relay-bin-group_replication_applier.000006
 50 -rw-r----- 1 mysql mysql       169 Jun 23 12:00 relay-bin-group_replication_applier.000007
 51 -rw-r----- 1 mysql mysql       169 Jun 25 11:47 relay-bin-group_replication_applier.000008
 52 -rw-r----- 1 mysql mysql       169 Jul  5 11:28 relay-bin-group_replication_applier.000009
 53 -rw-r----- 1 mysql mysql       169 Jul 10 11:41 relay-bin-group_replication_applier.000010
 54 -rw-r----- 1 mysql mysql       169 Jul 13 03:15 relay-bin-group_replication_applier.000011
 55 -rw-r----- 1 mysql mysql       150 Jul 13 03:15 relay-bin-group_replication_applier.000012
 56 -rw-r----- 1 mysql mysql       169 Jul 13 10:44 relay-bin-group_replication_applier.000013
 57 -rw-r----- 1 mysql mysql       169 Jul 13 10:53 relay-bin-group_replication_applier.000014
 58 -rw-r----- 1 mysql mysql       169 Jul 13 11:07 relay-bin-group_replication_applier.000015
 59 -rw-r----- 1 mysql mysql       169 Jul 16 09:28 relay-bin-group_replication_applier.000016
 60 -rw-r----- 1 mysql mysql       169 Jul 16 11:59 relay-bin-group_replication_applier.000017
 61 -rw-r----- 1 mysql mysql       169 Jul 18 11:27 relay-bin-group_replication_applier.000018
 62 -rw-r----- 1 mysql mysql       150 Jul 19 02:48 relay-bin-group_replication_applier.000019
 63 -rw-r----- 1 mysql mysql       169 Jul 19 11:25 relay-bin-group_replication_applier.000020
 64 -rw-r----- 1 mysql mysql       169 Jul 20 11:59 relay-bin-group_replication_applier.000021
 65 -rw-r----- 1 mysql mysql       169 Jul 25 04:14 relay-bin-group_replication_applier.000022
 66 -rw-r----- 1 mysql mysql       900 Jul 25 03:52 relay-bin-group_replication_applier.index
 67 -rw-r----- 1 mysql mysql       220 Jun 15 07:19 relay-bin-group_replication_recovery.000001
 68 -rw-r----- 1 mysql mysql       288 Jun 15 11:28 relay-bin-group_replication_recovery.000002
 69 -rw-r----- 1 mysql mysql       169 Jun 21 11:41 relay-bin-group_replication_recovery.000003
 70 -rw-r----- 1 mysql mysql       169 Jun 22 11:21 relay-bin-group_replication_recovery.000004
 71 -rw-r----- 1 mysql mysql       169 Jun 23 12:00 relay-bin-group_replication_recovery.000005
 72 -rw-r----- 1 mysql mysql       169 Jun 25 11:47 relay-bin-group_replication_recovery.000006
 73 -rw-r----- 1 mysql mysql       169 Jul  5 11:28 relay-bin-group_replication_recovery.000007
 74 -rw-r----- 1 mysql mysql       169 Jul 10 11:41 relay-bin-group_replication_recovery.000008
 75 -rw-r----- 1 mysql mysql       169 Jul 13 03:15 relay-bin-group_replication_recovery.000009
 76 -rw-r----- 1 mysql mysql       150 Jul 13 03:15 relay-bin-group_replication_recovery.000010
 77 -rw-r----- 1 mysql mysql       169 Jul 13 10:44 relay-bin-group_replication_recovery.000011
 78 -rw-r----- 1 mysql mysql       169 Jul 13 10:53 relay-bin-group_replication_recovery.000012
 79 -rw-r----- 1 mysql mysql       169 Jul 13 11:07 relay-bin-group_replication_recovery.000013
 80 -rw-r----- 1 mysql mysql       169 Jul 16 09:28 relay-bin-group_replication_recovery.000014
 81 -rw-r----- 1 mysql mysql       169 Jul 16 11:59 relay-bin-group_replication_recovery.000015
 82 -rw-r----- 1 mysql mysql       169 Jul 18 11:27 relay-bin-group_replication_recovery.000016
 83 -rw-r----- 1 mysql mysql       150 Jul 19 02:48 relay-bin-group_replication_recovery.000017
 84 -rw-r----- 1 mysql mysql       169 Jul 19 11:25 relay-bin-group_replication_recovery.000018
 85 -rw-r----- 1 mysql mysql       169 Jul 20 11:59 relay-bin-group_replication_recovery.000019
 86 -rw-r----- 1 mysql mysql       169 Jul 25 04:14 relay-bin-group_replication_recovery.000020
 87 -rw-r----- 1 mysql mysql       920 Jul 25 03:52 relay-bin-group_replication_recovery.index
 88 -rw-r----- 1 mysql mysql        57 Jul 25 03:52 relay-bin.index
 89 -rw-r----- 1 mysql mysql   1018030 Jul 25 03:52 slow.log
 90 drwxr-x--- 2 mysql mysql      8192 Apr 28 14:12 sys
 91 drwxr-x--- 2 mysql mysql      4096 Jul 18 10:06 zlm
 92 -rw-r----- 1 mysql mysql      8074 Jul  5 11:28 zlm3.log
 93 
 94 [root@zlm3 04:22:18 /data/mysql/mysql3306/data]
 95 #rm -rf *
 96 
 97 [root@zlm3 04:22:21 /data/mysql/mysql3306/data]
 98 #ls -l
 99 total 0
100 
101 [root@zlm3 04:22:22 /data/mysql/mysql3306/data]
102 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-25_04-00-42/
103 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
104 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
105 180725 04:22:57 innobackupex: Starting the copy-back operation
106 
107 IMPORTANT: Please check that the copy-back run completes successfully.
108            At the end of a successful copy-back run innobackupex
109            prints "completed OK!".
110 
111 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
112 180725 04:22:57 [01] Copying ib_logfile0 to /data/mysql/mysql3306/data/ib_logfile0
113 180725 04:22:57 [01]        ...done
114 180725 04:22:57 [01] Copying ib_logfile1 to /data/mysql/mysql3306/data/ib_logfile1
115 180725 04:22:58 [01]        ...done
116 180725 04:23:03 [01] Copying ib_logfile2 to /data/mysql/mysql3306/data/ib_logfile2
117 180725 04:23:03 [01]        ...done
118 180725 04:23:08 [01] Copying ibdata1 to /data/mysql/mysql3306/data/ibdata1
119 180725 04:23:09 [01]        ...done
120 180725 04:23:14 [01] Copying ./mysql/plugin.ibd to /data/mysql/mysql3306/data/mysql/plugin.ibd
121 180725 04:23:14 [01]        ...done
122 180725 04:23:14 [01] Copying ./mysql/servers.ibd to /data/mysql/mysql3306/data/mysql/servers.ibd
123 180725 04:23:14 [01]        ...done
124 180725 04:23:14 [01] Copying ./mysql/help_topic.ibd to /data/mysql/mysql3306/data/mysql/help_topic.ibd
125 180725 04:23:14 [01]        ...done
126 180725 04:23:14 [01] Copying ./mysql/help_category.ibd to /data/mysql/mysql3306/data/mysql/help_category.ibd
127 180725 04:23:14 [01]        ...done
128 180725 04:23:14 [01] Copying ./mysql/help_relation.ibd to /data/mysql/mysql3306/data/mysql/help_relation.ibd
129 180725 04:23:14 [01]        ...done
130 180725 04:23:14 [01] Copying ./mysql/help_keyword.ibd to /data/mysql/mysql3306/data/mysql/help_keyword.ibd
131 180725 04:23:14 [01]        ...done
132 180725 04:23:14 [01] Copying ./mysql/time_zone_name.ibd to /data/mysql/mysql3306/data/mysql/time_zone_name.ibd
133 180725 04:23:14 [01]        ...done
134 180725 04:23:14 [01] Copying ./mysql/time_zone.ibd to /data/mysql/mysql3306/data/mysql/time_zone.ibd
135 180725 04:23:14 [01]        ...done
136 180725 04:23:14 [01] Copying ./mysql/time_zone_transition.ibd to /data/mysql/mysql3306/data/mysql/time_zone_transition.ibd
137 180725 04:23:14 [01]        ...done
138 180725 04:23:14 [01] Copying ./mysql/time_zone_transition_type.ibd to /data/mysql/mysql3306/data/mysql/time_zone_transition_type.ibd
139 180725 04:23:14 [01]        ...done
140 180725 04:23:14 [01] Copying ./mysql/time_zone_leap_second.ibd to /data/mysql/mysql3306/data/mysql/time_zone_leap_second.ibd
141 180725 04:23:14 [01]        ...done
142 180725 04:23:14 [01] Copying ./mysql/innodb_table_stats.ibd to /data/mysql/mysql3306/data/mysql/innodb_table_stats.ibd
143 180725 04:23:14 [01]        ...done
144 180725 04:23:14 [01] Copying ./mysql/innodb_index_stats.ibd to /data/mysql/mysql3306/data/mysql/innodb_index_stats.ibd
145 180725 04:23:14 [01]        ...done
146 180725 04:23:14 [01] Copying ./mysql/slave_relay_log_info.ibd to /data/mysql/mysql3306/data/mysql/slave_relay_log_info.ibd
147 180725 04:23:14 [01]        ...done
148 180725 04:23:14 [01] Copying ./mysql/slave_master_info.ibd to /data/mysql/mysql3306/data/mysql/slave_master_info.ibd
149 180725 04:23:14 [01]        ...done
150 180725 04:23:14 [01] Copying ./mysql/slave_worker_info.ibd to /data/mysql/mysql3306/data/mysql/slave_worker_info.ibd
151 180725 04:23:14 [01]        ...done
152 180725 04:23:14 [01] Copying ./mysql/gtid_executed.ibd to /data/mysql/mysql3306/data/mysql/gtid_executed.ibd
153 180725 04:23:14 [01]        ...done
154 180725 04:23:14 [01] Copying ./mysql/server_cost.ibd to /data/mysql/mysql3306/data/mysql/server_cost.ibd
155 180725 04:23:14 [01]        ...done
156 180725 04:23:14 [01] Copying ./mysql/engine_cost.ibd to /data/mysql/mysql3306/data/mysql/engine_cost.ibd
157 180725 04:23:14 [01]        ...done
158 180725 04:23:14 [01] Copying ./mysql/db.opt to /data/mysql/mysql3306/data/mysql/db.opt
159 180725 04:23:14 [01]        ...done
160 180725 04:23:14 [01] Copying ./mysql/db.frm to /data/mysql/mysql3306/data/mysql/db.frm
161 180725 04:23:14 [01]        ...done
162 180725 04:23:14 [01] Copying ./mysql/db.MYI to /data/mysql/mysql3306/data/mysql/db.MYI
163 180725 04:23:14 [01]        ...done
164 180725 04:23:14 [01] Copying ./mysql/db.MYD to /data/mysql/mysql3306/data/mysql/db.MYD
165 180725 04:23:14 [01]        ...done
166 180725 04:23:14 [01] Copying ./mysql/user.frm to /data/mysql/mysql3306/data/mysql/user.frm
167 180725 04:23:14 [01]        ...done
168 180725 04:23:14 [01] Copying ./mysql/user.MYI to /data/mysql/mysql3306/data/mysql/user.MYI
169 180725 04:23:14 [01]        ...done
170 180725 04:23:14 [01] Copying ./mysql/user.MYD to /data/mysql/mysql3306/data/mysql/user.MYD
171 180725 04:23:14 [01]        ...done
172 
173 //A bundle of copying operations are omitted.
174 
175 180725 04:23:18 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
176 180725 04:23:18 [01]        ...done
177 180725 04:23:18 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
178 180725 04:23:18 [01]        ...done
179 180725 04:23:18 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
180 180725 04:23:18 [01]        ...done
181 180725 04:23:18 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
182 180725 04:23:18 [01]        ...done
183 180725 04:23:18 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
184 180725 04:23:18 [01]        ...done
185 180725 04:23:18 completed OK!
186 
187 //Notice to do the operation above after shutting dowd the MySQL instance.
188 //If the copy back operations show errors,we can copy them directly with the OS command into the target directory.

Restart the mysqld again.

 1 [root@zlm3 04:23:18 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 04:29:14 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      5920  0.0  0.0 112640   960 pts/1    R+   04:29   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 04:29:21 /data/mysql/mysql3306/data]
 9 #tail error.log 
10 2018-07-25T04:29:14.875825+01:00 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable //"ibdata1" is unwritable,we should check the privilege of ownership.
11 2018-07-25T04:29:14.875859+01:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
12 2018-07-25T04:29:15.480834+01:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
13 2018-07-25T04:29:15.480867+01:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
14 2018-07-25T04:29:15.480878+01:00 0 [ERROR] Failed to initialize builtin plugins.
15 2018-07-25T04:29:15.480882+01:00 0 [ERROR] Aborting
16 
17 2018-07-25T04:29:15.480888+01:00 0 [Note] Binlog end
18 2018-07-25T04:29:15.481278+01:00 0 [Note] mysqld: Shutdown complete
19 
20 
21 [root@zlm3 04:29:33 /data/mysql/mysql3306/data]
22 #ls -l
23 total 421940
24 -rw-r----- 1 mysql mysql      1910 Jul 25 04:29 error.log
25 -rw-r----- 1 root  root       1078 Jul 25 04:23 ib_buffer_pool
26 -rw-r----- 1 root  root  104857600 Jul 25 04:23 ibdata1
27 -rw-r----- 1 root  root  104857600 Jul 25 04:22 ib_logfile0
28 -rw-r----- 1 root  root  104857600 Jul 25 04:22 ib_logfile1
29 -rw-r----- 1 root  root  104857600 Jul 25 04:23 ib_logfile2
30 -rw-r----- 1 root  root   12582912 Jul 25 04:23 ibtmp1
31 -rw-r----- 1 mysql mysql         0 Jul 25 04:29 innodb_status.5902
32 drwxr-x--- 2 root  root       4096 Jul 25 04:23 mysql
33 drwxr-x--- 2 root  root       8192 Jul 25 04:23 performance_schema
34 drwxr-x--- 2 root  root       8192 Jul 25 04:23 sys
35 -rw-r----- 1 root  root         27 Jul 25 04:23 xtrabackup_binlog_pos_innodb
36 -rw-r----- 1 root  root        570 Jul 25 04:23 xtrabackup_info
37 -rw-r----- 1 root  root          1 Jul 25 04:23 xtrabackup_master_key_id
38 drwxr-x--- 2 root  root       4096 Jul 25 04:23 zlm
39 
40 [root@zlm3 04:29:48 /data/mysql/mysql3306/data]
41 #chown -R mysql.mysql *
42 
43 [root@zlm3 04:30:06 /data/mysql/mysql3306/data]
44 #ls -l
45 total 421940
46 -rw-r----- 1 mysql mysql      1910 Jul 25 04:29 error.log
47 -rw-r----- 1 mysql mysql      1078 Jul 25 04:23 ib_buffer_pool
48 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:23 ibdata1
49 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:22 ib_logfile0
50 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:22 ib_logfile1
51 -rw-r----- 1 mysql mysql 104857600 Jul 25 04:23 ib_logfile2
52 -rw-r----- 1 mysql mysql  12582912 Jul 25 04:23 ibtmp1
53 -rw-r----- 1 mysql mysql         0 Jul 25 04:29 innodb_status.5902
54 drwxr-x--- 2 mysql mysql      4096 Jul 25 04:23 mysql
55 drwxr-x--- 2 mysql mysql      8192 Jul 25 04:23 performance_schema
56 drwxr-x--- 2 mysql mysql      8192 Jul 25 04:23 sys
57 -rw-r----- 1 mysql mysql        27 Jul 25 04:23 xtrabackup_binlog_pos_innodb
58 -rw-r----- 1 mysql mysql       570 Jul 25 04:23 xtrabackup_info
59 -rw-r----- 1 mysql mysql         1 Jul 25 04:23 xtrabackup_master_key_id
60 drwxr-x--- 2 mysql mysql      4096 Jul 25 04:23 zlm
61 
62 [root@zlm3 04:30:07 /data/mysql/mysql3306/data]
63 #sh /root/mysqld.sh
64 
65 [root@zlm3 04:30:14 /data/mysql/mysql3306/data]
66 #ps aux|grep mysqld
67 mysql     5947  4.6 17.8 1110004 181632 pts/1  Sl   04:30   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
68 root      5980  0.0  0.0 112640   960 pts/1    R+   04:30   0:00 grep --color=auto mysqld
69 
70 [root@zlm3 04:30:19 /data/mysql/mysql3306/data]
71 #tail error.log 
72 2018-07-25T04:30:15.319399+01:00 0 [Note]   - '::' resolves to '::';
73 2018-07-25T04:30:15.319419+01:00 0 [Note] Server socket created on IP: '::'.
74 2018-07-25T04:30:15.476741+01:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180725  4:30:15
75 2018-07-25T04:30:15.514597+01:00 0 [Note] Relay log recovery skipped for group replication channel.
76 2018-07-25T04:30:15.514661+01:00 0 [Warning] Recovery from master pos 65 and file  for channel 'group_replication_applier'. Previous relay log pos and relay log file had been set to 4, ./relay-bin-group_replication_applier.000037 respectively.
77 2018-07-25T04:30:15.522471+01:00 0 [Note] Relay log recovery skipped for group replication channel.
78 2018-07-25T04:30:15.522493+01:00 0 [Warning] Recovery from master pos 4 and file  for channel 'group_replication_recovery'. Previous relay log pos and relay log file had been set to 4, ./relay-bin-group_replication_recovery.000034 respectively.
79 2018-07-25T04:30:15.579447+01:00 0 [Note] Event Scheduler: Loaded 0 events
80 2018-07-25T04:30:15.579569+01:00 0 [Note] mysqld: ready for connections. //It's okay now.
81 Version: '5.7.21-log'  socket: '/tmp/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)

Check the GTID information on slave.

 1 [root@zlm3 04:30:27 /data/mysql/mysql3306/data]
 2 #mysql
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 2
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 (zlm@192.168.1.102 3306)[(none)]>show master status;
16 +------------------+----------+--------------+------------------+------------------------------------------------+
17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
18 +------------------+----------+--------------+------------------+------------------------------------------------+
19 | mysql-bin.000006 |      150 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021 |
20 +------------------+----------+--------------+------------------+------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 //Let's compare the GTID information with master.
24 (zlm@192.168.1.101 3306)[(none)]>show master status;
25 +------------------+----------+--------------+------------------+------------------------------------------------+
26 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
27 +------------------+----------+--------------+------------------+------------------------------------------------+
28 | mysql-bin.000027 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021 |
29 +------------------+----------+--------------+------------------+------------------------------------------------+
30 1 row in set (0.00 sec)
31 
32 //The "Executed_Gtid_Set" are equal,it's "uuid:1-3730021".

Execute "change master to ... " to impement a slave based on GTID.

  1 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
  2 Empty set (0.00 sec)
  3 
  4 (zlm@192.168.1.102 3306)[(none)]>change master to \
  5     -> master_host='192.168.1.101'\
  6     -> master_port=3306\
  7     -> ^C
  8 (zlm@192.168.1.102 3306)[(none)]>
  9 (zlm@192.168.1.102 3306)[(none)]>change master to \
 10     -> master_host='192.168.1.101',\
 11     -> master_port=3306,\
 12     -> master_user='repl',\
 13     -> master_password='repl4slave',\
 14     -> master_auto_position=1;
 15 Query OK, 0 rows affected, 2 warnings (0.02 sec)
 16 
 17 (zlm@192.168.1.102 3306)[(none)]>show warnings;
 18 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 19 | Level | Code | Message                                                                                                                                                                                                                                                                              |
 20 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 21 | Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
 22 | Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
 23 +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 24 2 rows in set (0.00 sec)
 25 
 26 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
 27 *************************** 1. row ***************************
 28                Slave_IO_State: 
 29                   Master_Host: 192.168.1.101
 30                   Master_User: repl
 31                   Master_Port: 3306
 32                 Connect_Retry: 60
 33               Master_Log_File: 
 34           Read_Master_Log_Pos: 4
 35                Relay_Log_File: relay-bin.000001
 36                 Relay_Log_Pos: 4
 37         Relay_Master_Log_File: 
 38              Slave_IO_Running: No
 39             Slave_SQL_Running: No
 40               Replicate_Do_DB: 
 41           Replicate_Ignore_DB: 
 42            Replicate_Do_Table: 
 43        Replicate_Ignore_Table: 
 44       Replicate_Wild_Do_Table: 
 45   Replicate_Wild_Ignore_Table: 
 46                    Last_Errno: 0
 47                    Last_Error: 
 48                  Skip_Counter: 0
 49           Exec_Master_Log_Pos: 0
 50               Relay_Log_Space: 150
 51               Until_Condition: None
 52                Until_Log_File: 
 53                 Until_Log_Pos: 0
 54            Master_SSL_Allowed: No
 55            Master_SSL_CA_File: 
 56            Master_SSL_CA_Path: 
 57               Master_SSL_Cert: 
 58             Master_SSL_Cipher: 
 59                Master_SSL_Key: 
 60         Seconds_Behind_Master: NULL
 61 Master_SSL_Verify_Server_Cert: No
 62                 Last_IO_Errno: 0
 63                 Last_IO_Error: 
 64                Last_SQL_Errno: 0
 65                Last_SQL_Error: 
 66   Replicate_Ignore_Server_Ids: 
 67              Master_Server_Id: 0
 68                   Master_UUID: 
 69              Master_Info_File: mysql.slave_master_info
 70                     SQL_Delay: 0
 71           SQL_Remaining_Delay: NULL
 72       Slave_SQL_Running_State: 
 73            Master_Retry_Count: 86400
 74                   Master_Bind: 
 75       Last_IO_Error_Timestamp: 
 76      Last_SQL_Error_Timestamp: 
 77                Master_SSL_Crl: 
 78            Master_SSL_Crlpath: 
 79            Retrieved_Gtid_Set: 
 80             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021
 81                 Auto_Position: 1
 82          Replicate_Rewrite_DB: 
 83                  Channel_Name: 
 84            Master_TLS_Version: 
 85 1 row in set (0.00 sec)
 86 
 87 (zlm@192.168.1.102 3306)[(none)]>start slave;
 88 Query OK, 0 rows affected (0.00 sec)
 89 
 90 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
 91 *************************** 1. row ***************************
 92                Slave_IO_State: Waiting for master to send event
 93                   Master_Host: 192.168.1.101
 94                   Master_User: repl
 95                   Master_Port: 3306
 96                 Connect_Retry: 60
 97               Master_Log_File: mysql-bin.000027
 98           Read_Master_Log_Pos: 190
 99                Relay_Log_File: relay-bin.000002
100                 Relay_Log_Pos: 355
101         Relay_Master_Log_File: mysql-bin.000027
102              Slave_IO_Running: Yes
103             Slave_SQL_Running: Yes
104               Replicate_Do_DB: 
105           Replicate_Ignore_DB: 
106            Replicate_Do_Table: 
107        Replicate_Ignore_Table: 
108       Replicate_Wild_Do_Table: 
109   Replicate_Wild_Ignore_Table: 
110                    Last_Errno: 0
111                    Last_Error: 
112                  Skip_Counter: 0
113           Exec_Master_Log_Pos: 190
114               Relay_Log_Space: 548
115               Until_Condition: None
116                Until_Log_File: 
117                 Until_Log_Pos: 0
118            Master_SSL_Allowed: No
119            Master_SSL_CA_File: 
120            Master_SSL_CA_Path: 
121               Master_SSL_Cert: 
122             Master_SSL_Cipher: 
123                Master_SSL_Key: 
124         Seconds_Behind_Master: 0
125 Master_SSL_Verify_Server_Cert: No
126                 Last_IO_Errno: 0
127                 Last_IO_Error: 
128                Last_SQL_Errno: 0
129                Last_SQL_Error: 
130   Replicate_Ignore_Server_Ids: 
131              Master_Server_Id: 1013306
132                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
133              Master_Info_File: mysql.slave_master_info
134                     SQL_Delay: 0
135           SQL_Remaining_Delay: NULL
136       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
137            Master_Retry_Count: 86400
138                   Master_Bind: 
139       Last_IO_Error_Timestamp: 
140      Last_SQL_Error_Timestamp: 
141                Master_SSL_Crl: 
142            Master_SSL_Crlpath: 
143            Retrieved_Gtid_Set: 
144             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021
145                 Auto_Position: 1
146          Replicate_Rewrite_DB: 
147                  Channel_Name: 
148            Master_TLS_Version: 
149 1 row in set (0.00 sec)

Summary

Xtrabackup is a physical backup tool of MySQL.It provides incremental backup of innodb engine based on redo logfile.
innobackupex is a soft link of xtrabackup.It needs three steps to restore a instance on slave server(backup,apply-log,copy-back).
Before you do the copy-back operation,the instance should be shudown first and the target data directory should be empty.
If your database is tremendous,It's a better way to implement a new slave with Xtrabckup instead of mysqldump.

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有