Shulou(Shulou.com)06/01 Report--

Automatically generate cnf configuration file: http://imysql.com/my-cnf-wizard.html

Common configurations:

[mysql] field

No-auto-rehash # enable command completion

[mysqld] field

# port = 3306 # default # bind-address = 0.0.0.0 # default pid-file = / data/mysql/mysql.pidbasedir = / usr/local/mysql/symbolic-link = 0 # is equivalent to skip-symbolic-links, connection files cannot be used, and multiple customers may access the same database, so this prevents external customers from locking the MySQL server. This option turns on by default # tmpdir = / usr/local/mysql/tmp/ # this directory is used by MySQL to save temporary files. For example, it is used to handle large disk-based sorting, and 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 it on a separate disk. You can use ";" to place multiple paths, and they will be polled according to the roud-robin method to limit the file descriptor opened with .open _ files_limit = 65535 # MySQL. The default is at least 1024. When open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, which is the most useful, and when open_file_limit is configured, compare the values of open_files_limit and max_connections*5, which is the most useful. The service script needs to be modified. In the example centos7, / etc/systemd/system/mysql.service adds LimitNOFILE=65535 to restart the service on the last line of the mysql.service. Modified mysql.server may require systemctl daemon-reload and ulimit-n 65535 to fail after restart, so you also need to modify the system configuration file to modify the system configuration file / etc/security/limits.conf * soft nofile 65535 * hard nofile 65535datadir=/data/mysqllog-error = / data/mysql/error.logslow_query_log=on # slow query log related long_query_time=2 # default 10 seconds slow_query_log_file=/var/log/mariadb/slow_query.loglog-queries-not-using-indexes # if you run a SQL statement that does not use an index Then the mysql database also logs this SQL statement to the slow query log file. # log_output = FILE # parameter log_output specifies the format of slow query output, which defaults to FILE. You can set it to TABLE, and then you can query the slow_log table under mysql architecture. Socket=/var/lib/mysql/mysql.sock # this configuration needs to be configured in the [client] section at the same time, and port should also be configured. Otherwise, errors may occur: default_storage_engine=InnoDBinnodb_file_per_table = on # InnoDB is an independent tablespace mode, and each table in each database will generate a data space. Innodb_buffer_pool_size=4G # to be changed in production, it is recommended to be 70% of the operating system memory, and the service needs to be restarted. Skip_name_resolve = on # ignore hostname resolution Improve access speed (note that hostnames will not be resolved in the configuration file) lower_case_table_names = 1 # ignore form case character-set-server=utf8mb4 # set the default character to utf8mb4

1. Considerations for using innodb

A) all InnoDB data tables create a business-independent self-incrementing number as the primary key, which is very helpful to ensure performance; b) eliminate the use of text/blob and split it into a separate table as far as possible if it is really needed; c) timestamps are recommended to be stored in TIMESTAMP type; d) IPV4 addresses are recommended to be stored in INT UNSIGNED type E) the logic of gender is either yes or no, it is recommended to use TINYINT storage instead of CHAR (1); for example, it can be expressed by 0Magin1, Magi 2, unknown, male and female, advantages search fast, disadvantages display / storage all need to convert f) when storing long text content, it is recommended to use JSON/BSON format.

two。 Query cache related

Query_cache_type=1 # 0 means to disable caching, 1 means to cache all results, unless your select statement uses SQL_NO_CACHE to disable query caching, 2 means to cache only the query specified by SQL_CACHE in the select statement query_cache_limit = 2m # does not cache results whose query is greater than this value. Only results that are less than this setting are buffered. This setting is used to protect query buffers from overwriting all other query results by a very large result set. Query_cache_size = 64m or 32m or 128m # this value indicates that query caching is not enabled. Check whether the SHOW VARIABLES LIKE 'have_query_cache'; value of yes indicates support for query caching related field meaning show status like'% Qcache%'. Qcache_free_blocks: indicates how much blocks is left in the query cache. If the value is large, it means that there are too many memory fragments in the query cache and may be cleaned up in a certain amount of time. Qcache_free_memory remaining query cache size. Adjust query_cache_size Qcache_hits according to this value: indicate how many times you hit the cache Qcache_inserts: indicate how many times you missed and then insert. The SQL request was not found in the cache, so you have to execute query processing. After executing the query processing, insert the results to the query cache. The more times of this situation, the less query caching is applied, and the effect is not ideal. Of course, just after the system starts, the query cache is empty Qcache_lowmem_prunes: this parameter records how many queries have been removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately

3. System resource dependence

Back_log = 500 # accept the queue, and put the request queue without establishing a tcp connection into the cache. The queue size is back_log, which is restricted with the OS parameter. View method cat / proc/sys/net/ipv4/tcp_max_syn_backlog. You can edit / etc/sysctl.conf to adjust it. For example: net.ipv4.tcp_max_syn_backlog = 2048, execute sysctl-p after the change to make the change take effect immediately. Trying to set the limit that back_log is higher than your operating system will not be valid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems. If the system has many connections in a short period of time, you need to increase the value of this parameter max_connections = 1000 # to specify the maximum number of connection processes allowed by MySQL. If the "Too Many Connections" error occurs frequently when accessing the database, the default 100th production needs to increase the parameter value. Maximum number of connections allowed by the MySQL server is 16384

4. Binary log correlation

Server_id=1 log_bin=master-bin#log-bin-index=master-bin.indexexpire_logs_days = 7#binlog_format = row # defaults to mix Set these two items in the new version to improve security # binlog_row_image = minimalmax_binlog_size = 100m # default is 1Gbinlog_cache_size = 4m#binlog-do-db = DBNAME # specify which library the binlog log of mysql records only max_binlog_cache_size = 512m # production 4g#skip-slave-start # Note: when there is data recovery from the slave library, the slave library should be disabled to automatically start the slave process to avoid data inconsistency, and should be started manually on the slave database.

5. Configure master-slave related operations:

Preparatory work:

Confirm that binlog # show global variables like 'log_bin' is enabled

Record binary log file and number: show master status

The master node creates a user account with replication permission

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *. * TO 'repluser'@'192.168.5.%' identified by' repluser';flush privileges

From the node configuration:

Server_id=2relay_log=relay-log # enable relay log # max_relay_log_size = 200m # Mark the maximum allowed for relaylog. If the value is 0, the default value is max_binlog_size (1G). If it is not 0, max_relay_log_size is the maximum relay_log file size Relay_log_index=relay-log.indexread_only=ON # restart takes effect, and there is no restriction for users with super or all privileges permissions. # replicate_wild_do_table=DB_NAME.% # only copies a table of a certain library. If you add wild, you can use the wildcard # replicate_wild_ignore_table=DB_NAME.% # to ignore copying a table of a certain library. Ignore a library replicate_ignore_db, or the master node specifies that the database does not record the binlog,binlog-ignore-db=#slave_skip_errors=all of a library

# define the error number that can be skipped automatically from the server during replication. When you encounter a defined error number during replication, you can skip it automatically and execute the following SQL statement directly.

The slave_skip_errors option has four available values, which are: off,all,ErorCode,ddl_exist_errors.

By default, the parameter value is off, and we can list the specific error code, or we can select all,mysql5.6 and MySQL Cluster NDB 7.3.The parameter ddl_exist_errors is added in subsequent versions, which includes a series of error code (1007meme 1008, 1050, 1050, 1051, 1054, 1060, 1061, 1068, 1094, 146).

Some of the errors represented by error code are as follows:

1007: database already exists, failed to create database 1008: database does not exist, failed to delete database 1050: data table already exists, failed to create data table 1051: data table does not exist, failed to delete data table 1054: field does not exist, or program file conflicts with database 1060: field duplicate, resulting in unable to insert 1061: duplicate key name 1068: defined multiple primary keys 1094: location thread ID1146: missing data table Please restore database 1053: primary server down 1062 during replication: primary key conflict Duplicate entry'% s' for key% d

# from related operations

Specify the primary node

Change master to master_host='192.168.5.106',master_user='repluser',master_password='repluser',master_log_file='master_bin.000002',master_log_pos=343; # MASTER_PORT= defaults to 3306 IO_THREAD start slave [[IO_THREAD | SQL_THREAD] # start copy thread check: show slave status As follows, the number of seconds after starting Slave_IO_Running=yes Slave_SQl_Running=yes Seconds_Behind_Master:0 # lags behind the master library. 0 is relatively normal. The slave library relay-log.info is used to record the location of the last synchronization to the SQL thread. Generally speaking, after the master-slave switch, you need to reset slave and clear relay-log.info. Skip-slave-start # Note: when there is data recovery from the slave library, the slave library should turn off the automatic start of the slave process to avoid data inconsistencies, and start it manually on the slave library.

Install the Google plug-in for semi-synchronous replication:

Master:install plugin rpl_semi_sync_master soname 'semisync_master.so';SET GLOBAL rpl_semi_sync_master_enabled = 1 slave install plugin rpl_semi_sync_slave soname' semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled = 1 to prepare the library, in order to ensure that the semi-synchronization takes effect immediately, you need to restart the Slaveplugin rpl_semi_sync_slave soname thread of Slaveplugin rpl_semi_sync_slave soname

After installing the plug-in:

Master is added in the configuration file

Rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000 # units are milliseconds, and the default is 10000. If the master wait times out, it switches to normal asynchronous replication.

Slave add

Rpl_semi_sync_slave_enabled = 1

The author does not often use the configuration to be discovered:

[mysqld]

Slave-load-tmpdir = / usr/local/mysql/tmp/ # use skip-external-locking # when slave executes load data infile. Do not use system locking. To use myisamchk, you must shut down the server, avoid external locking on MySQL, reduce the probability of errors and enhance stability. Skip-networking # turn on this option to completely turn off MySQL's TCP/IP connection mode, but do not turn on this option if the WEB server accesses the MySQL database server remotely! Otherwise, you will not be able to connect properly! If all processes connect to the local mysqld on the same server, this setting will be a way to enhance security. Sysdate-is-now = 1 # programming the SYSDATE function as NOW alias default-time-zone = system # server time zone, or'+ 08:00'default_table_type = InnoDB # default table type default-storage-engine = InnoDB # default storage engine

# related to system resources

Max_connect_errors = 10000 # if the connection error initiated by a user exceeds this value, the user's next connection will be blocked until the administrator executes the flush hosts command or the service is restarted, preventing illegal passwords and other errors during the link from increasing this value connect-timeout = 10 # the maximum number of seconds before the connection times out, on the Linux platform This timeout is also used as the time to wait for the first response from the server wait-timeout = 28800 # time to wait for the connection to be closed interactive-timeout = 28800 # to allow interactive_timeout (instead of wait_timeout) seconds of inactivity before closing the connection. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable. If the front-end program uses a short connection, it is recommended to shorten these two values. If the front-end program uses a long connection, you can comment out these two options directly. The default configuration (8 hours) slave-net-timeout = 600# can also handle network connection interruptions from the server. However, it is only when the slave server exceeds slave_net_timeout seconds that no data is received from the master server to notify the network interrupt net_read_timeout = 30 # timeout for reading information from the server = 60 # timeout for writing information from the server net_retry_count = 10 # if the read operation of a communication port is interrupted, retry multiple times before giving up. The net_buffer_length = 16384 # packet message buffer is initialized to net_buffer_length bytes, but can grow to max_allowed_packet bytes max_allowed_packet = 64m # the maximum size of the request packet that the service can handle and the maximum request size that the service can handle (necessary when working with the large BLOB field), the independent size of each connection. The size increases dynamically. Set the maximum packet, limit the packet size accepted by server, avoid the execution of ultra-long SQL, the default value is 16m, when the MySQL client or mysqld server receives a packet larger than max_allowed_packet bytes, it will issue a "packet too large" error and close the connection. For some clients, if the communication packet is too large, you may encounter a "lost connection to the MySQL server" error during query execution. The default is 16m. Table_cache = 512 # the number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld so you need to make sure that the "open-files-limit" variable in [mysqld_safe] sets the number of open files to allow at least the heap size used by 4096thread_stack = 192 K # threads. This capacity of memory is reserved for each connection. MySQL itself often does not require more than 64K of memory. If you use your own UDF function that requires a lot of heap or your operating system needs more heap for some operations, you may need to set it higher. The default setting is sufficient for most applications thread_cache_size = 20 # how many threads are reserved in cache for reuse. When a client disconnects, if there are fewer threads in cache than in thread_cache_size, the client thread is put into cache. This can greatly reduce the overhead of thread creation when you need a large number of new connections (generally speaking, if you have a good thread model, there will be no significant performance improvement.) thread_concurrency = 8 # allows the application to give the thread system a prompt to give the number of threads that are eager to be run at the same time. The value of this parameter is the minimum block size allocated by the server logic CPU × 2query_cache_min_res_unit = 2K # query cache. The default is 4KB, and a large value is good for big data queries, but if all your queries are small data queries, it is easy to cause memory fragmentation and waste query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%. If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment cache, or try to reduce query_cache_min_res_unit, if your queries are small amounts of 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 setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there are too many fragments. Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%tmp_table_size = the maximum size of the 512m # temporary table. If this value is exceeded, the result is put to disk. This limit is for a single table, not the maximum capacity allowed by a total max_heap_table_size = 512m # independent memory table. This option prevents accidental creation of an oversized memory table resulting in exhaustion of all memory resources

# Log related

Log_slave_updates = 1 # means that slave writes replication events to its own binary log log-warnings = 1 log_long_format # to record more information in the slow log. Generally speaking, this item is best turned on, which records so that queries that do not use indexes are also appended to slow logs as slow queries. Min_examined_row_limit=1000 # records slow queries caused by more than 1000 searches, long-slow-admin-statements # records slow optimize table. Analyze table and alter table statements log-slow-slave-statements # record the slow query generated by Slave general_log = 1 # record all SQL statements that arrive at MySQL Server, and by default turn off general_log_file = / usr/local/mysql/log/mysql.log # general_log path relay-log-purge = 1 # when the relay log is no longer needed. Default is 1 (enabled)

# MyISAM related options

Key_buffer_size = 256m # specifies the size of the buffer used for the index, and increasing it results in better index processing performance. If the DB is based on the InnoDB engine, the key_buffer_size dedicated to the MyISAM engine can be set to small, and 8MB is sufficient. If it is based on MyISAM engine, it can be set larger, but not more than 4G. Here, it is strongly recommended not to use the MyISAM engine, which defaults to the buffer size that can be used when sorting queries using the InnoDB engine. Sort _ buffer_size = 2m #. Sort buffers are used to handle sorting caused by ORDER BY and GROUP BY queues. An alternative disk-based merge classification will be used. View the "Sort_merge_passes" status variable. When sorting occurs, it is allocated by each thread Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the actual total sort buffer size allocated is 100x6 = 600MB, so it is recommended to set it to 6-8m for servers with about 4GB memory. Read_buffer_size = 2m # the size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection! The buffer size used to do full table scan of MyISAM table. When a full table scan is needed, allocate the size of the buffer in the corresponding thread. Join _ buffer_size = 8m # the buffer size that can be used by the joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive per connection! This buffer is used to optimize full federation (full JOINs federation without indexes). Similar combinations have very poor performance in most cases, but setting this value high can mitigate the performance impact. The number of full federations is viewed through the "Select_full_join" state variable, which is allocated in each thread when full federation occurs. Read_rnd_buffer_size = 8m # MyISAM buffer size of scanned data in index scan (Random Scan) bulk_insert_buffer_size = 64m # MyISAM uses special tree-like cache to make burst inserts (these inserts are, INSERT... SELECT, INSERT... VALUES (…) , () ,... , and LOAD DATAINFILE) faster. This variable limits the number of bytes in the buffer tree per process. Setting to 0 turns off this optimization. Do not set this value greater than "key_buffer_size" for optimization. When burst inserts are detected, this buffer will be allocated the size of the tree buffer that MyISAM uses in block insertion optimization. Note: this is a per thread limitation (a large number of bulk). This buffer is allocated when MySQL needs to re-index REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE into an empty table. This is allocated in each thread. So be careful when setting large values myisam_sort_buffer_size = 64m # MyISAM sets the size of the buffer used when recovering the table, when sorting the buffer allocated by the MyISAM index during REPAIR TABLE or CREATE INDEX index creation or ALTER TABLE. Myisam_max_sort_file_size = 10G # the maximum temporary file size allowed for mysql re-indexing is Myisam _ repair_threads = 1 # if the value is greater than 1, the MyISAM table index is created in parallel during the Repair by sorting process (each index is in its own thread). If a table has more than one index, MyISAM can fix them with more than one thread through parallel sorting. This is a good choice for users with multiple CPU and a large amount of memory. Myisam _ recover = 64K # allows the maximum length of the GROUP_CONCAT () function transaction_isolation = REPEATABLE-READ # to set the default transaction isolation level. The available levels are as follows: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ,SERIALIZABLE 1.READ UNCOMMITTED- read uncommitted 2.READ COMMITTE- read committed 3.REPEATABLE READ- repeatable readable 4.SERIALIZABLE-Serial

# INNODB related options

Skip-innodb # if your MySQL service includes InnoDB support but does not plan to use it, using this option will save memory and disk space, and speed up some parts of innodb_status_file = 1 # enable InnoDB status file, easy for administrators to view and monitor, such as show engine innodb status\ G innodb_open_files = 2048 # limit the data of tables that Innodb can open. If there are too many tables in the library, please add this. This value defaults to 300innodb_additional_mem_pool_size = 100m # sets the memory space used by the InnoDB storage engine to store data dictionary information and some internal data structures, so when we have a lot of database objects in a MySQL Instance, we need to properly adjust the size of this parameter to ensure that all data can be stored in memory to improve access efficiency. Innodb_write_io_threads = 4innodb_read_io_threads = 4 # innodb uses background threads to process the read / write Imax O (input / output) request on the data page, and changes it according to your CPU core number. The default is 4 # Note: these two parameters do not support dynamic change, so you need to add this parameter to the my.cnf. Restart the MySQL service after modification, with values ranging from 1-64innodb_data_home_dir = / usr/local/mysql/var/ # to this option if you want the InnoDB tablespace file to be saved in another partition. By default, it is saved in the datadir of MySQL. Innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend # InnoDB saves data in one or more data files as tablespaces. If you only have a single logical driver to save your data, a single self-adding file is good enough. In other cases. One file per device is generally a good choice. You can also configure InnoDB to use the bare disk partition innodb_file_io_threads = 4 # the number of IO threads used to synchronize IO operations. This value is hard-coded to 4 under Unix, but it may perform better under a large number of Windows disk I _ thread_concurrency = 16 # the number of threads allowed in the InnoDb core, InnoDB tries to keep the number of operating system threads in InnoDB less than or equal to the limit given by this parameter, the optimal value depends on the application, hardware, and operating system scheduling. Excessively high values can cause thread mutex bumps. The default setting is 0, which means there is no limit on the number of concurrency. It is recommended to set it to 0 to make better use of the multi-core processing capacity of CPU and increase the concurrency innodb_flush_log_at_trx_commit = 1. If set to 1, InnoDB will refresh (fsync) the transaction log to disk after each commit, which provides complete ACID behavior. If you are willing to make a compromise on transaction security, and you are running a small food, you can set this value to 0 or 2 to reduce the disk ID O 0 caused by the transaction log, which means that the log is only written to the log file per second and the log file is flushed to disk. 2 means 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 _ log_buffer_size = 8m # the size of the buffer used to buffer log data. When this value is almost full, InnoDB will have to refresh the data to disk. Since it is basically refreshed every second, it is not necessary to set this value too large (even for long transactions) innodb_log_file_size = 500m # transaction log size. For the size of each log file in the log group, you should set the total log file size to 5% '100% of your buffer pool size to avoid unnecessary buffer pool refresh on log file overwriting. In any case, please note that a large log file size increases the time required for the recovery process. Innodb _ log_files_in_group = 2 # the total number of files in the log group. Generally speaking, 2-3 is better. Innodb_log_group_home_dir = / usr/local/mysql/var/ # InnoDB log file location. The default is MySQL's datadir. You can assign it to a separate hard disk or a RAID1 volume to improve its performance innodb_max_dirty_pages_pct = 90 # innodb main thread refreshes data in the cache pool so that the proportion of dirty data is less than 90%, which is a soft limit and is not guaranteed to execute absolutely. Innodb _ lock_wait_timeout = 50 # InnoDB transactions can wait for a lock timeout seconds before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own locking table. InnoDB notices the lock setting with the LOCK TABLES statement. The default value is 50 seconds innodb_flush_method = O_DSYNC # InnoDB method used to refresh the log. Tablespaces always use double write refresh methods. The default value is "fdatasync" and the other is "O_DSYNC" .innodb _ force_recovery=1 # if you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table. Start at 1 and increase this value until you can successfully export the table. Innodb _ fast_shutdown # accelerates the shutdown of InnoDB. This prevents InnoDB from doing full cleanup and inserting buffer merging when closed. But instead, InnoDB may do these operations the next time it starts.

# other related:

[mysqldump] QuickQuick[ MySQL] auto-rehash # allows you to prompt default-character-set=utf8 # database character set connect-timeout = 3 [client] default-character-set= UTF8 [mysqld _ safe] open-files-limit = 8192 # to increase the number of files that can be opened by each process. Make sure you have set the system-wide limit high enough! To open a large number of tables, you need to set this value to large.

Reference: https://www.cnblogs.com/panwenbin-logs/p/8360703.html

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.