Mysql-5.6.31 installation (Single Instance Linux), mysql-5.6.31linux

Mysql-5.6.31 installation (Single Instance Linux), mysql-5.6.31linux

Installation version: mysql-5.6.31

Installation environment: Linux RHEL6.5.x86

Installation requirements: Single Instance, default port: 3306
(1) require installation of Mysql database version and package name: mysql-5.6.31.tar.gz
(2) The installation directory is/usr/local/mysql.
(3) The data storage directory is/data/mysqldb/
(4) the configuration file is stored as/etc/my. cnf.
(5) The user name is mysql.
(6) environment variables must be set and services must be automatically started upon startup.

 

Environment preparation

1.Install related dependency packages

# yum install gcc* ncurses-devel perl cmake bison

 

2.Create users and related directories

Create a mysql user group
# Groupadd mysql

Create a mysql user (the group is mysql and cannot log on to shell) # useradd mysql-g mysql-s/sbin/nologin

Create the mysql installation directory # mkdir-p/usr/local/mysql
Create a mysql DATA DIRECTORY
# Mkdir-p/data/mysqldb

Create the BINLOG log directory # mkdir/data/mysqldb/binlog under the data Directory

Create a daily LOG directory in the data directory # mkdir/data/mysqldb/log

Create a temporary mysql table directory # mkdir/opt/mysqltmp

 

3.Compile and install

# Cmake
Set the mysql installation directory
-DCMAKE_INSTALL_PREFIX =/usr/local/mysql/

Set the listening socket path, which must be an absolute path name. The default value is/tmp/mysql. sock.
-DMYSQL_UNIX_ADDR =/tmp/mysql. sock

Set the configuration file to the/etc/directory
-DSYSCONFDIR =/etc

Set the character set of the server (by default, MySQL uses the latin1 (CP1252 Western Europe) Character Set. The cmake/character_sets.cmake file contains a list of allowed character set names)
-DDEFAULT_CHARSET = gbk

Set server sorting rules
-DDEFAULT_COLLATION = gbk_chinese_ci

Set the mysql database file directory
-DMYSQL_DATADIR =/data/mysqldb/

Set the listening port of the mysql server. The default value is 3306.
-DMYSQL_TCP_PORT = 3306

The following storage engine options:
YISAM, MERGE, MEMORY, and CSV engines are compiled to the server by default and do not need to be installed explicitly.
Statically compile a storage engine to the server and use-DWITH_engine_STORAGE_ENGINE = 1
Available storage engine values include ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), and PERFSCHEMA (Performance Schema)
-DWITH_INNOBASE_STORAGE_ENGINE = 1
-DWITH_ARCHIVE_STORAGE_ENGINE = 1
-DWITH_BLACKHOLE_STORAGE_ENGINE = 1
-DWITH_FEDERATED_STORAGE_ENGINE = 1
-DWITH_PARTITION_STORAGE_ENGINE = 1

# make ; make install

 

Note: If you want to re-run the configuration, delete the cmakecache.txt file in the decompressed package.

# ls mysql-5.6.31 |grep CMakeCache.txtCMakeCache.txt

 

 

Initialization and Configuration

1.Authorize related directories

# chown  mysql:mysql  /usr/localmysql  -R# chown  mysql:mysql  /data            -R# chown  mysql:mysql  /opt/mysqltmp    -R

 

2.Initialization

Start initialization #/usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql/-- datadir =/data/mysqldb/See the data directory what is generated # ls/data/mysqldb/binlog ibdata1 ib_logfile0 ib_logfile1 log mysql performance_schema test copy the mysql Service Startup Script and modify the permissions # cp/usr/local/mysql/support-files/mysql. server/etc/init. d/mysqld # chmod 755/etc/init. d/mysqld

Modify the mysql Service Startup Script (this step is not required for single-instance installation) # vim/etc/init. d/mysqldbasedir =/usr/local/mysqldatadir =/data/mysqldb

 

3.Configuration File

My is generated under/etc by default. cnf # ls/etc/| grep my. cnfmy. cnf if not, you can copy the default profile mysql-5.6.31/support-files/my-default.cnf/usr/local/mysql/support-files/my-default.cnf In the squeeze package or installation directory

### Configuration File Parsing ###

# Vim/etc/my. cnf [client] port = 3306 // port number connected by the client socket =/tmp/mysql. sock // The storage location of the sock file connected to the Client [mysqld] # baseport = 3306 // default port number of mysql, you can modify user = mysql // mysql user to specify socket =/tmp/mysql. sock // port used for connection pid-file =/tmp/mysql. pid // main PID file basedir =/usr/local/mysql // Mysql installation directory datadir =/data/mysqldb // Mysql data DIRECTORY tmpdir =/opt/mysqltmp/ /Mysql temporary table directory open_files_limit = 10240 // The number of opened file handles explicit_defaults_for _ Timestamp SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLESfederated // supports SQL syntax and data verification. SQL _mode three modes ANSI mode: loose mode, verify the inserted data. If the data type or length does not meet the defined type or length, adjust or save the data type, and report the "warning" warning TRADITIONAL mode: strict mode: when data is inserted into mysql, strict data validation is performed to ensure that data cannot be inserted and an error is reported. When used for transactions, transaction rollback is performed. STRICT_TRANS_TABLES mode: strictly checks the data. The error data cannot be inserted and an error is reported. server_id = 706 // unique service identification number for master-slave replication, the value ranges from 1 to # replicate-do-db = posp // defines the database for unique replication # replicate-ignore-db = mysql // defines Unique database # log-slave-updates = 1 // This option is followed by master-slave replication. Enable event_scheduler = 1 // The Time Scheduler max_connections = 2000 // The maximum number of concurrent connections, to increase this value, you need to increase the number of file descriptors that can be opened. max_connect_errors = 10000 // if the connection error initiated by a user exceeds this value, interactive_timeout = 600 // number of seconds waiting for activity before the server closes the interactive connection wait_timeout = 600 // number of seconds waiting for activity before the server closes the non-interactive connection skip- name-resolve // garnt, the ip address is required, and the host name cannot be used. Disabling DNS resolution sync_binlog = 0 // ensures transaction security. The default value is 0log_bin_trust_function_creators = 1 // open the mysql customizable function character-set-server = utf8 // set the character set default_storage_engine = InnoDB // define the default engine # loglog-bin =/data/ mysqldb/binlog/mysql-bin // specify binlog binary log file name binlog_cache_size = 32 m // binlog cache size max_binlog_cache_size = 10g // set the maximum binlog cache binlog_stmt_cache_size = 32 m // set the minimum binlo cache table_open_cache = 2048 // The table descriptor cache size, reduce the number of times the file is opened/closed max_binlog_size = 1024 m // set binlog The maximum file size is 1Gbinlog_format = mixed // binlog log format. log_output = FILE // confirm the output to the log FILE log-error =/data/mysqldb/log/mysql-error.log // mysql system error log output slow_query_log = 1 // open the slow log record slow_query_log_file = /data/mysqldb/log/mysql-slow_query.log // define the path of slow log output general_log = 0 // define common log general_log_file =/data/mysqldb/log/mysql-general-query.log // define common log output path expire-logs-days = 30 // The log retention time is 30 days relay-log =/data/mysqldb/binlog/r Elay-bin // define the reloa_log copy address relay-log-index =/data/mysqldb/binlog/relay-bin.index // define reloa_log index # buffersort_buffer_size = 2 m // The MyISAM table has changed the buffer required for re-sorting. Usually 64 m is enough read_buffer_size = 2 m // the buffer size used for full table scan in the MyISAM table. read_rnd_buffer_size = 2 m // when a row is read from a sorted sequence, the row data will be read from this buffer to prevent the disk from seeking join_buffer_size = 2 m // when InnoDB is used to cache data and net_buffer_length = 16 k // when mysqldump is executed, the maximum length of net buffer is 16 Mb. The default value is 1Mbmax_allowed_packet = 512 Mb. // the size of each connection is independent. the size of bulk_insert_buffer_size is dynamically increased to 32 m // when burst insertion is detected, the buffer will be allocated for myisammax_heap_table_size = 512 m // the size of the memory table tmp_table_size = 512 m // internal (in memory)) The maximum size of a temporary table is thread_cache_size = 100 // The number of reusable threads in the cache. You can adjust query_cache_size = 256 m // to specify the buffer size of the MySQL query result. You can adjust the query_cache_limit = 10 m // The maximum number of result sets of a single SQL statement. The default value is 4 kb. You can adjust query_cache_min_res_unit = 4kkey_buffer_size = 16 m // the buffer size of the keyword. this parameter is generally used to buffer the index block of the MyISAM table. myisam_sort_buffer_size = 64 m // allocated in each thread. therefore, you must be careful when setting the delimiter myisam_max_sort_file_size = 10g.
// The maximum temporary file size allowed by MySQL during index reconstruction. If the file size is larger than this value, the index will be created through the key value buffer (slower) myisam_repair_threads = 1 // If a table has more than one index, MyISAM # innodbinnodb_file_per_table = 1 // you can change InnoDB to the independent tablespace mode, each table in each database generates a data space innodb_data_file_path = ibdata1: 2048 M: autoextend // if you only have a single logic driver to save your data, A single auto-increment file is enough for innodb_log_file_size = 128 m // the size of each log file in the log group, innodb_log_files_in_group = 3 // the total number of files in the log group. generally, 2 ~ 3 is a relatively good innodb_buffer_pool_size = 1g // innodb_buffer_pool_instances =-1innodb_max_dirty_pages_pct = 70 // maximum allowable proportion of dirty pages in the innodb buffer pool. every 60-90 can be # innodb_thread_concurrency = 8innodb_flush_method = O_DIRECTinnodb_log_buffer_size = 16 m // the buffer size used to buffer log data. When this value is full, innoDB must refresh the data to the disk. innodb_flush_log_at_trx_commit = 2 // 0 indicates that the log is only written to the log file every second and the log file is refreshed to the disk. 2 indicates that the log is written to the log file after each submission, however, log files are refresh to the disk only every second. [mysql] no-auto-rehash // You can use the tab key to complete prompt = (\ u @ \ h) in the command) [\ d] \_// display the host name in the mysql Command default-character-set = utf8 // set the character set combination

 

 

Service Startup and environment variable settings

1.Set Environment Variables

# vim /etc/profileMYSQL=/usr/local/mysql/binPATH=$PATH:$MYSQLexport PATH# source /etc/profile 

 

2.Start the service

# /etc/init.d/mysqld start

 

For the startup error and solution, see the blog: mysql startup error (1) -- two examples