一、 数据备份与恢复
1.1 备份数据的方式
物理备份:cp tar
Mysqlhotcopy:只能备份存储引擎是myisam的表
且依赖于perl-DBD-MySQL
Yum -y install perl-DBD-MySQL
备份格式:
Mysqlhotcopy -uroot -p123 原库名 新库名;
逻辑备份:用mysql自己提供的命令或第三方软件提供的备份命令,
备份创建库,表和产生表记录时的sql语句。
1.2 数据备份策略
完全/完整备份:备份数据库服务器上的所有库所有表。
差异备份:备份自完整备份后所产生的新数据。
增量备份:备份自上一次备份后所产生的新数据。
(1) 完整备份:
Mysqldump 命令做数据的完整备份
备份格式:
mysqldump -h 服务ip -u用户名 -p密码 数据库名 > 目录名/备份文件名
数据库名表示方式:--all-databases 备份所有库所有表
数据库名 对某个库备份
数据库名 表名 指定库的指定表备份
-B数据库1 数据库2 备份多个数据库
目录名:若备份时不写目录名,备份文件存放在执行备份命令时所在的库目录下
备份文件名:可以自己定义,要有标识性,文件名以.sql结尾
若文件名重复,上次备份会被当前备份覆盖。
*备份时用来连接数据库服务器的用户,要对备份的目标库有相应的权限
[root@localhost ~]# service mysql restart
[root@localhost ~]# mkdir /mysqlbak
[root@localhost ~]# mysqldump -hlocalhost -uroot -p999 --all-databases > /mysqlbak/all-`date +%d`.sql
恢复格式:
Mysql -h服务器ip -u用户名 -p密码 目标库名 <目录名/备份文件
[root@localhost~]#mysqldump -hlocalhost -uroot -p999 db100 > /mysqlbak/aldb100-`date +%d`.sql //备份
mysql> use db100
mysql>drop table t1,t2,t3;
[root@localhost~]#mysql-hlocalhost -uroot -p999 db100 < /mysqlbak/db100.06.sql; //恢复
二、 使用binlog日志
1、 mysql日志类型
常用日志及选项
2、 启用binlog日志
采用binlog日志的好处
实现数据的增量备份和增量恢复
掌控所有更改操作,必要时可用于数据恢复
数据库主从复制的必要条件
[root@localhost ~]# Vim /etc/my.cnf
[mysqld]
.. ..
Log_bin //启用二进制日志
[root@localhost ~]# Service mysql restart
[root@localhost ~]# ls /var/lib/mysql/
localhost-bin.000001 //第一份二进制文件
localhost-bin.index //记录日志列表
*一下情况可以生成一份新的日志:
(1)重启mysql服务
(2)执行sql操作flush logs;
(3)备份时添加- -flush-logs选项
Mysqldump - -flush-logs -h -u -p 库名 > xxx.sql
(4)在命令行下执行SQL语句(用选项-e)
Mysql -h -uroot -p密码 -e “flush logs”
3、 查看binlog日志文件
[root@localhost mysql]# mysqlbinlog [选项] binlog日志名
常用选项:
--start-datetime=”YY-mm-dd HH:MM:SS”
--stop-datetime=”YY-mm-dd HH:MM:SS”
--start-position=起始位置
--start-position=结束位置
*应用实例:
[root@localhostmysql]#mysqlbinlog --start-datetime="2014-05-06 15:26:40" localhost-bin.000001
4、 清理binlog日志
删除早于指定版本的binlog日志:
Purge master logs to “binlog文件”;
删除所有binlog日志,重建新日志:
Reset master;
5、 曾量恢复
Mysqlbinlog 日志名(xxx.sql) | mysql –hip -u用户名 -p密码
示例:
[root@localhost mysql]# cd /var/lib/mysql/
[root@localhost mysql]# mysqlbinlog localhost-bin.000001 | mysql -uroot -ptarena
6、 错误日志
存放位置:数据库目录下
默认名:主机名.err
设置错误日志:
[root@localhost ~]# Vim /etc/my.cnf
[mysqld_safe] //启用错误日志
Log_error=文件名 //指定错误日志名
[root@localhost ~]#Service mysql restart
7、 查询日志
存放在:数据库目录/localhost.log
[root@localhost ~]#Vim /etc/my.cnf
[mysqld]
General-log //启用查询日志
General-log-file=目录名/xxx //指定查询日志名(默认名:主机名.log)
[root@localhost ~]#Service mysql restart
[root@localhost ~]#tail -f /var/lib/mysql/localhost.log //动态查询
8、 慢查询日志
只记录超过指定时间显示查询结果的sql语句。默认时间是10秒。
帮助找出执行效率低的SQL语句
[root@localhost ~]#Vim /etc/my.cnf
[mysqld]
Slow-query-log //启用慢查询日志
Long-query-time=3 //设置耗时超过3秒才记录
Long-query-file=目录名/xxx //设置慢查询文件名
[root@localhost ~]#Service mysql restart
慢查询的默认名:主机名-slow.log
三、 XtraBackup备份
Mysqldump:数据备份与恢复都会锁表
Mysqlhotcopy:只适用于myisam引擎,也会锁表
Binlog:不是真正意义上的增量备份与恢复
Xtrabackup:是第三方数据备份软件,完整备份、增量备份、差异备份、在线备份
(1) 安装软件包:
Rpm -ivh percona-xtrabackup(或用yum)
依赖包(perl-DBD-MySQL、perl-Time-HiRes 、perl-DBI)
[root@localhost Packages]# rpm -ql percona-xtrabackup
.. ..
/usr/bin/xtrabackup_55 //适用于MySQL 5.5.x
/usr/bin/xtrabackup_56 //适用于MySQL 5.6.x
(2) Xtrabackup基本选项
--backup:执行备份操作
--target-dir:备份的目标文件
--datadir:备份的原始文件夹(MySQL库的位置)
--prepare:准备恢复数据
--incremental-basedir:增量备份时,指定参照的完整备份路径
--incremental-dir:准备恢复目录时,指定增量备份的路径
1、 Xtrabackup完整备份与恢复
Xtrabackup:只备份表记录,不备份表结构。所以用Xtrabackup备份的文件恢复时库、表一定要存在
完整备份:
[root@localhost ~]#mkdir -p /dbbak/first
[root@localhost~]#xtrabackup_56 --backup
--datadir=/var/lib/mysql --target-dir=/dbbak/first
[root@localhost ~]#ls /dbbak/first
Xtrabackup_logfile
Xtrabackup_checkpoints
恢复:
[root@localhost~]#xtrabackup_56 --prepare
--datadir=/var/lib/mysql --target-dir=/dbbak/first
[root@localhost~]#cp /dbbak/first/xtrabackupdb/*
/var/lib/mysql/ xtrabackupdb/
2、 Xtrabackup曾量备份与恢复
增量备份时,必须事先有一个完整备份,这样才知道哪些数据是新产生的数据。
增量备份:
[root@localhost ~]# mkdir -p /dbbak/second
[root@localhost ~]#xtrabackup_56 --backup --datadir=/var/lib/mysql
---target-dir=/dbbak/second --incremental-dasedir=/dbbak/first
增量备份:
[root@localhost ~]# mkdir -p /dbbak/three
[root@localhost ~]#xtrabackup_56 --backup --datadir=/var/lib/mysql
---target-dir=/dbbak/three --incremental-dasedir=/dbbak/first
增量恢复:
原文:http://changnan.blog.51cto.com/8825738/1409445