mysql数据库运维的五大指标 mysql数据维护

MySQL技术内幕——数据库维护、备份和复制

• 数据库维护、备份和复制

• 预防性维护的基本原则
• 在服务器运行时维护数据库

• 为只读或读／写访问锁定单个表
• 为只读访问锁定表
• 为读／写访问锁定表
• 为只读访问锁定所有数据库

• 基本的预防性维护
• 数据库备份

• 存储引擎的可移植特点
• 用mysqldump制作文本备份
• 制作二进制数据库备份
• 备份InnoDB表

• 将数据库复制到另一个服务器

• 使用备份文件来复制数据库
• 将数据库从一个服务器复制到另一个

• 使用备份来恢复数据

• 恢复整个数据库
• 恢复单个表
• 重新执行二进制日志文件里的语句
• 应对InnoDB的自动恢复问题

• 设置复制服务器

• 复制工作原理
• 建立主从复制关系

数据库维护、备份和复制

预防性维护的基本原则

为预防数据库出现的问题，应该采取以下措施。

• 启用MySQL服务器提供的自动恢复功能。
• 有计划地开展预防性维护工作，定期对表进行检查。
• 建立数据库备份计划。 定期备份+启用二进制日志。

出现了表损坏或数据丢失的问题，可以尝试使用下面的方法处理这些问题。

• 检查你的表，并尽可能地修复一发现的损坏表。轻微的损坏通常可以使用MySQL的表修复功能来修正。
• 如果对表进行检查和修复仍不能让服务器恢复运行，则需要使用备份和二进制日志来进行数据恢复。
  首先，利用备份把表恢复到备份时的状态。然后，再根据备份之后、崩溃发生之前所做的二进制日志，把表恢复到最新状态。

为完成上述任务，可供使用的工具除了MySQL服务器自身的功能以外，还有MySQL发行版自带的其他几种工具。

• 当服务器启动时，事务型存储引擎会进行自动恢复。
• 使用 mysqldump 程序来备份数据库，以后很可能会需要恢复它们。
• 如果想让服务器按需来执行维护操作，可以使用SQL语句，如 CHECK TABLE 和 REPAIR TABLE 。

在服务器运行时维护数据库

在其他一些情况下，也需要放止 MySQL服务器访问表。

• 使用了 myisampack 程序压缩的MyISAM表。
• 重定位了MyISAM表的数据文件或索引文件。
• 重定位了数据库。
• 使用了会复制表文件的备份技术。
• 使用了把损坏表替换为完好备份副本的恢复方法。

MySQL服务器提供了下面两种锁定机制。

• 内部锁定机制。
  服务器使用这一机制，可以防止不同客户端的请求互相干扰。
• 外部锁定机制。
  服务器使用这一机制，可以防止其他程序修改它正在使用的表文件。

为只读或读／写访问锁定单个表

如果要用服务器的内部锁定机制来防止它访问你正在处理的表，可用mysql连接服务器，并执行LOCK TABLE锁定表；
接着，把mysql闲置在那里，然后根据你的需要处理标文件。
在处理完成之后，切换回你的 mysql 会话，是否刚才的锁，告诉服务器它可以再次使用这个表了。

使用哪种锁定协议，取决于你是要对这个表的文件进行只读访问，还是要进行读/写访问。

这两种锁定协议都需要使用 LOCK TABLE 和 UNLOCK TABLE 语句，前者用来请求锁，后者用来释放锁。
它们还需要使用 FLUSH TABLE 语句来通知服务器刷新（flush）缓存的更改，将缓存的内容写入磁盘，这条语句的另一个作用是告诉服务器在下次访问这个表的时候，需要重新打开它。

执行锁定操作的一个简单方法是，同时打开两个窗口。
你可以让其中一个窗口持续运行 mysql ，而在另一个窗口里处理表文件。
在非窗口化环境里，你可以一边利用 shell 的作业控制设备来挂起和恢复 mysql 进程，一边处理表。

为只读访问锁定表

只读锁定协议适用于只需对表文件进行读取的操作，如复制文件或者检查文件的不一致性。
对于这种情况，获取读锁就够用了；服务器会阻止其他客户修改表，但会运行客户读取它。
在需要修改表时，请不要使用这种锁定方式。

• （1）在窗口Ａ里，调mysql，执行下列语句，获得读锁，刷新表

% mysql db_name
mysql> LOCK TABLE tbl_name READ;
mysql> FLUSH TABLE tbl_name;

• （2）切换到窗口Ｂ，开始处理表文件。

% cp tbl_name.* /var/backup

• （3）完成对表的处理后，再切换回窗口Ａ里的mysql会话，释放表锁

mysql> UNLOCK TABLE;

为读／写访问锁定表

既要获取读锁，也要获取写锁；
处理完后需FLUSH TABLE。

为读/写访问锁定表的过程如下所示。

• （1）在窗口Ａ里调mysql程序
  执行下面语句，以获得写锁，在刷新表

% mysql db_name
mysql> LOCK TABLE tbl_name WRITE;
mysql> FLUSH TABLE tbl_name;

• （2）切换到窗口Ｂ，直接处理表文件

% cp /var/backup/tbl_name.* .

• （3）刷新并释放表

mysql> FLUSH TABLE tbl_name;
mysql> UNLOCK TABLE;

为只读访问锁定所有数据库

如果想要阻止客户端修改任何表，最简单的办法莫过于以只读方式一次性锁定所有数据库里的所有表。

使用下面的这些语句可以做到这一点：

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

FLUSH可获得一个全局性读锁，SET会进入阻塞。

如果想再次允许修改，可以使用下面这些语句：

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

基本的预防性维护

• 启用MySQL服务器提供的自动恢复。
• 安排预防性维护，定期对表检查。
• 制定定期备份策略，这样，在数据库遭到破坏或出现数据丢失时，可以进行恢复。

数据库备份

数据库备份分为以下两类。

• 文本格式的备份。
  可通过mysqldump程序把表内容写入转储文件。
  文件内语句可用于后续恢复表。
• 二进制备份。
  可通过直接复制包含表内容的文件来获得。
  创建此类型备份的办法有很多种。例如，可以使用像 cp、tar 或 raync 之类的程序。

每一种备份方法都有其自己的优缺点。在挑选备份工具时需要考虑的因素包括是否需要关闭服务器、备份需要花费的时间、备份的可移植性和备份的范围。

无论选用哪种备份方法，都必须遵守以下原则，才能确保在需要恢复数据库内容时，获得最好的结果。

• 定期进行备份。
• 配置服务器，启用其二进制日志功能。
• 备份文件的命名方案，既要一致，又要有意义。
• 不要把备份文件和数据库放在同一个文件系统上。
• 要把数据库备份文件定期放到文件系统备份中。
• 定期清理过期的备份文件，防止它们用尽硬盘空间。

存储引擎的可移植特点

总结了几种存储引擎的二进制可移植性。

• InnoDB 和 MyISAM 表都是以与机器无关的格式存储的，并且都具有二进制可移植性。
• CSV表具有二进制可移植性，因为他们的 .CSV 数据文件是纯文本的。
• MEMORY 表不具有二进制可移植性，因为其内容存储在内存里，而非磁盘上。

用mysqldump制作文本备份

想要转储和重新加载某个表（如 sampdb.member），可以使用下面这些命令：

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

对整个数据库产生一个文件。

% mysqldump sampdb > /archive/mysql/sampdb.2013-01-02

制作二进制数据库备份

%cd /usr/local/mysql/data
%tar czf /archive/mysql/backup-all-2013-04-11.tar.gz .

%cd /usr/local/mysql/data
%cp -r mydb /archive/mysql // 备份mydb数据库

备份InnoDB表

如果想要制作二进制 InnoDB 备份，则必须注意以下几个特殊要求。

• InnoDB 有其自己的事务管理日志文件，这些日志文件在服务器允许时是激活的。
• 如果想要制作InnoDB表的二进制备份，需复制以下文件。
  １．系统表空间文件。
  ２．每个表的 .frm文件。
  ３．每个表的.ibd文件。
  ４．InnoDB日志文件。
  ５．系统表空间配置所特有的选项文件。

将数据库复制到另一个服务器

两种方法。

• 第一种方法，把数据库备份为一个文件或一组文件。
  可把这些文件复制到第二台服务器主机上，并把它们加载到第二台主机的MySQL服务器里。
• 第二种方法，通过网络把数据库从一个服务器之间转储到另一个服务器，使用这种方法无需用任何中间文件。

使用备份文件来复制数据库

使用文件备份文件来复制数据库的操作流程是：先用 mysqldump 程序创建备份文件，然后将其复制到第二台服务器主机上，最后将其加载到该主机的MySQL服务器里。

• （1）创建转储文件

% mysqldump --databases sampdb  > sampdb.sql

• （2）把转储文件复制到远程主机

% scp sampdb.sql boa.example.com:/tmp

• （3）登录远程主机，把转储文件加载到它的MySQL服务器

% mysql < /tmp/sampdb.sql

也可用二进制备份计数。

% cd /usr/local/mysql/data
% scp -r mydb boa.example.com:/var/mysql/data

如果想要这种方式把数据库文件复制到另一台主机上，就必须满足以下要求。

• 两台机器必须拥有相同的硬件架构，或者你所复制的那些表都使用了二进制可移植的存储引擎。否则，第二台主机上的结果表可能会出现非常奇怪的内容。
• 你必须阻止两台主机上的两个服务器尝试更改你正在复制的表。最安全的做法是，在处理表时，停止这两个服务器。

将数据库从一个服务器复制到另一个

%mysqldump --databases sampdb | mysql -h boa.example.com

使用备份来恢复数据

数据恢复过程涉及两个信息源：备份文件和二进制日志。

备份文件可以是转储文件，也可是二进制文件。

二进制日志文件里记载着自该次备份后所执行过的表更改语句。
mysqlbinlog可把这些日志文件转换回文本形式的SQL语句。以便使用mysql来执行它们，进而重新应用在备份和问题出现这两个时间点之间所产生的更改。

恢复整个数据库

• （1）复制一份数据库目录的内容。
• （2）用最近的备份文件重新加载数据库。

如果你的备份是用mysqldump生成的转储文件，需依次将每个文件作为mysql的输入，重新加载它们。

如果需恢复的数据库里有含权限表的mysql数据库，并且打算用转储文件来恢复那些表，则可在服务器运行时，使用–skip-grant-tables来重新加载它们。

把表都恢复好后，需关闭服务器，再重启它。

如果备份文件源自二进制备份，则需停止服务器，然后，在把备份文件复制到它们原来的位置，并重启服务器。

• （3）利用二进制日志，,重新应用制作备份后的数据更改。

恢复单个表

对由mysqldump生成的转储文件，其包含要恢复的表，只需重新加载这个文件即可。

对二进制方式的备份。

• 找到包含目标表的数据库，恢复其全部内容。
  实现方法是：利用备份，重新应用二进制日志。
• 从第二个数据库开始，用mysqldump转储感兴趣的表。
• 删除原先表，并把那个转储文件加载到原理的数据库，重建那个表。

重新执行二进制日志文件里的语句

mysqlbinlog可把二进制日志文件转换为文本形式的语句，使其输出可用作mysql输入。

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

应对InnoDB的自动恢复问题

如果需恢复所有的InnoDB表，则需用备份。
所采用的具体方法取决于备份的类型。

• 如果是二进制备份，那么你应有这样几个文件的副本：系统和单个的表空间文件，InnoDB日志文件，各个表的.frm文件，选项文件。
  服务器停止后，删掉已有的InnoDB文件，将其替换为备份副本。
• 如果用运行musqldump生成的转储文件来备份InnoDB，那么应重新初始化系统表空间和InnoDB日志文件，并将这个转储文件重新加载到InnoDB。
  （1）停止服务器并删除已有的与InnoDB有关的文件，如系统表空间文件，单个表空间文件，InnoDB日志文件，InnoDB表的.frm文件。
  （2）按原先设置重新配置系统表空间，重启服务器。
  （3）将转储文件当做mysql输入的方式重新加载它们。

设置复制服务器

复制工作原理

在MySQL里，数据库的复制原理如下。

• 复制关系中，有两种服务器，一种为主服务器，一种为从服务器。
  每个服务器会分配一个唯一的复制ID。
• 每个主服务器可有多个从服务器。
  一个从服务器可作为另一个从服务器的主服务器。
• 每个从服务器开始时，其数据库需与主服务器上的数据库同步。
• 更新通信基于主服务器的二进制日志。
  主服务器上需启用二进制日志功能
• 每个从服务器需有权限连接主服务器，并请求更新。
  从服务器连到主服务器时，会告诉主服务器，自它上次连接来在主服务器的二进制日志里已进展了多远。
  主服务器将把在二进制日志里记录的，自给定坐标后发生的事件传送给从服务器。
• 主服务器有新的更新时，会把这些更新写入主服务器的二进制日志，后续再传给从服务器。
• 从服务器最大连接个数可通过参数设置。
• 在从服务器上，服务器会使用两个线程来完成复制任务。
  I/O线程接收来自主服务器的待处理事件，将其写到从服务器的中继日志。
  SQL线程从中继日志中读出事件，并执行。

建立主从复制关系

如何在两个服务器之间建立主从复制关系。

• （1）确定你向分配给每个服务器的ID值，并把它们记录到服务器在启动时会读取的选项文件。
  主服务器需启用二进制日志。

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

[mysqld]
server-id=slave_server_id

• （2）在主服务器上，创建一个账户，让从服务器可用它来连接主服务器，并请求更新信心。
• （3）连接主服务器，并通过执行SHOW MASTER STATUS确定其当前的复制坐标：

mysql> FLUSH TABLES;SHOW MASTER STATUS;

确保确定复制坐标到数据快照传到从服务器后这段时间主服务器上没发生过更新。

• （4）需在从服务器上为将要复制的数据库建立一个副本.
• （5）连接从服务器，并使用CHANGE MASTER来配置它。
• （6）让从服务器开始复制。

学习参考资料：

《MySQL技术内幕》第5版