One, MySQL installation

MySQL version:

　　mysql5.6 (Linux General Edition)

Installation Environment:

MySQL relies on libaio libraries. If this library is not installed locally, data Catalog initialization and subsequent server startup steps will fail. If necessary, install using the appropriate package manager. For example, on a yum-based-based system:

shell> Yum Search Libaio  # Search for infoshell> yum install Libaio # Install Library

　　Starting with MySQL 5.6.37, the Linux Generic tarball package format is EL6 rather than EL5. As a side effect, MySQL client bin/mysql needs libtinfo.so.5. So check the/lib64/folder for any libtinfo.so.5 before installing. If it does not exist, it needs to be installed.

Yum install  libncurses.so.5 ln-s libncurses.so.5.6/lib64/libtinfo.so.5 #64位系统ln-S libncurses.so.5.6/lib/ Libtinfo.so.5 #32位系统

Installation steps:

Shell>Groupadd Mysqlshell> Useradd-r-G mysql-s/bin/falseMysqlshell> cd/usr/Localshell>TarZxvf/path/to/mysql-version-os.Tar. Gzshell>LN-S full-path-to-mysql-version-OS Mysqlshell>CD Mysqlshell>Chown-R MySQL. Shell>chgrp-R MySQL. #这两句可以用Chown-R mysql:mysql. Shell> scripts/mysql_install_db--user=MySQL #这句执行脚本会使用my. CNF configuration data. So you need to configure the My.cnfshell before performing the installation.>Chown-R root. Shell>Chown-R MySQL Datashell> Bin/mysqld_safe--user=mysql &# Next command is Optionalshell>CPSupport-files/mysql.server/etc/init.d/mysql.server
shell> export PATH= $PATH:/usr/local/mysql/bin  

configuration file:

　　Description: From GitHub

　　Configured Configuration files: Links

# The following options will be read by the MySQL client app. Note Only the client application included with MySQL guarantees that this content can be read. # If you want your own MySQL application get these values. # These options need to be specified when the MySQL client library is initialized. # for advice The change settings see# http://dev.mysql.com/doc/refman/5.7/en/ server-configuration-defaults.html# * * * does not EDIT the this FILE.  It's a template which'll be copied to the# * * * default location during install, and would be replaced if you# * * * Upgrade To a newer version of mysql.# mysqld program [mysqld]# Remove leading # and set to the amount of RAM for the most important data # Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128m#★★★ Is it important here? To make the MySQL login link fast Skip-nam e-resolve# Remove Leading # to turn on a very important data integrity option:logging# changes to the binary log between backups.# log_bin# These is commonly set, remove the # and set as required.# use the given directory as the root directory (installation directory). # Basedir = ... # reads the database file from the given directory. # DataDir = ... # Specify a file that holds the process ID for the MYSQLD program (only for Unix/linux systems); # Pid-file = ... # Specifies the port that MSSQL listens on # port = ...server_id = ... # Specify a socket file for local communication between the MySQL client and server (the default is/var/lib/mysql/mysql.sock file under Linux) # socket = ... sql_mode=no_ engine_substitution,strict_trans_tables# General configuration Options basedir =/data/apps/mysqldatadir =/data/appdata/mysqlport = 3306socket =/var/run/mysqld/mysqld.sock# Setting character-set-server=utf8# Specifies the number of possible connections for MySQL. # when the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and the main thread takes a short time to check the connection and start a new thread. # The value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request. # 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. # Attempting to set Back_log above your operating system limit will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512. # Back_log is the number of connections that the operating system can hold in the listening queue, and the # queue holds the connection before the MySQL Connection Manager thread is processed. # If you have a very high connection rate and "connection refused" error, # You should increase the value here. # Check your Operating system documentation to get the maximum value for this variable. # If you set Back_log to a value that is higher than your operating system limit, it will have no effect Back_log = 300# does not listen on the TCP/IP port. # If all the processes are connected to the same server on the local mysqld,# This setting will be a way to enhance security # All MYSQLD connections are made through Unix Sockets or named pipes. # Note Under Windows if the Named Pipes option is not turned on but only with this item # (through the "Enable-named-pipe "Option" will cause the MySQL service to have no effect! #skip-networking# The maximum number of simultaneous sessions allowed by the MySQL service # One of the connections will be logged in as an administrator with the SUPER privilege reserved. # Even if the maximum number of connections is reached. max_connections = 3000# Maximum number of errors per client connection, if this limit is reached. # This client will be blocked by the MySQL service until the "FLUSH HOSTS" or the service restart # Illegal passwords and other errors at the time of linking increase this value. # View the "aborted_connects" status to get the global counters. Max_connect_errors = 50# The number of tables opened by all threads. # Increasing this value increases the MYSQLD The number of file descriptors required # So you need to confirm that the "open-files-limit" variable in [Mysqld_safe] sets the number of open files allowed to be at least equal to the value of table_cache Table_open_cache = 4096# Allows external file-level locks. Opening a file lock can negatively affect performance # So use this option only if you are running multiple DB instances on the same file (note that there are still other constraints!) # or you use some other software dependencies on the file level to lock down the maximum size of the request packet that the MyISAM table #external-locking# service can handle and the maximum request size that the service can handle (which is necessary when working with a large BLOB field) # Each connection is independent of size, size dynamically increases Max_allowed_packet = 32m# in a transaction binlog in order to record the cache size held by the SQL state # If you often use large, multi-declarative transactions, you can increase this value to get greater performance. # All engaged in The status of the service will be buffered in the Binlog buffer and then written to Binlog once committed. # if the transaction is larger than this value, the temporary file on the disk is used instead. # This buffer is created when the state is first updated for each connected transaction Binlog_cache_size = 4m# The maximum capacity allowed for a separate memory table. # This option prevents accidental creation of a large memory table resulting in all memory resources being exhausted. Max_heap_table_size = 128m# random-read data buffers use memory (read_rnd_buffer_size): and sequential reads correspond, # when MySQL makes non-sequential reads (random reads) blocks, it takes advantage of > This buffer holds the read data # such as reading the table data based on the index information, joins the table based on the sorted result set, etc. # in general, it is the case that when the data blocks are read in order to meet the > sequence, MySQL needs to generate random reads,In order to use the memory buffer set by the read_rnd_buffer_size parameter read_rnd_buffer_size = 16m# the sort buffer is used to handle the sort caused by the order by and the GROUP by queue # If the sorted data cannot be placed in the row Sequential buffering, an alternative disk-based merge classification is used to view the "sort_merge_passes" state variable. # Each thread is assigned a buffer that is assigned the size of each thread that needs to be sorted when the sort occurs. Increase this value to accelerate the order by or group by operation. Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, then the actual allocated total sort buffer size is 100x6=600mbsort_buffer_size = 16m# This buffer is used to optimize the Union (full JOINS without index union). # Similar unions have very poor performance in most cases, but setting this value to large can mitigate the performance impact. # See the number of full unions through the "Select_full_join" state variable # when full federation occurs, allocate join_buffer_size = 16m# cache of reusable threads in each thread # Thread_cache = 8# avoid mysql external lock, minus Less chance of error increases stability. # skip-locking # How many threads we keep in the cache for reuse # When a client disconnects, the client thread is placed in the cache if the thread in the cache is less than thread_cache_size. # This can greatly reduce the overhead of thread creation when you need a lot of new connections # (Generally speaking, if you have a good threading model, this will not have a noticeable performance boost.) Thread_cache_size = 16# This allows the application to give the thread system a hint at the same time to give the number of threads that are eager to run. # This value is only meaningful for systems that support the thread_concurrency () function (for example, Sun Solaris). # you can try using [CPU number]* (2..4) as the value of Thread_concurrency #**** (this property is not valid for the current environment) ****# thread_concurrency = 8# query buffering is often used to buffer the results of SELECT and The direct return result is no longer executed the next time the same query is performed. # Open query buffering can greatly increase the server speed if you haveA large number of identical queries and seldom modify tables. # Check the "qcache_lowmem_prunes" status variable to see if the current value is high enough for your load. Note: In situations where your table is constantly changing or if your query text is different every time, # query buffering may cause performance degradation rather than performance gains. Query_cache_size = 128m# Only results smaller than this setting are buffered # This setting is used to protect query buffering, preventing a large result set from overwriting all other query results. Query_cache_limit = 4m# is the smallest word length of the index for full-text indexing. # You may want to reduce it if you need to search for shorter words. Note After you modify this value, you need to rebuild your fulltext index Ft_min_word_len = 8# If your system supports the Memlock () function, you might want to turn this option on so that the running MySQL is in high memory tension, The data remains locked in memory and prevents possible swapping out# this option is useful for performance #memlock# when creating a new table as the default table type, # This value will be used if there is no special execution table type in the Create representation (this property is not valid for the current environment) ****# Default_table_type = The heap size used by the innodb# thread. The memory for this capacity is reserved on each connection. # MySQL does not usually need more than 64K of memory # If you use your own UDF functions that require a lot of heap, or if your operating system requires more heap for some operations, you may need to set it up a bit higher. Thread_stack = 512k# sets the default transaction isolation level. The available levels are as follows: # read-uncommitted, read-committed, repeatable-read, serializabletransaction_isolation = repeatable-read# the maximum size of the internal (in-memory) temporary table # If a table grows larger than this value, it will be automatically converted to a disk-based table. # This limit is for a single table, not a sum. Tmp_table_size = 128m# Open the binary log feature. # in Replication (replication) configuration, this key must be opened as Master Master server # If you need to do a point-in-time recovery from your last backup, you also need a binary log. log-bin=mysql-bin# If you are using a chained copy mode from the server structure (A-&GT B->C), # You need to open this item on Server B. # This option opens the log of the update that was re-made on the thread and writes it to the binary log from the server. #log_slave_updates # Open the full query log. All queries received by the server (even queries for an error syntax) will be recorded. This is useful for debugging, which is often turned off in a production environment. #log # Print out the warning to the error log file. If you have any problems with MySQL # You should open the warning log and review the error log carefully to isolate possible causes. #log_warnings # Record slow queries. A slow query is a query that consumes more time than the definition of "long_query_time". # If Log_long_format is opened, queries that do not use the index are also logged. # If you frequently add new queries to existing systems. In general this is a good idea, #log_slow_queries # Some use more than this time (in seconds) more queries will be considered as slow queries. # do not use "1″" here, otherwise it will cause all queries, even very fast query pages to be recorded (due to the accuracy of MySQL current time can only reach the second level). Long_query_time = 6# More information is logged in the slow log. # generally this is best opened. # Opening this entry will record that queries that do not use the index are also attached to the slow log as a slow query #log_long_format# This directory is used by MySQL to save temporary files. For example, # It is used to handle large disk-based sorting, as in internal sorting. # and a simple temporary table. # If you don't create very large temporary files, it might be better to put them on the Swapfs/tmpfs file system # Another option is that you can also place them on separate disks. # you can use ";" To place multiple paths # They will be polled in accordance with the Roud-robin method. #tmpdir =/tmp# * * * * master copy Related Settings # Unique service identification number, numeric value is between 1 and 2^32-1. # This value needs to be set on both master and slave. # if "Master-host" is not set, the default is 1, but if this option is omitted, MySQL will not take effect as master. Server-id = # copy of slave (remove the comment from the master segment to make it effective) # # In order to configure this host as a replicated slave server, you can choose between two methods: # 1) Use of CHANGE MASTER to command (full description in our manual) the-# syntax is as follows: # change MASTER to master_host=, master_port=,# master_user=, master_password=; # # You need to replace, wait for the field surrounded by angle brackets and the port number to use master (default 3306). # # Example: # change MASTER to Master_host= ' 125.564.12.1′, master_port=3306,# master_user= ' Joe ', master_password= ' secret '; # or # 2) set the following variables. Anyway, in case you choose this method, and then first start the copy (even if unsuccessful, # for example if you enter the wrong password in the Master-password field and slave cannot connect), # Slave creates a master.info file, And then any changes to the parameters contained within this file will be ignored # and overwritten by the contents of the Master.info file, unless you close the slave service, delete the master.info and restart the slave service. # for this reason, you might not want to touch the configuration (commented out) and use change MASTER to (view above) instead of # # The unique ID number required between 2 and 2^32–1 # (and unlike MASTER) # if Master-host is set The default value is * * But if omitted, it will not take effect #server-id = 2## in the replication structure master– must #master-host =## when connecting to master slave the username used for authentication – must #master-user =## when connected The password that slave used to authenticate when it was received on master – must #master-password the port =## master listens on. # optional – The default is 3306#master-port =# makes slave read-only. Only the user has super privileges and the slave thread above can modify the data. # You can use this to ensure that no application accidentally modifies slave instead of the data on Master #read_only#*** MyISAM related options # keyword buffer size, commonly used to buffer the index blocks of MyISAM tables. # don't set it larger than you can use inside30%,# because part of the memory is also used by the OS to buffer line data # Even if you do not use the MyISAM table, you will still need to set up 8-64m memory because it will also be used by the internal temporary disk table. # key_buffer_size = 128m# is used to do the buffer size of the full table scan of the MyISAM table. # When a full table scan is required, allocate it in the corresponding thread. # read_buffer_size = 8m# When a row is read from a sorted sequence after sorting, the line data is Read from this buffer to prevent disk seek. # If you increase this value, you can improve the performance of many ORDER by. # read_rnd_buffer_size = 64m# MyISAM uses a special tree-like cache to make burst insertion # ( These insertions are, insert ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) faster. # This variable limits the number of bytes in the buffer tree per process. # set to 0 turns off this optimization. # for optimization do not set this value greater than "Key_buffer_size". # When a burst insert is This buffer is allocated when detected. # bulk_insert_buffer_size = 256m# This buffer when MySQL needs to be allocated in REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE into an empty table causing the rebuild index to be assigned. # This is assigned in each thread. So be careful when setting a large value. # myisam_sort_buffer_size = 256m# the size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA I NFILE). # If the file size is larger than this value, the index is created by the key buffer (slower) # myisam_max_sort_file_size = 10g# Use the key-value buffering method if the temporary file used to create the index using a faster index is greater than the set value. # This is primarily used to force a long string key in a large table to use a slow key-value buffering method to create an index. # myisam_max_extra_sort_file_size = 10g# If a table has more than one index, MyISAM You can use more than one thread to fix them by using parallel sorting. # This is a good choice for users with multiple CPUs and large memory conditions. # Myisam_repair_threads = # automatic check and fix MyISAM table without proper shutdown. # myisam_recover# default off federated# skip-federated# * * * BDB related options ***# if you run my This option is used when the SQL Service has BDB support but you are not ready to use it. This saves memory and may speed things up. #**** (this property is not valid for the current environment) * * * * * * * * INNODB related options ***# If your MySQL service contains INNODB support but not intended to be used, # Using this option saves memory and disk space, and accelerates portions of #skip-innodb# additional memory pools that are innodb used to hold metadata information (no longer recommended in 5.6) # If InnoDB needs more memory for this purpose, it will start from OS Apply for memory here. # because this operation is fast enough on most modern operating systems, you generally do not need to modify this value. # show INNODB The STATUS command displays the amount yourselves is used. #**** (this property is not valid for the current environment) * * * #innodb_ Additional_mem_pool_size = 64m# InnoDB uses a buffer pool to hold indexes and raw data, unlike myisam.#, where you set the larger, which ensures that you use memory instead of the hard disk for most read operations, The less disk I/O is required to access the data in the table. # on a standalone database server, you can set this variable to the server physical memory size of the 80%# do not set too large, otherwise, due to the competition of physical memory may cause the operating system to change the page bumps. # Note on 32-bit systems you may be limited to 2-3.5g user-level memory limits on each process, # so don't set it too high. Innodb_buffer_pool_size = 6g# InnoDB saves data in one or more data files as a tablespace. # If you only have a single logical drive to save your data, a single self-increment file is good enough. # in other cases. A file Per device is generally a good choice. # You can also configure InnoDB to use bare disk partitioning – please refer to the manual for more information Innodb_data_file_ Path = ibdata1:10m:autoextend# Set this option if you want the InnoDB tablespace file to be saved in a different partition. # is saved in the MySQL DataDir by default. #innoDb_data_home_dir =# The number of IO threads used to synchronize IO operations. # This value is hardcoded to 8 under UNIX, but the Windows disk I/O may perform better under a large value. #innodb_file_io_threads = 8# If you find that the InnoDB tablespace is corrupted, setting this value to a value other than 0 may help you to export your table. # starting from 1 and increasing this value know you can successfully export the table. #innodb_force_recovery =1# The number of threads allowed within the InnoDB core. Optimal values depend on how the application, hardware, and operating system are scheduled. # High values can cause mutually exclusive bumps on threads. innodb_thread_concurrency = 16# If set to 1, InnoDB flushes (fsync) the transaction log to disk after each commit, # This provides complete ACID behavior. # If you are willing to compromise on transactional security and you are running a small thing, you can set this value to 0 or two to reduce the disk caused by the transaction log i/o# 0 represents the log only about every second written to the log file and the log file is flushed to disk. # 2 indicates that the log is written to the log file after each commit, but the log file is flushed to disk only about every second. Innodb_flush_log_at_trx_commit = * (Note: If this is a game server, this value is set to 2; if it is a very high data security application, The recommended setting is 1 #设置为0性能最高, but if a failure occurs, the data may be at risk of loss! #默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入 (flush) hard drive, which is time-consuming. #特别是使用电池供电缓存 (Battery backed up cache). #设成2对于很多运用, especially from the MyISAM table, is yes, it means writing to the system cache instead of writing to the hard disk. #日志仍然会每秒flush到硬盘, so you generally don't lose more than 1-2 seconds of updates. #设成0会更快一点, but the security aspect is poor, even if MySQL hangs up may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung. ) # accelerates the InnoDB shutdown. This prevents InnoDB from doing a full purge and inserting a buffer merge when it shuts down. # This can greatly increase the shutdown time, but instead the InnoDB may do so at the next boot. #innodb_fast_shutdown # The size of the buffer used to buffer the log data. # When this value is nearly full, InnoDB will have to flush the data to disk. # due to the basicIt is refreshed once per second, so it is not necessary to set this value too large (even for long transactions) Innodb_log_buffer_size = 16m# the size of each log file in the log group. # You should set the total size of the log file to the size of your buffer pool 25%~100%# To avoid unnecessary buffer pool refresh behavior in the log file overwrite. # Regardless, note that a large log file size increases the time that is required to recover the process. Innodb_log_file_size = 512m# The total number of files in the log group. # Generally speaking 2~ 3 is better. Innodb_log_files_in_group = 3# The location of the log file for InnoDB. The default is MySQL datadir.# you can assign it to a standalone hard disk or to a RAID1 volume to improve its performance #innodb_log_group_home_dir# the maximum allowable dirty pages in the InnoDB buffer pool. # If the limit is reached, InnoDB will start refreshing them to prevent them from interfering with the Clean data page. # This is a soft limit and is not guaranteed to be absolutely executed. innodb_max_dirty_pages_pct = 90# InnoDB The method used to refresh the log. # tablespace always uses the double write Refresh method # The default value is "Fdatasync" and the other is "O_dsync". # in general, if you have a hardware RAID controller and its standalone cache has a write-back mechanism and has a battery power-down protection, you should set the configuration to o_direct# otherwise, In most cases it should be set to fdatasync#innodb_flush_method=fdatasync# before the rollback, a INNODB transaction should wait for how long a lock is approved. # INNODB automatically detects the transaction deadlock and rolls back the transaction in its own lock table . # If you use the lock TABLES directive, or use a storage engine other than InnoDB for transaction security in the same transaction # then a deadlock can occur and InnoDB cannot notice. # This timeout value is very helpful in solving this problem. In Nodb_lock_wait_timeout = 120# This setting tells InnoDB if the data and indexes of all tables need to be stored in the shared table space (innodb_file_per_table = OFF) Or the data for each table is placed separately in an. ibd file (innodb_file_per_table = ON) # one file per table allows you to reclaim disk space in drop, truncate, or Rebuild tables # This is also necessary for some advanced features, such as data compression, but it does not yield any performance gains innodb_file_per_table = on[ mysqldump]# do not cache the entire result in memory before it is written to disk. This is required when exporting very large tables Quickmax_allowed_packet = 32m[mysql]no-auto-rehashdefault-character-set=utf8# only allows UPDATEs with key values and Deletes. Safe-updates[myisamchk]key_buffer = 16msort_buffer_size = 16mread_buffer = 8mwrite_buffer = 8M[mysqlhotcopy] interactive-timeout[mysqld_safe]# increase the number of open files per process. # Warning: Verify that you have set the full system limit high enough!# to open a large number of tables you need to set this value to large Open-files-limit = 8192## MySQL Server #[client]default-character-set=utf8

　　

Configuration Parameters Note:

1. Innodb_log_file_size
2. DataDir

One, MySQL installation