[译]Innodb 性能优化基础

Innodb 性能优化基础

原文链接 http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
以便于理解,略作删改

问一个基础的问题–如果你有一个16G内存的服务器,专用于mysql大型的Innodb数据库.应该做什么样的设置?

硬件
如果你的Innodb数据库很大,内存是首要的.16-32G现在很便宜了.CPU方面 2个双核的core 就非常好了.但是这跟应用也有很大的关系.第三是IO系统-DAS和RAID是很好的选择.一般来说6-8块硬盘就够了,有时可能需要更多.而且新的2.5″的SAS硬盘,小却速度快.RAID10对于数据存储和主要是读的场合下十分好.需要冗余性的话RAID5也不错但注意对于RAID5的随机写操作.

操作系统
首先 运行64位的操作系统.现在还有很多32位的系统带着很大的内存运行着.建议不要这么做.如果系统是linux,对数据库的目录使用LVM可以获得更高效的备份.ext3文件系统大部分情况下都不会出问题,如果碰到问题的话,试试XFS.如果你使用innodb_file_per_table而且表很多的话可以使用noatime和nodiratime选项,但是这样做效果不是很大.Also make sure you wrestle OS so it would not swap out MySQL out of memory.
(最后这句话不知道该如何翻译)

MYSQL 的Innodb 设置
最重要的地方有:

innodb_buffer_pool_size 设为内存的70%-80%都是安全的.我在一个16G的机器上把它设成12G.
UPDATE 关于它具体的查看http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
innodb_log_file_size 这取决于你需要的回复速度.256M这个数值是适当的恢复时间和良好性能之间的一个好的平衡.
innodb_log_buffer_size=4M 大多数情况4M足够,除非正将很大的blob数据导入到Innodb中可以增加一点.
innodb_flush_log_at_trx_commit=2 如果你不是很关心ACID,可以容许在系统完全crash的情况下丢失最后一两秒的事务,那么可以设置这个值.它可以极大的提高“短“的写事务的效率.
innodb_thread_concurrency=8 这个值取决于你的程序,可能高或者低.8是代表起始值.
innodb_flush_method=O_DIRECT 避免双缓冲(double buffering)和降低swap的压力.大多数情况下可以提高性能.但是注意如果你RAID cache不够的话,写IO的操作会有麻烦.
innodb_file_per_table 如果你的表不多可以使用这个选项.这样你就不会有不受控的innodb主表空间的增长,这个主表空间是不能重新定义的.这个选项在4.1版中引入,现在可以放心使用.

查看你的程序是否可以运行在READ-COMMITED 隔离模式下,如果可以,就可以设为默认的transaction-isolation=READ-COMMITTED.这个选项有一些性能的优势,特别是在5.0,5.1版和行级别的复制方面.

其他的可以参考
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
http://www.mysqlperformanceblog.com/mysql-performance-presentations/

应用程序的优化
如果原来是MyISAM,现在你可能需要对应用做一些修改.首先确保你在进行数据库更新的时候使用事务,这对数据一致性和性能都有好处.
其次如果你的应用有写操作的话要注意处理死锁问题.
第三你要重新检视你的表结构,尽可能利用Innodb的优势–主键的群集(clustering by primary key),在所有的索引里面有主键,让主键简单.使用主键来快速查询(在连接中使用),large unpacked indexes (try to be easy on indexes).

使用这些基本的innodb性能优化技术,你就会比一般按照默认配置来运行mysql用户上了一个层次.

http://yahoon.blog.51cto.com/13184/76592

[翻译][注解]Innodb Performance Optimization Basics

原文链接地址如下:http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
这篇文章写于2007年11月
翻译参考了这篇译稿:http://yahoon.blog.51cto.com/13184/76592
推荐详细阅读原作者的这篇演讲稿
Innodb性能优化基础
面试别人的时候我喜欢问一个基础的问题:如果你有一个16G内存,专用于mysql大型innodb的数据库服务器,
对于典型的web负载,你应该怎样调整mysql的设置?有趣的是其中大多数并不能提出任何有益的建议。
所以我决定公布答案,并且我很乐意在硬件,操作系统和应用方面谈谈基础的一些优化。
这篇文章的标题是‘Inodb性能优化基础’,所以这里面的是一些普遍的准则,适用于很多的应用场景,
当然最佳的设置要依据具体的应用而定。

硬件
如果你的Innodb数据库很大,那么内存是最重要的。现在16-32G的内存性价比就不错。
From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads.
CPU方面,两个双核的CPU,似乎就不错了,而即使只有两个四核心CPU的可扩展性问题都可以观察到很多的工作量,但是这跟应用也有很大的关系。(这里翻译的很别扭,大家看原文)
第三是IO系统--DAS和RAID是很好的选择.一般来说6-8块硬盘就够了,有时可能需要更多。同时注意新的2.5″的SAS硬盘,小却速度快。RAID10对于数据存储和主要是读的场合下十分合适。需要冗余性的话RAID5也不错,但注意对于RAID5的随机写操作。

操作系统

首先--运行64位的操作系统。现在不少有大内存的服务器,上面还跑着32位的操作系统。建议不要这么做。
如果系统是linux,对数据库的目录使用LVM可以获得更高效的备份。
ext3文件系统大部分情况下都不会出问题,如果碰到问题的话,试试XFS。
如果你使用innodb_file_per_table而且表很多的话可以使用noatime和nodiratime选项,但是这样做效果不是很大。
同时注意给系统留出足够的内存,防止mysql和系统发生内存竞争导致被交换出内存。

MYSQL 的Innodb 设置

(关于更多更详细的参数说明,请参考这里(中文文档))
最重要的地方有:

innodb_buffer_pool_size 设为内存的70%-80%都是安全的。我在一个16G的服务器上把它设成12G。
UPDATE: 如果你想了解更多的细节,请查看tuning innodb buffer pool
innodb_log_file_size 这取决于你需要的出错恢复速度。256M是合理的恢复时间和良好性能之间不错的一个平衡值。
innodb_log_buffer_size=4M 大多数情况4M就够了。如果你有大量的事务处理,这个数值可以增加一点儿。
innodb_flush_log_at_trx_commit=2 如果你不是很关心ACID,可以容许在系统完全崩溃的情况下丢失最后一两秒的事务,那么可以设置这个值为2。它可以极大的提高短的写事务的效率。
innodb_thread_concurrency=8 即使目前的InnoDB可扩展性修复后,对并发的支持也是有限的。这个值取决于你的程序,可能高或者低一些。8是可以接受的默认值。
innodb_flush_method=O_DIRECT 避免双缓冲(double buffering)和降低swap的压力,大多数情况下可以提高性能。但是注意如果你RAID cache不够的话,写IO的操作会有麻烦。
innodb_file_per_table 如果你的表不多可以使用这个选项。这样你就不会有不受控的innodb主表空间的增长,这个主表空间是不能重新定义的。这个选项在4.1版中引入,现在可以放心使用。
查看你的程序是否可以运行在READ-COMMITED 隔离模式下,如果可以,就可以设为默认的transaction-isolation=READ-COMMITTED。这个选项有一些性能的优势,特别是在5.0,5.1版本的行级复制方面。
还有很多的参数选项需要调整,今天我们就只关注关于和Innodb相关的。其他的可以参考 tuning other options 和 MySQL Presentations.

应用程序的优化

如果原来是MyISAM,现在你可能需要对应用做一些修改。首先确保你在进行数据库更新的时候使用事务,这对数据一致性和性能都有好处。
其次如果你的应用有写操作的话要注意处理死锁问题。
第三你要重新检视你的表结构,尽可能利用Innodb的优势–簇集主键索引(clustering by primary key),在所有的索引里面有主键(所以要保持主键简短)。使用主键来快速查询(试着在joins时使用),large unpacked indexes (try to be easy on indexes)。(这一句不懂)
With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it on hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM tables in mind.
原文 http://moonbingbing.blogspot.com/2008/08/innodb-performance-optimization-basics.html

什么时候该增加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 | Value |
+-----------------------------------+------------+
| Innodb_buffer_pool_read_ahead_rnd | 1660545 |
| Innodb_buffer_pool_read_ahead_seq | 576767 |
| Innodb_buffer_pool_read_requests | 2080081461 |
| Innodb_buffer_pool_reads | 292415839 |
+-----------------------------------+------------+
4 rows in set (0.02 sec)
key_efficiency(Read hit)= 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) = 85.942096764834353%

从文档里面找出这几个参数的意义:

Key_read_requests:从缓存读键的数据块的请求数。
Key_reads:从硬盘读取键的数据块的次数
Innodb_buffer_pool_read_ahead_rnd:InnoDB初始化的“随机”read-aheads数。当查询以随机顺序扫描表的一大部分时发生。
Innodb_buffer_pool_read_ahead_seq:InnoDB初始化的顺序read-aheads数。当InnoDB执行顺序全表扫描时发生。
Innodb_buffer_pool_read_requests:InnoDB已经完成的逻辑读请求数。
Innodb_buffer_pool_reads:不能满足InnoDB必须单页读取的缓冲池中的逻辑读数量。
这几个都是时刻在变化的,一两次的查询并不能暴露问题。

原文 http://moonbingbing.blogspot.com/2008/12/mysql_23.html

mysql中InnoDB的强制恢复

如果数据库页被破坏,你可能想要用SELECT INTO OUTFILE从从数据库转储你的表,通常以这种方法获取的大多数数据是完好的。即使这样,损坏可能导致SELECT * FROM tbl_name或者InnoDB后台操作崩溃或断言,或者甚至使得InnoDB前滚恢复崩溃。 尽管如此,你可以用它来强制InnoDB存储引擎启动同时阻止后台操作运行,以便你能转储你的表。例如:你可以在重启服务器之前,在选项文件的[mysqld]节添加如下的行:

[mysqld]
innodb_force_recovery = 4
innodb_force_recovery 被允许的非零值如下。一个更大的数字包含所有更小数字的预防措施。如果你能够用一个多数是4的选项值来转储你的表,那么你是比较安全的,只有一些在损坏的单独页面上的数据会丢失。一个为6的值更夸张,因为数据库页被留在一个陈旧的状态,这个状态反过来可以引发对B树和其它数据库结构的更多破坏。

· 1 (SRV_FORCE_IGNORE_CORRUPT)

即使服务器检测到一个损坏的页,也让服务器运行着;试着让SELECT * FROM tbl_name 跳过损坏的索引记录和页,这样有助于转储表。

· 2 (SRV_FORCE_NO_BACKGROUND)

阻止主线程运行,如果崩溃可能在净化操作过程中发生,这将阻止它。

· 3 (SRV_FORCE_NO_TRX_UNDO)

恢复后不运行事务回滚。

· 4 (SRV_FORCE_NO_IBUF_MERGE)

也阻止插入缓冲合并操作。如果你可能会导致一个崩溃。最好不要做这些操作,不要计算表统计表。

· 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

启动数据库之时不查看未完成日志:InnoDB把未完成的事务视为已提交的。

· 6 (SRV_FORCE_NO_LOG_REDO)

不要在恢复连接中做日志前滚。

数据库不能另外地带着这些选项中被允许的选项来使用。作为一个安全措施,当innodb_force_recovery被设置为大于0的值时,InnoDB阻止用户执行INSERT, UPDATE或DELETE操作.

即使强制恢复被使用,你也可以DROP或CREATE表。如果你知道一个给定的表正在导致回滚崩溃,你可以移除它。你也可以用这个来停止由失败的大宗导入或失败的ALTER TABLE导致的失控回滚。你可以杀掉mysqld进程,然后设置innodb_force_recovery为3,使得数据库被挂起而不需要回滚,然后舍弃导致失控回滚的表。

本文地址:http://www.bhcode.net/article/20090227/4256.html

只有一个ibd文件如何恢复数据表

如果目前只有一个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.

However, I’ve found two work-arounds for this:

Note: You will need the .ibd file and the CREATE TABLE statement for each table you want to recover using these methods.

Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore). (See Method #1)
Manually hex edit the .ibd file, changing the table id. (See Method #2)
*Note: There are some internal structures with this meta information, so you’ll need to dump/import that data after you get it loaded, so you avoid unpleasantries that will inevitably show their face.

Method #1 – Create work tables

1. Start up clean/fresh instance of MySQL with innodb_file_per_table enabled.

2. Now, we need to find the table id that MySQL is currently set at, as well as the table id for the table we need to recover.

Note:
Step 2 (2a – 2f) is simply to find the table id that is stored inside of the .ibd file. I’ve written a PHP script to determine this, so using the script can save a bunch of time. See the bottom of this page (under “Associated Files”) for the exact script.

2a. Create a test database:

mysql> CREATE DATABASE test1;
mysql> USE test1;

2b. Issue the create table command for the table:

mysql> CREATE TABLE `product` (
  `PRODUCT_ID` bigint(20) unsigned NOT NULL auto_increment,
  `BRAND_ID` int(10) unsigned default NULL,
  `PRODUCT_TYPE_ID` int(10) unsigned default NULL,
  `GROUP_ID` int(10) unsigned default NULL,
  `PRODUCT_NAME` varchar(500) NOT NULL,
  `DEFAULT_EMAIL_ID` varchar(48) default NULL,
  `PRODUCT_STATUS` tinyint(1) NOT NULL,
  `CLIENT_ID` bigint(20) unsigned default NULL,
  `LAST_MODIFIED_BY` varchar(45) NOT NULL,
  `LAST_MODIFIED_DATE` datetime NOT NULL,
  PRIMARY KEY  (`PRODUCT_ID`)
  ) ENGINE=InnoDB;
 

2c. Discard the tablespace, which will delete the newly created .ibd file:

mysql> ALTER TABLE product DISCARD TABLESPACE;

2d. Copy the pre-existing .ibd file to the datadir/test1 folder

2e. Import this tablespace:

mysql> ALTER TABLE product IMPORT TABLESPACE;

This should produce the following error (at least this is most likely). The only way it would not is if MySQL’s current table id matched that of the preexisting ibd table id. In which case, you can now dump your table.

ERROR 1030 (HY000): Got error -1 from storage engine
2f. So, now to check the error log (manually). Look for the following entry:

081010 11:47:40 InnoDB: Error: tablespace id in file
‘.\test1\product.ibd’ is 1193, but in the InnoDB
InnoDB: data dictionary it is 1.
So, now we know the internal table id is at 1, and that of the ibd table is 1193.

3. Clean up working database:

3a. Manually move the ibd file from the $datadir to a safe location (as you will need this file again).

3b. Drop this table.

mysql> DROP TABLE product;

Note this does not re-set the internal table counter.

4. You’ll need to create the number of tables you need to increase the internal table id value.

In this case, you’d create 1191 test InnoDB tables (already at 1, and need to leave 1 for the actual table, so 1193-2=1191). Run below in a loop.

for ($1=1; $i<=1191; $1++) {
  CREATE TABLE t# (id int) ENGINE=InnoDB;
}

I accomplished this via a simple php script. See the bottom of this page (under “Associated Files”) for the exact script.

5. After these are created, go ahead and drop this database and all tables (as they are not needed).

DROP DATABASE test1;

6. Now, re-perform steps 2a through 2e.

mysql> CREATE DATABASE test1;
mysql> USE test1;
mysql> CREATE TABLE `product` ( ... ) ENGINE=InnoDB;
mysql> ALTER TABLE product DISCARD TABLESPACE;

<-- Here is where you copy back the original ibd file to />

mysql> ALTER TABLE product IMPORT TABLESPACE;
Success!

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| product         |
+-----------------+
1 row in set (0.00 sec)

7. Now, dump the table using mysqldump, and then you can import this to any MySQL instance. Note, you must dump this and re-import it, or you’ll run into problems.

However, it’s possible to encounter crashes and/or reports of corruption in the logs.

If this happens, try to force innodb recovery (which is most likely), and then dump the table.

Start by setting innodb_force_recovery=1 (and try 2,3,4,5,6) until the dump works.

For this example table, I had to set innodb_force_recovery=5 before the dump would succeed.

The # in the output file name is the value I had innodb_force_recovery set to when trying to perform the dump:

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump1.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump2.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump3.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump4.txt
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */
* FROM `product`': Lost connection to MySQL server during
query (2013)

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump5.txt

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqladmin -u root -P 3385 shutdown

C:\Program Files\MySQL\mysql-5.0.68\bin>
mysqldump -uroot -P3385 test1 product > product_dump6.txt
In fact, in this case, I could have simply started with 5. This is because the error log stated this:

InnoDB: Error: trying to access update undo rec field 19
in index PRIMARY of table test1/product
InnoDB: but index has only 12 fields
So, I knew there was a problem trying to look at the undo logs, and from the manual, a setting of 5 says this:

“Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed”

However, it’s best to start at 1 and work your way forward so as to prevent as much data loss as possible.

Method #2 - Hex Edit .ibd file

First of all, you’ll need to backup everything (ibdata files, ib_logfile(s), data). I’d also perform a mysqldump of everything you currently have, just so you have a mysqldump of it in the event that you need it.

Also, very important, be sure to make a copy of the .ibd file for the specific table in question.

Lastly, get a copy of the CREATE TABLE statement that will recreate this table.

Then, you’ll follow the steps #1-5 (but do not perform step #6 yet) outlined on the following page:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

Let me post them here for completeness, however:

Use mysqldump to dump all your InnoDB tables.
Stop the server.
Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
Remove any .frm files for InnoDB tables.
Configure a new tablespace.
Restart the server.
Import the dump files.
At this point, MySQL should be running fine with an empty slate (and should have just re-created your new ibdata and log files).

Now, you’ll want to recreate the table (just using the CREATE TABLE output from above), and its database to hold it.

Then, you’ll basically be following the steps #1-3 outlined on the following page:

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

1. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name DISCARD TABLESPACE;
Caution: This statement deletes the current .ibd file.

2. Put the backup .ibd file back in the proper database directory (the one that you copied above).

3. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name IMPORT TABLESPACE;
Everything should go smoothly until step #3 (above). More than likely, this will produce an error like the following on your console:

"Got error -1 from storage engine"
Now, if you look in the error log, you’ll see something like:

"InnoDB: Error: tablespace id in file '.\test\t2.ibd' is 2,
but in the InnoDB data dictionary it is 1."
It would not produce the above error and would work fine if the existing table already had a tablespace id of 1. However, this is unlikely.

So, assuming you see the above errors, then you can modify the tablespace id actual ibd file using a hex editor. I would do this on a different copy of the ibd file (other than the original, just in case).

Note that I used “Freeware Hex Editor XVI32″ for Windows for this step. Start the program, and then open the .ibd file. You’ll see each byte in it’s own cell. You can then click on a cell, click edit, and then edit that byte. (http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm)

Now, in this file, there are 2 places where this tablespace id is located.

For me, and I assume it should be the same for you, but just look at the values to be sure, I see the tablespace id values listed at position 37 and 41 (positions 25 and 29 in hex). In the actual hex column, if you’re previous tablespace id was 2, then in positions 37 and 41, you’d see 02 and 02.

(Note these positions can change. For instance, I tested on a table with an internal id of 1193. This in hex is 04A9. However, when searching the file, for the first instance of the table id, I found the ‘04′ in position 39 and ‘A9′ in position 40. Then, for the second instance of the table id, the ‘04′ was at position 43 and the ‘A9′ was at position 44. So, you’ll have to convert the table id to hex, and then search for that value, near the beginning of the file.)

Note that this value (02) may vary depending on what your actual tablespace id is.

Then, simply modify both of those fields to 01, and save the file.

Then, re-do the following 3 steps:

1. ALTER TABLE tbl_name DISCARD TABLESPACE;
2. Put the newly saved .ibd file back in the proper database directory
3. ALTER TABLE tbl_name IMPORT TABLESPACE;
This time, step #3 works fine.

It is at this point you should dump/import the data. At least, get a good mysqldump of this table now. You may find that this causes corruption in InnoDB, and you may need to start MySQL using –force-innodb-recovery.

http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html Forcing InnoDB Recovery

Associated Files :: PHP Scripts

Simple PHP script - Used to create a number of InnoDB tables (to increase internal table id counter):

$dbhost = "localhost:3385";
$dbname = "test1";
$dbuser = "root";
$dbpwd  = "";

for ($i = 1033; $i <= 1190; $i++) {
   $dbquery = "CREATE TABLE test1.t" . $i . " (id int) ENGINE=InnoDB";

   echo "" . $dbquery . "";

   mysql_connect($dbhost,$dbuser,$dbpwd) or die(mysql_error());

      $result = mysql_db_query($dbname,$dbquery) or die(mysql_error());

      $j = 0;

      while($row = mysql_fetch_array($result)) {
         $j++;
         echo $row[0];
      }
}

mysql_close();
PHP Internal Table ID Finder - Used to determine the internal Table ID from the binary .ibd file:

/*
Tested with tables from 4.1.23, 5.0.68, 5.1.28, and 6.0.7.
*/

// Set the filename
$filename = "C:\\Users\\Chris\\Desktop\\mysql\\working\\ibds\\z1.ibd";

// Read 2 bytes in at a time
$offset = 2;

// Echo filename and path
echo "filename = $filename

";

// Open the filename - need 'rb' for binary file on Windows
$handle = fopen($filename, "rb");

// Define redundant, local variables for possible later functionality and/or checks
$ibd_id_bin = 0;
$ibd_id_hex = 0;
$ibd_id_dec = 0;
$ibd_id_bin2 = 0;
$ibd_id_hex2 = 0;
$ibd_id_dec2 = 0;

// Find the filesize (note: below command messes up script)
//$filesize = filesize($filename));

// Only loop through first 21 bytes - as table is is in $array[18] and $array[20]
for ($z = 0; $z <= 20; $z++) {

	// Set variable $contents equal to 2 ($offset) bytes of binary data
	$contents = fread($handle, $offset);

	// Convert $contents from binary data to hex data
	$contents2 = bin2hex($contents);

	// Convert $contents2 from hex data to decimal data
	$contents3 = hexdec($contents2);

	// Debug Output
	//echo "contents[$z] = " . $contents . "";
	//echo "contents2[$z] = " . $contents2 . "

";
	//echo "contents3[$z] = " . $contents3 . "

";

	// If position 19, array position [18], then store the values
	if ($z == 18) {
		$ibd_id_bin = $contents;
		$ibd_id_hex = $contents2;
		$ibd_id_dec = $contents3;
	}

	// If position 21, array position [20], then store the values
	if ($z == 20) {
		$ibd_id_bin2 = $contents;
		$ibd_id_hex2 = $contents2;
		$ibd_id_dec2 = $contents3;
	}
}
fclose($handle);

// More Debug output
//echo "

The table id is $ibd_id_dec

";
//echo "

The table id is $ibd_id_dec2

";

// Check to see if both values are equal.  If so, then it's
// most certain this is the correct value.
// If not, then there's a chance the positions are off for
// this table (due to versions, etc.).
if ($ibd_id_dec == $ibd_id_dec2) {
	echo "

The table id is $ibd_id_dec

";
} else {
	echo "The values from positions [18] and [20] did not match,";
             echo "so please enable debug output, and check for proper positions.";
}

原文 http://www.maycode.com/index.php/linux/34-linuxbase/1351-mysql.html

更新與安裝 – Install Apache 2.2.11 or Upgrade on FreeBSD

Apache 2.2.11 主要是更新了個錯誤
步驟如下

1.更新 FreeBSD 的 src 與 ports 到目前最新版

cd /usr/src
make update

就個人經驗而言,最好是將相關的
apache-*, php5-* , pear-* , pecl-* , Smarty , eaccelerator , webmail 等都備份後移除
然後再一步一步安裝更新上去
這樣雖然很麻煩,卻最能避免產生問題後無法處理的情況
PS : 都安裝好了,沒有錯誤訊息, Apache 卻開不起來

2.先備份設定後,然後將相關套件及設定全數移除

pkg_info |sort > /root/pkg.info.txt
tar -zcvf /root/etc.tar.gz /etc
tar -zcvf /root/usr.local.etc.tar.gz /usr/local/etc
tar -zcvf /root/etc.namedb.tar.gz /var/named/etc/namedb
tar -zcvf /root/ports_options.tar.gz /var/db/ports
pkg_delete -f /var/db/pkg/apache*
pkg_delete -f /var/db/pkg/pecl*
pkg_delete -f /var/db/pkg/eaccelerator*
pkg_delete -f /var/db/pkg/smarty*
pkg_delete -f /var/db/pkg/joomla*
pkg_delete -f /var/db/pkg/eGroupware*
pkg_delete -f /var/db/pkg/pear*
pkg_delete -f /var/db/pkg/php5*

3.手動安裝更新 Apache 2.2.11_3


cd /usr/ports/www/apache22
make WITH_MPM=worker WITH_THREADS=yes WITH_STATIC_SUPPORT=yes WITH_APACHE_SUEXEC=yes WITH_ALL_STATIC_MODULES=yes WITHOUT_PROXY=yes WITH_AUTH_MODULES=yes WITH_DAV_MODULES=yes WITH_SSL_MODULES=yes
make install clean 
rehash
或是
cd /usr/ports/www/apache22
make config  
make install clean 
rehash

4.重新編譯 php-5.2.9

cd /usr/ports/lang/php5
make WITH_CLI=yes WITH_CGI=yes WITH_APACHE=yes WITH_MULTIBYTE=yes WITH_FASTCGI=yes WITH_PATHINFO=yes ;make install clean ; rehash
或是
cd /usr/ports/lang/php5
make config ; make install clean ;rehash
cd /usr/ports/lang/php5-extensions
make WITHOUT_X11=yes WITH_BZ2=yes WITH_CURL=yes WITH_FTP=yes WITH_GD=yes WITH_GETTEXT=yes WITH_MBSTRING=yes WITH_MYSQL=yes WITH_ZLIB=yes 
make install clean 
rehash
或是
cd /usr/ports/lang/php5-extensions
make config
make install clean
rehash
cd /usr/ports/databases/php5-mysqli
make install clean
rehash
cd /usr/ports/www/smarty
make install clean 
rehash
cd /usr/ports/www/eaccelerator
make install clean 
rehash
cd /usr/ports/security/pear-Auth_SASL
make install clean 
rehash

若 pear 無法安裝時,請先註解recode

ee /usr/local/etc/php/extensions.ini
;extension=recode.so
pear channel-update pear.php.net ( pear 更新 )

cd /usr/ports/deskutils/egroupware
make install clean
rehash

5. 修改 httpd.conf 設定

如果有在 make config 勾選 THREADS選項,就需要修改舊的 httpd.conf ,
因為之前的 載入模組( Load Module ) 改成新的模組內建 ( Built-in )

ee /usr/local/etc/apache22/httpd.conf
#Load Module xxxx

除了php5之外,全部的載入模組都用註解取消
如果沒有在 apache 的 make config 勾選 THREADS選項
則 httpd.conf 無需修改

6.修改php.ini

ee /usr/local/etc/php.ini
apache 未勾選 THREADS選項
extension_dir = “/usr/local/lib/php/20060613/”
apache 有勾選 THREADS選項
extension_dir = “/usr/local/lib/php/20060613-zts/”
ee /usr/local/etc/php/extensions.ini
;extension=recode.so

不註解的話, PHP 似乎無法正常運作( core dumped )

7. eaccelerator PHP 加速器設定

ee /usr/local/etc/php/extensions.ini
extension=bcmath.so
extension=bz2.so
extension=calendar.so
extension=ctype.so
extension=curl.so
extension=dom.so
extension=exif.so
extension=fileinfo.so
extension=filter.so
extension=ftp.so
extension=gd.so
extension=gettext.so
extension=gmp.so
extension=hash.so
extension=iconv.so
extension=imap.so
extension=json.so
extension=ldap.so
extension=mbstring.so
extension=mcrypt.so
extension=mhash.so
extension=ming.so
extension=mssql.so
extension=mysql.so
;extension=mysqli.so
extension=odbc.so
extension=openssl.so
extension=pcntl.so
extension=pcre.so
extension=pdf.so
extension=pdo.so
extension=pdo_sqlite.so
extension=pgsql.so
extension=posix.so
;extension=pspell.so
extension=radius.so
;extension=recode.so
extension=readline.so
extension=session.so
extension=simplexml.so
extension=snmp.so
extension=soap.so
extension=sockets.so
extension=spl.so
extension=sqlite.so
extension=tidy.so
extension=tokenizer.so
extension=wddx.so
extension=xml.so
extension=xmlreader.so
extension=xmlrpc.so
extension=xmlwriter.so
extension=xsl.so
extension=yaz.so
extension=zip.so
extension=zlib.so
;加入下列設定 
extension=eaccelerator.so
ee /usr/local/etc/php.ini
;加入下列設定
[eaccelerator]
zend_extension = “/usr/local/lib/php/20060613-zts/eaccelerator.so”
eaccelerator.shm_size = “128″
eaccelerator.cache_dir = “/tmp/eaccelerator”
eaccelerator.enable = “1″
eaccelerator.optimizer = “1″
eaccelerator.check_mtime = “1″
eaccelerator.debug = “0″
eaccelerator.log_file = “/var/log/httpd/eaccelerator.log”
eaccelerator.filter = “”
eaccelerator.shm_max = “0″
eaccelerator.shm_ttl = “0″
eaccelerator.shm_prune_period = “0″
eaccelerator.shm_only = “0″
eaccelerator.compress = “1″
eaccelerator.compress_level = “9″
eaccelerator.keys = “shm”
eaccelerator.sessions = “shm”
eaccelerator.content = “shm”

8.測試設定檔是否正確

/usr/local/sbin/apachectl configtest

9.重新起動 Apache

/usr/local/etc/rc.d/apache22 start

注意有無出現錯誤訊息
lynx http://localhost/
原文 http://gary1974tw.pixnet.net/blog/post/26613606

windows2003不能上网更新及注册码更换问题解决

先打开注册表,在
HKEY_LOCAL_MACHINE\Software\Microsoft\WindowsNT\CurrentVersion\WPAEvents

随便修改一个字,并保存退出regedit。
然后到c:\windows\system32\oobe下
运行msoobe /a 打开激活程序。

选择电话,

更换密钥为

JCGMJ-TC669-KCBG7-HB8X2-FXG7M

JK6JC-P7P7H-4HRFC-3XM7P-G33HM

然后关掉窗口不要管那个什么四个步骤

update即可
然后重启。
点击“Windows Update”,连接到更新网站,试一下是否可以在线升级和更新了。

以上方法就可以更换cd key了。。注册码写错了的可以改。。。