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

The following content mainly brings you the detailed configuration analysis of mysql parameters, the knowledge mentioned here, which is slightly different from books, is summarized by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

[client]

Port = 3306

Socket = / data/mysql/data/mysql.sock

[mysql]

Prompt= "\\ u @\\ h\ R:\ m:\ s [\\ d] >"

No-auto-rehash

[mysqld]

Sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Lower_case_table_names=1

Log_timestamps = SYSTEM

Default_time_zone = + 08:00

User = mysql

Port = 3306

Basedir = / usr/local/mysql

Datadir = / data/mysql/data

Socket = / data/mysql/data/mysql.sock

Pid-file = / data/mysql/data/mysql.pid

Character-set-server = utf8

Skip_name_resolve = 1

Open_files_limit = 65535

Back_log = 1024

Max_connections = 1000

Max_connect_errors = 1000000

Table_open_cache = 2000

Table_definition_cache = 400

Table_open_cache_instances = 64

Thread_stack = 512K

External-locking = FALSE

Max_allowed_packet = 32m

Sort_buffer_size = 4m

Join_buffer_size = 4m

Thread_cache_size = 3000

Query_cache_size = 0

Query_cache_type = 0

# interactive_timeout = 600

# wait_timeout = 600

Tmp_table_size = 32m

Max_heap_table_size = 32m

Slow_query_log = 1

Slow_query_log_file = / logs/mysql/mysql-slow.log

Log-error = / logs/mysql/mysql-error.log

Long_query_time = 0.5

Log_queries_not_using_indexes = 1

Log_throttle_queries_not_using_indexes = 60

Min_examined_row_limit = 100

Log_slow_admin_statements = 1

Log_slow_slave_statements = 1

Server-id = 271883306

Log-bin = / data/mysql/data/mysql-bin

Sync_binlog = 1

Binlog_cache_size = 4m

Max_binlog_cache_size = 2G

Max_binlog_size = 1G

Expire_logs_days = 7

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

Gtid_mode = on

Enforce_gtid_consistency = 1

Log_slave_updates

Slave-parallel-type=LOGICAL_CLOCK

Slave-parallel-workers=8

Slave_preserve_commit_order=1

Binlog_format = row

Relay-log = / data/mysql/data/relay-bin

Relay_log_recovery = 1

Relay-log-purge = 1

Key_buffer_size = 32m

Read_buffer_size = 8m

Read_rnd_buffer_size = 4m

Bulk_insert_buffer_size = 64m

Myisam_sort_buffer_size = 128m

Myisam_max_sort_file_size = 10G

Myisam_repair_threads = 1

Lock_wait_timeout = 3600

Explicit_defaults_for_timestamp = 0

Innodb_thread_concurrency = 0

Innodb_sync_spin_loops = 100

Innodb_spin_wait_delay = 30

Innodb_undo_directory = / data/mysql/data

Innodb_undo_logs = 128,

Innodb_undo_tablespaces = 3

Innodb_undo_log_truncate = 1

Innodb_purge_rseg_truncate_frequency = 128,

Transaction_isolation = REPEATABLE-READ

# innodb_additional_mem_pool_size = 16m

Innodb_buffer_pool_size = 3072m

Innodb_buffer_pool_instances = 2

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_data_file_path = ibdata1:1G:autoextend

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 32m

Innodb_log_file_size = 512m

Innodb_log_files_in_group = 3

Innodb_max_undo_log_size = 4G

# adjust according to your CVM IOPS capabilities

# if it is usually equipped with an ordinary SSD disk, it can be adjusted to 10000-20000

# if you configure a high-end PCIe SSD card, you can adjust it higher, such as 50000-80000

Innodb_io_capacity = 800,

Innodb_io_capacity_max = 1600

Innodb_flush_neighbors = 1

Innodb_write_io_threads = 8

Innodb_read_io_threads = 8

Innodb_purge_threads = 4

Innodb_page_cleaners = 4

Innodb_open_files = 65535

Innodb_max_dirty_pages_pct = 50

Innodb_flush_method = O_DIRECT

Innodb_lru_scan_depth = 4000

Innodb_checksum_algorithm = crc32

# innodb_file_format = Barracuda

# innodb_file_format_max = Barracuda

Innodb_lock_wait_timeout = 10

Innodb_rollback_on_timeout = 1

Innodb_print_all_deadlocks = 1

Innodb_file_per_table = 1

Innodb_online_alter_log_max_size = 4G

Internal_tmp_disk_storage_engine = InnoDB

Innodb_stats_on_metadata = 0

Innodb_status_file = 1

# Note: when innodb_status_output & innodb_status_output_locks is enabled, log-error files may grow faster.

Innodb_status_output = 0

Innodb_status_output_locks = 0

# performance_schema

Performance_schema = 1

Performance_schema_instrument ='% = on'

# innodb monitor

Innodb_monitor_enable=module_innodb,module_server,module_dml,module_ddl,module_trx,module_os,module_purge,module_log,module_lock,module_buffer,module_index,module_ibuf_system,module_buffer_page,module_adaptive_hash

[mysqldump]

Quick

Max_allowed_packet = 32m

Reference for initialization information:

Create a user:

Useradd mysql

Mkdir-p / data/mysql/data

Mkdir-p / logs/mysql/

Chown mysql:mysql-R / data/mysql/data

Chown mysql:mysql-R / logs/mysql

Yum-y install libaio

Initialize the database:

/ usr/local/mysql/bin/mysqld-initialize-user=mysql-basedir=/usr/local/mysql-datadir=/data/mysql/data

Cat / logs/mysql/mysql-error.log | grep password | awk'{print $NF}'

Change the password:

Alter user root@'localhost' identified by "password"

The configuration file automatically generates reference information:

Online cnf document production tool http://imysql.com/my-cnf-wizard.html

For the above detailed parameter configuration analysis of mysql, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact those before and after sale on the official website. I hope this article can bring you some knowledge updates.

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.