mysql 5.6安装优化_mysql5.6安装优化实例

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

log-bin = /data/mysql/bin/mysqlbinlog

server-id = 1

log-bin-trust-function-creators = 1

#binlog-do-db = ProjectM

#binlog-ignore-db = mysql

server-id = 1001

#binlog-do-db = ProjectM

#binlog-ignore-db = mysql

binlog_format = "MIXED"

bind-address = 0.0.0.0

port = 3306

tadir = /data/mysql

basedir = /usr/local/mysql

socket = /tmp/mysql.sock

default-storage-engine = INNODB

character-set-server = utf8

back_log = 500

slow-query-log = on

slow-query-log-file = /data/mysql/centos-slow.log

long_query_time = 0.5

max_connections = 512

max_connect_errors = 102400

max_allowed_packet = 16M

query_cache_type = 1

query_cache_size = 32M

query_cache_limit = 1M

max_heap_table_size = 64M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 100

thread_concurrency = 8

thread_stack = 192K

ft_min_word_len = 4

tmp_table_size = 64M

binlog_cache_size = 1M

long_query_time = 100

key_buffer_size = 32M

myisam_repair_threads= 1

myisam_recover

wait_timeout = 28800

interactive_timeout = 28800

skip-name-resolve

sql-mode="NO_ENGINE_SUBSTITUTION"

transaction_isolation = REPEATABLE-READ

innodb_buffer_pool_size = 1G

innodb_lock_wait_timeout = 120

"/etc/my.cnf" 82L, 2304C written

[root@node1 data]# service mysql restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.. SUCCESS!

[root@node1 data]# vim /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

log-bin = /data/mysql/bin/mysqlbinlog

server-id = 1001

#binlog-do-db = ProjectM

#binlog-ignore-db = mysql

binlog_format = "MIXED"

bind-address = 0.0.0.0

port = 3306

datadir = /data/mysql

basedir = /usr/local/mysql

socket = /tmp/mysql.sock

default-storage-engine = INNODB

character-set-server = utf8

back_log = 500

slow-query-log = on

slow-query-log-file = /data/mysql/centos-slow.log

long_query_time = 0.5

max_connections = 512

max_connect_errors = 102400

max_allowed_packet = 16M

query_cache_type = 1

query_cache_size = 32M

query_cache_limit = 1M

max_heap_table_size = 64M

sort_buffer_size = 8M

join_buffer_size = 8M

thread_cache_size = 100

thread_concurrency = 8

thread_stack = 192K

ft_min_word_len = 4

tmp_table_size = 64M

binlog_cache_size = 1M

long_query_time = 100

key_buffer_size = 32M

myisam_repair_threads= 1

myisam_recover

wait_timeout = 28800

interactive_timeout = 28800

skip-name-resolve

sql-mode="NO_ENGINE_SUBSTITUTION"

transaction_isolation = REPEATABLE-READ

innodb_buffer_pool_size = 1G

innodb_lock_wait_timeout = 120

innodb_thread_concurrency = 16

innodb_file_io_threads = 4

innodb_mirrored_log_groups = 1

innodb_flush_method = O_DIRECT

innodb_max_dirty_pages_pct = 90

innodb_log_file_size = 256M

innodb_log_buffer_size = 8M

innodb_log_files_in_group = 4

innodb_flush_log_at_trx_commit = 0

innodb_additional_mem_pool_size = 16M

innodb_data_file_path = ibdata1:10M:autoextend:max:400M

innodb_file_per_table = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

pager = more

no-auto-rehash

prompt = '[\u@\h] (\d) \R:\m> '

[isamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 8M

write_buffer= 8M

[myisamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 8M

write_buffer= 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit= 65535

user = mysql

log-error = /data/mysql/data/centos.err

pid-file = /data/mysql/data/centos.pid

有时候需要同时连接到多台服务器的，并且操作不同的 mysql，由于 mysql 命令行默认没有用户或主机的提示符，这样容易误操作，所以可以在 /etc/my.cnf 文件中添加以下配置来显示 mysql 的提示符：

[mysql]

prompt="\\u@\\h : \\d \\r:\\m:\\s>"