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,如需转载请自行联系原作者