CentOS7.2 install MySQL 5.6.32 on the smallest installed virtual machine

1, MySQL 5.6.32 64-bit installation package download

Use Thunder on the official website http://dev.mysql.com/downloads/mysql/5.6.html# to download MySQL 5.6.32 64-bit installation package download (or wget download:

# cd / usr / local / src
# wget http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.32-1.linux_glibc2.5.x86_64.rpm-bundle.tar)

650) this.width = 650; "title =" image "style =" background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; padding-right: 0px; border -top-width: 0px; "border =" 0 "alt =" image "src =" http://s3.51cto.com/wyfs02/M00/86/95/wKioL1fEWd6xPYbjAABRxMhCHes013.png "width =" 669 "height = "371" />

(Http://cdn.mysql.com/Downloads/MySQL-5.6/MySQL-5.6.32-1.linux_glibc2.5.x86_64.rpm-bundle.tar)

Pass the downloaded file to the / usr / local / src / directory through SecureFX (the IP of the virtual host CentOS7.2 is: 192.168.1.11)

650) this.width = 650; "title =" image "style =" background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; padding-right: 0px; border -top-width: 0px; "border =" 0 "alt =" image "src =" http://s3.51cto.com/wyfs02/M01/86/95/wKioL1fEWd-Qqdm3AAFpIO9ORQw062.png "width =" 913 " height = "705" />

2.Unzip the file.

# cd / usr / local / src /

# tar xvf MySQL-5.6.32-1.linux_glibc2.5.x86_64.rpm-bundle.tar
MySQL-shared-compat-5.6.32-1.linux_glibc2.5.x86_64.rpm #RHEL compatible package
MySQL-client-5.6.32-1.linux_glibc2.5.x86_64.rpm #MySQL client program
MySQL-shared-5.6.32-1.linux_glibc2.5.x86_64.rpm #MySQL shared library
MySQL-server-5.6.32-1.linux_glibc2.5.x86_64.rpm #MySQL server program
MySQL-devel-5.6.32-1.linux_glibc2.5.x86_64.rpm #MySQL libraries and header files
MySQL-test-5.6.32-1.linux_glibc2.5.x86_64.rpm #MySQL test component
MySQL-embedded-5.6.32-1.linux_glibc2.5.x86_64.rpm # MySQL's embedded program

3. Check whether other MySQL components are installed on the system. Use the command:

# rpm -qa | grep -i mysql
mysql-connector-odbc-5.2.5-6.el7.x86_64

Uninstall MySQL, the command to uninstall mysql is as follows:

# rpm -e --nodeps mysql-connector-odbc-5.2.5-6.el7.x86_64

Delete all files in the / var / lib / mysql folder.

4. Install Server and Client:

# rpm -ivh MySQL-server-5.6.32-1.linux_glibc2.5.x86_64.rpm

The following error message appears during installation:

file /usr/share/mysql/charsets/swe7.xml from install of MySQL-server-5.6.32-1.linux_glibc2.5.x86_64 conflicts with file from package mariadb-libs-1: 5.5.50-1.el7_2. x86_64

There is a conflict with mariadb-libs-1: 5.5.50-1.el7_2.x86_64 and it needs to be uninstalled.

Query this package:

# rpm -qa | grep mari *
mariadb-libs-5.5.50-1.el7_2.x86_64

Uninstall this package (use yum to uninstall the current package and dependent packages):

# yum remove -y mariadb-libs-5.5.50-1.el7_2.x86_64

Reinstall again:

# rpm -ivh MySQL-server-5.6.32-1.linux_glibc2.5.x86_64.rpm

# rpm -ivh MySQL-client-5.6.32-1.linux_glibc2.5.x86_64.rpm

Successful installation.

MySQL default installation location

/ var / lib / mysql / #Database directory datadir

/ usr / share / mysql #Configuration file directory

/ usr #Related command directory basadir

/etc/init.d/mysql #Startup script

 

5, prepare for starting mysql

a. New mysql user

# useradd -s / sbin / nologin mysql

 

b. Establish a directory to store the database and access rights, preferably on a directory with sufficient space.

# mkdir -p / data / mysql
# chown -R mysql: mysql / data / mysql


c. Initialize the database, the user is mysql, and the database directory is = / data / mysql

# mysql_install_db --user = mysql --datadir = / data / mysql

An error occurred:

FATAL ERROR: please install the following Perl modules before executing / usr / local / mysql / scripts / mysql_install_db:

Solution: install autoconf library

# yum-y install autoconf // Data: Dumper module will be installed when this package is installed

After installing the dependent files, initialize again, and see two "OK" in the prompt file to indicate success.

# mysql_install_db --user = mysql --datadir = / data / mysql

 

d. Copy the configuration file and edit

# cp /usr/share/mysql/my-default.cnf /etc/my.cnf

# vim /etc/my.cnf #Modify as follows

#### Standard my.cnf file ############################################ ##

[mysqld]

basedir = / usr
datadir = / data / mysql
port = 3306
# server_id = .....
socket = /var/lib/mysql/mysql.sock

sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

#### Standard my.cnf file ############################################ ##

 

e. Modify the startup script and authorize

# cp /usr/share/mysql/mysql.server /etc/init.d/mysqld

# chmod 755 /etc/init.d/mysqld

Edit file:

# vim /etc/init.d/mysqld

Modify the basic directory and database directory of mysql as follows:

basedir = ‘/ usr’
datadir = ‘/ data / mysql’

 

6. Add the system service item to the startup script, and set the startup to start, and finally start mysql:

# chkconfig --add mysqld

# chkconfig mysqld on

# service mysqld start

 

 

 

————————————————— Detailed explanation of standard my.cnf file ———————————

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysql]
#This configuration section sets the conditions for starting the MySQL service; in this case, no-auto-rehash ensures that the service starts quickly.
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
basedir = / usr
datadir = / var / lib / mysql
open_files_limit = 10240

back_log = 600
#How many requests can be stored in the stack for a short time before MYSQL temporarily stops responding to new requests. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for the incoming TCP / IP connection. The default value is 80.

max_connections = 3000
#MySQL allows the maximum number of process connections. If Too Many Connections error messages often appear, you need to increase this value. Default 151

max_connect_errors = 6000
#Set the maximum number of abnormal interruptions in connection requests for each host. When this number is exceeded, the MYSQL server will prohibit host connection requests until the mysql server is restarted or the relevant information of this host is cleared by the flush hosts command. Default 100

external-locking = FALSE
#Use the --skip-external-locking MySQL option to avoid external locking. This option is turned on by default

max_allowed_packet = 32M
#Set the maximum value of one message transmission in network transmission. The system default is 4MB and the maximum is 1GB. You must set a multiple of 1024.

#sort_buffer_size = 2M
# Sort_Buffer_Size is a connection-level parameter. When each connection (session) needs to use this buffer for the first time, the set memory is allocated once.
#Sort_Buffer_Size is not as large as possible. Because it is a connection-level parameter, an excessively large setting + high concurrency may exhaust system memory resources. For example: 500 connections will consume 500 * sort_buffer_size (8M) = 4G memory
When #Sort_Buffer_Size exceeds 2KB, it will use mmap () instead of malloc () for memory allocation, resulting in reduced efficiency.
The system default is 2M, just use the default value.

#join_buffer_size = 2M
#Used for the size of the associated cache between the tables. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection. The system default is 2M, just use the default value.

thread_cache_size = 300
#Default 38
# Server thread cache This value indicates that the number of threads stored in the cache can be reused. If there is space in the cache when the connection is disconnected, the client's threads will be placed in the cache. If the threads are requested again, the request will Read from the cache. If the cache is empty or a new request, then this thread will be re-created. If there are many new threads, increasing this value can improve system performance. By comparing the variables of the Connections and Threads_created states, You can see the effect of this variable. The setting rules are as follows: 1GB memory is configured as 8, 2GB is configured as 16, 3GB is configured as 32, 4GB or higher, and can be configured larger.

#thread_concurrency = 8
#The system default is 10, use 10 to observe first
# Setting the correct value of thread_concurrency has a great impact on the performance of MySQL. In the case of multiple CPUs (or multi-cores), incorrectly setting the value of thread_concurrency will cause MySQL to make full use of multiple CPUs (or multi-cores) There can be only one CPU (or core) working at the same time. Thread_concurrency should be set to twice the number of CPU cores. For example, if you have a dual-core CPU, then thread_concurrency should be 4; 2 dual-core CPUs, and the value of thread_concurrency should be 8

query_cache_size = 64M
#In MyISAM engine optimization, this parameter is also an important optimization parameter. But some problems emerged. The memory of the machine is getting larger and larger, and the value of parameter allocation is habitually getting larger and larger. The increase of this parameter also caused a series of problems. Let's first analyze the working principle of query_cache_size: After a SELECT query works in the DB, the DB caches the statement. When the same SQL comes to the DB again to call, the DB changes the table without changing the table. The result is returned to the client from the cache. Here is a key point, when the DB uses Query_cache to work, it requires that the tables involved in the statement have not changed during this time. So what happens to the data in Query_cache when the table changes? First, all the statements related to Query_cache and the table are invalidated, and then update is written. Then if the Query_cache is very large, the table has a lot of query structures, and the query invalidation is slow, an update or insert will be slow, so you can see how the update or insert is so slow. Therefore, in a system with a large amount of database writes or updates, this parameter is not suitable for over-allocation. And in systems with high concurrency and large write volume, it is recommended to disable this function.

query_cache_limit = 4M
#Specify the buffer size that a single query can use, the default is 1M

query_cache_min_res_unit = 2k
#The default is 4KB. A large value is good for big data queries, but if your queries are all small data queries, it will easily cause memory fragmentation and waste.
#Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%
#If the query cache fragmentation rate is more than 20%, you can use FLUSH QUERY CACHE to defragment the cache fragments, or try to reduce the query_cache_min_res_unit, if your queries are small data.
#Query cache utilization = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#If the query cache utilization is below 25%, it means that the query_cache_size is set too large and can be appropriately reduced; if the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, it means that the query_cache_size may be a little small, or there is too much fragmentation.
#Query cache hit rate = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

# default-storage-engine = MyISAM
#default_table_type = InnoDB #Open failed

#thread_stack = 192K
#Set the stack size of each thread in MYSQL. The default value is large enough to meet common operations. Can be set from 128K to 4GB, default is 256KB, use default observation

transaction_isolation = READ-COMMITTED
# Set the default transaction isolation level. The available levels are as follows: READ UNCOMMITTED-read uncommitted READ COMMITTE-read committed REPEATABLE READ-repeatable read SERIALIZABLE-serial

tmp_table_size = 256M
# The default size of tmp_table_size is 32M. If a temporary table exceeds this size, MySQL generates an error in the form The table tbl_name is full. If you do many advanced GROUP BY queries, increase the tmp_table_size value. If this value is exceeded, temporary tables are written to disk.
max_heap_table_size = 256M

expire_logs_days = 7
key_buffer_size = 2048M
#Batch the size of the buffer used for indexing. Increasing it can get better indexing performance. For servers with about 4GB of memory, this parameter can be set to 256MB or 384MB.

read_buffer_size = 1M
#Default 128K
# MySql read into the buffer size. A request to sequentially scan the table will allocate a read buffer, and MySql will allocate a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan request for the table is very frequent, and you think that the frequent scan is too slow, you can improve its performance by increasing the value of this variable and the size of the memory buffer. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.

read_rnd_buffer_size = 16M
# MySql's random read (query operation) buffer size. When rows are read in any order (for example, in sorted order), a random read buffer is allocated. When sorting queries, MySql will scan the buffer first to avoid disk searching and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql will issue this buffer space for each client connection, so this value should be set as appropriate to avoid excessive memory overhead.

bulk_insert_buffer_size = 64M
#Bulk insert data cache size, which can effectively improve the insertion efficiency, the default is 8M

myisam_sort_buffer_size = 128M
# MyISAM buffer required for reordering table changes 8M default

myisam_max_sort_file_size = 10G
# The maximum temporary file size allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).
# If the file size is larger than this value, the index will be created by the key value buffer (slower)

#myisam_max_extra_sort_file_size = 10G 5.6 This value is not set
#myisam_repair_threads = 1 defaults to 1
# If a table has more than one index, MyISAM can use more than one thread to repair them by parallel sorting.
# This is a good choice for users with multiple CPUs and a large amount of memory.

myisam_recover
#Automatically check and repair MyISAM tables that are not closed properly
skip-name-resolve
lower_case_table_names = 1
server-id = 1

innodb_additional_mem_pool_size = 16M
#This parameter is used to set the data directory information stored by InnoDB and the size of the memory pool of other internal data structures, similar to Oracle's library cache. This is not a mandatory parameter and can be broken.

innodb_buffer_pool_size = 2048M
# This is very important for Innodb tables. Innodb is more sensitive to buffering than MyISAM tables. MyISAM can run with the default key_buffer_size setting, but Innodb behaves like a snail with the default innodb_buffer_pool_size setting. Because Innodb caches both data and indexes without leaving too much memory for the operating system, if you only need to use Innodb, you can set it up to 70-80% of available memory. Some rules applied to key_buffer are — if your data volume is not large, and it will not increase, then you do not need to set innodb_buffer_pool_size too large

#innodb_data_file_path = ibdata1: 1024M: autoextend If the setting is too large, an error will be reported. The default is 12M.
#TABLE SPACE FILE IMPORTANT DATA

#innodb_file_io_threads = 4 ambiguous, use default
#The number of file IO threads is generally 4, but it can be set larger under Windows.


innodb_thread_concurrency = 8
#The server has a few CPUs and is set to a few. It is recommended to use the default setting, which is generally 8.

innodb_flush_log_at_trx_commit = 2
# If this parameter is set to 1, the log will be written to disk after each transaction commit. To provide performance, it can be set to 0 or 2 but at the risk of losing data in the event of a failure. A setting of 0 means that the transaction log is written to the log file, and the log file is flushed to disk every second. A setting of 2 means that the transaction log will be written to the log upon commit, but the log file will be flushed to disk each time.

#innodb_log_buffer_size = 16M Use default 8M
#This parameter determines the memory size used by some log files, in M units. Larger buffers can improve performance, but unexpected failures will lose data. MySQL developers recommend setting it between 1-8M

#innodb_log_file_size = 128M Use default 48M
#This parameter determines the size of the data log file, in M units. A larger setting can improve performance, but will also increase the time required to recover a failed database

#innodb_log_files_in_group = 3 use default 2
#To improve performance, MySQL can write log files to multiple files in a circular fashion. Recommended setting is 3M

#innodb_max_dirty_pages_pct = 90 use the default 75 observations
#Recommended reading http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
The number of Dirty_Pages in # Buffer_Pool directly affects the shutdown time of InnoDB. The parameter innodb_max_dirty_pages_pct can directly control the ratio of Dirty_Page in Buffer_Pool, and fortunately innodb_max_dirty_pages_pct can be dynamically changed. Therefore, before closing InnoDB, make innodb_max_dirty_pages_pct smaller and force the data block to be flushed for a period of time, which can greatly shorten the shutdown time of MySQL.

innodb_lock_wait_timeout = 120
#Default is 50 seconds
# InnoDB has its built-in deadlock detection mechanism, which can cause unfinished transactions to roll back. However, if you use MyISAM's lock tables statement or a third-party transaction engine in conjunction with InnoDB, InnoDB cannot recognize the deadlock. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer value, which instructs MySQL to allow other transactions to modify data that will eventually be rolled back by the transaction.