The whole process analysis _mysql of compiling Mysql and installing the basic configuration under Linux system

Installation dependencies:

Centos:

# yum-y Install wget gcc-c++ cmake make Bison ncurses-devel perl unzip

Ubuntu: (use Ubuntu below a lot of need root permission, please note)

Copy Code code as follows:

# sudo apt-get install-y g++ gcc make libpcre3 zlib1g libbz2-dev automake cmake perl Libncurses5-dev Bison

Add MySQL System user

# groupadd MySQL
# useradd-r-G MySQL MySQL

Set up the relevant catalogue in advance.

# mkdir/data/logs/mysql
# Mkdir/data/mysql

Download MySQL source code:

# wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.12.tar.gz

Configuration

# cmake \

-dcmake_install_prefix=/usr/local/server/mysql-5.6.12 \
-dmysql_datadir=/data/mysql \
-DMYSQL_UNIX_ADDR= /tmp/mysql.sock \
-dmysql_user=mysql \
-ddefault_charset=utf8 \
-default_collation=utf8_general_ci \
-dwith_innobase_storage_engine=1 \
-denable_downloads=1

Configuration explanation:

-dcmake_install_prefix=/usr/local/server/mysql-5.6.12 Set up installation directory
-dmysql_datadir=/data/mysql set up Database storage directory
-dmysql_unix_addr=/tmp/mysql.sock set up UNIX sockets directory
-dmysql_user=mysql Settings Run user
-ddefault_charset=utf8 Set the default character set, default Latin1
-default_collation=utf8_general_ci Set default proofing rules, default latin1_general_ci
-dwith_innobase_storage_engine=1 Add InnoDB engine support
-denable_downloads=1 automatically downloads optional files, such as downloading Google's Test kit automatically
-dmysql_tcp_port=3306 set the server listening port, default 3306
-dsysconfdir=/data/etc set MY.CNF directory, default to installation directory

More parameter execution # CMake. -LH or check the official instructions

Note:

Occurs during execution:

CMake Error:problem with Tar_extract_all (): Invalid argument CMake Error:problem extracting
tar:/usr/local/src/mys Ql-5.6.12/source_downloads/gmock-1.6.0.zip

Workaround:
CD MySQL directory below will find a source_downloads directory, you need to extract unzip gmock-1.6.0.zip, and then rerun the configuration process. Of course you can also remove-denable_downloads=1 this option, do not compile Google's test package is not a problem, but some previous versions of the problem will not compile.

Compiling the installation

# Make && make install

Create linked files for later use and upgrades:

# ln-s/usr/local/server/mysql-5.6.12/usr/local/server/mysql

To set permissions:

# chown-r Mysql:mysql/usr/local/server/mysql
# chown-r mysql:mysql/usr/local/server/mysql-5.6.12
# chown-r m Ysql:mysql/data/mysql
# Chown-r Mysql:mysql/data/logs/mysql

Initializing the database

Copy Code code as follows:

#/usr/local/server/mysql/scripts/mysql_install_db--user=mysql--datadir=/data/mysql--basedir=/usr/local/server/ MySQL--collation-server=utf8_general_ci

The following warning appears, and the following configuration my.cnf resolves the problem
For more information, see the TIMESTAMP changes in MySQL 5.6.

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (the documentation for more details).
To configure a startup project:

# Cp/usr/local/server/mysql/support-files/mysql.server/etc/init.d/mysql

Modify the installation directory my.cnf, some of the parameters need to be modified in combination with the actual situation

[Mysqld]
 
DataDir =/data/mysql
socket =/tmp/mysql.sock
pid-file =/data/logs/mysql/mysql.pid
user = MySQL
port = 3306
default_storage_engine = InnoDB
 
# InnoDB
#innodb_buffer_pool_size = 128M
#innodb_log_file_ Size = 48M
innodb_file_per_table = 1
innodb_flush_method = o_direct
 
# MyISAM
#key_buffer_size = 48M
 
# character-set
character-set-server=utf8
collation-server=utf8_general_ci
 
# name-resolve
Skip-host-cache
skip-name-resolve
 
# LOG
log_error =/data/logs/mysql/mysql-error.log
Long_query_time = 1
slow-query-log
slow_query_log_file =/data/logs/mysql/mysql-slow.log
 
# Others
explicit_defaults_for_timestamp=true
#max_connections =
Open_files_limit = 65535
Sql_mode =no_engine_substitution,strict_trans_tables
 
[client]
socket =/tmp/mysql.sock
port = 3306
Note:

Commented out part of the statement, need to combine the business and hardware resources to modify, so first placeholder, use the default value can be. No spelling errors can occur in the my.cnf file, otherwise there will be a lot of unexpected errors to start, see: Mysql-error.log files if they fail to start. Improper permissions settings can also cause startup failures.

Before starting, confirm the permissions again:

It's very likely that my.cnf right now is not MySQL, check if/etc/my.cnf exists, delete!!! If it exists

# Chown-r mysql:mysql/usr/local/server/mysql-5.6.12

Start MySQL:

# service MySQL Start

Adding environment variables

#echo ' Export path= $PATH:/usr/local/server/mysql/bin ' >>/etc/profile

Security settings (set initial password, remove anonymous user, remove test database):

#/usr/local/server/mysql/bin/mysql_secure_installation

Add power-on Boot

Centos:

# chkconfig MySQL on

Ubuntu:

# sudo update-rc.d mysql defaults

Configuration notes:

MySQL has a large number of configuration options that can be modified, usually by simply configuring the basic items correctly, and spending more time on schema optimization, indexing, and query design. Once you have properly configured MySQL's basic configuration items, the benefits of trying to modify other configuration options are usually small.
An overly optimized server can cause a lot of potential risks, prone to frequent crashes, and slow running.
MySQL most of the default configuration options are already the best configuration, so it is best not to make too many changes, the parameters set above for most people already enough.
There is no permanent configuration, with the data in the database, the number of users changes, after a period of time, you can selectively adjust the configuration of the database.
Basic configuration options Detailed:

Socket =/tmp/mysql.sock
pid_file =/data/logs/mysql/mysql.pid

Sockets and Pid_file files, if not specified, are placed in the default compilation location and may cause errors in different versions of MySQL

Default_storage_engine = InnoDB

Set the default storage engine, and it is best to explicitly configure the storage engine when creating tables

Innodb_buffer_pool_size = 2G
innodb_log_file_size = 128M
innodb_file_per_table = 1
innodb_flush_method = 0 _direct

The most important two options for InnoDB are the buffer pool size
innodb_buffer_pool_size and log file size innodb_log_file_size, default values are generally too small.

If most of the tables are InnoDB tables, the INNODB buffer pool may require more memory than anything else. InnoDB does not just cache indexes: It also caches data, Adaptive HA index, insert buffer (insert buffer), locks, and other internal data structures. InnoDB also uses a buffer pool to help delay writing, merging multiple writes, and sequentially writing back. In short, InnoDB relies heavily on the buffer pool and must allocate enough memory.

Note:
the larger the buffer pool, the longer it takes to preheat and close.

InnoDB uses logs to reduce the cost of transaction submissions. Because the log has logged transactions, it is not necessary to flush the buffer pool to disk on each transaction commit. InnoDB uses logs to turn random I/O into sequential I/O. Once the log is securely written to disk, the transaction is persisted. If the power is off, InnoDB can replay the log and restore the committed transaction.
The overall log size is controlled by the product of two parameters of Innodb_log_file_size and Innodb_log_file_in_group. Typically, you need to set up hundreds of MB or even GB. As a rule of thumb, the general log file size should be sufficient to accommodate the active content of the server for an hour.

Buffer pool Size Setting method:
start from server total memory
Minus the memory consumed by the operating system and other services (if there are other services)
Subtracting MySQL itself requires memory, such as allocating some buffering for each query operation.
Subtracting enough memory to allow the operating system to cache InnoDB log files is at least enough to cache the most recent frequently accessed portions. Minus some of the last parts of the binary log that can be cached to prevent replication from causing delays, the standby may read the old binary log files on the main library, causing pressure on the main library memory.
Less memory needed for other MySQL buffering and caching, such as MyISAM key caching (key_buffer_size), or query caching (queries cache)
Punishable by 105%, down to a reasonable value.

Note: Settings do not need to be precise, and are rather cautious rather than set too large, if setting less than 20% may only have a small impact, but if a large 20% can cause a very serious problem: Memory swap, disk jitter, memory exhaustion and panic.

#MyISAM
key_buffer_size = 128M

MyISAM itself caches only indexes and does not cache data (dependent on operating system cache data). If most of the tables are MyISAM, the larger key_buffer_size should be allocated.
Before allocating much memory, first understand, MyISAM occupy a lot of space, certainly do not need to allocate than need to cache the index data also large memory (sometimes for data volume amplification reserved Some, will set relatively large).
There are two methods for calculating the index size:
Using SQL statements, query the index_length fields of the Information--schema table and add them together.

SELECT SUM (index_length) from INFORMATION_SCHEMA. TABLES
WHERE engine= ' MYISAM ';

Unix system used, the following command is used

# Du-sch ' find/mysqldatadir-name ' *. Myi "'

How much value does the key cache need to store?
Do not exceed the total size of the index or the total size reserved for the operating system cache. Take both smaller values.

Buffer usage calculations, view the following variables through the show status and variables command:

((key_blocks_unused * key_cache_block_size) * 100/key_buffer_size)

If the server is running for a long period of time, or do not use all the buffer keys, you can reduce the buffer key.

Key buffer hit ratio
In terms of experience, the number of misses per second is more useful, assuming that one disk can produce 100 random reads per second, and 5 cache misses per second will not cause I/O to be busy, and 80 times per second may cause I/O to be busy. Calculation formula:

Key_reads/uptime

Set default character set and encoding

# character-set-server
Character-set-server=utf8
collation-server=utf8_general_ci

The following options configure the MySQL server to be linked using only IP, omitting DNS query time
Reading material: http://dev.mysql.com/doc/refman/5.6/en/host-cache.html

# name-resolve
skip-name-resolve
Skip-host-cache

Set error logs and slow query logs. slow query greater than 1s

# LOG
log_error =/data/logs/mysql/mysql-error.log
long_query_time = 1
slow_query_log
slow_ Query_log_file =/data/logs/mysql/mysql-slow.log

# Others
#timestamp默认设置, 5.6.6 does not set up after the error
Explicit_ Defaults_for_timestamp=true

Setting the maximum link for processing at the same time, to ensure that the application explosion is overwhelmed, when the query can not be executed, that open a link does not have any benefit, so being "too many links" error rejection is a quick and costly way to fail. Specific settings need to refer to the expected maximum concurrent number and native configuration.

Max_connections = 500

Table_open_cache is known as Table_cache in MySQL 5.1.2 and earlier.
Table_open_cache should be set large enough to avoid always reopening and parsing table definitions. If the value of open_tables varies significantly per second, then Table_open_cache may be set to a smaller size. This value is set to start at 10 times times the max_connections, but in most cases it is not more than 10000.

Table_open_cache = 5000

If there is a constant error link in the period of time, such as: Permission error, application configuration error. When the client reaches the Max_connect_errors set number, it is blacklisted and cannot be connected until the host cache is refreshed again. (How to operate?) ) can effectively prevent the violence to crack the password.

max_connect_errors = 2000

On a typical Linux system we set the Open_files_limit as large as possible. Open file handles are less expensive in modern operating systems. If this parameter is not set large enough, there will be a classic number 24th error: "Too many open files".

Open_files_limit = 65535