MySQL参数详解及优化

lfjgh

                       MySQL安装完成之后，需要对MySQL进行配置以及优化，从而使MySQL可以更高效的运行。

  vi /etc/my.cnf    #打开配置文件

    [mysqld]
      datadir=/data/mysqldata             #在此位置读取数据

      socket=/tmp/mysql.sock        #sock 通信设置

      user=mysql                    #使用mysql启动


      symbolic-links=0                #是否支持快捷方式

      log-bin=mysql-bin                 #开启 bin-log 日志

      server-id=1                   #mysql 服务ID

      auto_increment_offset=1            #自增长字段从那个数开始，取值
                                范围：1-65535

      auto_increment_increment=2           #自增长每次递增的量，默认1，取
                                值范围：1-65535

##########在做 主-主同步时需要设置此两项
########## master：auto_increment_offset=1

##########     auto_increment_increment=2

########## master: auto_increment_offset=2
##########     auto_increment_increment=2

##########   将 auto_increment_offset 分别设置为1和2，可以避免两台服务器同时做更新时自增
########## 长字段的值之间发生冲突

      port = 3306

      key_buffer = 384M            #用于索引块的缓冲区大小，增加它可以

                          更好的处理索引（对所有读和多重写）。

                    索引块是缓冲的并且被所有线程共享，大小视内存而定

      table_cache = 512        #为所有线程打开表的数量。增加该值能增加mysql

                       描述符的数量。可以避免频繁打开数据表产生开销

      sort_buffer_size = 2M      #每个线程排序时的一个缓冲区，可加速ORDER BY 或

                    GROUP BY  操作。注意：该分配大小是每个连接独占，

                    如有100个连接，将占用 2 * 100 = 600M

      read_buffer_size = 2M      #查询操作使用的缓冲区，每连接独占

      query_cache_size = 32M      #指定查询结果缓冲区大小

      read_rnd_buffer_size = 8M     #在使用指针排序后，随机读取用

      myisam_sort_buffer_size = 64M   #MyISAM表发生变化时重新排序所需要的缓冲

      thread_concurrency = 8     #最大线程并发线程数，取值为：CPU数量 * 2，如果

                     CPU 支持 H.T 超线程，再 * 2

      thread_cache = 8        #缓存可重用的线程数

      skip-locking            #避免MYSQL的外部锁定，减少出错几率增强稳定性

    [mysqld_safe]            #mysql安全启动配置

      log-error=/var/log/mysqld.log        #日志


      pid-file=/var/run/mysqld/mysqld.pid     #PID文件


      replicate-do-db=all             #允许 slave 同步哪个库


      key_buffer_size               #指定索引缓冲区的大小，它决定索引
                    处理的速度，尤其是索引读的速度。一般为内存的 50%


      show variables lik 'key_buffer_size';


      max_connection = 3000    #每个客户端连接最大的错误允许数量，达到此限制，
                    阻止连接，直到执行“FLUSH HOSTS” 或重启服务



      innodb_buffer_pool_size    #相当于key_buffer_size对于MyISAM 表的作用，使用
                        该参数指定的内存大小来缓冲数据和索引

    ###对于单独的MySQL 服务器来说，可以把该值设置成物理内存的 80%


      basedir = path      #  使用此目录作为根目录（安装目录）

      datadir = path      #  从此目录读取数据文件

      pid-file= path      #  为mysqld程序指定一个存放进程 ID 的文件，仅
                    适用于UNIX/Linux 系统




    [mysqldump]

      max_allowed_packet = 16M



此处附一个真实环境 mysql 配置 my.cnf 内容，可以根据实际情况修改----


#########################################################################################

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

server_id = 10

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mysql/data1

old_passwords = 1

lower_case_table_names = 1

character-set-server = utf8

default-storage-engine = MYISAM

log-bin = bin.log

log-error = error.log

pid-file = mysql.pid

long_query_time = 2

slow_query_log

slow_query_log_file = slow.log

binlog_cache_size = 4M

binlog_format = mixed

max_binlog_cache_size = 16M

max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

back_log = 512

max_allowed_packet = 64M

max_connections = 4096

max_connect_errors = 100

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

sort_buffer_size = 2M

query_cache_size = 64M

table_open_cache = 10000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack = 192K

thread_concurrency = 24

local-infile = 0

skip-show-database

skip-name-resolve

skip-external-locking

connect_timeout = 600

interactive_timeout = 600

wait_timeout = 600

#*** MyISAM

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

concurrent_insert = 2

myisam_recover

#*** INNODB

innodb_buffer_pool_size = 16G

innodb_additional_mem_pool_size = 32M

innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 120

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_thread_concurrency = 16

innodb_open_files = 10000

#innodb_force_recovery = 4

#*** Replication Slave

read-only

#skip-slave-start

relay-log = relay.log

log-slave-updates