Mysql的备份和恢复_MySQL

Mysql的备份分类：
mysql的数据备份：
逻辑备份、物理备份
mysql按照数据集分类：
完全备份
增量备份
差异备份
一般这几种都是组合起来使用的，比如完全备份可以一周进行一次，增量备
份和差异备份可以一天备份一次，但是这要根据企业的业务逻辑而定，所以
合适的选择，将会在数据恢复的时候将损失降到最低。
mysql按照服务器是否停机分类：
冷备份：服务完全停止
温备份：服务不关闭，但是在备份的时候，其他的事务只有查询的操作
热备份:服务在线，不影响读写（但是非事务存储引擎的很难实现热备，但
是可以使用LVM实现几乎热备的功能）
需要备份的东西：
1.数据文件
2.日志文件（事务日志，二进制日志）
3.存储过程，存储函数，触发器
4.配置文件
日志分类：
1.事务日志：
随机IO转换成顺序IO
事件重放（撤销）提供基础
2.事件事务:
记录历史时间
MySQL的日志文件
1.the error log 错误日志
错误信息，服务器进程启动或停止产生的信息，主从中添加复制的时
候，也会记录
主配置文件启动错误日志，
log_error=/var/log/mysqld.err（确保对mysqld有写的权限）
2.the binary log 二进制日志
提供增量备份
基于时间点的恢复
复制架构提供基础
log_bin=/data/logs/binary/changelog （默认在数据文件中）
log_bin_index=/data/logs/relay/binarylog 定义二进制文件的索引
位置
3.the general query log 平时建议不开
general_log=ON|OFF
general_log_file=/PATH
4.the slow query log 慢查询日志
slow_query_log=ON
slow_query_log_file=/PATH
long_query_time=NUM 定义超时时间
5.relay log 中继日志
默认Mysql不开启任何日志，但在mysqld脚本中开启了错误日志(the
error log)

mysql常用的备份工具：
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy (MyISAM存储引擎的几乎冷备份，看着名字像是热备)

接下来我将借助我的一个简单的数据库进行演示数据的备份和还原的过程：
1.使用mysqldump来备份
mysqldump的备份是一个逻辑备份；
备份的格式如下;
#mysqldump db_name [table_name] > /PATH
当然mysqldump的备份还有很多选项：
--all-databases 备份所有的数据库
--events 备份事件
--routines 备份存储函数
--triggers 备份触发器
--extended-insert 备份扩展的插入
--skip-extended-insert 关闭备份扩展的插入
--lock-all-tables 加锁，这个是必须的
--flush-logs 刷新日志文件到磁盘中去，也是必须的
--master-data=n{0|1|2} 做复制的时候用到（2）
--no-data 只备份数据库中的表结构
--where "WHERE CLAUSE" 备份指定的数据
mysql> CREATE DATABASE luowei;
mysql> SHOW DATABASES;//查看所有的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| luowei |
| mysql |
| ndodb |
| test |
+--------------------+
mysql> USE luowei;
mysql> CREATE TABLE st (
-> ID INT UNIQUE AUTO_INCREMENT,
-> Name VARCHAR(30) );
mysql> SHOW TABLES; //查看表
+------------------+
| Tables_in_luowei |
+------------------+
| st |
+------------------+
mysql> INSERT INTO st (Name) VALUE (&＃39;a&＃39;),(&＃39;b&＃39;),(&＃39;c&＃39;);
mysql> SELECT * FROM st; //显示表中的数据
+----+------+
| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
这是我的数据库中原始的信息，因为等会要删掉数据库和表，所以先看一下
里面的数据，方便等会验证；
[root@nfs ~]# mysqldump -uroot -p luowei st > /root/st.sql //开始
备份表st，这里没有使用任何参数的
Enter password:
OK，这个时候备份完了，由于mysqldump使用的是逻辑备份，所以备份下来
的是一些create、insert语句
我们可以打开看一下
[root@nfs ~]# cat /root/luoweidb | grep -v "^/" | grep -v "^$"
-- MySQL dump 10.11
--
-- Host: localhost Database: luowei
-- ------------------------------------------------------
-- Server version 5.0.77
--
-- Current Database: `luowei`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `luowei` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `luowei`;
--
-- Table structure for table `st`
--
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `st` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(30) default NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `st`
--
LOCK TABLES `st` WRITE;
INSERT INTO `st` VALUES (1,&＃39;a&＃39;),(2,&＃39;b&＃39;),(3,&＃39;c&＃39;);
UNLOCK TABLES;
-- Dump completed on 2011-09-05 8:00:08
看到了吧，这里面是整个我刚做过的创建数据库，创建表，插入数据的语句
；
接下来，我们做一下数据库恢复：
[root@nfs ~]# mysql -uroot -e "DROP DATABASE luowei;" -p //删除
数据库
Enter password:
[root@nfs ~]# mysql -uroot -e "SHOW DATABASES;" -p //查看删除后
的数据库
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndodb |
| test |
+--------------------+
我把luowei的数据库给删除了，现在将进行数据库的恢复
[root@nfs ~]# mysql -uroot -p Enter password:
[root@nfs ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| luowei |
| mysql |
| ndodb |
| test |
+--------------------+
OK ，数据库是恢复过来了，接下来就是查看表中的信息是否完整了
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st" -uroot -p
Enter password:
+----+------+
| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
Ok,一起安好，呵呵，当然其他的参数就不做详细介绍了，需要用到的话，
可以使用HELP命令寻求帮助。
下面介绍一个本机不用输入Mysql密码的方法：
#vim /root/.my.cnf
[client]
user = root
password = redhat
保存起来，下次对数据库的操作就直接使用mysql命令了，不用在输入密码
了；
接下来的实验我也就不输入密码了；

2.使用SELECT INTO OUTFILE 的方法进行备份
还是用上面的表，
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st"
+----+------+
| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
如果想把ID小于等于2的备份出来，就可以使用这种方法备份了
mysql> SELECT * INTO OUTFILE &＃39;/tmp/st.bak&＃39; FROM st WHERE ID <=2;
[root@nfs ~]# cat /tmp/st.bak
1 a
2 b
我们可以看到一个文本文件，类似于表，但是这种方法只备份数据本身，不
备份表结构，所以恢复的时候，就需要按照结构重新建立一个新表，然后在
恢复
mysql> CREATE TABLE mt LIKE st; //我这里新创建一个基于st表格式的新
表mt，然后把刚备份的数据恢复到新表mt中，这也类是与恢复到原表了，呵
呵，小偷懒一下！！！
mysql> LOAD DATA INFILE &＃39;/tmp/st.bak&＃39; INTO TABLE mt;
mysql> SELECT * FROM mt;
+----+------+
| ID | Name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
ok了，我们现在来总结一下这两种方法，首先使用mysqldump备份的数据使
用逻辑备份的方式，备份的是一些对数据库的操作的语句，恢复简单；而使
用SELECT INTO OUTFILE这种可以备份指定的数据，有选择性的备份数据，
但是在恢复的时候就要麻烦一点，还要创建表。

3.基于LVM的快照备份：
操作步骤：
a.确保数据文件在LV上
b.对数据库中的表施加读锁
c.对数据所在的LV做快照
d.释放数据库的读锁
e.挂载快照卷，备份数据文件（对于InnoDB存储引擎，同时备份事务日志
文件）
f.删除快照
使用LVM做快照备份的条件是：
>SHOW VARIABLES LIKE &＃39;log_binlog&＃39;;处于ON
>SHOW VARIABLES LIKE &＃39;sync_binlog&＃39;改为1
>SHOW VARIABLES LIKE &＃39;innodb_support_xa&＃39; 处于ON的状态
LV快照备份
#mysql
>FLUSH TABLES WITH READ LOCK; //加上读锁
# lvcreate -L 50M -n mysnap -s /dev/myvg/mydata //创建快照
>UNLOCK TABLES; //解锁
#mount /dev/myvg/mysnap /mnt //挂载快照卷
#cd /mnt/data
#tar -jcf /root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-
bin.* //备份数据并压缩
#umount /mnt //卸载快照
#lvremove --force /dev/myvg/mysnap //移除快照

LV恢复
cd /usr/local/mysql
#chown -R mysql:mysql . //修改权限
#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ //
重新生成
#chown -R root .
#killall mysqld //杀掉所有关于mysql的进程
#cd /mydata/data
#tar xf /root/mysql.tar.bz2 -C ./ //把备份恢复到mysql的数据文件中
然后我们在登录到Mysql，就会发现原来的数据仍然存在了

4.使用binary日志文件实现时间点的恢复
显示当前正在使用的事务文件
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 330 | | |
+------------------+----------+--------------+------------------+
mysql> FLUSH LOGS; //滚动日志文件
再查看正在使用的事务文件，然后记录下这个Position就行了
由于二进制日志文件只记录数据修改的信息，所以每次的修改对应的
Position都会改变
现在我向表中插入一行数据
mysql> INSERT INTO st (Name) VALUE (&＃39;d&＃39;);
mysql> INSERT INTO st (Name) VALUE (&＃39;E&＃39;);
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 553 | | |
+------------------+----------+--------------+------------------+
由于我们前面已经做了一个完全备份，所有我们现在可以备份从330开始的
数据了
# mysqlbinlog --start-position 330 mysql-bin.000007
>/root/mysql.incr.sql //备份增加的二进制文件
然后我们这个时候登录到mysql
mysql>DROP DATABASE luowei;
然后恢复该数据库
# service mysqld stop
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# cd /mydata/data/
# tar xf /root/mysql.incr.sql -C .
# mysql # service mysqld start
登录到数据库，
就会发现有数据恢复正常

作者 “IT梦-齐-分享”