Topic: MySQL configuration (Netease)

The hompy project uses two data servers with 8 GB and 6 GB Master/Slave databases respectively.
At the beginning, I always went down the machine. I used my-huge as the basic configuration many times,
Later, I borrowed the configuration of mail.sanook.com and made a break, which is still unsatisfactory.
I tried my. CNF, a subitem of Netease (163.com). I didn't go down for a few months. I tried it first.
Note: Netease uses 2 GB memory.

Share:
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1g-2g where the system runs mainly
# MySQL.
#
# You can copy this file
#/Etc/My. CNF to set global options,
# Mysql-data-DIR/My. CNF to set server-specific options (in this
# Installation this directory is/var/lib/MySQL) or
#~ /. My. CNF to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# With the "-- Help" option.

# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306
Socket =/var/lib/MySQL. Sock

# Here follows entries for some specific programs

# The MySQL Server
[Mysqld]

Port = 3306
Socket =/var/lib/MySQL. Sock
Skip-locking
Skip-InnoDB
Skip-bdb

# Back_logs = 100
Back_log = 200

# Key_buffer = 500 m
Key_buffer = 512 m

# Key_buffer = 384 m
# Max_allowed_packet = 2 m
# Max_allowed_packet = 1 m
Max_allowed_packet = 4 m

# Table_cache = 512
Table_cache = 1024

# Sort_buffer_size = 32 m
# Sort_buffer_size = 2 m
Sort_buffer_size = 16 m

# Read_buffer_size = 8 m
# Read_buffer_size = 2 m
Read_buffer_size = 16 m

#Read_rnd_buffer_size= 16 m
#Read_rnd_buffer_size= 8 m
Read_rnd_buffer_size= 32 m

# Myisam_sort_buffer_size = 256 m
Myisam_sort_buffer_size = 64 m
# Thread_cache = 80
Thread_cache = 8
# Query_cache_size = 64 m
# Query_cache_size = 32 m
Query_cache_size = 16 m

Log-Slow-queries =/var/log/slowlog. Log
Long_query_time = 2
# Try Number of CPU's * 2 for thread_concurrency
Thread_concurrency = 4
Default-character-set = utf8
### MySQL Tuning
# Set-variable = max_user_connections = 600
Set-variable = max_user_connections = 1000

Set-variable = max_connections = 1000

# Set-variable = table_cache = 1200
# Set-variable = max_allowed_packet = 1 m

# Set-variable = max_connect_errorrs = 999999
# Set-variable = max_connect_errors = 20
Set-variable = max_connect_errorrs = 1000

Set-variable = wait_timeout = 10
# Set-variable = interactive_timeout = 300
Set-variable = interactive_timeout = 120

# Set-variable = tmp_table_size = 2 m
# Set-variable = join_buffer_size = 2 m
###

# Don't listen on a TCP/IP Port at all. This can be a security enhancement,
# If all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via UNIX sockets or named pipes.
# Note that using this option without enabling Named Pipes on Windows
# (Via the "enable-named-pipe" option) will render mysqld useless!
#
# Skip-networking

# Replication master server (default)
# Binary logging is required for replication

# Log-bin =/var/log/MySQL/mysql-bin.log
# BINLOG-do-DB = exampledb

# 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

# Replication slave (comment out Master section to use this)
#
# To configure this host as a replication slave, you can choose
# Two methods:
#
#1) use the change master to command (fully described in our manual )-
# The syntax is:
#
# Change master to master_host = # Master_user = <user>, master_password = <password>;
#
# Where you replace # <Port> by the master's port number (3306 by default ).
#
# Example:
#
# Change master to master_host = '192. 564.12.1 ', master_port = 125,
# Master_user = 'job', master_password = 'secret ';
#
# Or
#
#2) set the variables below. However, in case you choose this method, then
# Start replication for the first time (even unsuccessfully, for example
# If you mistyped the password in master-password and the slave fails
# Connect), the slave will create a master.info file, and any later
# Change in this file to the variables 'values below will be ignored and
# Overridden by the content of the master.info file, unless you shutdown
# The slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (Commented) and instead use change master to (see above)
#
# Required unique ID between 2 and 2 ^ 32-1
# (And different from the master)
# Defaults to 2 if master-host is set
# But will not function as a slave if omitted
# Server-id = 2
#
# The replication master for this slave-required
# Master-host = #
# The username the slave will use for authentication when connecting
# To the master-required
# Master-user = <username>
#
# The password the slave will authenticate with when connecting
# The Master-required
# Master-Password = <password>
#
# The port the master is listening on.
# Optional-defaults to 3306
# Master-Port = <port>
#
# Binary logging-not required for slaves, but recommended
Log-bin
BINLOG-do-DB = hompy_mweb

# Point the following paths to different dedicated Disks
# Tmpdir =/tmp/
Tmpdir =/var/mysqltmp
# Log-update =/path-to-dedicated-directory/hostname

# Uncomment the following if you are using bdb tables
# Bdb_cache_size = 384 m
# Bdb_max_locked = 100000

# Uncomment the following if you are using InnoDB tables
# Innodb_data_home_dir =/var/lib/MySQL/
# Innodb_data_file_path = ibdata1: 2000 m; ibdata2: 10 m: autoextend
# Innodb_log_group_home_dir =/var/lib/MySQL/
# Innodb_log_arch_dir =/var/lib/MySQL/
# You can set .. _ buffer_pool_size up to 50-80%
# Of Ram but beware of setting memory usage too high
# Innodb_buffer_pool_size = 384 m
# Innodb_additional_mem_pool_size = 20 m
# Set .. _ log_file_size to 25% of buffer pool size
# Innodb_log_file_size = 100 m
# Innodb_log_buffer_size = 8 m
# Innodb_flush_log_at_trx_commit = 1
# Innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16 m

[MySQL]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-Updates
Default-character-set = utf8
[Isamchk]
Key_buffer = 256 m
Sort_buffer_size = 256 m
Read_buffer = 2 m
Write_buffer = 2 m

[Isamchk]
Key_buffer = 256 m
Sort_buffer_size = 256 m
Read_buffer = 2 m
Write_buffer = 2 m

[Myisamchk]
Key_buffer = 256 m
Sort_buffer_size = 256 m
Read_buffer = 2 m
Write_buffer = 2 m

[Mysqlhotcopy]
Interactive-Timeout