Parsing the entire process of MySQL compilation and installation and basic configuration in Linux _ MySQL

This article mainly introduces how to compile and install MySQL in Linux and how to parse the entire process of basic configuration. the configuration is mainly for the InnoDB engine. For more information, see Install dependency:

CentOS:

# yum -y install wget gcc-c++ cmake make bison ncurses-devel perl unzip

Ubuntu: (using ubuntu requires a lot of root permissions. Please note that)

The code is as follows:

# Sudo apt-get install-y g ++ gcc make libpcre3 zlib1g libbz2-dev automake cmake perl libncurses5-dev bison

Add a MySQL User

# groupadd mysql# useradd -r -g mysql mysql

Create related directories in advance

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

Download the 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 description:

-DCMAKE_INSTALL_PREFIX =/usr/local/server/mysql-5.6.12 setup installation directory
-DMYSQL_DATADIR =/data/mysql
-DMYSQL_UNIX_ADDR =/tmp/mysql. sock: Set the UNIX socket Directory
-DMYSQL_USER = set the running user for mysql
-DDEFAULT_CHARSET = utf8: sets the default character set. the default value is latin1.
-DEFAULT_COLLATION = utf8_general_ci: sets the default proofreading rule. the default value is latin1_general_ci.
-DWITH_INNOBASE_STORAGE_ENGINE = 1 add InnoDB engine support
-DENABLE_DOWNLOADS = 1 automatic download of optional files, for example, automatic download of Google's test package
-DMYSQL_TCP_PORT = 3306: Set the server listening port. the default value is 3306.
-DSYSCONFDIR =/data/etc: Set the directory where my. cnf is located. the default directory is the installation directory.

More parameter execution # cmake.-LH or view official instructions

Note:

During execution, the following error occurs:

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

Solution:
There will be a source_downloads directory under the cd mysql directory, you need to extract the unzip gmock-1.6.0.zip, and then re-execute the above configuration process. Of course, you can also remove the-DENABLE_DOWNLOADS = 1 option. there is no problem if you do not compile Google's test package, but some earlier versions may not be able to compile.

Compile and install

# make && make install

Create a link file for future use and upgrade:

# 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

Initialize database

The 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

At this time, the following warning will appear. the configuration of my. cnf below will solve this 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 (see documentation for more details ).
Configure the startup project:

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

Modify my. cnf in the installation directory. some parameters must be modified according to 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 comments must be modified based on business and hardware resources. Therefore, use the default value to place a space first. The my. cnf file cannot contain spelling errors. otherwise, many unexpected errors may occur during the next startup. if the startup fails, see the mysql-error.log file. Improper permission setting may also cause startup failure.

Confirm the permissions again before starting:

It is very likely that my. cnf permission is not mysql now, and check whether/etc/my. cnf exists. If yes, delete it !!!

# chown -R mysql:mysql /usr/local/server/mysql-5.6.12

Start mysql:

# service mysql start

Add Environment variables

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

Security Settings (set the initial password, remove anonymous users, and remove the test database ):

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

Add boot start

CentOS:

# chkconfig mysql on

Ubuntu:

# sudo update-rc.d mysql defaults

Configuration instructions:

MySQL has a large number of configuration options that can be modified. Generally, you only need to configure the basic items correctly. you should spend more time on schema optimization, indexing, and query design. After you correctly configure the basic configuration items of MySQL, it is usually relatively small to make efforts to modify other configuration options.
Over-optimized servers cause high potential risks, which may cause frequent crashes and slow operation.
Most of the default configuration options of MySQL are already the best configuration, so it is best not to make too many changes. the parameters set above are already enough for most people.
There is no configuration once and for all. as the data and number of users in the database change, you can selectively adjust the database configuration after a period of time.
Detailed description of basic configuration options:

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

If no location is specified for the socket and pid_file files and the files are stored in the default compilation location, errors may occur in different MySQL versions.

default_storage_engine = InnoDB

Set the default storage engine. it is best to configure the storage engine explicitly when creating a table.

innodb_buffer_pool_size = 2Ginnodb_log_file_size = 128Minnodb_file_per_table = 1innodb_flush_method = 0_DIRECT

The two most important options of InnoDB are buffer pool size.
Innodb_buffer_pool_size and log file size innodb_log_file_size. the default value is usually too small.

If most of the tables are InnoDB tables, InnoDB buffer pool settings may require more memory than anything else. InnoDB not only caches indexes: it also caches data, adaptive Kazakhstan indexes, Insert Buffer, lock, and other internal data structures. InnoDB also uses the buffer pool to help delayed writing, merge multiple write operations, and write back in sequence. In short, InnoDB is heavily dependent on the buffer pool and must allocate enough memory.

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

InnoDB uses logs to reduce the overhead of transaction commit. Because the log has recorded transactions, you do not need to refresh the expansion of the buffer pool to the disk when each transaction is committed. InnoDB uses logs to change random I/O to sequential I/O. Once the log is securely written to the disk, the transaction becomes persistent. If a power failure occurs, InnoDB can replay logs and restore committed transactions.
The overall log size is controlled by the product of innodb_log_file_size and innodb_log_file_in_group. Generally, you need to set several hundred MB or even upper GB. As an empirical rule, generally all log files are of sufficient size to accommodate the activity content of the server for one hour.

Buffer pool size setting method:
Starting from the total server memory
Less memory occupied by the operating system and memory occupied by other services (if there are other services)
Remove the memory required by MySQL, for example, allocate some buffer for each query operation.
Less memory that allows the operating system to cache InnoDB log files, at least enough memory to cache recent frequent accesses. The last part of binary logs that can be cached is subtracted to prevent replication delay. the slave database may read the old binary log files from the master database, which puts pressure on the memory of the master database.
Subtract the memory required for other MySQL buffers and caches, such as the MyISAM key cache (key_buffer_size) or query cache (query cache)
Take 105% to a reasonable value.

Note: The setting does not need to be accurate, and you should be careful not to set it too large. if it is set to 20% less, it may only have a small impact, however, a 20% increase may cause serious problems: memory swap, disk jitter, memory depletion, and crashes.

#MyISAMkey_buffer_size = 128M

MyISAM only caches indexes and does not cache data (dependent on the operating system cache data ). If most of the tables are MyISAM tables, a large key_buffer_size should be allocated.
Before allocating large memory, you should first understand how much space MyISAM occupies. you certainly do not need to allocate a large proportion of memory for the index data to be cached (sometimes reserved for the increase of data volume, will be set to relatively large ).
There are two ways to calculate the index size:
Use an SQL statement to query the INDEX_LENGTH field of the INFORMATION-SCHEMA table and add them together.

SELECT SUM( INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLESWHERE ENGINE='MYISAM';

The following command is used for UNIX systems:

# du -sch `find /mysqldatadir -name “*.MYI”`

What value does the key cache need to store?
Do not exceed the total index size or the total size reserved for the operating system cache. Take a small value for both.

Calculate the buffer usage. run the show status and show variables commands to view the following VARIABLES:

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

If the server has not used all the buffer keys after running for a long time, you can reduce the buffer key.

Key buffer hit rate
In terms of experience, the number of hits per second is more useful. assuming that a disk can generate 100 random reads per second, 5 cache misses per second won't lead to I/O busy, 80 times per second may lead to I/O busy. Calculation formula:

Key_reads / Uptime

Set the default character set and encoding

# character-set-servercharacter-set-server=utf8collation-server=utf8_general_ci

The following option sets that only IP addresses are used to connect to the MySQL server, omitting the DNS query time
Reading material: http://dev.mysql.com/doc/refman/5.6/en/host-cache.html

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

Set error logs and slow query logs. A query that exceeds 1 s is slow.

# LOGlog_error =/data/logs/mysql/mysql-error.loglong_query_time = Sources =/data/logs/mysql/mysql-slow.log # Others # timestamp default settings, 5.6.6 after not set will report an error explicit_defaults_for_timestamp = true

Set the maximum link to be processed at the same time to ensure that the application surge is generated and overwhelmed. when the query cannot be executed, it is no good to open a link, therefore, the error "too many links" is a fast and cost-effective method. For specific settings, see the expected maximum concurrency and local configuration.

max_connections = 500

Table_open_cache was known as table_cache in MySQL 5.1.2 and earlier.
Table_open_cache should be large enough to avoid re-opening and parsing table definitions. If the value of Open_tables changes greatly every second, the value of table_open_cache may be too small. This value is better set from 10 times of max_connections, but it should not exceed 10000 in most cases.

table_open_cache = 5000

If there are continuous error links within a period of time, such as permission errors and application configuration errors. When the client reaches the number of max_connect_errors settings, it will be blacklisted and cannot be connected until the host cache is refreshed again. (How ?) This effectively prevents brute-force password cracking.

max_connect_errors = 2000

In a typical Linux system, we set open_files_limit as large as possible. In modern operating systems, the overhead of opening file handles is small. If this parameter is not set large enough, the classic 24 error will occur: "too open files ".

open_files_limit = 65535