2. 然后在不同位置创建各自版本的数据库存放目录
mkdir /data/4020 存放4.0.20版本数据库
mkdir /data/5022 存放5.0.22版本数据库
3. 将各自版本创建的授权表及相关文件复制到各自目录,授权表的创建及执行各自的
scripts/mysql_install_db -u mysql所产生的文件,类似列表如下:
-rw-r----- 1 mysql mysql    25088 Sep 11 02:54 ib_arch_log_0000000000
-rw-r----- 1 mysql mysql 10485760 Sep 11 02:54 ibdata1
-rw-r----- 1 mysql mysql  5242880 Sep 11 02:54 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Sep 11 02:54 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Sep 11 02:54 mysql
drwxr-x--- 2 mysql mysql     4096 Sep 11 02:54 test
这里就不再详细说明.
4. 在各自目录创建MYSQL的启动脚本start_mysql.sh,内容如下:

MYSQL 4.0.20:
#!/bin/sh
rundir=`dirname "$0"`
echo "$rundir"
/usr/local/mysql4022/bin/safe_mysqld --user=mysql --pid-file="$rundir"/mysql.pid --datadir="$rundir" $@ -O max_connections=500 -O max_connect_errors=600000 -O wait_timeout=600 -O key_buffer=32M --port=3301 --socket="$rundir"/mysql.sock &

MYSQL 5.0.22:
#!/bin/bash
rundir=`dirname "$0"`
echo "$rundir"
/usr/local/mysql5022/bin/safe_mysqld --defaults-file="$rundir"/my.cnf --user=mysql --skip-name-resolve --skip-locking --pid-file="$rundir"/mysql.pid --datadir="$rundir" "$@"   -O port=3306 -O back_log=500 -O max_connect_errors=10000 -O max_connections=10000 -O log -slow-queries=slow_query.txt -O key_buffer=256M -O sort_buffer=64M -O table_cache=2048 -O record_buffer=64M -O interactive_timeout=1 20 -O wait_timeout=120 -O max_heap_table_size=64M -O tmp_table_size=128M -O thread_concurrency=8 -O myisam_sort_buffer_size=128M --socket="$rundir"/mysql.sock --skip-innodb &
相关参数根据自己需要调整,不详述.
5.修改各自版本的safe_mysqld脚本,指定ledir参数,各自版本mysql的执行文件的目录位置
ledir=/usr/local/mysql5022/bin

否则就会出现各种因版本冲突引起的错误,最常见的错误是:

070916 01:27:20  mysqld started
070916  1:27:20  Warning: Asked for 196608 thread stack, but got 126976
070916  1:27:20  Fatal error: Can't open privilege tables: File '/usr/local/mysql/share/mysql/charsets/?.conf' not found (Errcode: 2
)
070916  1:27:20  Aborting

070916  1:27:20  /usr/local/mysql/bin/mysqld: Shutdown complete

070916 01:27:20  mysqld ended
该错误就是由于我的mysql运行目录是在'/usr/local/mysql5022/'或'/usr/local/mysql4020/'而非'/usr/local/mysql/'下.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_flush_logs_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

快速重建索引

通常情况下，如果你想改变服务器的全文搜索变量，你需要在表格中重新建立全文索引，以确保你的更新得到映射。这一操作将会花费大量的时间，特别是如果你需要处理很多数据的时候。一种快速的解决方法是使用REPAIR TABLE命令，以下为演示过程：

mysql> REPAIR TABLE content QUICK;

+-----------+--------+----------+----------+

| Table| Op| Msg_type | Msg_text |

+-----------+--------+----------+----------+

| content| repair | status| OK|

+-----------+--------+----------+----------+

1 row in set (0.05 sec)

以CSV格式输出表格数据

MySQL输出文件包含一个全部SQL命令列表。如果你想将输出文件导入到MySQL，这一功能非常实用，但如果目标程序(比如Excel)不能与SQL相互通讯，这一方法将行不通。在这种情况下，可以通过告诉MySQL以CSV格式建立输出文件，这种CSV格式很方便地导入到绝大部分的程序。这里演示了mysqldump的操作过程：

shell> mysqldump -T . --fields-terminated-by=", " mydbmytable

这将在当前目录中生成一个文本文件，包含来自mydb.mytable列表中以逗号为间隔符的记录。

使用方法:将以上代码复制到mysql2excel.pl文件中.

命令行格式: mysql2excel.pl 文件名 " SQL命令行",

例: mysql2excel.pl /root/abc.xls "select * from abc"

1. MySQL服务的安装/配置的通用性；
2. 系统的升级和数据迁移方便性；
3. 备份和系统快速恢复；
4. 数据库应用的设计要点；
5. 一次应用优化实战；

MySQL服务器的规划

=================

为了以后维护，升级备份的方便和数据的安全性，最好将MySQL程序文件和数据分别安装在“不同的硬件”上。

         /   / 


        |    /usr                     <== 操作系统         


        |    /home/mysql              <== mysql主目录，为了方便升级，这只是一个最新版本目录的链接 


硬盘1==>|    /home/mysql-3.23.54/     <== 最新版本的mysql /home/mysql链接到这里


         \   /home/mysql-old/         <== 以前运行的旧版本的mysql





         /   /data/app_1/             <== 应用数据和启动脚本等


硬盘2==>|    /data/app_2/


         \   /data/app_3/

注释：

--pid-file="$rundir"/mysql.pid --socket="$rundir"/mysql.sock --datadir="$rundir"/var

目的都是将相应数据和应用临时文件放在一起；

-O 后面一般是服务器启动全局变量优化参数，有时候需要根据具体应用调整；

--port: 不同的应用使用PORT参数分布到不同的服务上去，一个服务可以提供的连接数一般是MySQL服务的主要瓶颈；

修改不同的服务到不同的端口后，在rc.local文件中加入：

/data/app_1/start_mysql.sh

/data/app_2/start_mysql.sh

/data/app_3/start_mysql.sh

注意：必须写全路径



3 MySQLD的停止脚本：stop_mysql.sh

#!/bin/sh

rundir=`dirname "$0"`

echo "$rundir"

/home/mysql/bin/mysqladmin -u mysql -S"$rundir"/mysql.sock shutdown



使用这个脚本的好处在于：

1 多个服务启动：对于不同服务只需要修改脚本中的--port[=端口号]参数。单个目录下的数据和服务脚本都是可以独立打包的。

2 所有服务相应文件都位于/data/app_1/目录下：比如：mysql.pid mysql.sock，当一台服务器上启动多个服务时，多个服务不会互相影响。但都放到缺省的/tmp/下则有可能被其他应用误删。

3 当硬盘1出问题以后，直接将硬盘2放到一台装好MySQL的服务器上就可以立刻恢复服务（如果放到my.cnf里则还需要备份相应的配置文件）。

服务启动后/data/app_1/下相应的文件和目录分布如下：

/data/app_1/

    start_mysql.sh 服务启动脚本

    stop_mysql.sh 服务停止脚本

    mysql.pid 服务的进程ID

    mysql.sock 服务的SOCK

    var/ 数据区

       mysql/ 用户库

       app_1_db_1/ 应用库

       app_1_db_2/

...

/data/app_2/

...

查看所有的应用进程ID：

cat /data/*/mysql.pid

查看所有数据库的错误日志：

cat /data/*/var/*.err

个人建议：MySQL的主要瓶颈在PORT的连接数上，因此，将表结构优化好以后，相应单个MySQL服务的CPU占用仍然在10％以上，就要考虑将服务拆分到多个PORT上运行了。

服务的备份

==========

尽量使用MySQL DUMP而不是直接备份数据文件，以下是一个按weekday将数据轮循备份的脚本：备份的间隔和周期可以根据备份的需求确定

/home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql db_name | gzip -f>/path/to/backup/db_name.`data +%w`.dump.gz

因此写在CRONTAB中一般是：

15 4 * * * /home/mysql/bin/mysqldump -S/data/app_1/mysql.sock -umysql db_name | gzip -f>/path/to/backup/db_name.`data +\%w`.dump.gz

注意：

1 在crontab中'%'需要转义成'\%'

2 根据日志统计，应用负载最低的时候一般是在早上4-6点



先备份在本地然后传到远程的备份服务器上，或者直接建立一个数据库备份帐号，直接在远程的服务器上备份，远程备份只需要将以上脚本中的-S /path/to/msyql.sock改成-h IP.ADDRESS即可。



数据的恢复和系统的升级

======================

日常维护和数据迁移：在数据盘没有被破坏的情况下

硬盘一般是系统中寿命最低的硬件。而系统（包括操作系统和MySQL应用）的升级和硬件升级，都会遇到数据迁移的问题。

只要数据不变，先装好服务器，然后直接将数据盘（硬盘2）安装上，只需要将启动脚本重新加入到rc.local文件中，系统就算是很好的恢复了。



灾难恢复：数据库数据本身被破坏的情况下

确定破坏的时间点，然后从备份数据中恢复。

应用的设计要点

==============

如果MySQL应用占用的CPU超过10%就应该考虑优化了。

1. 如果这个服务可以被其他非数据库应用代替（比如很多基于数据库的计数器完全可以用WEB日志统计代替）最好将其禁用：
   
   非用数据库不可吗？虽然数据库的确可以简化很多应用的结构设计，但本身也是一个系统资源消耗比较大的应用。在某些情况下文本，DBM比数据库是更好的选择，比如：很多应用如果没有很高的实时统计需求的话，完全可以先记录到文件日志中，定期的导入到数据库中做后续统计分析。如果还是需要记录简单的2维键－值对应结构的话可以使用类似于DBM的HEAP类型表。因为HEAP表全部在内存中存取，效率非常高，但服务器突然断电时有可能出现数据丢失，所以非常适合存储在线用户信息，日志等临时数据。即使需要使用数据库的，应用如果没有太复杂的数据完整性需求的化，完全可以不使用那些支持外键的商业数据库，比如MySQL。只有非常需要完整的商业逻辑和事务完整性的时候才需要Oracle这样的大型数据库。对于高负载应用来说完全可以把日志文件，DBM，MySQL等轻量级方式做前端数据采集格式，然后用Oracle MSSQL DB2 Sybase等做数据库仓库以完成复杂的数据库挖掘分析工作。
   
   有朋友和我说用标准的MyISAM表代替了InnoDB表以后，数据库性能提高了20倍。
   
   
   
   
2. 数据库服务的主要瓶颈：单个服务的连接数
   
   对于一个应用来说，如果数据库表结构的设计能够按照数据库原理的范式来设计的话，并且已经使用了最新版本的MySQL，并且按照比较优化的方式运行了，那么最后的主要瓶颈一般在于单个服务的连接数，即使一个数据库可以支持并发500个连接，最好也不要把应用用到这个地步，因为并发连接数过多数据库服务本身用于调度的线程的开销也会非常大了。所以如果应用允许的话：让一台机器多跑几个MySQL服务分担。将服务均衡的规划到多个MySQL服务端口上：比如app_1 ==> 3301 app_2 ==> 3302...app_9 ==> 3309。一个1G内存的机器跑上10个MySQL是很正常的。让10个MySQLD承担1000个并发连接效率要比让2个MySQLD承担1000个效率高的多。当然，这样也会带来一些应用编程上的复杂度；
   
   
   
   
3. 使用单独的数据库服务器（不要让数据库和前台WEB服务抢内存），MySQL拥有更多的内存就可能能有效的进行结果集的缓存；在前面的启动脚本中有一个-O key_buffer=32M参数就是用于将缺省的8M索引缓存增加到32M（当然对于）
   
   
   
   
4. 应用尽量使用PCONNECT和polling机制，用于节省MySQL服务建立连接的开销，但也会造成MySQL并发链接数过多（每个HTTPD都会对应一个MySQL线程）；
   
   
   
   
5. 表的横向拆分：让最常被访问的10%的数据放在一个小表里，90%的历史数据放在一个归档表里（所谓：快慢表），数据中间通过定期“搬家”和定期删除无效数据来节省，毕竟大部分应用（比如论坛）访问2个月前数据的几率会非常少，而且价值也不是很高。这样对于应用来说总是在一个比较小的结果级中进行数据选择，比较有利于数据的缓存，不要指望MySQL中对单表记录条数在10万级以上还有比较高的效率。而且有时候数据没有必要做那么精确，比如一个快表中查到了某个人发表的文章有60条结果，快表和慢表的比例是1:20，那么就可以简单的估计这个人一共发表了1200篇。Google的搜索结果数也是一样：对于很多上十万的结果数，后面很多的数字都是通过一定的算法估计出来的。
   
   
   
   
6. 数据库字段设计：表的纵向拆分（过渡范化）：将所有的定长字段（char, int等）放在一个表里，所有的变长字段（varchar,text,blob等）放在另外一个表里，2个表之间通过主键关联，这样，定长字段表可以得到很大的优化（这样可以使用HEAP表类型，数据完全在内存中存取），这里也说明另外一个原则，对于我们来说，尽量使用定长字段可以通过空间的损失换取访问效率的提高。在MySQL4中也出现了支持外键和事务的InnoDB类型表，标准的MyISAM格式表和基于HASH结构的HEAP内存表，MySQL之所以支持多种表类型，实际上是针对不同应用提供了不同的优化方式；
   
   
   
   
7. 仔细的检查应用的索引设计：可以在服务启动参数中加入 --log-slow-queries[=file]用于跟踪分析应用瓶颈，对于跟踪服务瓶颈最简单的方法就是用MySQL的status查看MySQL服务的运行统计和show processlist来查看当前服务中正在运行的SQL，如果某个SQL经常出现在PROCESS LIST中，一。有可能被查询的此时非常多，二，里面有影响查询的字段没有索引，三，返回的结果数过多数据库正在排序（SORTING）；所以做一个脚本：比如每2秒运行以下show processlist;把结果输出到文件中，看到底是什么查询在吃CPU。
   
   
   
   
8. 全文检索：如果相应字段没有做全文索引的话，全文检索将是一个非常消耗CPU的功能，因为全文检索是用不上一般数据库的索引的，所以要进行相应字段记录遍历。关于全文索引可以参考一下基于Java的全文索引引擎lucene的介绍。
   
   
   
   
9. 前台应用的记录缓存：比如一个经常使用数据库认证，如果需要有更新用户最后登陆时间的操作，最好记录更新后就把用户放到一个缓存中（设置2个小时后过期），这样如果用户在2个小时内再次使用到登陆，就直接从缓存里认证，避免了过于频繁的数据库操作。
   
   
   
   
10. 查询优先的表应该尽可能为where和order by字句中的字段加上索引，数据库更新插入优先的应用索引越少越好。
    
    

总之：对于任何数据库单表记录超过100万条优化都是比较困难的，关键是要把应用能够转化成数据库比较擅长的数据上限内。也就是把复杂需求简化成比较成熟的解决方案内。

一次优化实战

============

以下例子是对一个论坛应用进行的优化：

1. 用Webalizer代替了原来的通过数据库的统计。
   
   
2. 首先通过TOP命令查看MySQL服务的CPU占用左右80%和内存占用：10M，说明数据库的索引缓存已经用完了，修改启动参数，增加了-O key_buffer=32M，过一段时间等数据库稳定后看的内存占用是否达到上限。最后将缓存一直增加到64M，数据库缓存才基本能充分使用。对于一个数据库应用来说，把内存给数据库比给WEB服务实用的多，因为MySQL查询速度的提高能加快web应用从而节省并发的WEB服务所占用的内存资源。
   
   
3. 用show processlist;统计经常出现的SQL：
   
   
   
   每分钟运行一次show processlist并记录日志：
   
   * * * * * (/home/mysql/bin/mysql -uuser -ppassword < /home/chedong/show_processlist.sql >>  /home/chedong/mysql_processlist.log)
   
   
   
   show_processlist.sql里就一句：
   
   show processlist;
   
   
   
   比如可以从日志中将包含where的字句过滤出来：
   
   grep where mysql_processlist.log
   
   如果发现有死锁，一定要重新审视一下数据库设计了，对于一般情况：查询速度很慢，就将SQL where字句中没有索引的字段加上索引，如果是排序慢就将order by字句中没有索引的字段加上。对于有%like%的查询，考虑以后禁用和使用全文索引加速。
   
   
   
   
4. 还是根据show processlist;看经常有那些数据库被频繁使用，考虑将数据库拆分到其他服务端口上。

MSSQL到MySQL的数据迁移：ACCESS＋MySQL ODBC Driver

在以前的几次数据迁移实践过程中，我发现最简便的数据迁移过程并不是通过专业的数据库迁移工具，也不是MSSQL自身的DTS进行数据迁移（迁移过程中间会有很多表出错误警告），但通过将MSSQL数据库通过ACCESS获取外部数据导入到数据库中，然后用ACCESS的表==>右键==>导出，制定ODBC，通过MySQL的DSN将数据导出。这样迁移大部分数据都会非常顺利，如果导出的表有索引问题，还会出添加索引提示（DTS就不行），然后剩余的工作就是在MySQL中设计字段对应的SQL脚本了。

参考文档：

MySQL的参考：

http://www.mysql.com/documentation/mysql/bychapter/