1 数据库预防性维护工作的基本原则
为预防可能发生的数据库故障,应该采取以下措施
>激活MySQL服务器的自动恢复能力。
>有计划地安排一些预防性的维护工作，定期对数据表进行检查。
>指定一份数据库备份计划。

遇到数据表损坏或数据丢失，处理原则：
>检查数据表。尽可能对发现的问题进行修复。MySQL的数据表修复能力能应付很多小破坏。
>如果对数据表进行的检查和修复扔不能使你的MySQL服务器恢复运行，就要用备份和二进制来进行数据恢复了。

用来完成以上任务的工具包括MySQL服务器本身的能力和MySQL发行版本自带的工具程序：
>在MySQL服务器启动时，事务型存储引擎将自动进行数据表检查和恢复处理。在此基础上，MySQL管理员还可以激活MyISAM存储引擎的数据表自动修复功能。
>使用mysqldump或mysqlhotcopy程序为数据库制作备份。
>可以用check table和repair table等SQL语句让MySQL服务器根据需要执行几种数据表维护操作。mysqlcheck工具为这些SQL语句提供了一个命令行操作界面。myisamchk工具也能对数据表进行检查并对它们进行多种修复。

myisamchk程序是一个可以直接操作数据表文件的独立程序。

2 在MySQL服务器允许时维护数据库
执行数据库维护的办法之一是连接MySQL服务器并告诉它应该做什么。比如，如果需要对某个MyISAM数据表进行数据完整性检查或进行修复，一种做法是发出check table或repair table语句让服务器去做这个工作，也就是让MySQL服务器去访问代表着这个数据表的.frm、.MYD和.MYI文件。

执行数据库维护操作的另一种办法，是使用一个不依赖于MySQL服务器的外部程序，但这个办法需要由你来协调各有关数据表上的访问冲突。如：检查和修复MyISAM数据表的工作还可以通过允许myisamchk程序的办法来进行，该程序将直接打开有关的数据表文件。在myisamchk程序在在访问数据表文件时，必须阻止MySQL服务器去修改那个数据表。

MySQL服务器提供了两种锁定机制：
>内部锁定机制。
>外部锁定机制。

2.1 以只读方式或读写方式锁定一个或多个数据表
过程是用mysql程序连接到MySQL服务器并发出一条lock table语句锁定打算使用的数据表，然后在另一个地方完成工作，回来刚才的mysql会话并释放刚才锁定的数据表。

使用lock table和unlock table语句来锁定数据表和接触锁定。它们还使用flush table语句通知MySQL服务器把挂起的改动写入硬盘。这个思路关键是必须在同一个mysql会话任务里执行所有的lock flush 和 unlock语句。

这里描述的利用MySQL服务器的内部锁定机制去锁定数据表的办法仅适用于那些把每个数据表分别表示为一组相关文件的存储引擎，不适用把多个数据表的信息保存在同一个给定文件里的存储一引擎。
1） 以只读方式锁定一个数据表(只能锁定MyISAM数据表)

mysql> lock table tbl_name read;
mysql> flush table tbl_name;			释放已经打开的表（如果已经打开）

然后在另一窗口运行：

myisamchk tbl_name				或者需要指定绝对路径

然后回来：

mysql> unlock table;

2) 以读写方式锁定一个数据表
为了修复数据而进行锁定，一、必须以写方式进行锁定。二、在处理完数据之后必须再发出一条flush table语句。有些操作，如用myisamchk程序去修复数据表会创建出一个新的索引文件，如果没有刷新数据表缓存，MySQL服务器就不会知道它的存在。

mysql> lock table tbl_name write;
mysql>flush table tbl_name;

切换到另一个终端：

myisamchk –recover tbl_name;

然后回来：

flush table tbl_name;		再次刷新
unlock table;

2.2 以只读方式锁定所有的数据库

flush tables with read lock;
set global read_only = ON;

FLUSH语句将申请一个全局性的读操作锁，set语句将在其他客户释放他们锁定的所有数据表并完成所有正在执行的事务之后才开始执行，在此之前一直处于被堵塞状态。

释放锁：

set global read_only = off;
unlock tables;

3 预防性维护
3.1 充分利用MySQL服务器的恢复能力
如果启用了InnoDB存储引擎，它能自动查出和纠正很多问题。比如，对于记载在重做日志里的事务，他会重做一遍；对于记载撤销日志里的事务，它会进行回滚。

对于MyISAM数据表，MySQL服务器提供了一种可选的数据表自动恢复能力，但必须启用这一能力。启用MyISAM数据表自动恢复能力的办法是使用—myisam-recover=level选项来启动MySQL服务器。level的值是一个以逗号分隔的、由以下几个值中的一个或多个构成的列表：backup(如果自动恢复工作需要修改某个数据表，先为它创建一个备份)，force(强制恢复，可能丢失数据)，quick(快速恢复)或default(不进行任何其它特殊处理的普通恢复，跟把该选项省略一样)。

如果在运行MySQL服务器时使用了—delay-key-write选项或把一些MyISAM数据表配置成使用键字缓写功能，就更应该启用MyISAM存储引擎的自动恢复能力了。所谓键字缓写是把索引数据的修改缓存在内存里，等到关闭数据表时才把它们写入磁盘文件。

3.2 定期进行预防性维护

4 制作数据库备份
数据库备份按照它们的格式可以分为两大类：
>文本格式的备份。
>二进制备份。

mysqldump程序必须与MySQL服务器配合使用，所以可以可以在MySQL服务器正在运行时使用它。MyISAM和InnoDB数据表通常与具体的机器无关，这两类数据表的数据表文件都可以直接复制到运行在另一台有着不同硬件结果的机器上的MySQL服务器去。

4.1 用mysqldump程序制作文本备份

mysqldump sampdb member > member.sql
mysql sampdb < member.sql

mysqldump –all-databases > /home/root/dump.2013.03.05
mysqldump sampdb > /home/root/sample.bk

一定不要用mysqlimport程序去重新加载mysqldb程序的SQL格式的输出。mysqlimport程序只能读取数据行，不能用来读取SQL语句。

减少转存文件长度的一个办法是对它进行压缩。

mysqldum sampldb | gzip > /root/db.bk

还可以分别转存各个数据表

mysqldump sampdb member president > hist-l.sql
mysqldump sampdb student score grade_event absence > gradebook.sql

mysqldump程序有许多选项：
>–databases 或 –all-databases选项都会在每个数据输出前插入必要的create database if not exists 和 use语句。
>默认mysqldump同时备份表结构和数据，如果只想备份其一，请使用—no-create-info或—no-data选项。
>–opt选项可以对转存过程进行优化。但是—opt选项会把正在转存的数据表全部锁上，这情况在数据表非常大是，效果非常明显。如果制作备份文件是为了用它们去定期更新另一个数据库的内容，–opt选项就会很有用。因为—opt选项将自动启用—add-drop-table选项。–opt选项的效果之一是它将启用—extended-insert选项，它可以生成一次插入多行的insert语句。
>在转存InnoDB数据表时，最好是用—single-transaction选项吧转存操作放在一个事务里执行。

4.2 制作二进制数据库备份
1)制作一个完整的二进制备份
2)制作一个部分的二进制备份
3)用mysqlhotcopy工具制作备份（只能用来备份MyISAM和ARCHIVE数据表）

4.3 备份InnoDB数据表

5 把数据库复制到另一个服务器
5.1 使用备份文件来复制数据库
5.2 把数据库从一个服务器复制到另一个

6 数据表的检查和修复
6.1 用服务器检查和修复数据表
1）用check table语句检查数据表（对MyISAM和InnoDB都有效）
要使用check table语句，先列出一组数据表名称，然后给出一个或多个可选的限定符表明要进行何种类型的检查。

check table tbl1, tbl2, tbl3 fast medium;

2)用repair table语句修复数据表（不能对应InnoDB）

6.2 用mysqlcheck程序检查和修复数据表
mysqlcheck程序是check table和 repair table语句的命令行界面。

mysqlcheck sampledb;
mysqlcheck sampledb president member;
mysqlcheck –databases sampledb test;
mysqlcheck –all-databases

与check table和repair table语句相比，语句需要明确给出想检查或修复的数据表的名字。

mysqldump程序选项				check table语句选项
--check-only-changed				changed
--extended						extended
--fast							fast
--medium-check					medium
--quick							quick

mysqlcheck程序选项				repair table语句选项
--repair							无
--repair –extended					extended
--repair –quick					quick
--repair –use-frm					use_frm

6.3 用myisamchk程序检查和修复数据表
1）用myisamchk程序检查数据表

myisamchk tbl_name
myisamchk –check tbl_name

如果不带任何选项，myisamchk程序的默认行为将是进行—check检查。如果myisamchk程序报告数据有错误，应该尽量修复它。

2)用myisamchk程序修复数据表

myisamchk –recover –quick tbl_name
myisamchk –recover tbl_name
myisamchk –safe-recover tbl_name

3)让myisamchk程序运行得更快

myisamchk –sort_buffer_size=16M –read_buffer_size=1M –wreite_buffer_size=1M other-options tbl_name

7 使用备份进行数据恢复
数据恢复工作需要两个信息来源：备份文件和二进制日志。
7.1 恢复整个数据库
7.2 恢复数据表
7.3 重新执行二进制日志文件里的语句
myslbinlog程序可以把二进制日志文件转换回文本形式的SQL语句，让它们容易执行：把mysqlbinlog程序的输出用作mysql程序的输入。

mysqlbinlog binlog.[0-9]* | mysql

如果要先编辑日志文件在重新执行它们，可以先把它们转换为文本格式并把转换结果保存为一个临时文件，再编辑那个临时文件，然后再把编辑结果导向mysql程序：

mysqlbinlog binlog.[0-9]* > text_file
vi text_file
mysql < text_file

不要使用mysqlbinlog和mysql一个个地处理二进制日志文件。这是因为个日志文件之间可能存在着某种依赖关系。

如只需要把作用于某特定数据库的语句提取出来：

mysqlbinlog –database=db_name binlog.[0-9]* | mysql

7.4 InnoDB存储引擎的自动恢复功能
在发生崩溃后，如果InnoDB存储引擎的自动恢复过程真的失败了，请把innodb_force_recovery系统变量设置为1到6之间的一个非零值并强行启动。

[mysqld]
innodb_forece_recovery=level

InnoDB存储引擎默认使用的自动恢复策略比较保守，它会尽量使用一个比较小的level值。在需要强行启动的绝大多数场合，建议从4开始。在强行启动后，用mysqldump转存数据，然后删除InnoDB数据表，再用备份恢复它。

8 设置复制服务器
8.1 复制机制的工作原理
必须给两个MySQL服务器分别分配一个独一无二的复制ID。
每个主服务器可以有多个从服务器。一个从服务器可以用作另一个从服务器的主服务器。
主服务器必须启用二进制日志功能。存储在二进制日志里的修改被称为事件。
每一个连接到主服务器的从服务器都将占用max_connections系统变量所设置的系统最大连接个数。
在从服务器端，服务器使用两个线程来完成复制任务。IO线程负责接收来自主服务器的待处理事件并把它们写入从服务器的中继日志。SQL线程负责从中继日志读出事件并执行它们。

8.2 建立主从复制关系
在启动主从服务器的时候，必须用server_id启动选项给出其ID值。在此基础上，一定要启用主服务器上的二进制日志功能。要想在主从服务器上启用二进制日志，最好的办法是在选项文件中加入：

[mysqld]
server-id=master_server_id
log-bin=binlog_name

[mysqld]
server-id=slave_server_id

在主服务器上，创建一个账户供从服务器连接主服务器并修改信息：

create user ‘slave_user’@’slave_host’ identified by ‘slave_pass’;
grant replication slave on *.* to ‘slave_user’@’slave_host’

连接到主服务器并通过执行show master status语句确定当前的复制坐标

mysql> flush tables; show master status;

记住File和Position值。

把主服务器上的数据库复制到从服务器。

连接到从服务器并使用change master语句来配置它，这包括用来连接主服务器的参数和初始复制坐标告诉从服务器：

change master to
	master_host=’’,
	master_user=’’,
	master_password=’’,
	master_log_file=’log_file_name’,
	master_log_pos=log_file_pos;

如果主服务器所监听的网络端口不是默认端口，可以在change语句里包含一个master_port选项以明确给出一个端口号。

让从服务器开始复制
start slave
从服务器将连接主服务器并开始复制。可以在从服务器上使用show slave status语句来查看它的工作状态。

从服务器把create master语句所给出的参数保存在其数据目录中的一个名为master.info的文件里以记录初始复制状态，并随着镜像工作的进展而刷新那个文件。

在从服务器的选项文件里加上以下代码，让它不要执行来自主服务器的对mysql数据库的任何修改：

[mysqld]
replicate-ignore-db=mysql

从服务器使用两个内部线程来管理服务活动。IO线程负责与主服务器进行通信，接收来自主服务器的信息，把接收到的数据修改命名写入从服务器的中继日志。SQL线程负责从中继日志读出数据修改命令并执行。可以通过在slave stop或slave start语句末尾加上io_thread 或sql_thread关键字的办法分别挂起或重新开始这两个线程中的任何一个。

8.3 二进制日志的格式
一般来说，基于语句的日志功能所生成的日志文件比较短小，内容页比较容易理解。基于数据行的日志功能在将要执行哪些修改方面可以提供更细致的控制。更细致的控制对复制来说是件好事，这是因为有些语句可能不够明确。

日志格式可以通过在启动时使用—binlog-format选项或是通过在运行时设置binlog_format系统变量的办法来加以选择。从MySQL 5.1.2开始，默认的日志格式是MIXED。其它可取值是STATEMENT或ROW。

8.4 使用复制机制制作备份