1 备份
进行备份前需要先创建备份用户，直接使用 root 用户进行备份也行，但是这样不太规范。
|
1
2
|
create user backup@'localhost' identified by '123456';grant reload,process,lock tables,replication client on *.* to backup@localhost; |
1.1 全备
备份整个库，使用的是备份用户，备份文件存放地址为 /backup/
|
1
|
innobackupex --defaults-file=/etc/my.cnf --user=backup --password=123456 /backup/ |
1.2 增备
指定为增量备分，使用的是备份用户，增量的基础为上一次的全备，已经使用 --incremental-basedir 进行指定了，备份后存放的文件为 /backup/
|
1
|
innobackupex --defaults-file=/etc/my.cnf --user=backup --password=123456 --incremental --incremental-basedir=/backup/2021-06-01_14-44-54 /backup/ |
2 备份恢复
2.1 准备数据
回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
|
1
|
innobackupex --apply-log --redo-only /backup/2021-06-01_14-44-54/ |
2.2 进行恢复
在恢复前，需要确保 mysql 的数据目录为已经删除了。
|
1
|
innobackupex --copy-back --datadir=/usr/local/mysql/data /backup/2021-06-01_14-44-54/ |
恢复后，需要对 mysql 的data 目录进行重新赋权：
|
1
|
chown -r mysql:mysql data/ |
到这恢复就完成了。
3 目录结构
4 备份脚本
4.1 脚本
backup.sh
|
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
|
#!/bin/bash# 获得程序路径名program_dir=`dirname $0`/..# 读取配置文件中的所有变量值, 设置为全局变量# 配置文件conf_file="$program_dir/conf/backup.conf"# mysql 用户user=`sed '/^user=/!d;s/.*=//' $conf_file`# mysql 密码password=`sed '/^password=/!d;s/.*=//' $conf_file`# mysql 备份目录backup_dir=`sed '/^backup_dir=/!d;s/.*=//' $conf_file`# mysql 备份压缩打包目录gzip_dir=`sed '/^gzip_dir=/!d;s/.*=//' $conf_file`# percona-xtrabackup命令xtrabackup路径xtrabackup_bin=`sed '/^xtrabackup_bin=/!d;s/.*=//' $conf_file`# mysql 全备前缀标识full_backup_prefix=`sed '/^full_backup_prefix=/!d;s/.*=//' $conf_file`# mysql 增量备前缀标识increment_prefix=`sed '/^increment_prefix=/!d;s/.*=//' $conf_file`# 备份错误日志文件error_log=$program_dir/var/`sed '/^error_log=/!d;s/.*=//' $conf_file`# 备份索引文件index_file=$program_dir/var/`sed '/^index_file=/!d;s/.*=//' $conf_file`# 备份日期backup_date=`date +%f`# 备份时间backup_time=`date +%h-%m-%s`# 备份时的周几backup_week_day=`date +%u`# 创建相关目录log_dir=$program_dir/log/backupvar_dir=$program_dir/varmkdir -p $backup_dirmkdir -p $log_dirmkdir -p $var_dirmkdir -p $gzip_dir# 全量备份function full_backup() { backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day} mkdir -p $backup_dir/$backup_folder $xtrabackup_bin \ --user=$user \ --password=$password \ --backup \ --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1 return $?}# 增量备份function increment_backup() { backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day} incr_base_folder=`sed -n '$p' $index_file | \ awk -f '[, {}]*' '{print $3}' | \ awk -f ':' '{print $2}'` mkdir -p $backup_dir/$backup_folder $xtrabackup_bin \ --user=$user \ --password=$password \ --backup \ --target-dir=$backup_dir/$backup_folder \ --incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1 return $?}# 删除之前的备份(一般在全备完成后使用)function delete_before_backup() { cat $index_file | awk -f '[, {}]*' '{print $3}' | \ awk -v backup_dir=$backup_dir -f ':' '{if($2!=""){printf("rm -rf %s/%s\n", backup_dir, $2)}}' | \ /bin/bash cat $index_file | awk -f '[, {}]*' '{print $3}' | \ awk -v gzip_dir=$gzip_dir -f ':' '{if($2!=""){printf("rm -rf %s/%s\n", gzip_dir, $2)}}' | \ /bin/bash cat $index_file | awk -f '[, {}]*' '{print $3}' | \ awk -v log_dir=$log_dir -f ':' '{if($2!=""){printf("rm -rf %s/%s.log\n", log_dir, $2)}}' | \ /bin/bash}# 备份索引文件function backup_index_file() { cp $index_file ${index_file}_$(date -d "1 day ago" +%f)}# 备份索引文件function send_index_file_to_remote() { # ./expect_scp ip地址 账号 密码 ${index_file} 目标服务器存放的文件夹 端口号 echo 'send index file ok'}# 添加索引, 索引记录了当前最新的备份function append_index_to_file() { echo "{week_day:$backup_week_day, \ dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \ type:${1}, \ date:${backup_date}}" >> $index_file}# 记录错误消息到文件function logging_backup_err() { echo "{week_day:$backup_week_day, \ dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \ type:${1}, \ date:${backup_date}}" >> $error_log}# 清空索引function purge_index_from_file() { > $index_file}# 清空错误日志信息function purge_err_log() { > $error_log}# 打包备份function tar_backup_file() { cd $backup_dir tar -jcf ${gzip_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2 \ ${1}_${backup_date}_${backup_time}_${backup_week_day} cd - > /dev/null rm -rf ${backup_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}}# 发送备份到远程function send_backup_to_remote() { # ./expect_scp ip地址 账号 密码 ${gzip_dir}/${1}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2 目标服务器存放的文件夹 端口号 echo "send $1 remote ok"}# 判断是应该全备还是增量备份# 0:full, 1:incrfunction get_backup_type() { backup_type=0 if [ 1 -eq `date +%h` ]; then backup_type=0 else backup_type=1 fi touch $index_file if [ ! -n "`cat $index_file`" ]; then backup_type=0 fi return $backup_type}# 测试配置文件正确性function test_conf_file() { # 判断每个变量是否在配置文件中有配置，没有则退出程序 if [ ! -n "$user" ]; then echo 'fail: configure file user not set'; exit 2; fi if [ ! -n "$password" ]; then echo 'fail: configure file password not set'; exit 2; fi if [ ! -n "$backup_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi if [ ! -n "$gzip_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi if [ ! -n "$full_backup_prefix" ]; then echo 'fail: configure file full_backup_prefix not set'; exit 2; fi if [ ! -n "$increment_prefix" ]; then echo 'fail: configure file increment_prefix not set'; exit 2; fi if [ ! -n "$error_log" ]; then echo 'fail: configure file error_log not set'; exit 2; fi if [ ! -n "$index_file" ]; then echo 'fail: configure file index_file not set'; exit 2; fi}# 执行function main() { # 检测配置文件值 test_conf_file # 判断是执行全备还是增量备份 get_backup_type backup_type=$? case $backup_type in 0 ) # 全量备份 full_backup backup_ok=$? if [ 0 -eq "$backup_ok" ]; then # 全备成功 # 打包最新备份 tar_backup_file $full_backup_prefix # # 将tar备份发送到远程 send_backup_to_remote $full_backup_prefix # 备份索引文件 backup_index_file # 清除之前的备份 delete_before_backup # 清除索引文件 purge_index_from_file # 添加索引, 索引记录了当前最新的备份 append_index_to_file $full_backup_prefix # 发送索引文件到远程 send_index_file_to_remote else # 全备失败 # 删除备份目录 rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day} # 记录错误日志 logging_backup_err $full_backup_prefix fi ;; 1 ) # 增量备份 increment_backup backup_ok=$? if [ "$backup_ok" -eq 0 ]; then # 增量备份成功 # 打包最新备份 tar_backup_file $increment_prefix # # 将tar备份发送到远程 send_backup_to_remote $increment_prefix # 添加索引, 索引记录了当前最新的备份 append_index_to_file $increment_prefix # # 发送索引文件到远程 send_index_file_to_remote else # 增量备份失败 # 删除备份目录 rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day} # 记录错误日志 logging_backup_err $increment_prefix fi ;; esac}main |
4.2 配置文件
backup.conf
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
# mysql 用户名user=backup# mysql 密码password=123456# 备份路径backup_dir=/data/backup# 备份压缩打包目录gzip_dir=/data/backups/backups_zip# innobackupex 命令路径xtrabackup_bin=/opt/xtrabackup/bin/xtrabackup# 全量备信息名称 前缀full_backup_prefix=full# 增量备信息名称 前缀increment_prefix=incr# 错误日志文件(根据此文件知道备份是否成功)# format:# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}error_log=mysql_increment_hot_backup.err# 索引文件# format:# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}index_file=mysql_increment_hot_backup.index |
5 恢复脚本
5.1 脚本
restore.sh
|
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
|
#!/bin/bash# 获得程序路径名program_dir=`dirname $0`/..# 读取配置文件中的所有变量值, 设置为全局变量# 配置文件conf_file="$program_dir/conf/restore.conf"# mysql 数据文件夹data_dir=`sed '/^data_dir=/!d;s/.*=//' $conf_file`# 备份索引文件路径backup_index_file=`sed '/^backup_index_file=/!d;s/.*=//' $conf_file`# percona-xtrabackup命令xtrabackup路径xtrabackup_bin=`sed '/^xtrabackup_bin=/!d;s/.*=//' $conf_file`# 备份文件目录backup_restore_dir=`sed '/^backup_restore_dir=/!d;s/.*=//' $conf_file`# 检查配置文件正确性function exam_conf_file() { # 判断每个变量是否在配置文件中有配置，没有则退出程序 if [ ! -n "$data_dir" ]; then echo 'fail: configure file data_dir not set'; exit 2; fi if [ ! -n "$backup_index_file" ]; then echo 'fail: configure file backup_index_file not set'; exit 2; fi if [ ! -n "$xtrabackup_bin" ]; then echo 'fail: configure file xtrabackup_bin not set'; exit 2; fi if [ ! -n "$backup_restore_dir" ]; then echo 'fail: configure file backup_restore_dir not set'; exit 2; fi}# 检查备份文件是否是压缩格式function exam_backup_restore_file(){ file_backup_restore_name_arr=`ls $backup_restore_dir` for file_name in $file_backup_restore_name_arr;do if [ "${file_name##*.}"x = "bz2"x ];then tar -jxf $backup_restore_dir/$file_name -c $backup_restore_dir rm -rf $backup_restore_dir/$file_name fi done}# 检查 mysql 是否停止function exam_mysql_is_stop(){ if [ 0 -eq `ps -ef | grep mysql | grep -v grep | wc -l` ]; then echo "mysql 服务已停止" else /etc/init.d/mysqld stop echo "正在停止 mysql 服务" sleep 3 echo "已停止 mysql 服务" fi}# 检查 mysql data 文件是否删除function exam_data_is_del(){ if [ -d $data_dir ];then echo "正在删除 mysql 的data文件" rm -rf $data_dir else echo "mysql 的数据文件已删除 " fi}# 读取备份索引文件function read_backup_index() { cat $backup_index_file | awk '{print $2}' | awk -f: '{print $2}' | awk '{sub(/.$/,"")}1'}# 准备全备文件function ready_full(){ full_file_name=`echo ${1} | awk '{print $1}'` $xtrabackup_bin/innobackupex \ --apply-log \ --redo-only \ $backup_restore_dir/$full_file_name echo "全备文件已准备好"}# 准备增备文件function ready_incr(){ backup_index=$(read_backup_index) full_file_name=`echo $backup_index | awk '{print $1}'` for file_name in $backup_index;do if [ 1 -eq `echo "$file_name" | grep incr | wc -l` ]; then $xtrabackup_bin/innobackupex \ --apply-log \ --redo-only \ $backup_restore_dir/$full_file_name \ --incremental-dir=$backup_restore_dir/$file_name fi done echo "增备文件已准备好"}# 执行备份恢复function exec_backup_restore(){ echo "开始进行备份恢复" full_file_name=`echo ${1} | awk '{print $1}' ` $xtrabackup_bin/innobackupex \ --copy-back \ --datadir=$data_dir \ $backup_restore_dir/$full_file_name}# 执行function main() { # 检查配置文件正确性 exam_conf_file # 检查备份文件是否是压缩格式 exam_backup_restore_file # 检查 mysql 是否停止 exam_mysql_is_stop # 检查 mysql data 文件是否删除 exam_data_is_del # 读取索引文件 backup_index=$(read_backup_index) # 准备全备文件 ready_full $backup_index # 准备增备文件 ready_incr # 执行备份恢复 exec_backup_restore $backup_index # 对数据文件进行赋权 echo "重新对数据目录赋权" chown -r mysql:mysql $data_dir echo "正在启动mysql" /etc/init.d/mysqld start echo "备份恢复成功"}main |
5.2 配置文件
restore.conf
|
1
2
3
4
5
6
7
8
|
# mysql 数据文件夹data_dir=/opt/mysql/data#备份索引文件路径backup_index_file=/opt/xtrabackup/backup/var/mysql_increment_hot_backup.index#xtrabackup bin 的目录 xtrabackup_bin=/opt/xtrabackup/bin# 备份文件目录backup_restore_dir=/data/backups/backups_zip |
以上就是mysql如何使用使用xtrabackup进行备份和恢复的详细内容，更多关于mysql 用xtrabackup备份和恢复的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/FrostBoy/p/14885065.html
