docker run --name mysql --restart=always -p3307:3306 -v /home/dev/mysql_data:/var/lib/mysql -eMYSQL_ROOT_PASSWORD=123456-d mysql:5.7.36
sql_mode解决 only_full_group_by问题
#查看当前sql_mode
SELECT @@sql_mode
#查看最大连接数
show variables like '%max_connections%'#将docker mysqld.cnf拷贝到容器外dockercp my-mysql7:/etc/mysql/mysql.conf.d/mysqld.cnf /root
#编辑mysqld.cnf文件并保存(注意:在mysql8.0.11及以后的版本中NO_AUTO_CREATE_USER已经被移除)sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#修改最大连接数max_connections=1024#重做日志文件大小innodb_log_file_size=256M
#最大可允许的包的大小max_allowed_packet=256M
#设置时区
default-time_zone='+8:00'#临时数据文件innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G
#将mysqld.cnf拷贝回容器dockercp mysqld.cnf my-mysql7:/etc/mysql/mysql.conf.d/
#重启mysqldocker restart mysql
修改密码
update user setauthentication_string=password('123456') where user='root';
flush privileges;
参数调优
show global variables like 'key_buffer_size'; 64M //默认16M //64G的机器设置:256M
show global variables like 'max_allowed_packet'; 32M // 默认16M //64G的机器设置:32M
show global variables like 'table_open_cache';2048 // 默认2000 //64G的机器设置:16384
show global variables like 'sort_buffer_size'; 4M //默认是2M //64G的机器设置:32M
show global variables like 'net_buffer_length';16384 //默认16384 //64G的机器设置:16384
show global variables like 'read_buffer_size'; 1M //默认128k //64G的机器设置:16M
show global variables like 'read_rnd_buffer_size'; 512k //默认256k //64G的机器设置:32M
show global variables like 'myisam_sort_buffer_size';128M //默认128M //64G的机器设置:128M
show global variables like 'thread_cache_size';64 //默认8 //64G的机器设置:64
show global variables like 'query_cache_size';0 //默认16M //64G的机器设置:0
show global variables like 'tmp_table_size'; 32M //默认16M //64G的机器设置:128M
show xx like 'explicit_defaults_for_timestamp'; ON //默认OFF //64G的机器设置:ON
show global variables like 'max_connections';5000 //默认151 //64G的机器设置:100000
show global variables like 'max_connect_errors';500000 //默认100 //64G的设置:10000000
show global variables like 'open_files_limit';65535 //默认4184 //64G的机器设置:500000
show global variables like 'expire_logs_days';10 //默认10 //64G的机器设置:10
show global variables like 'innodb_file_per_table'; ON //默认ON //64G的机器设置:ON
show global variables like 'innodb_data_file_path';ibdata1:76M;ibdata2:1G:autoextend //默认12M //64G的机器设置:1G
===innodb相关====
show global variables like 'innodb_buffer_pool_size'; 2G //默认128M //64G的机器设置:32G
show global variables like 'innodb_log_file_size'; 256M //默认48M //64G的机器设置:4G
show global variables like 'innodb_log_buffer_size'; 32M //默认16M //64G的机器设置:64M
show xx like 'innodb_flush_log_at_trx_commit';2 //默认1 //64G的机器设置:2
show global variables like 'sync_binlog';1000 //默认0 //64G的机器设置:1000
show global variables like 'innodb_lock_wait_timeout';30s //默认50 //64G的机器设置:30
show global variables like 'back_log';500 //默认80 //64G的机器设置:4096
常见问题
1.The innodb_system data file'./ibdata1' is of a different size 4864 pages (rounded down to MB) than the 768 pages specified in the .cnf file!#调整innodb_data_file_path参数,每页16K,4864 * 16 = 76M
innodb_data_file_path = ibdata1:76M;ibdata2:100M:autoextend