Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to install mysql5.7, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

I. Software download

Log in to http://dev.mysql.com/downloads/mysql/ and select the corresponding version to download.

Second, create mysql users

[root@localhost ~] # groupadd mysql

[root@localhost ~] # useradd-g mysql mysql

[root@localhost ~] # passwd mysql

Copy the tar package to the home directory of mysql, and rename the directory to mysql-5.7.9 after decompression

Third, install the database

Enter / mysql/mysql5.7/bin

[mysql@localhost bin] $. / mysql_install_db-- user=mysql-- basedir=/mysql/mysql5.7-- datadir=/mysql/mysql5.7/data

2017-04-19 06:49:00 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld-initialize

2017-04-19 06:49:07 [ERROR] The bootstrap log isn't empty:

2017-04-19 06:49:07 [ERROR] 2017-04-19T14:49:01.852039Z 0 [Warning]-- bootstrap is deprecated. Please consider using-initialize instead

2017-04-19T14:49:01.868000Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2017-04-19T14:49:01.868056Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2017-04-19T14:49:01.888730Z 0 [ERROR] Can't read from messagefile'/ usr/share/mysql/english/errmsg.sys'

Prompt that the mysql_install_db command is deprecated and use mysqld

[mysql@localhost bin] $. / mysqld-- user=mysql-- basedir=/mysql/mysql5.7-- datadir=/mysql/mysql5.7/data

[root@webtest1 mysql] # bin/mysqld-initialize-user=mysql-basedir=/mysql/mysql5.7-datadir=/mysql/mysql5.7/data

2017-04-19T14:50:18.145225Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2017-04-19T14:50:18.145526Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2017-04-19T14:50:18.797964Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2017-04-19T14:50:18.798019Z 0 [Warning] Insecure configuration for-- secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.

2017-04-19T14:50:18.798053Z 0 [Note]. / mysqld (mysqld 5.7.9) starting as process 3578.

2017-04-19T14:50:18.798104Z 0 [ERROR] Can't find error-message file'/ mysql/mysql5.7share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.

2017-04-19T14:50:18.802586Z 0 [Note] InnoDB: PUNCH HOLE support not available

2017-04-19T14:50:18.802645Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-04-19T14:50:18.802656Z 0 [Note] InnoDB: Uses event mutexes

2017-04-19T14:50:18.802665Z 0 [Note] InnoDB: GCC builtin _ _ sync_synchronize () is used for memory barrier

2017-04-19T14:50:18.802674Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-04-19T14:50:18.802682Z 0 [Note] InnoDB: Using Linux native AIO

2017-04-19T14:50:18.803153Z 0 [Note] InnoDB: Number of pools: 1

2017-04-19T14:50:18.803868Z 0 [Note] InnoDB: Using CPU crc32 instructions

2017-04-19T14:50:18.843406Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128m, instances = 1, chunk size = 128m

2017-04-19T14:50:18.860507Z 0 [Note] InnoDB: Completed initialization of buffer pool

2017-04-19T14:50:18.864349Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority ().

2017-04-19T14:50:18.878937Z 0 [Note] InnoDB: Highest supported file format is Barracuda.

2017-04-19T14:50:18.901395Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2017-04-19T14:50:18.901629Z 0 [Note] InnoDB: Setting file'. / ibtmp1' size to 12 MB. Physically writing the file full; Please wait...

2017-04-19T14:50:18.943961Z 0 [Note] InnoDB: File'. / ibtmp1' size is now 12 MB.

2017-04-19T14:50:18.945376Z 0 [Note] InnoDB: 96 redo rollback segment (s) found. 96 redo rollback segment (s) are active.

2017-04-19T14:50:18.945403Z 0 [Note] InnoDB: 32 non-redo rollback segment (s) are active.

2017-04-19T14:50:18.952368Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2471242

2017-04-19T14:50:18.974882Z 0 [Note] InnoDB: Loading buffer pool (s) from / mysql/mysql5.7/data/ib_buffer_pool

2017-04-19T14:50:18.976665Z 0 [Note] InnoDB: Buffer pool (s) load completed at 151119 6:50:18

2017-04-19T14:50:18.978694Z 0 [Note] InnoDB: not started

2017-04-19T14:50:18.978878Z 0 [Note] Plugin 'FEDERATED' is disabled.

2017-04-19T14:50:18.983412Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2017-04-19T14:50:18.984289Z 0 [Warning] CA certificate ca.pem is self signed.

2017-04-19T14:50:19.003696Z 0 [Note] Server hostname (bind-address):'*'; port: 3306

2017-04-19T14:50:19.021246Z 0 [Note] IPv6 is available.

2017-04-19T14:50:19.021291Z 0 [Note] -':: 'resolves to'::

2017-04-19T14:50:19.021299Z 0 [Note] Server socket created on IP:':'.

2017-04-19T14:50:19.037949Z 0 [ERROR] Could not create unix socket lock file / var/lib/mysql/mysql.sock.lock.

2017-04-19T14:50:19.037976Z 0 [ERROR] Unable to setup unix socket lock file.

2017-04-19T14:50:19.037982Z 0 [ERROR] Aborting

2017-04-19T14:50:19.038004Z 0 [Note] Binlog end

2017-04-19T14:50:19.038084Z 0 [Note] Shutting down plugin 'ngram'

2017-04-19T14:50:19.038094Z 0 [Note] Shutting down plugin 'ARCHIVE'

2017-04-19T14:50:19.038098Z 0 [Note] Shutting down plugin 'partition'

2017-04-19T14:50:19.038102Z 0 [Note] Shutting down plugin 'BLACKHOLE'

2017-04-19T14:50:19.038106Z 0 [Note] Shutting down plugin 'CSV'

2017-04-19T14:50:19.038112Z 0 [Note] Shutting down plugin 'MEMORY'

2017-04-19T14:50:19.038118Z 0 [Note] Shutting down plugin 'MRG_MYISAM'

2017-04-19T14:50:19.038124Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'

2017-04-19T14:50:19.038128Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'

2017-04-19T14:50:19.038131Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'

2017-04-19T14:50:19.038135Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'

2017-04-19T14:50:19.038138Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'

2017-04-19T14:50:19.038142Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'

2017-04-19T14:50:19.038145Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'

2017-04-19T14:50:19.038149Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'

2017-04-19T14:50:19.038152Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'

2017-04-19T14:50:19.038155Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'

2017-04-19T14:50:19.038159Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'

2017-04-19T14:50:19.038162Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'

2017-04-19T14:50:19.038165Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'

2017-04-19T14:50:19.038168Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'

2017-04-19T14:50:19.038172Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'

2017-04-19T14:50:19.038175Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'

2017-04-19T14:50:19.038179Z 0 [Note] Shutting down plugin 'INNODB_METRICS'

2017-04-19T14:50:19.038182Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'

2017-04-19T14:50:19.038186Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'

2017-04-19T14:50:19.038189Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'

2017-04-19T14:50:19.038192Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'

2017-04-19T14:50:19.038196Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'

2017-04-19T14:50:19.038199Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'

2017-04-19T14:50:19.038203Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'

2017-04-19T14:50:19.038206Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'

2017-04-19T14:50:19.038210Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'

2017-04-19T14:50:19.038213Z 0 [Note] Shutting down plugin 'INNODB_CMP'

2017-04-19T14:50:19.038217Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'

2017-04-19T14:50:19.038221Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'

2017-04-19T14:50:19.038224Z 0 [Note] Shutting down plugin 'INNODB_TRX'

2017-04-19T14:50:19.038228Z 0 [Note] Shutting down plugin 'InnoDB'

2017-04-19T14:50:19.038317Z 0 [Note] InnoDB: FTS optimize thread exiting.

2017-04-19T14:50:19.038421Z 0 [Note] InnoDB: Starting shutdown...

2017-04-19T14:50:19.151526Z 0 [Note] InnoDB: Dumping buffer pool (s) to / mysql/mysql5.7/data/ib_buffer_pool

2017-04-19T14:50:19.152515Z 0 [Note] InnoDB: Buffer pool (s) dump completed at 151119 6:50:19

2017-04-19T14:50:20.802442Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2471261

2017-04-19T14:50:20.811473Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2017-04-19T14:50:20.811528Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'

2017-04-19T14:50:20.811690Z 0 [Note] Shutting down plugin 'MyISAM'

2017-04-19T14:50:20.811730Z 0 [Note] Shutting down plugin 'sha256_password'

2017-04-19T14:50:20.811739Z 0 [Note] Shutting down plugin 'mysql_native_password'

2017-04-19T14:50:20.812061Z 0 [Note] Shutting down plugin 'binlog'

2017-04-19T14:50:20.813161Z 0 [Note]

During the installation of mysql5.7.17, the initialization parameter uses-- initialize-insecure, so the initialization root password is not set. If it is-- initialize, a password is randomly generated, which is displayed at the end of the installation process.

2017-04-19T14:50:20.813161Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2800d0e1-a3ec-11e7-a082-005056bb44b1.

2017-04-19T14:50:20.813161Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-04-19T14:50:20.813161Z 1 [Note] A temporary password is generated for root@localhost: VfAG=aal=8;/

IV. Configuration file

[root@localhost ~] # cd / mysql/mysql5.7/support-files/

[root@localhost support-files] # cp my-default.cnf / etc/my.cnf

Cp: overwrite `/ etc/my.cnf'? Yes

[root@localhost support-files] # cp mysql.server / etc/init.d/mysql

[root@localhost ~] # vim / etc/init.d/mysql

Basedir=/mysql/mysql5.7

Datadir=/mysql/mysql5.7/data

Fifth, configure environment variables

[mysql@localhost ~] $vi .bash _ profile

PATH=$PATH:$HOME/bin

# .bash _ profile

# Get the aliases and functions

If [- f ~ / .bashrc]; then

. ~ / .bashrc

Fi

# User specific environment and startup programs

Export MYSQL_HOME= "/ mysql/mysql5.7"

Export PATH= "$PATH:$MYSQL_HOME/bin"

~

Add boot startup service

Chkconfig-add mysql

Chkconfig mysql on

7. Start mysql

[root@localhost support-files] # su-mysql

Error 1

[mysql@localhost ~] $service mysql start

/ etc/init.d/mysql: line 256: my_print_defaults: command not found

Starting MySQL ERROR! Couldn't find MySQL server (/ mysql/mysql5.7/bin/mysqld_safe)

Error was reported in startup. It turned out that there was an error in editing / etc/init.d/mysql file. Change it to the following

Vim / etc/init.d/mysql

Basedir=/mysql/mysql5.7

Datadir=/mysql/mysql5.7/data

[mysql@localhost ~] $service mysql start

Starting MySQL. SUCCESS!

Start again successfully

Error 2

[mysql@goldqua1 ~] $service mysqld start

Starting MySQL.2017-04-19T02:28:36.399450Z mysqld_safe The file / usr/local/mysql/bin/mysqld

Does not exist or is not executable. Please cd to the mysql installation

Directory and restart this script from there as follows:

. / bin/mysqld_safe&

See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

The server quit without updating PID file (/ mysql/mysql5.7/data/goldqua1.pid). [FAILED]

Description: the mysqld_safe startup script reads another startup script mysqld from the / usr/local/mysql directory by default, because my installation directory is / mysql/mysql5.7product/5.6/mysql-1. So I couldn't find the relevant documents. It can be solved from two aspects.

Method 1: create a link file in / usr/local/mysql

1.mkdir-p / usr/local/mysql/bin

2.ln-s / mysql/mysql5.7/bin/mysqld / usr/local/mysql/bin/mysqld

Method 2: change all / usr/local/mysql directories in mysqld_safe to your actual installation directory

1.sed-I's Universe Universe usrUniverse mysql/mysql5.7product/5.6/mysql-1/bin/mysqld_safe MyqlUniverse Myql Universe MySQL 5.7productUniverse 5.6Universe MySQL Universe 1ql

Started successfully again.

Error 3

Mysql > show databases

ERROR 1820 (HY000): Unknown error 1820

Mysql > use mysql

ERROR 1820 (HY000): Unknown error 1820

Mysql > show databases

ERROR 1820 (HY000): Unknown error 1820

Here, no matter how it is executed, it will report an error, which is very strange, and later found that it is necessary to change the password first.

Mysql > set password=password ('(Kl1b4X*'))

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

+-+

4 rows in set (0.00 sec)

Initial password:

[root@localhost bin] # cat / root/.mysql_secret

# Password set for user 'root@localhost' at 2016-06-01 15:23:25

, xxxxxR5H9

[root@localhost bin] #. / mysql-uroot-p

Eighth, the methods to deal with the faults on the network:

1. Fault phenomenon

[root@mysqltest mysqldata] # mysql-uroot-p123456

Warning: Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ data/mysqldata/mysql.sock'

# failure environment

[root@mysqltest mysqldata] # more / etc/issue

CentOS release 5.9 (Final)

Kernel\ r on an\ m

2. Fault analysis

# View the status of mysql instances

[root@mysqltest mysqldata] # netstat-ntlp | grep 3306

Tcp 00: 3306: * LISTEN 13001/mysqld

# View the configuration of socket in my.cnf

[root@mysqltest mysqldata] # more / etc/my.cnf | grep sock

Socket = / tmp/mysql.sock

# as can be seen from the above, the definition in my.cnf is under the / tmp directory, and the error prompt is under the / data/mysqldata/ directory

# that is, mysqld has claimed the correct sock file, but the client connection still looks for the sock file from the original directory

# check the background log below. There is an ERROR about the full query log. It is an error caused by the non-existence of the directory and has nothing to do with the current failure.

[root@mysqltest mysqldata] # more mysqltest.err

.

2014-10-11 13:17:21 13001 [Note] InnoDB: 5.6.12 started; log sequence number 1625997

/ app/soft/mysql/bin/mysqld: File'/ log/mysql_logs/slowquery.log' not found (Errcode: 2-No such file or directory)

2014-10-11 13:17:21 13001 [ERROR] Could not use / log/mysql_logs/slowquery.log for logging (error 2). Turning logging off for the who

Le duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2014-10-11 13:17:21 13001 [Note] Server hostname (bind-address):'*'; port: 3306

2014-10-11 13:17:21 13001 [Note] IPv6 is available.

2014-10-11 13:17:21 13001 [Note] -:: 'resolves to'::'

2014-10-11 13:17:21 13001 [Note] Server socket created on IP:':'

2014-10-11 13:17:21 13001 [Note] Event Scheduler: Loaded 0 events

2014-10-11 13:17:21 13001 [Note] / app/soft/mysql/bin/mysqld: ready for connections.

Version: '5.6.12 socket:' / tmp/mysql.sock' port: 3306 Source distribution

3. Solve the problem

A. Solve the problem by configuring my.cnf mysql option socket file location

# stop the mysql server first

[root@mysqltest mysqldata] # service mysqld stop

Shutting down MySQL. [OK]

# modify my.cnf as follows

[root@mysqltest mysqldata] # vi / etc/my.cnf

[mysql]

No-auto-rehash

Socket = / tmp/mysql.sock # add the line

# restart mysql server

[root@mysqltest mysqldata] # service mysqld start

Starting MySQL.. [OK]

# connection is normal again

[root@mysqltest mysqldata] # mysql-uroot-p

Enter password:

Mysql > show variables like 'version'

+-+ +

| | Variable_name | Value |

+-+ +

| | version | 5.6.12-log |

+-+ +

B. Establish a link for socket files

[root@mysqltest mysqldata] # ln-s / tmp/mysql.sock / data/mysqldata/mysql.sock

Ln: creating symbolic link `/ data/mysqldata/mysql.sock' to` / tmp/mysql.sock': File exists

[root@mysqltest mysqldata] # rm mysql.sock # indicates that the file exists, so delete the previous mysql.sock file

[root@mysqltest mysqldata] # ln-s / tmp/mysql.sock / data/mysqldata/mysql.sock

[root@mysqltest mysqldata] # ls-hltr mysql.sock

Lrwxrwxrwx 1 root root 15 Oct 11 14:00 mysql.sock-> / tmp/mysql.sock

[root@mysqltest mysqldata] # mysql-uroot-p

Enter password:

Mysql > show variables like 'socket'

+-+ +

| | Variable_name | Value |

+-+ +

| | socket | / tmp/mysql.sock |

+-+ +

IX. Mysql undo space description

[ERROR] InnoDB: Expected to open 4 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

Reason for reporting an error:

The innodb_undo_tablespaces parameter was not specified when initializing the database

Solution:

Write the-defaults-file parameter when initializing the database, and write the corresponding innodb_undo_tablespaces parameter in the corresponding configuration file

[mysql@localhost mysql_data] $vi / tmp/my.cnf

# # InnoDB engine undo log related setting

Innodb_undo_directory = / u01/mysql_data

Innodb_undo_tablespaces = 4

[mysql@localhost percona] $bin/mysqld-- defaults-file=/tmp/my.cnf-- user=mysql-- initialize-insecure

Pay attention to the writing of the initialization database statement. Putting-- defaults-file at the end of the statement will cause the parameters in the configuration file to not take effect.

10. Parameter description

# MySql5.7 profile my.cnf Settings

[client]

Port = 3306

Socket = / tmp/mysql.sock

[mysqld]

# # basic Settings # #

# unique number of Mysql services. Each mysql service Id should be unique.

Server-id = 1

# Service port number defaults to 3306

Port = 3306

# mysql installation root directory

Basedir = / opt/mysql

# location of mysql data file

Datadir = / opt/mysql/data

# temporary directories such as load data infile will be used

Tmpdir = / tmp

# set the directory where the socke file is located

Socket = / tmp/mysql.sock

# is mainly used for MyISAM storage engine. If multiple servers connect to a database, it is recommended to comment on the following

Skip-external-locking

# can only check the login of the client with the IP address, not the host name

Skip_name_resolve = 1

# transaction isolation level, which defaults to repeatable reading, while MySQL defaults to repeating read level (under this level, there may be a lot of gap locks, which may affect performance)

Transaction_isolation = READ-COMMITTED

# database default character set, mainstream character set supports some special emoticons (special emoticons occupy 4 bytes)

Character-set-server = utf8mb4

# the database character set corresponds to some sorting and other rules, and be careful to correspond to character-set-server

Collation-server = utf8mb4_general_ci

# set the character set when client connects to mysql to prevent garbled codes

Init_connect='SET NAMES utf8mb4'

# whether it is case-sensitive to sql statements, 1 means insensitive

Lower_case_table_names = 1

# maximum number of connections

Max_connections = 400

# maximum number of error connections

Max_connect_errors = 1000

# TIMESTAMP allows null values if the declaration NOT NULL is not displayed

Explicit_defaults_for_timestamp = true

# the size of the SQL packet sent. If there is a BLOB object, it is recommended to modify it to 1G.

Max_allowed_packet = 128m

# MySQL connection will be forcibly closed after it has been idle for more than a certain period of time (in seconds)

# the default wait_timeout value of MySQL is 8 hours, and the interactive_timeout parameter needs to be configured at the same time to take effect.

Interactive_timeout = 1800

Wait_timeout = 1800

# the maximum value of the internal memory temporary table, set to 128m.

# such as group by with a large amount of data, temporary tables may be used in order by

# if this value is exceeded, it will be written to disk, and the system IO pressure will increase.

Tmp_table_size = 134217728

Max_heap_table_size = 134217728

# disable mysql's caching query result set feature

# later test to decide whether to open it according to the business situation.

# turn off the following two items in most cases

Query_cache_size = 0

Query_cache_type = 0

# #-BEGIN--## the memory allocated by the user process

# # each session will allocate the memory size set by the parameter

# for sequential scanning of tables. The read data is temporarily stored in read_buffer_size. When the buff is full or finished, the data is returned to the upper caller.

# generally used in 128kb ~ 256kb for MyISAM

# read_buffer_size = 131072

# for random reading of tables, which is used when sorting reads by a non-indexed field

# generally used in 128kb ~ 256kb for MyISAM

# read_rnd_buffer_size = 262144

# used for order by or group by

# it is recommended to adjust it to 2m first and then observe and adjust it later.

Sort_buffer_size = 2097152

# there are no big transactions in general database. Set it to 1cm 2m. Default is 32kb.

Binlog_cache_size = 524288

# #-END--## the memory allocated by the user process

# how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to new requests

# official recommendation: back_log = 50 + (max_connections / 5), the number of caps is 900

Back_log = 130

# # Log Settings # #

# Database error log file

Log_error = error.log

# slow query sql log settings

Slow_query_log = 1

Slow_query_log_file = slow.log

# check the sql that does not use the index

Log_queries_not_using_indexes = 1

# record the frequency of slow sql and the number of entries per minute after log_queries_not_using_indexes is enabled

Log_throttle_queries_not_using_indexes = 5

# as effective from the library, how slow sql is copied from the library will also be recorded

Log_slow_slave_statements = 1

# the number of seconds for slow query execution, which must be reached to be recorded

Long_query_time = 8

# the number of rows retrieved must reach this value before it can be recorded as a slow query

Min_examined_row_limit = 100

# the expiration time of mysql binlog log files, which is automatically deleted after expiration

Expire_logs_days = 5

# # Master-Slave replication Settings # #

# enable mysql binlog function

Log-bin=mysql-bin

# the way binlog records content, recording every line that is manipulated

Binlog_format = ROW

# for binlog_format = ROW mode, reduce the contents of the log and record only the affected columns

Binlog_row_image = minimal

# master status and connection information output to table mysql.slave_master_info

Master_info_repository = TABLE

# the slave's position in the relay logs output to table mysql.slave_relay_log_info

Relay_log_info_repository = TABLE

# effective as a slave library. This parameter is required for cascading replication.

Log_slave_updates

# as a slave library, relay log relay-log can repair itself

Relay_log_recovery = 1

# errors that take effect as a slave library and are ignored during master-slave replication

Slave_skip_errors = ddl_exist_errors

# #-setting BEGIN---## for the relationship between redo log and binlog

# (step 1) prepare dml-related SQL operations, and then persist the cache in redo log buff to disk

# (step 2) if the previous prepare is successful, then continue to persist the transaction log to binlog

# (step 3) if the previous is successful, then write a commit record in redo log

# it is the safest when both innodb_flush_log_at_trx_commit and sync_binlog are 1

# in the case of a mysqld service crash or a server host crash, binary log can only lose at most one statement or transaction.

# but setting both to 1 results in frequent io operations, so this mode is also the slowest way.

# when innodb_flush_log_at_trx_commit is set to 0memmysqld, the crash of the process will result in the loss of all transaction data in the previous second.

# when innodb_flush_log_at_trx_commit is set to 2, all transaction data can be lost in the last second only if the operating system crashes or the system is powered off.

In # commit transactions, the mode for controlling redo log buff persistent disks defaults to 1

Innodb_flush_log_at_trx_commit = 2

# in commit transactions, the mode that controls writing to the mysql binlog log defaults to 0

When # innodb_flush_log_at_trx_commit and sync_binlog are both 1, mysql is the most secure but also the most stressful in performance.

Sync_binlog = 1

# #-setting END---## for the relationship between redo log and binlog

# # Innodb Settings # #

# the unit of data block is 8k. The default is 16kCPU. The pressure of 16kCPU is slightly lower, and the throughput of 8k to select is large.

The parameter value of # innodb_page_size also affects the maximum index length. The maximum index length of 8k is smaller than that of 16k.

# innodb_page_size = 8192

# generally set 60% to 70% of physical storage

Innodb_buffer_pool_size = 1G

# default of 16m after 5.7.6

# innodb_log_buffer_size = 16777216

# this parameter is annotated directly on unix and linux,window. The default value is NULL

# O_DIRECT reduces conflicts between operating system-level VFS caches and Innodb's own buffer caches

Innodb_flush_method = O_DIRECT

# this format supports compression, which is the default value after 5.7.7

Innodb_file_format = Barracuda

# CPU multicore processing capacity setting, assuming that the CPU is 2-core 4-core, set as follows

# read more and write less can be set to 2:6

Innodb_write_io_threads = 4

Innodb_read_io_threads = 4

# increase the number of dirty pages refreshed and the number of merge inserts, and improve the processing capacity of disk Ipicuro

# default value 200 (in pages)

# this value can be determined based on the recent IOPS of the disk

Innodb_io_capacity = 500,

# in order to obtain the maximum waiting time for locked resources, the default is 50 seconds. If this time is exceeded, the following error will be reported:

# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Innodb_lock_wait_timeout = 30

# adjust the percentage of recently used page reads and dump in buffer pool. You can reduce the number of page dumped by setting this parameter

Innodb_buffer_pool_dump_pct = 40

# set the directory where the redoLog file is located, and redoLog records the specific operation contents of the transaction

Innodb_log_group_home_dir = / opt/mysql/redolog/

# set the directory where the undoLog file is located, and undoLog is used for transaction rollback operation

Innodb_undo_directory = / opt/mysql/undolog/

# the number of redoLog files in innodb_log_group_home_dir, and the contents of redoLog files are written in a circular overwrite.

Innodb_log_files_in_group = 3

# MySql5.7 officially recommends setting it as large as possible, close to the size of innodb_buffer_pool_size

# setting this value before may lead to mysql downtime recovery time is too long, now the recovery has been much faster

# this value reduces the frequency of dirty data flushing to disk

# maximum innodb_log_file_size * innodb_log_files_in_group

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.