白天’s Blog

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。Xtrabackup中包含两个工具： * xtrabackup – 用于热备份innodb, xtradb表的工具，不能备份其他表。 * innobackupex – 对xtrabackup封装的perl脚本，提供了myisam表备份的能力。 Xtrabackup可以做什么 * 在线(热)备份整个库的InnoDB, XtraDB表 * 在xtrabackup的上一次整库备份基础上做增量备份（innodb only） * 以流的形式产生备份，可以直接保存到远程机器上（本机硬盘空间不足时很有用） Xtrabackup如何工作的 * xtrabackup – 具体原理有待研究。。。 * innobackupex整库备份 1. 调用xtrabackup对innodb表空间文件（这一瞬间的映像Time1）备份，而在这个innodb表备份期间数据库是不加锁的，外部可以继续往库里增减数据（这才能叫热备份）。而在Time1和Time2这两个时间点之间的改动由一个线程不断地扫innodb log获得(ChangeSet1)。 2. 锁所有库。 3. 以直接拷贝的方式备份frm,MYD,MYI,MRG,TRG,TRN,opt格式的文件。 4. 步骤3中的数据备份完毕时(Time2)，停止扫innodb log的线程，把ChangeSet1的数据拷贝到备份中。 5. 解锁所有库。 6. 终止挂起，备份完毕。 注意要点 * 根据innobackupex的原理可知它不是真正的热备份，MyISAM表越少越小就越有利。要利用Xtrabackup的好处就尽量用innodb表。 * 还原备份前关闭mysql服务；还原备份后检查数据文件权限是否正确。 * 性能：备份一个数据目录总大小5.6G，其中ibdata 2G，总时间4分钟，锁表时间2.5分钟。如果用mysqldump做这个库的备份锁表时间是5-8倍。 安装 tar zxf xtrabackup-0.7.tar.gz cd xtrabackup-0.7 [...]

全局共享内则主要是 MySQLInstance（mysqld进程）以及底层存储引擎用来暂存各种全局运算及可共享的暂存信息，如存储查询缓存的 QueryCache，缓存连接线程的 Thread Cache，缓存表文件句柄信息的 Table Cache，缓存二进制日志的 BinLogBuffer， 缓存 MyISAM 存储引擎索引键的 Key Buffer以及存储 InnoDB 数据和索引的 InnoDB BufferPool 等等。下面针对 MySQL 主要的共享内存进行一个简单的分析。 查询缓存（Query Cache）：查询缓存是 MySQL 比较独特的一个缓存区域，用来缓存特定Query 的结果集（Result Set）信息，且共享给所有客户端。通过对 Query 语句进行特定的 Hash 计算之后与结果集对应存放在Query Cache 中，以提高完全相同的 Query 语句的相应速度。当我们打开 MySQL 的 Query Cache 之后，MySQL接收到每一个 SELECT 类型的 Query 之后都会首先通过固定的 Hash 算法得到该 Query 的 Hash 值，然后到 QueryCache 中查找是否有对应的 Query Cache。如果有，则直接将 Cache的结果集返回给客户端。如果没有，再进行后续操作，得到对应的结果集之后将该结果集缓存到 Query [...]

  对于任何一个数据库管理系统来说，内存的分配使用绝对可以算的上是其核心之一了，所以很多希望更为深入了解某数据库管理系统的人，都会希望一窥究竟，我也不例外。 从内存的使用方式MySQL 数据库的内存使用主要分为以下两类 线程独享内存 全局共享内存 今天这篇文章暂时先分析 MySQL 中主要的 “线程独享内存” 的。 在 MySQL 中，线程独享内存主要用于各客户端连接线程存储各种操作的独享数据，如线程栈信息，分组排序操作，数据读写缓冲，结果集暂存等等，而且大多数可以通过相关参数来控制内存的使用量。 线程栈信息使用内存(thread_stack)：主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。 排序使用内存(sort_buffer_size)：MySQL用此内存区域进行排序操作（filesort），完成客户端的排序请求。当我们设置的排序区缓存大小无法满足排序实际所需内存的时候，MySQL会将数据写入磁盘文件来完成排序。由于磁盘和内存的读写性能完全不在一个数量级，所以sort_buffer_size参数对排序操作的性能影响绝对不可小视。排序操作的实现原理请参考：MySQL Order By 的实现分析。 Join操作使用内存(join_buffer_size)：应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候（all/indexjoin），为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作（具体Join 实现算法请参考：MySQL 中的 Join 基本实现原理）。当 Join Buffer 太小，MySQL 不会将该 Buffer 存入磁盘文件，而是先将Join Buffer中的结果集与需要 Join的表进行 Join 操作，然后清空 Join Buffer 中的数据，继续将剩余的结果集写入此 Buffer中，如此往复。这势必会造成被驱动表需要被多次读取，成倍增加 IO 访问，降低效率。 顺序读取数据缓冲区使用内存(read_buffer_size)：这部分内存主要用于当需要顺序读取数据的时候，如无发使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL按照数据的存储顺序依次读取数据块，每次读取的数据快首先会暂存在read_buffer_size中，当 buffer空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。 随机读取数据缓冲区使用内存(read_rnd_buffer_size)：和顺序读取相对应，当MySQL进行非顺序读取（随机读取）数据块的时候，会利用这个缓冲区暂存读取的数据。如根据索引信息读取表数据，根据排序后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read_rnd_buffer_size参数所设置的内存缓冲区。 连接信息及返回客户端前结果集暂存使用内存(net_buffer_size)：这部分用来存放客户端连接线程的连接信息和返回客户端的结果集。当 MySQL [...]

除了优化好数据库配置文件外，更换/增加MySQL数据库服务器的硬件，是提高数据库性能最直接有效的方法。 这里先从最便宜的内存入手。（服务器内存和硬盘价格一般是台式机的5倍左右） 最便捷的方法是使用mysqlreport，来持续关注报告里面‘Key’和‘InnoDB Buffer Pool’这两个部分。如果你的my.cnf参数设置正确，但是Read hit一直低于99%，那么就要考虑增加内存了。 那么Read hit是怎么计算出来的呢？为什么要持续关注？在MySQL的命令行下： mysql> show status like ‘key_read%’; +——————-+————+ | Variable_name | Value | +——————-+————+ | Key_read_requests | 3041374401 | | Key_reads | 60959876 | +——————-+————+ 2 rows in set (0.02 sec) key_efficiency(Read hit) = 1 – (Key_reads / Key_read_requests) = 97.995647100207184% mysql> show status like ‘Innodb_buffer_pool_read%’; +———————————–+————+ | Variable_name [...]

  如果目前只有一个ibd的文件，那么我们该怎么恢复整个数据库？原文的作者给出了两种恢复的方法。这两种方法的前提有两个： 1、一是知道所有需要恢复表的创建语句 2、需要一个16进制的编辑器，修改表id 具体的内容请看原文： Sometime you may need to recover a table when all you have is the .ibd file. In this case, if you try to load it into a new instance, your likely to encounter some errors about the table id not matching. And there is not really a way around this. [...]

1、选取最适用的字段属性 　　MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的 性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间， 甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是 BIGIN来定义整型字段。 　　另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 　　对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数 值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) 　　MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查 询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给 主查询，如下所示： 　　DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 　　使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下， 子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成： 　　SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 　　如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将 会更好，查询如下： 　　SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID [...]

作者：heiyeluren 时间：2008-1-28 博客：http://blog.csdn.net/heiyeshuwu   【 测试平台 】 服务器端： OS：FreeBSD 6.2 Lua： 5.1.1 MySQL Server：4.1.22-log MySQL Proxy： 0.6.0 客户端： OS：Windows XP Pro SP2 PHP： PHP 5.2.4 (cli) MySQL Client： Ver 14.13 Distrib 5.1.19-beta for Win32 注意：本文使用的是最新版本 MySQL Proxy alpha 0.6.0 ，如果是0.5.0版本可能部分选项和操作不一致，同样的，以后如果除了新版本，请参照新版本文档。 【 MySQL Proxy 安装 】 $ fetch http://mirror.mysql-partners-jp.biz/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-freebsd6-x86.tar.gz $ tar zxvf mysql-proxy-0.6.0-freebsd6-x86.tar.gz $ cd mysql-proxy-0.6.0-freebsd6-x86 $ cp sbin/mysql-proxy /usr/local/sbin/ 如果无法正常使用，请检查相应的其他组件是否安装了，mysql-proxy 需要依赖 lua、libtool、glib、libevent 等库。 【 初步使用 [...]

从 hi!admin 抄来的一份配置.注释得非常好.精 #BEGIN CONFIG INFO #DESCR: 4GB RAM, 只使用InnoDB, ACID, 少量的连接, 队列负载大 #TYPE: SYSTEM #END CONFIG INFO # # 此mysql配置文件例子针对4G内存 # 主要使用INNODB #处理复杂队列并且连接数量较少的mysql服务器 # # 将此文件复制到/etc/my.cnf 作为全局设置, # mysql-data-dir/my.cnf 作为服务器指定设置 # (@localstatedir@ for this installation) 或者放入 # ~/.my.cnf 作为用户设置. # # 在此配置文件中, 你可以使用所有程序支持的长选项. # 如果想获悉程序支持的所有选项 # 请在程序后加上”–help”参数运行程序. # # 关于独立选项更多的细节信息可以在手册内找到 # # # 以下选项会被MySQL客户端应用读取. [...]