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

1. MySQL 5.6.32 64-bit installation package download

Download the MySQL 5.6.32 64-bit installation package download (or wget download) using Thunderbolt in the official website http://dev.mysql.com/downloads/mysql/5.6.html#:

# 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=" border-left-0px; border-right-width:0px; 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 download file through Securefx to the/usr/local/src/directory (the IP of the virtual host CentOS7.2 is: 192.168.1.11)

650) this.width=650; "title=" image "style=" border-left-0px; border-right-width:0px; 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
mysql-client-5.6.32-1.linux_glibc2.5.x86_64.rpm
mysql-shared-5.6.32-1.linux_glibc2.5.x86_64.rpm
mysql-server-5.6.32-1.linux_glibc2.5.x86_64.rpm
mysql-devel-5.6.32-1.linux_glibc2.5.x86_64.rpm
mysql-test-5.6.32-1.linux_glibc2.5.x86_64.rpm
mysql-embedded-5.6.32-1.linux_glibc2.5.x86_64.rpm

3. To see if the system has other MySQL components installed , use the command:

# Rpm-qa | Grep–i MySQL
Mysql-connector-odbc-5.2.5-6.el7.x86_64

To uninstall the installed MySQL, uninstall the MySQL command as follows:

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

Remove all files under the/var/lib/mysql folder cleanly.

4. Install Server and client:

# RPM-IVH mysql-server-5.6.32-1.linux_glibc2.5.x86_64.rpm

The installation appears with the following error message:

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

Hint and mariadb-libs-1:5.5.50-1.el7_2.x86_64 has a conflict and needs to be uninstalled.

Check out 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 the dependent package):

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

Re-install 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

The installation was successful.

Default installation location for MySQL

/var/lib/mysql/#数据库目录

/usr/share/mysql #配置文件目录

/usr #相关命令目录

/etc/init.d/mysql #启动脚本

5. Prepare for starting MySQL

A. New MySQL user

# useradd-s/sbin/nologin MySQL

B. Create a directory to store the database, and access the authorized, preferably in a directory with sufficient space.

# mkdir-p/data/mysql
# Chown-r Mysql:mysql/data/mysql

C. Initialize the database, the user is MySQL, the database directory is =/data/mysql

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

Error occurred:

FATAL Error:please Install the following Perl modules before executing/usr/local/mysql/scripts/mysql_install_db:

Workaround: Install the autoconf Library

# yum-y Install autoconf//The Data:dumper module will be installed when this package is installed

After installing the dependent files and initializing again, you will 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 #如下修改

# # # #标准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

# # # #标准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

To edit a file:

# Vim/etc/init.d/mysqld

Modify the MySQL base directory and database directory as follows:

basedir= '/usr '
Datadir= '/data/mysql '

6, start the script to join the system service item, and set the boot start, and finally start MySQL:

# chkconfig--add mysqld

# Chkconfig Mysqld on

# service Mysqld Start

————————————————— Standard MY.CNF file —————————————

[Client]
Port = 3306
Socket =/var/lib/mysql/mysql.sock

[MySQL]
#这个配置段设置启动MySQL服务的条件; In this case, No-auto-rehash ensures that the service starts faster.
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
#在MYSQL暂时停止响应新请求之前, the number of requests within a short period of time can be present on the stack. 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 incoming TCP/IP connections. The default value is 80.

Max_connections = 3000
#MySQL允许最大的进程连接数, this value needs to be increased if the too many connections error is frequently present. Default 151

Max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数, when this number of times is exceeded, the MySQL server will disallow host connection requests until the MySQL server restarts or clears the host information through the flush hosts command. Default 100

external-locking = FALSE
#使用 –skip-external-locking mysql option to avoid external locking. This option is turned on by default

Max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值. The system defaults to 4MB and the maximum value is 1GB, which must be set to a multiple of 1024.

#sort_buffer_size = 2M
# sort_buffer_size is a connection-level parameter that allocates the set of memory once for the first time each connection (session) needs to use this Buffer.
#Sort_Buffer_Size is not as large as possible, because it is a connection-level parameter, too large a setting + high concurrency may deplete system memory resources. For example: 500 connections will consume 500*sort_buffer_size (8M) =4g memory
When the #Sort_Buffer_Size exceeds 2KB, memory allocations are made using mmap () rather than malloc (), resulting in reduced efficiency. System default 2M, use the default value

#join_buffer_size = 2M
#用于表间关联缓存的大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection. System default 2M, use the default value

Thread_cache_size = 300
#默认38
# server thread Cache This value indicates that the number of threads stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache, and if the thread is requested again, then the request will be read from the cache, if the cache is empty or a new request, Then this thread will be recreated, and if there are many new threads, adding this value can improve system performance. You can see the effect of this variable by comparing the variables of the Connections and threads_created states. Set the rules as follows: The 1GB memory configuration is 8,2gb configured to 16,3GB configured for 32,4GB or higher memory, which can be configured larger.

#thread_concurrency = 8
#系统默认为10, use 10 to observe first
# Setting the value of the thread_concurrency is correct or not, the performance of MySQL is very significant, in the case of multiple CPUs (or multicore), the error set the value of Thread_concurrency, will cause MySQL can not fully utilize the multi-CPU (or multicore), Occurs at the same moment only one CPU (or core) is in the working condition. The thread_concurrency should be set to twice times the number of CPU cores. For example, there is a dual-core CPU, then the thread_concurrency should be 4; 2 Dual-core CPUs, the value of thread_concurrency should be 8

Query_cache_size = 64M
#在MyISAM引擎优化中, this parameter is also an important optimization parameter. But it also revealed some problems. The memory of the machine is getting bigger and larger, and the value of the parameter assignment is more and more habitual. This increase in parameters also raises a number of questions. Let's start by analyzing how Query_cache_size works: When a select query works in DB, the DB caches the statement, and when the same SQL comes back to the DB, the DB returns the result from the cache to the client if the table does not change. There is a shut-down point, that is, when DB is working with Query_cache, it requires that the table involved in the statement not be changed during this time period. So what happens to the data in Query_cache if the table is changed? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow. Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in high concurrency, write a large-scale system, it is recommended to disable this function.

Query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小, default is 1M

Query_cache_min_res_unit = 2k
#默认是4KB, setting a big value is good for big data queries, but if your queries are small data queries, it's easy to create memory fragmentation and waste.
#查询缓存碎片率 = Qcache_free_blocks/qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.
#查询缓存利用率 = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
#查询缓存利用率在25% below indicates that the Query_cache_size setting is too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 description query_cache_ Size may be a little bit small, or too much fragmentation.
#查询缓存命中率 = (qcache_hits–qcache_inserts)/qcache_hits * 100%

#default-storage-engine = MyISAM
#default_table_type = InnoDB #开启失败

#thread_stack = 192K
#设置MYSQL每个线程的堆栈大小, the default value is large enough to satisfy normal operations. Can be set to 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-reads uncommitted reading committe-read submitted repeatable READ-REPEATABLE read SERIALIZABLE-serial

Tmp_table_size = 256M
# The default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value. If this value is exceeded, the temporary table is written to disk.
Max_heap_table_size = 256M

Expire_logs_days = 7
Key_buffer_size = 2048M
#批定用于索引的缓冲区大小, increase it to get better index processing performance, the parameter can be set to 256MB or 384MB for a server that has around 4GB.

Read_buffer_size = 1M
#默认128K
# MySQL read into buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Read_rnd_buffer_size = 16M
# mysql Random read (query operation) buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

Bulk_insert_buffer_size = 64M
#批量插入数据缓存大小, can effectively improve the efficiency of insertion, the default is 8M

Myisam_sort_buffer_size = 128M
# MyISAM Table changes when reordering required buffer default 8M

Myisam_max_sort_file_size = 10G
# The size of the maximum temporary file 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 buffer (slower)

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

Myisam_recover
#自动检查和修复没有适当关闭的 MyISAM Table
Skip-name-resolve
Lower_case_table_names = 1
Server-id = 1

Innodb_additional_mem_pool_size = 16M
#这个参数用来设置 the memory pool size of the data directory information and other internal data structures stored by the InnoDB, similar to Oracle's library cache. This is not a mandatory parameter and can be breached.

Innodb_buffer_pool_size = 2048M
# This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. Some rules apply to Key_buffer-if your data volume is small and does not increase, then you do not need to set the innodb_buffer_pool_size too large

#innodb_data_file_path = Ibdata1:1024m:autoextend Set too large to cause error, default 12M observation
#表空间文件 Important data

#innodb_file_io_threads = 4 Ambiguous, default value is used
#文件IO的线程数, typically 4, but under Windows, you can set it larger.

Innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几, the default setting, typically 8, is recommended.

Innodb_flush_log_at_trx_commit = 2
# If this parameter is set to 1, the log will be written to disk after each commit transaction. To provide performance, you can set to 0 or 2, but assume the risk of losing data in the event of a failure. A setting of 0 indicates that the transaction log is written to the log file, and the log file is flushed to disk once per second. A setting of 2 indicates that the transaction log will be written to the log at commit time, but the log file is flushed to disk one at a time.

#innodb_log_buffer_size = 16M Using default 8M
#此参数确定些日志文件所用的内存大小, in M. Larger buffers can improve performance, but unexpected failures will cause data loss. MySQL developer recommends setting the 1-8m between

#innodb_log_file_size = 128M Using default 48M
#此参数确定数据日志文件的大小, in M, larger settings can improve performance, but also increase the time it takes to recover a failed database

#innodb_log_files_in_group = 3 Using the default 2
#为提高性能, MySQL can write log files to multiple files in a circular fashion. Recommended set to 3M

#innodb_max_dirty_pages_pct = 90 Using the default 75 observation
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
# The amount of dirty_page in Buffer_pool directly affects the closing time of the 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 changed dynamically. Therefore, the innodb_max_dirty_pages_pct is reduced before closing the InnoDB, forcing the data block to flush for a period of time, which can greatly shorten the time of MySQL shutdown.

Innodb_lock_wait_timeout = 120
#默认为50秒
# InnoDB has its built-in deadlock detection mechanism that can cause incomplete transactions to be rolled back. However, if you combine InnoDB with a MYISAM lock tables statement or a third-party transaction engine, the INNODB does not recognize the deadlock. To eliminate this possibility, you can set Innodb_lock_wait_timeout to an integer value that indicates how long (in seconds) the MySQL waits before allowing other transactions to modify data that is eventually rolled back by the transaction.

innodb_file_per_table = 0
#默认为No
#独享表空间 (OFF)

[Mysqldump]
Quick
# max_allowed_packet = 32M

[Mysqld_safe]
Log-error=/data/mysql/mysql_oldboy.err
Pid-file=/data/mysql/mysqld.pid

Sql_mode=no_engine_substitution,strict_trans_tables

———————————— Standard MY.CNF file ——————————————————

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