MySQL 的备份和恢复

一、实验环境

系统版本：fedora 10

MySQL版本: MySQL 5.0

二、完整备份如果你的数据库允许停止服务并且需要备份数据量很大，你可以停止服务后直接复制数据库对应的文件：/var/lib/mysql/database 这样的每个数据库对应于一个实际的目录，直接复制备份即可。
如果你的服务器不允许停止服务，那么我们可以使用MySQL的热备份工具：mysqldump,它的原理是把数据库内的内容全部用sql语句 进行描述，这样做的好处是我们导出来的这些sql语句只需简单的批量修改一些和别的数据库不兼容的地方，就能把Mysql的数据导出到oracle等其它 数据库内。
mysqldump -uroot -p test > /tmp/test.sql
这条语句导出了test表到test.sql文件中，我们来看下导出的主要内容吧：

-- Table structure for table `books`
DROP TABLE IF EXISTS `books`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `books` (
`id` int(11) default NULL,
`name` text,
`publish` date default NULL,
`author` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
-- Dumping data for table `books`
--
LOCK TABLES `books` WRITE;
INSERT INTO `books` VALUES (0,'Go to China','2002-10-21','Jim');

我们可以看到创建表和插入数据的sql语句。

三、数据的恢复有了完整备份和二进制日志，如果发生一些误操作或数据库当机，我们就能够利用完全备份以及二进制日志来把我们发生误操作之前的内容恢复出。
举例：我们在完整备份后，对数据库有删除和更新记录等操作，随后有人误操作，把整个表全部清空，我们现在来恢复到清空前的状态。
首先，实验的前提是在打来了二进制日志的情况下，去/etc/my.conf下，添加：

log-bin=BinLog
binlog-do-db=test

当服务重新运行起来之后，进入mysql
mysql>show master status;

+---------------------+---------------+--------------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+---------------+--------------------+-------------------------------+
| BinLog.000003 | 98 | test | |
+---------------------+---------------+--------------------+-------------------------------+

提示我们test这个数据库当前在用BinLog.000003这个二进制日志记录。
下面，我们开始模拟一些操作：

mysql> insert into books set
-> id=2,
-> name='Learn C++',
-> publish='2003-01-12',
-> author='jak'
-> ;


mysql> update books set author='Jerry' where id=0;
delete from books;

误操作以后，我们发现数据全部清空了，下面开始回复。
首先是恢复到完全备份：
mysqldump -uroot -p test < /tmp/test.sql
接下来，我们用二进制日志来恢复后来一直到清除表以前的操作：
首先我们看下完全备份文件的时间戳：

stat /tmp/test.sql | grep Change
Change: 2009-05-22 21:56:42.000000000 +0800

可以看到，完全备份完成的时间是2009-05-22 21:56:42，我们使用这个时间作为二进制日志回复的开始时间。
接下来，我们需要知道清空表的那个操作执行的时间：
mysqlbinlog /var/lib/mysql/BinLog.000003
我们找到delete from books这条句子，查看时间。

#090522 22:24:12 server id 1 end_log_pos 532 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1243002252/*!*/;
delete from books
/*!*/;

可见删除发生的时间为090522 22:24:12
这样，我们就确定了开始的时间和结束的时间，接下来开始重新执行完全备份到发生误操作这段时间内的这些操作。
mysqlbinlog --start-date="2009-05-22 21:56:42" --stop-date="2009-05-22 22:24:12" /var/lib/mysql/BinLog.000003 | mysql -uroot -p
执行完后，我们去数据库内可以验证一下。
由于二进制日志对操作的时间记录精确到秒，如果我们一秒内发生多个操作就不能这样了，我们可以用另外一个选项。
mysqlbinlog --start-position=”****” --stop-position=”*****” /var/lib/mysql/BinLog.000003 | mysql -uroot
以上的****表示对应的操作在二进制日志内的POS数值，我们需要自己从日志中得到这个数据来确定，读者可以尝试之。
四、AB复制Ab复制可以使两个数据库服务器在运行中保持某个库或全部库内容的一致，其原理基于MySQL的二进制日志会不断的记录对数据库 的所有操作，只要我们初期两个数据库内容一致的话，让主服务器打开某个库的二进制日志、从数据库在相同的库内容的基础上接受主服务的日志并执行相同的操 作。
主服务器IP：192.168.0.32 从服务器IP：192.168.0.21
1、首先我们需要打开主服务器需要同步的库的二进制日志功能：修改/etc/my.conf

[mysqld]
server-id=1 //修改服务器ID
log-bin=BinLog //二进制日志文件名
binlog-do-db=test //对test这个库执行二进制日志操作

接下来我们重启主服务器，并且添加一个授权用户让从服务器获取日志使用：
service mysqld restart;
mysql>grant replication slave,reload,super on test.* to slave@192.168.0.21 identified by 'slavepass'
mysql>flush privileges #刷新授权表
以上操作完成后我们从从服务器使用新账户登录一下主服务器，检验是否连接成功。
mysql -uslave -pslavepass -h191.168.0.21
2、从服务器的设置稍复杂一些，首先是修改/etc/my.conf
增加这些内容：

[mysqld]
server-id=2
master-host=192.168.0.32
master-user=slave
master-password=slavepass

配置完成后，我们需要重启服务：
service mysqld restart
进入从服务器的mysql客户端，

mysql>start slave;
mysql>load data from master;
mysql>show slave status"G; #显示从服务器状态

上面的命令的返回结果：
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.32 #主服务器IP地址
Master_User: slave #用户名
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: BinLog.000002 #读取的主服务器的二进制日志
Read_Master_Log_Pos: 98 #读取到的二进制日志的位置
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 232
Relay_Master_Log_File: BinLog.000002
Slave_IO_Running: Yes #IO是否已经启动
Slave_SQL_Running: Yes #SQL是否已经启动