#!/bin/bash
database info
user=‘root’
password=’*****’
host=‘127.0.0.1’
db_name=‘roi_manager’
backup_path
backup_filepath=’/sunlands/db_backup/mysql/zlcx_wx’
backup_time=date '+%Y-%m-%d-%H_%M_%S'
backup_logpath=’/sunlands/db_backup/logs’
mkdir
if [ ! -d $backup_filepath ]; then
mkdir -p $backup_filepath
fi
if [ ! -d $backup_logpath ]; then
mkdir -p $backup_logpath
fi
set backup file omit authority
umask 177
Dump db to sqlfile
echo “backup time is ${backup_time},backup db is ${db_name}, backup start…” >>
b
a
c
k
u
p
l
o
g
p
a
t
h
/
b
a
c
k
u
p
M
y
s
q
l
L
o
g
−
{backup_logpath}/backupMysqlLog-
backuplogpath/backupMysqlLog−{backup_time}.log
mysqldump --user=
u
s
e
r
−
−
p
a
s
s
w
o
r
d
=
user --password=
user−−password=password --single-transaction --host=$host $db_name >
b
a
c
k
u
p
f
i
l
e
p
a
t
h
/
backup_filepath/
backupfilepath/db_name-
b
a
c
k
u
p
t
i
m
e
.
s
q
l
2
>
>
backup_time.sql 2>>
backuptime.sql2>>{backup_logpath}/backupMysqlLog-${backup_time}.log
#delete 3 days before files
echo “delete 3 days before files…” >>
b
a
c
k
u
p
l
o
g
p
a
t
h
/
b
a
c
k
u
p
M
y
s
q
l
L
o
g
−
{backup_logpath}/backupMysqlLog-
backuplogpath/backupMysqlLog−{backup_time}.log
find $backup_filepath/* -mtime +2 -name ‘*.sql’ -exec rm {} ;
说明:
格式:mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
例如: mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql
–lock-all-tables 是对要备份的数据库的所有表施加读锁
–lock-table 仅对单张表施加读锁,即使是备份整个数据库,它也是在我们备份某张表的时候才对该表施加读锁,因此适用于备份单张表
–single-transaction: 基于此选项能实现热备InnoDB表;因此,不需要同时使用–lock-all-tables;
–master-data=2 记录备份那一时刻的二进制日志的位置,并且注释掉,1是不注释的
恢复数据
mysql -uroot -p’123456’ mytest < /mnt/mytest_bak.sql