备份类型 | 备份方式 |
逻辑备份 | |
优点 | 缺点 |
物理备份 | |
优点 | 缺点 |
备份对象: 数据文件、配置文件、代码:存储过程,存储函数、触发器等、二进制日志、事务日志、服务器配置、管理脚本、任务计划等。 |
1
2
3
|
mysqldump -uroot -p123.com --lock-all-tables --flush-logs --databases
test
>
/mysqlbackup/test_
`
date
+%F`.sql
--lock-all-tables:备份时为所有表请求加锁
--flush-log:备份之前刷新日志
|
1
|
cp
/usr/local/mysql/data/mysqld-bin
.00000*
/mysqlbackup
|
1
2
3
4
5
6
7
|
mysql> create table abc (number INT(11), name varchar(100));
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000004 | 221 | | |
+-------------------+----------+--------------+------------------+
|
1
2
3
|
less
/mysqlbackup/test_2013-09-29
.sql
#前几行可以看到在106
-- Position to start replication or point-
in
-
time
recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysqld-bin.000005'
, MASTER_LOG_POS=106;
|
1
2
3
|
mysqlbinlog --start-position=106
/var/lib/mysql/mysqld-bin/mysql/mysqld-bin
.000005 >
/mysqlbackup/test_incremental
.sql
--start-position:从哪个位置开始导出二进制日志
--stop-position:从哪个位置结束,到末尾可以不指定
|
1
2
3
4
5
6
7
|
mysql>create table abd (number INT(1),name varchar(10));
mysql> show master status;
#查看二进制所在位置
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000005 | 527 | | |
+-------------------+----------+--------------+------------------+
|
1
2
3
4
5
6
|
mysqlbinlog
/var/lib/mysql/mysqld-bin
.000005
#查看二进制,看在什么时候做的,文件最后下面看出在446
# at 446
#130929 11:04:16 server id 1 end_log_pos 527 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1380467056/*!*/;
drop database
test
/*!*/;
|
1
2
3
4
5
6
|
mysqlbinlog --start-position=220 --stop-position=446
/var/lib/mysql/mysqld-bin
.000005 >
/mysqlbackup/test_446
.sql
mysql>
source
/backupn/test_2013-09-06
.sql
#恢复完整备份
mysql>
source
/backup/test_incremental
.sql
#恢复增量备份
mysql>
source
/backup/test_446
.sql
#恢复增量后至删除前的数据
mysql> use
test
;
mysql> show tables;查看数据恢复成功!
|
1
2
3
|
vi
/etc/my
.cnf
datadir=
/myvg/data
bin-log=
/myvg/binlog/mysqld-bin
|
1
2
3
4
5
6
7
8
9
10
|
1>.创建物理卷
pvcreate
/dev/sdb
2>.创建卷组
vgcreate myvg
/dev/sdb
3>.创建逻辑卷
lvcreate -n mylv -L 19G myvg
4>.格式化文件系统
mkfs.ext4
/dev/myvg/mylv
5>.挂载使用
mount
/dev/myvg/mylv/
/data
|
1
2
3
|
wget http:
//www
.lenzg.net
/mylvmbackup/mylvmbackup-0
.14-0.noarch.rpm
yum
install
-y perl-TimeDate perl-Config-IniFiles perl-DBD-MySQL
#安装依赖包
rpm -ivh mylvmbackup-0.14-0.noarch.rpm
|
|