[client] port = 13301 socket = /opt/data/logs/my_13301.sock default-character-set=utf8 [mysql] prompt =\\u@\\h \\D \\R:\\m:\\s [\\d]> default-character-set=utf8 [mysqld] user = mysql server-id = 33003 port = 3306 socket = /opt/data/logs/my_13301.sock pid-file = /opt/data/logs/my_13301.pid basedir = /opt/app/mysql datadir = /opt/data/mysql_13301 tmpdir = /opt/data/tmp character_set_server = utf8mb4 collation_server = utf8mb4_bin #skip_name_resolve open_files_limit = 65535 back_log = 103 max_connections = 1000 max_connect_errors = 10000 table_open_cache = 512 external-locking = FALSE max_allowed_packet = 1024M #----session memory setting----# sort_buffer_size = 8M join_buffer_size = 4M thread_cache_size = 51 tmp_table_size = 96M max_heap_table_size = 96M event_scheduler = ON #----log settings----# log_timestamps = SYSTEM log_error_verbosity = 2 init_connect = 'SET NAMES utf8mb4' sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' log-error = /opt/data/logs/my_13301_error.log log-bin = /opt/data/mysql_13301/mysql-bin default-time_zone = '+8:00' binlog_format =row binlog_checksum =none binlog_rows_query_log_events =on long_query_time = 3 slow_query_log = 1 slow_query_log_file = /opt/data/logs/my_13301_slow.log sync_binlog = 1 binlog_cache_size = 32M max_binlog_cache_size = 512M max_binlog_size = 1024M log_queries_not_using_indexes = true log_slow_admin_statements = true log_slow_slave_statements = true log_throttle_queries_not_using_indexes = 10 expire_logs_days = 3 min_examined_row_limit = 100 binlog-rows-query-log-events = 0 interactive_timeout=120 wait_timeout=120 #transaction_isolation = READ-COMMITTED key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M default-storage-engine = InnoDB explicit_defaults_for_timestamp=true read_only=0 #off log_slave_updates=on skip-slave-start=1 innodb_file_per_table = 1 innodb_page_size = 16384 #default value innodb_data_file_path = ibdata1:1024M:autoextend innodb_temp_data_file_path = ibtmp1:2048M:autoextend innodb_buffer_pool_size = 300M innodb_buffer_pool_instances = 1 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50 #innodb_thread_concurrency =2 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 gtid_mode=ON enforce_gtid_consistency=ON [mysqldump] quick max_allowed_packet = 32M
如果不需要日志，可以考虑关闭binlog或者不使用ROW格式
binlog_format=STATEMENT 这时，事务级别不能低于RR RC就用不了了
8.0日志超时时间
set global binlog_expire_logs_seconds=10800; # 3*60*60 binlog_expire_logs_seconds=10800
两个开关
mysql tmpdir参数简述
tmpdir是Mysql的临时目录，用于存放mysql运行过程中产生的临时文件 大表查询或join时，内存不够用就会使用tmpdir
tmpdir参数修改
只读变量，无法在内存中修改，只能在配置文件中修改 Variable 'tmpdir' is a read only variable mysql> mysql> show global variables like '%tmpdir%'; +---------------------+-------------------------+ | Variable_name | Value | +---------------------+-------------------------+ | innodb_tmpdir | | | replica_load_tmpdir | /opt/app/mysql/data/tmp | | slave_load_tmpdir | /opt/app/mysql/data/tmp | | tmpdir | /opt/app/mysql/data/tmp | +---------------------+-------------------------+ 4 rows in set (0.00 sec) 属于安装时要修改的变量 目录授权 mkdir /data/tmp chown -R mysql.mysql /data/tmp 修改配置文件 tmpdir=/data/tmp
相关内容简述
几个临时表 Created_tmp_disk_tables ：超过内存限制，写到磁盘的临时表 Created_tmp_files： 内存当中的临时表 Created_tmp_tables：临时表 全局临时表空间innodb_temp_data_file_path 在8.0版本之后全局临时表空间就只用于存放用户创建的临时表更改的回滚段。 在mysql服务启动时创建，关闭时截断并释放。全局临时表空间不能存放在裸设备上。 为了防止全局表空间无限制增长可以配置最大值 innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M 参考mysql 临时表空间
mysql 临时表空间
临时内存设置
mysql 临时表空间