使用xtrabackup备份和还原mysql的多实例(基于全备)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

1)安装percona-xtrabackup

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Xtrabackup是一个对InnoDB做数据备份的工具,支持在线热备份(备份时不影响数据读写),是商业备份
工具InnoDB Hotbackup的一个很好的替代品
# 下载安装包:
# cd /usr/local/src
# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/redhat/6/x86_64/percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
# 安装依赖库:
# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
# wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm 
# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
# rpm -ivh percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
# 创建备份的目录:
# mkdir -p /databackup/fullbackup
# mkdir -p /databackup/increasebackup
# 创建备份用户和授权:
mysql>grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOW VIEW,EVENT,FILE on *.* to backup@ 'localhost'  identified by  'MANAGER' ;

2) 备份3306实例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# innobackupex --defaults-file=/data/3306/my.cnf --socket=/data/3306/mysql.sock --user=backup --password='MANAGER' /mysqlbackup/fullbackup/3306
说明:
--defaults- file ,指定mysql实例的配置文件
--socket,指定socket的位置
--user,指定备份的用户
--password,指定用户名称
/mysqlbackup/fullbackup/3306 ,这里我们将实例备份到 /mysqlbackup/fullbackup/3306 目录下
# du -sh /mysqlbackup/fullbackup/3306
130M /mysqlbackup/fullbackup/3306
# ls -lrt /mysqlbackup/fullbackup/3306/*
总用量 131136
-rw-r----- 1 root root 134217728 10月 12 04:32 ibdata1
drwx------ 2 root root      4096 10月 12 04:32 james
drwx------ 2 root root      4096 10月 12 04:32 ems
drwx------ 2 root root      4096 10月 12 04:32 mysql
drwx------ 2 root root      4096 10月 12 04:32 performance_schema
drwx------ 2 root root      4096 10月 12 04:32 wh410
drwx------ 2 root root      4096 10月 12 04:32 www
drwx------ 2 root root      4096 10月 12 04:32 blog
drwx------ 2 root root      4096 10月 12 04:32 opark
drwx------ 2 root root      4096 10月 12 04:32 dawnpro
drwx------ 2 root root      4096 10月 12 04:32 eip
drwx------ 2 root root      4096 10月 12 04:32 hangzhou_dawnpro
-rw-r----- 1 root root        21 10月 12 04:32 xtrabackup_binlog_info
-rw-r----- 1 root root      2560 10月 12 04:32 xtrabackup_logfile
-rw-r----- 1 root root       113 10月 12 04:32 xtrabackup_checkpoints
-rw-r----- 1 root root       386 10月 12 04:32 backup-my.cnf
-rw-r----- 1 root root       551 10月 12 04:32 xtrabackup_info

3)模拟3306数据文件丢失

1
2
3
4
5
6
7
8
9
10
11
# rm -rf /data/3306/data
# lsof -i :3306
说明:此时数据库已经没法正常运行,检查错误日志,发现没有了data目录
161012 04:40:35 mysqld_safe mysqld from pid  file  /data/3306/mysqld .pid ended
161012 04:42:10 mysqld_safe Starting mysqld daemon with databases from  /data/3306/data
161012  4:42:10 [Warning] Can't create  test  file  /data/3306/data/mysql-master .lower- test
161012  4:42:10 [Warning] Can't create  test  file  /data/3306/data/mysql-master .lower- test
/application/mysql-5 .5.32 /bin/mysqld : Can 't change dir to ' /data/3306/data/ ' (Errcode: 2)
161012  4:42:10 [ERROR] Aborting
161012  4:42:10 [Note]  /application/mysql-5 .5.32 /bin/mysqld : Shutdown complete
161012 04:42:10 mysqld_safe mysqld from pid  file  /data/3306/mysqld .pid ended

4)mysql全备数据恢复:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
# mkdir -p /data/3306/data
# 对于一般恢复,都是直接用备份文件还原,如果我们这里也是直接运用该备份文件,则可能会导致
一些意想不到的问题,比如:备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据
文件中的事务。因此,此时数据文件处于不一致的状态,我们现在就是要通过回滚未提交的事务及同步
已经提交的事务至数据文件也使得数据文件处于一致性状态。所以我们要用如下命令进行检查:
# innobackupex  --apply-log --redo-only /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/
161012 04:53:07 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.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision  id : 306a2e0)
xtrabackup:  cd  to  /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/
xtrabackup: This target seems to be already prepared with --apply-log-only.
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:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration  for  recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance  for  recovery.
xtrabackup: Using 104857600 bytes  for  buffer pool ( set  by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported  file  format  is Barracuda.
xtrabackup: Last MySQL binlog  file  position 2442,  file  name  /data/3306/mysql-bin .000008
xtrabackup: starting  shutdown  with innodb_fast_shutdown = 1
InnoDB: Starting  shutdown ...
InnoDB: Shutdown completed; log sequence number 1606678
161012 04:53:08 completed OK!
# innobackupex --defaults-file=/data/3306/my.cnf --copy-back /mysqlbackup/fullbackup/3306/2016-10-12_04-32-49/
161012 04:56:32 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.3.2 based on MySQL server 5.6.24 Linux (x86_64) (revision  id : 306a2e0)
161012 04:56:32 [01] Copying ib_logfile0 to  /data/3306/data/ib_logfile0
161012 04:56:33 [01]        ... done
161012 04:56:33 [01] Copying ib_logfile1 to  /data/3306/data/ib_logfile1
161012 04:56:34 [01]        ... done
161012 04:56:34 [01] Copying ibdata1 to  /data/3306/data/ibdata1
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /james/db .opt to  /data/3306/data/james/db .opt
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /ems/db .opt to  /data/3306/data/ems/db .opt
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/func .MYD to  /data/3306/data/mysql/func .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone .MYD to  /data/3306/data/mysql/time_zone .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/help_topic .MYD to  /data/3306/data/mysql/help_topic .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/tables_priv .MYI to  /data/3306/data/mysql/tables_priv .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/db .MYD to  /data/3306/data/mysql/db .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/proc .frm to  /data/3306/data/mysql/proc .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/help_relation .MYI to  /data/3306/data/mysql/help_relation .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/proxies_priv .MYI to  /data/3306/data/mysql/proxies_priv .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/func .frm to  /data/3306/data/mysql/func .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/plugin .MYD to  /data/3306/data/mysql/plugin .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/user .frm to  /data/3306/data/mysql/user .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone_transition .MYD to  /data/3306/data/mysql/time_zone_transition .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone_transition_type .MYD to  /data/3306/data/mysql/time_zone_transition_type .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/general_log .CSV to  /data/3306/data/mysql/general_log .CSV
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/event .MYI to  /data/3306/data/mysql/event .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone_leap_second .frm to  /data/3306/data/mysql/time_zone_leap_second .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/help_relation .frm to  /data/3306/data/mysql/help_relation .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/ndb_binlog_index .MYD to  /data/3306/data/mysql/ndb_binlog_index .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/procs_priv .frm to  /data/3306/data/mysql/procs_priv .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/db .frm to  /data/3306/data/mysql/db .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/event .frm to  /data/3306/data/mysql/event .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/plugin .MYI to  /data/3306/data/mysql/plugin .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/help_category .frm to  /data/3306/data/mysql/help_category .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone_name .MYD to  /data/3306/data/mysql/time_zone_name .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/event .MYD to  /data/3306/data/mysql/event .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/proc .MYI to  /data/3306/data/mysql/proc .MYI
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/tables_priv .MYD to  /data/3306/data/mysql/tables_priv .MYD
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/servers .frm to  /data/3306/data/mysql/servers .frm
161012 04:56:36 [01]        ... done
161012 04:56:36 [01] Copying . /mysql/time_zone_leap_second .MYI to  /data/3306/data/mysql/time_zone_leap_second .MYI
161012 04:56:36 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/proxies_priv .MYD to  /data/3306/data/mysql/proxies_priv .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/procs_priv .MYI to  /data/3306/data/mysql/procs_priv .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone .MYI to  /data/3306/data/mysql/time_zone .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/slow_log .CSV to  /data/3306/data/mysql/slow_log .CSV
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_transition .frm to  /data/3306/data/mysql/time_zone_transition .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/procs_priv .MYD to  /data/3306/data/mysql/procs_priv .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_topic .MYI to  /data/3306/data/mysql/help_topic .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/user .MYI to  /data/3306/data/mysql/user .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_keyword .MYD to  /data/3306/data/mysql/help_keyword .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/db .MYI to  /data/3306/data/mysql/db .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/func .MYI to  /data/3306/data/mysql/func .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/servers .MYI to  /data/3306/data/mysql/servers .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_category .MYD to  /data/3306/data/mysql/help_category .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/host .MYI to  /data/3306/data/mysql/host .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone .frm to  /data/3306/data/mysql/time_zone .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_category .MYI to  /data/3306/data/mysql/help_category .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/proxies_priv .frm to  /data/3306/data/mysql/proxies_priv .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/general_log .frm to  /data/3306/data/mysql/general_log .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_keyword .frm to  /data/3306/data/mysql/help_keyword .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/proc .MYD to  /data/3306/data/mysql/proc .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/columns_priv .MYI to  /data/3306/data/mysql/columns_priv .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/slow_log .frm to  /data/3306/data/mysql/slow_log .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_topic .frm to  /data/3306/data/mysql/help_topic .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_keyword .MYI to  /data/3306/data/mysql/help_keyword .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_name .MYI to  /data/3306/data/mysql/time_zone_name .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/host .MYD to  /data/3306/data/mysql/host .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/columns_priv .MYD to  /data/3306/data/mysql/columns_priv .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/user .MYD to  /data/3306/data/mysql/user .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/ndb_binlog_index .frm to  /data/3306/data/mysql/ndb_binlog_index .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/tables_priv .frm to  /data/3306/data/mysql/tables_priv .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/general_log .CSM to  /data/3306/data/mysql/general_log .CSM
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_leap_second .MYD to  /data/3306/data/mysql/time_zone_leap_second .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/servers .MYD to  /data/3306/data/mysql/servers .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/host .frm to  /data/3306/data/mysql/host .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_name .frm to  /data/3306/data/mysql/time_zone_name .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/help_relation .MYD to  /data/3306/data/mysql/help_relation .MYD
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_transition .MYI to  /data/3306/data/mysql/time_zone_transition .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/slow_log .CSM to  /data/3306/data/mysql/slow_log .CSM
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/ndb_binlog_index .MYI to  /data/3306/data/mysql/ndb_binlog_index .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/columns_priv .frm to  /data/3306/data/mysql/columns_priv .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_transition_type .MYI to  /data/3306/data/mysql/time_zone_transition_type .MYI
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/time_zone_transition_type .frm to  /data/3306/data/mysql/time_zone_transition_type .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /mysql/plugin .frm to  /data/3306/data/mysql/plugin .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_summary_by_instance .frm to  /data/3306/data/performance_schema/events_waits_summary_by_instance .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/file_instances .frm to  /data/3306/data/performance_schema/file_instances .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/performance_timers .frm to  /data/3306/data/performance_schema/performance_timers .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/file_summary_by_event_name .frm to  /data/3306/data/performance_schema/file_summary_by_event_name .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_summary_by_thread_by_event_name .frm to  /data/3306/data/performance_schema/events_waits_summary_by_thread_by_event_name .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_current .frm to  /data/3306/data/performance_schema/events_waits_current .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/setup_instruments .frm to  /data/3306/data/performance_schema/setup_instruments .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/threads .frm to  /data/3306/data/performance_schema/threads .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/file_summary_by_instance .frm to  /data/3306/data/performance_schema/file_summary_by_instance .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_history .frm to  /data/3306/data/performance_schema/events_waits_history .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/rwlock_instances .frm to  /data/3306/data/performance_schema/rwlock_instances .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/setup_consumers .frm to  /data/3306/data/performance_schema/setup_consumers .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/setup_timers .frm to  /data/3306/data/performance_schema/setup_timers .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_summary_global_by_event_name .frm to  /data/3306/data/performance_schema/events_waits_summary_global_by_event_name .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/mutex_instances .frm to  /data/3306/data/performance_schema/mutex_instances .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/events_waits_history_long .frm to  /data/3306/data/performance_schema/events_waits_history_long .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/cond_instances .frm to  /data/3306/data/performance_schema/cond_instances .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /performance_schema/db .opt to  /data/3306/data/performance_schema/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /wh410/db .opt to  /data/3306/data/wh410/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /www/db .opt to  /data/3306/data/www/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /xtrabackup_info  to  /data/3306/data/xtrabackup_info
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /blog/db .opt to  /data/3306/data/blog/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /xtrabackup_binlog_pos_innodb  to  /data/3306/data/xtrabackup_binlog_pos_innodb
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /opark/person .frm to  /data/3306/data/opark/person .frm
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /opark/db .opt to  /data/3306/data/opark/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /dawnpro/db .opt to  /data/3306/data/dawnpro/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /eip/db .opt to  /data/3306/data/eip/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 [01] Copying . /hangzhou_dawnpro/db .opt to  /data/3306/data/hangzhou_dawnpro/db .opt
161012 04:56:37 [01]        ... done
161012 04:56:37 completed OK!
说明:innobackup的copy-back用于执行恢复操作时,它是通过复制所有数据相关文件至MySQL数据目录,因此,需要清空数据目录。
[root@mysql-master data] # ll /data/3306/data
总用量 229432
drwx------ 2 root root      4096 10月 12 04:56 blog
drwx------ 2 root root      4096 10月 12 04:56 dawnpro
drwx------ 2 root root      4096 10月 12 04:56 eip
drwx------ 2 root root      4096 10月 12 04:56 ems
drwx------ 2 root root      4096 10月 12 04:56 hangzhou_dawnpro
-rw-r----- 1 root root 134217728 10月 12 04:56 ibdata1
-rw-r----- 1 root root  50331648 10月 12 04:56 ib_logfile0
-rw-r----- 1 root root  50331648 10月 12 04:56 ib_logfile1
drwx------ 2 root root      4096 10月 12 04:56 james
drwx------ 2 root root      4096 10月 12 04:56 mysql
drwx------ 2 root root      4096 10月 12 04:56 opark
drwx------ 2 root root      4096 10月 12 04:56 performance_schema
drwx------ 2 root root      4096 10月 12 04:56 wh410
drwx------ 2 root root      4096 10月 12 04:56 www
-rw-r----- 1 root root        33 10月 12 04:56 xtrabackup_bin


5)更改数据库目录权限

1
2
# chown -R mysql.mysql /data/3306/data
这一步很重要,一定要记得

6) 开启mysql服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# /data/3306/mysql start
启动mysql发现没法起来,检查日志信息如下(报错信息:InnoDB: Error: log  file  . /ib_logfile0  is of different size 0 50331648 bytes):
161012  5:00:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161012  5:00:15 InnoDB: Compressed tables use zlib 1.2.3
161012  5:00:15 InnoDB: Using Linux native AIO
161012  5:00:15 InnoDB: Initializing buffer pool, size = 32.0M
161012  5:00:15 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log  file  . /ib_logfile0  is of different size 0 50331648 bytes
InnoDB: than specified  in  the .cnf  file  0 4194304 bytes!
161012  5:00:15 [ERROR] Plugin  'InnoDB'  init  function  returned error.
161012  5:00:15 [ERROR] Plugin  'InnoDB'  registration as a STORAGE ENGINE failed.
161012  5:00:15 [ERROR] Unknown /unsupported  storage engine: InnoDB
161012  5:00:15 [ERROR] Aborting
161012  5:00:15 [Note]  /application/mysql-5 .5.32 /bin/mysqld : Shutdown complete
161012 05:00:15 mysqld_safe mysqld from pid  file  /data/3306/mysqld .pid ended

解决方法:

1
2
3
4
5
# rm ib_logfile* -rf
推荐的方法:不删除而直接重命名
# /data/3306/mysql start
# netstat -tunlp| grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      29697 /mysqld

7) 进入数据库进行验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# mysql -uroot -predhat12345 -S /data/3306/mysql.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog               |
| dawnpro            |
| eip                |
| ems                |
| hangzhou_dawnpro   |
| james              |
| mysql              |
| opark              |
| performance_schema |
| wh410              |
| www                |
+--------------------+
12 rows  in  set  (0.01 sec)









本文转自 冰冻vs西瓜 51CTO博客,原文链接:http://blog.51cto.com/molewan/1861487,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
84 4
|
20天前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
43 3
|
20天前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
32 3
|
6月前
|
存储 SQL 关系型数据库
|
2月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
60 3
|
2月前
|
存储 关系型数据库 MySQL
MySQL如何备份?
【10月更文挑战第2天】MySQL如何备份?
112 3
|
4月前
|
SQL 关系型数据库 MySQL
在Linux中,如何备份和恢复MySQL数据库?
在Linux中,如何备份和恢复MySQL数据库?
|
4月前
|
SQL 数据可视化 关系型数据库
MySQL 备份可视化巡检系统
MySQL 备份可视化巡检系统
|
4月前
|
SQL 存储 关系型数据库
MySQL备份:mydumper 备份恢复工具生产实战
MySQL备份:mydumper 备份恢复工具生产实战
|
4月前
|
安全 关系型数据库 MySQL
揭秘MySQL海量数据迁移终极秘籍:从逻辑备份到物理复制,解锁大数据迁移的高效与安全之道
【8月更文挑战第2天】MySQL数据量很大的数据库迁移最优方案
714 17