Linux system self-compiled installation MySQL and basic configuration of the whole process of analysis

Installation dependencies:

Centos:

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

Ubuntu: (There are a lot of root privileges to use Ubuntu, please note)

Copy CodeThe code is 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

-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 setting up the installation directory
-dmysql_datadir=/data/mysql Setting the database storage directory
-dmysql_unix_addr=/tmp/mysql.sock setting up the UNIX socket directory
-dmysql_user=mysql setting up a running user
-ddefault_charset=utf8 Set 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 download optional files, such as automatically download Google's test package
-dmysql_tcp_port=3306 Setting the server listening port, default 3306
-dsysconfdir=/data/etc set MY.CNF directory, default to installation directory

More parameter execution # CMake. -LH or view official notes

Note:

During execution, it appears:

CMake Error:problem with Tar_extract_all (): Invalid argumentcmake Error:problem extracting tar:/usr/local/src/mysql-5. 6.12/source_downloads/gmock-1.6.0.zip

Workaround:
CD MySQL directory below you will find a source_downloads directory, need to decompress unzip Gmock-1.6.0.zip, and then re-execute the above 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 will be unable to compile the problem.

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

Set permissions:

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

Initializing the database

Copy CodeThe code is 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 will resolve this issue
See the TIMESTAMP changes in MySQL 5.6 for details

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

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

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

[mysqld] DataDir =/data/mysqlsocket =/tmp/mysql.sockpid-file =/data/logs/mysql/mysql.piduser = Mysqlport = 3306default _storage_engine = InnoDB # innodb#innodb_buffer_pool_size = 128m#innodb_log_file_size = 48minnodb_file_per_table = 1innodb_flush_method = o_direct # myisam#key_buffer_size = 48M # character-setcharacter-set-server= UTF8COLLATION-SERVER=UTF8_GENERAL_CI # name-resolveskip-host-cacheskip-name-resolve # LOGlog_error =/data/logs/ Mysql/mysql-error.loglong_query_time = 1slow-query-logslow_query_log_file =/data/logs/mysql/mysql-slow.log # Othersexplicit_defaults_for_timestamp=true#max_connections = 500open_files_limit = 65535sql_mode=NO_ENGINE_ substitution,strict_trans_tables [client]socket =/tmp/mysql.sockport = 3306Note:

Some statements that are commented out need to be modified in conjunction with the business and hardware resources, so take the first place and use the default values. MY.CNF file can not be misspelled, otherwise, the next launch will be a lot of unexpected errors, if it does not start see: Mysql-error.log file. Improper setting of permissions can also cause startup failure.

Before starting, confirm the permissions again:

It is possible now that the MY.CNF permissions are not MySQL, while checking if the/etc/my.cnf exists, if there is delete!!!

# 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 Boot 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 just to configure the basic items correctly, and should spend more time on schema optimization, indexing, and query design. Once the basic configuration items of MySQL are properly configured, the benefits of having to modify other configuration options are usually small.
Over-optimized servers can lead to large potential risks, 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 have enough.
Without a once and for all configuration, with the data in the database, the number of users change, after a period of time, you can selectively adjust the configuration of the database.
The basic configuration options are detailed:

Socket =/tmp/mysql.sockpid_file =/data/logs/mysql/mysql.pid

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

Default_storage_engine = InnoDB

Setting the default storage engine, it is best to explicitly configure the storage engine when creating a table

Innodb_buffer_pool_size = 2ginnodb_log_file_size = 128minnodb_file_per_table = 1innodb_flush_method = 0_DIRECT

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

If most tables are InnoDB tables, the INNODB buffer pool settings may require memory more than anything else. InnoDB not only caches indexes: It also caches data, adaptive ha indexes, insert buffers (insert buffer), locks, and other internal data structures. InnoDB also uses a buffer pool to help delay writes, merge multiple writes, and write back sequentially. In summary, InnoDB heavily relies on the buffer pool and must allocate enough memory.

Note:
The larger the buffer pool, the longer it takes to warm up and shut down.

InnoDB uses logs to reduce the overhead of transaction commits. Because the log has logged transactions, it is not necessary to flush the buffer pool to disk when each transaction commits. 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 power is lost, the InnoDB can replay the log and recover the committed transaction.
The overall log size is controlled by the product of the two parameters of Innodb_log_file_size and Innodb_log_file_in_group. You typically need to set hundreds of MB or even up to GB. As a rule of thumb, the overall size of the log file should be sufficient to accommodate the server's active content for one hour.

Buffer pool Size Setting method:
Starting with the total server memory
Minus the operating system consuming memory and other services consuming memory (if there are other services)
Subtracting MySQL itself requires memory, such as allocating some buffers for each query operation.
Subtract enough memory for the operating system to cache InnoDB log files, at least enough to cache the most recently accessed portions. Subtract some of the last part of the binary log that can be cached to prevent replication from causing delays, and the repository may read old binary log files on the main library, causing pressure on the main library's memory.
Subtract the memory required by other MySQL buffers and caches, such as the MyISAM key cache (key_buffer_size), or query cache
The penalty is 105%, and the downward choice is a reasonable value.

Note: Settings do not need to be very precise, and rather cautious, rather than set too large, if the setting is less than 20% may only have a small impact, but if the size of 20% can cause serious problems: memory Exchange, disk jitter, memory exhaustion and panic.

#MyISAMkey_buffer_size = 128M

MyISAM itself caches only indexes and does not cache data (dependent on operating system cache data). If the majority of the MyISAM table, you should allocate a larger key_buffer_size.
Before allocating much memory, first understand how much space MyISAM occupies, and certainly do not need to allocate more memory than the index data that needs to be cached (sometimes for the amount of data amplification reserved, will be set relatively large).
There are two ways to calculate the size of the index:
Using SQL statements, query the index_length fields of the Information--schema table and add them together.

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

Using the UNIX system, use the following command

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

How much value does the key cache need to store?
Do not exceed the total index size, and do not exceed the total size reserved for the operating system cache. Take a smaller value for both.

Buffer usage calculation, view the following variables through the show status and show variables commands:

-((key_blocks_unused * key_cache_block_size) * 100/key_buffer_size)

If the server has been running for a long time, or if all the buffers have not been used, you can turn the buffer key down a little bit.

Key buffer hit ratio
In terms of experience, the number of misses per second is more useful, assuming that a disk can generate 100 random reads per second, 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

Setting the default character set and encoding

# CHARACTER-SET-SERVERCHARACTER-SET-SERVER=UTF8COLLATION-SERVER=UTF8_GENERAL_CI

The following options set the use of IP only to link the MySQL server, omitting the DNS query time
Read material: http://dev.mysql.com/doc/refman/5.6/en/host-cache.html

# Name-resolveskip-name-resolveskip-host-cache

Set the error log and slow query log. More than 1s for slow queries

# loglog_error =/data/logs/mysql/mysql-error.loglong_query_time = 1slow_query_logslow_query_log_file =/data/logs/ mysql/mysql-slow.log# others#timestamp default settings, 5.6.6 after the error is not set Explicit_defaults_for_timestamp=true

Setting the maximum link to be processed at the same time ensures that the application is overwhelmed by the explosion, and when the query cannot be executed, there is no benefit in opening a link, so the "too many links" error rejection is a quick and costly failure. The settings need to refer to the expected maximum number of concurrent and native configurations.

Max_connections = 500

Table_open_cache is known as Table_cache in MySQL 5.1.2 and earlier.
The Table_open_cache should be set large enough to avoid always reopening and parsing the table definition. If the value of Open_tables varies greatly per second, then Table_open_cache may be set to a small size. This value is good from 10 times times max_connections, but not more than 10000 in most cases.

Table_open_cache = 5000

If there are constant error links in the period of time, such as: Permissions error, application configuration error, etc. When the client reaches the Max_connect_errors set number, it will be blacklisted and unable to connect until the host cache is refreshed again. (How does it work?) ) can effectively prevent brute force 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, a classic 24th error will appear: "Too many open files".

Open_files_limit = 65535

Ask--One-click Programmer to answer the question artifact, one-man service for cattle, developer Programming Essentials official website: www.wenaaa.com

QQ group 290551701 gathers a lot of Internet elite, technical director, architect, project Manager! Open source technology research, Welcome to the industry, Daniel and beginners are interested in engaging in IT industry personnel to enter!

Linux system self-compiled installation of MySQL and basic configuration of the entire process analysis