分类: LINUX
2007-12-12 19:40:20
/etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#default-character-set=utf8 #指定所有客户端默认的字符集为utf8,由于一些系统在原来的生产环境里面是默认采用latin1字符集的,这些系统若重新安装MySQL,字符集需要与原来的保持一致。
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir =/usr/local/mysql/
datadir =/usr/local/mysql/var/
skip-locking
default-character-set=utf8 #指定所有客户端默认的字符集为utf8,由于一些系统在原来的生产环境里面是默认采用latin1字符集的,对这些系统如果灾难发生,重新安装MySQL,字符集需要与原来的保持一致。若不指定#default-character-set,默认采用latin1。
#---------- Global cache and buffer configuration-------------------
#cache for index
key_buffer =
用mysql > show status like 'Key%'; #查看Key_blocks_unused的Value值,如果Value很小,就要增加key_buffer了。
#cache for table
table_cache = 256 # 开启表缓存,加快访问和写入表文件的速度。一般设为32~256即可
#cache for thread
thread_cache_size = 64 # 开启线程缓存,有利于加快应用程序连接mysqld的速度,一般设为32~128即可
#---------Thread specific cache and buffer configuration------------
sort_buffer_size =
read_buffer_size =
read_rnd_buffer_size =
myisam_sort_buffer_size =
#Connetion pool cache
net_buffer_length = 16K
#设置最大连接数
max_connections=300
max_allowed_packet =
#max_allowed_packet太小,会引起数据同步失败。CETS数据库的max_allowed_packet太小了,在客户端浏览器常#常会显示MySQL have gone away 这类的提示,需要重新登录。otrs 的统一设置了max_allowed_packet =
#-------------Query cache configuration-----------------------------
query_cache_size =
#-------------MySQL Log configuration-------------------------------
#--Error log
#log-error = /usr/local/mysql/var/mysql.err
#--The General Query Log
#log = /usr/local/mysql/var/mysql.log
#--The slow query log
log_slow_queries = /usr/local/mysql/var/mysql-slow.log # 慢查询日志
long_query_time = 3 # 设定慢查询的时间值
#log_queries_not_using_indexes # 记录不使用索引的查询,一般情况下不要把这个选项打开,以免日志暴涨
# Bin Log Size
binlog_cache_size = 1000000 # 二进制日志文件缓存,默认没有,把它设置为
max_binlog_size = 100000000 # 二进制日志文件的最大值,设置为
#lower_case_table_names=0 # 控制数据库表名、文件名大小写选项,默认设置为0;若设置为1,是以小写的形#式保存。
tmp_table_size =
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 2
#skip-networking
#skip-name-resolve
#local-infile=0
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin # 也可以用绝对路径指定
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var/ # 如果innodb_data_home_dir = 为空,可以为表空间文件指定绝对路径
#innodb_data_file_path = ibdata1:
# 多个表空间文件的情形,配置如下:
#innodb_data_home_dir =
#innodb_data_file_path=/ibdata/ibdata1:
#设置表空间最大尺寸:
# innodb_data_file_path=ibdata1:
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size =
#innodb_additional_mem_pool_size =
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M #在导入数据库后,不能更改此参数,更改此参数为不同的值会引起MySQL无法启#动。正确操作步骤:在导入数据库之前,先调节好此参数。
#innodb_log_buffer_size =
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
1. 服务器缓存优化
需要随数据库的变化做适当调整的缓存变量:
key_buffer
query_cache_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
myisam_sort_buffer_size
tmp_table_size
innodb_buffer_pool_size
2. 安全优化
4.1) 帐号安全:查看当前账号及其权限
mysql>use mysql;
mysql>select user,host,password from user;
4.2) 重新命名root用户,防范暴力破解密码攻击
mysql>UPDATE user SET user='Creasian007' WHERE user='root';
4.3) 设置root密码
mysql>UPDATE user SET password=PASSWORD('123456') WHERE user='root';
4.4) 增加特定帐号
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 具体数据库.* TO '用户名'@'主机名' IDENTIFIED BY '具体密码';
4.5) 例如:mysql > GRANT all privileges ON otrs.* TO ‘creasian’@‘192.168.10.101’ IDENTIFIED BY ‘250536’;删除匿号账户
mysql>DELETE FROM user WHERE user='';
mysql>FLUSH PRIVILEGES;
4.6) 删除test数据库
mysql>drop database test;
4.7) 更改配置文件权限
shell>chmod 600 /etc/my.cnf