mysql完全定制

mysql完全定制. 一般是用手动编译,但是看了mysql文档后觉得手动编译即麻烦又浪费时间,基本上我都是参考官方的编译参数 手动编译参数: 代码: CHOST="i686-pc-linux-gnu" \ CFLAGS="-march=i686 -O3 -pipe -fomit-frame-pointer" \ CXX=gcc \ CXXFLAGS="-march=i686 -O3 -pipe -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \ ./configure \ --prefix=/usr/local/mysql4.0.16 \ --enable-assembler \ --without-debug \ --with-extra-charsets=complex \ --with-mysqld-ldflags=-all-static 官方编译参数: 代码: CFLAGS="-O2 -mcpu=pentiumpro" CXX=gcc CXXFLAGS="-O2 -mcpu=pentiumpro -felide-constructors" \ ./configure --prefix=/usr/local/mysql \ --with-extra-charsets=complex \ --enable-thread-safe-client \ --enable-local-infile \ --enable-assembler \ --disable-shared \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static 自己编译的参数和mysql官方的差不多.而且在大量的商业生产应用上,用bin版本不仅方便而且省时间. ok.我们先来看看我们解压bin版本后应该如何定制我们的mysql 我比较习惯把mysql按版本号安装到/usr/local下, 所以比较新的mysql4.0.16的解压目录就是 /usr/local/mysql4.0.16 因为我的内存比较大2G,而且多个cpu所以,我用my-large.cnf,你们可以根据自己的情况选择不同的配置文件. 在mysql的support-files中. 我们来看看配置文件都要加什么定制的东西. 代码: [mysqld] log-error #启用错误日值,如果没有定义路径,默认在mysql的db目录中. max_connections = 1500 #最大连接数,根据官方文档,bin版本完全可以开到1500以上 max_connect_errors = 200 #最大连接错误数. skip-bdb #取消bdb的支持(省一些内存,如果你不用bdb的话) skip-innodb #取消innodb支持(省一些内存,如果你不用bdb的话) user = mysql #定义mysqld的启动用户 basedir = /usr/local/mysql4.0.16 #定义mysql的目录位置/usr/local/mysql4.0.16 pid-file = /usr/local/mysql4.0.16/data/mysql.pid #定义pid文件 datadir = /wwwroot/mysql_data #定义数据库文件的位置. 一般我都习惯把数据文件同mysql的目录分开.这样比较利于mysql的升级和管理. bin版本的启动..我们需要自己写一个脚本来启动bin版本. mysql提供了一个mysqld_safe的脚本来启动mysql,但是在bin版本中不怎么好用.因为默认编译的路径是/usr/local/mysql 所以当你把mysql解压到其他地方的时候就不能启动mysql的.实际上mysqld_safe所做的只是带参数启动mysql而已,我们可以绕过 直接写启动脚本. 实际上所有mysqld的参数都可以在my.cnf的[mysqld]字段添加就像上面的例子一样. 具体参数在: 同时一些变量也一样,(max_connections 等)具体在: 在把变量在[msyqld]字段的时候注意一下. mysql4.x max_connections = 1000 max_connect_errors = 500 mysql3.x版本用(4.0也能认识) set-variable = max_connections = 1000 set-variable = max_connect_errors = 500

iamok

	08-02-2004, 19:18   #

管理员 注册日期: Sep 2004 帖子: 4,905 Status: Online

另一个脚本 代码: #!/bin/sh mysqldir=/usr/local/mysql4.0.20 ver="mysql 4.0.20 bin version" pid_file=${mysqldir}/data/mysql.pid my_cnf=${mysqldir}/my.cnf pid=`cat ${pid_file} 2>/dev/null` # #just test the pid have process in "ps aux" list # p=`ps aux|grep "$pid"|grep -v "grep"|awk '{print $1}'` start() { if [ ! -z "$pid" ] ; then if [ ! -z "$p" ]; then echo "$ver is already run" else echo "there is pid in pidfiles,but no such pid process running,it will del the pid file,then start mysqld ^_^" rm -rf ${pid_file} sleep 1 ${mysqldir}/bin/mysqld --defaults-file=${my_cnf} 1>/dev/null & if [ $? = 0 ]; then echo "start done" else echo "start fail" fi fi else echo "start $ver" sleep 1 ${mysqldir}/bin/mysqld --defaults-file=${my_cnf} 1>/dev/null & if [ $? = 0 ]; then echo "start done" else echo "start fail" fi fi } stop() { if [ ! -z "$pid" ]; then if [ ! -z "$p" ]; then echo "stop mysql..." sleep 1 kill "$pid" sleep 2 if [ $? = 0 ]; then sleep 1 echo "done" else echo "stop fail" fi else echo "there is pid in pidfiles,but no such pid process running,stop fail,user manual stop mysqld" fi else echo "no pid in pid file ,user manual stop mysqld" fi } case $1 in start) start ;; stop) stop ;; restart) stop sleep 1 start ;; *) echo "usage: $0 {start|stop|restart}" ;; esac 这个教本是把my.cnf放在mysql的安装目录中,而非统一的/etc/my.cnf.具体在教本中的defaults-file参数中.这样如果你有多个mysqld,那么每个mysqld都有自己的my.cnf.便于升级和管理.

iamok

	08-26-2005, 20:06   #

管理员 注册日期: Sep 2004 帖子: 4,905 Status: Online	http://dev.mysql.com/doc/mysql/en/s...-variables.html 系统参数

iamok

	10-22-2005, 17:46   #

管理员 注册日期: Sep 2004 帖子: 4,905 Status: Online

代码: [mysqld] replicate-ignore-db=91userdb replicate-wild-ignore-table=91userdb.% replicate-ignore-db=nd91_pay replicate-wild-ignore-table=nd91_pay.% replicate-ignore-db=nd91_webmoney replicate-wild-ignore-table=nd91_webmoney.% replicate-ignore-db=nd91twpay replicate-wild-ignore-table=nd91twpay.% replicate-ignore-db=mysql replicate-wild-ignore-table=mysql.% skip-name-resolve myisam_max_sort_file_size=150G myisam_max_extra_sort_file_size=150G tmpdir = /usr/temp relay-log=/usr/relaylog/relaylog log-bin = /usr/logbin/log-bin #log-slave-updates lower_case_table_names=1 open_files_limit=65535 #log=/usr/local/mysql_date/mysql.log max_binlog_size=200M server-id = 20 #skip-bdb #skip-innodb log-slow-queries long_query_time=1 #tmp_table_size=16M log-error port = 3306 socket = /tmp/mysql.sock basedir = /usr/local/mysql-4.0.27 pid-file = /usr/local/mysql-4.0.27/data/mysql.pid datadir = /ext/mysql_data user=mysql #wait_timeout=30 #interactive_timeout=30 max_connections = 1000 max_connect_errors = 9999999999 skip-locking key_buffer = 2000M max_allowed_packet = 16M table_cache = 3000 sort_buffer_size = 1M read_buffer_size = 1M #read_rnd_buffer= 8M myisam_sort_buffer_size = 128M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8