Oracle版面本的MySQL8.0的配置文件 my.cnf

标签：log 配置文件 home work cnf innodb mysql 3306 MySQL8.0

##############################

创建用户和用户组：

groupadd work

useradd work

准备目录：

mkdir -p /home/work/mysql_3306
mkdir -p /home/work/mysql_3306/data
mkdir -p /home/work/mysql_3306/etc
mkdir -p /home/work/mysql_3306/tmp
mkdir -p /home/work/mysql_3306/log
mkdir -p /home/work/mysql_3306/binlog

下载：

https://dev.mysql.com/downloads/mysql/

选择：mysql-boost-8.0.32.tar.gz

Oracle版面本的MySQL8.0的配置文件 my.cnf_mysql

安装依赖包：

yum -y update
yum -y install  ncurses ncurses-devel wget make  cmake cmake3  perl gcc gcc-c++ autoconf automake zlib libxml2 libxml2-devel libgcrypt libtool bison bison-devel  devtoolset-10-gcc devtoolset-10-gcc-c++ devtoolset-10-binutils openssl openssl-devel perl perl-devel libaio libaio-devel

#启用新版Gcc;

scl enable devtoolset-10 bash

设置cmake为cmake3：

mv /usr/bin/cmake  /usr/bin/cmake.bak
ln -s /usr/bin/cmake3 /usr/bin/cmake

解压：

# tar -pxzvf mysql-boost-8.0.32.tar.gz

mysql-8.0.32

预编译：

cmake . 
-DCMAKE_INSTALL_PREFIX=/home/work/mysql_3306
-DMYSQL_DATADIR=/home/work/mysql_3306/data
-DSYSCONFDIR=/home/work/mysql_3306/etc   
-DWITH_BOOST=boost
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii 
-DWITH_MYISAM_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DMYSQL_TCP_PORT=3306 
-DENABLED_LOCAL_INFILE=1 
-DWITH_EMBEDDED_SERVER=OFF  
-DENABLED_LOCAL_INFILE=1 
-DWITH_PARTITION_STORAGE_ENGINE=1 
-DWITH_FEDERATED_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 
-DWITH_FAST_MUTEXES=1 
-DWITH_DEBUG=0 
-DWITH_EMBEDDED_SERVER=1 
-DFORCE_INSOURCE_BUILD=1

编译：

make -j4

安装：

make -j4 install

初始化：

/home/work/mysql_3306/bin/mysqld --defaults-file=/home/work/mysql_3306/etc/my.cnf --initialize --basedir=/home/work/mysql_3306 --datadir=/home/work/mysql_3306/data --user=work

启动：

/home/work/mysql_3306/bin/mysqld --defaults-file=/home/work/mysql_3306/etc/my.cnf &

配置文件my.cnf如下：

[client]
port = 3306
socket = /home/work/mysql_3306/tmp/mysql.sock

[mysqld]
# 在mysql8.0以上版本默认为caching_sha2_password，为了与mysql5.7版本兼容，设为
default_authentication_plugin=mysql_native_password
user    = work
port = 3306

basedir = /home/work/mysql_3306
datadir = /home/work/mysql_3306/data
tmpdir = /home/work/mysql_3306/tmp
socket = /home/work/mysql_3306/tmp/mysql.sock
pid_file = /home/work/mysql_3306/tmp/mysql.pid
log-error = /home/work/mysql_3306/log/error.log
general_log = /home/work/mysql_3306/log/general.log
slow_query_log_file = /home/work/mysql_3306/log/slow.log
log-bin = /home/work/mysql_3306/binlog/mysql-bin
innodb_log_group_home_dir=/home/work/mysql_3306/data
innodb_undo_directory=/home/work/mysql_3306/data


default-time-zone = "+08:00"
performance_schema = 1
log_slave_updates
log_timestamps = SYSTEM
slow_query_log
long_query_time = 0.5
lock_wait_timeout    = 120# 注意：mysql8.0以上版本的sql_mode没有NO_AUTO_CREATE_USER这个值。
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
table_open_cache_instances = 16 
##################################
optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on'
super_read_only = off 
log_slow_slave_statements = on
log_slow_admin_statements = on
event-scheduler = off 
back_log = 1000 
skip_name_resolve
max_connections = 10240
max_user_connections = 4000
max_connect_errors = 1000
table_open_cache = 8192 
table_definition_cache = 65535
max_allowed_packet = 64M 
expire_logs_days = 7
max_heap_table_size = 1024M 
read_rnd_buffer_size = 512K 
group_concat_max_len = 1024000
sort_buffer_size = 256K 
read_buffer_size = 64K
join_buffer_size = 128K 
thread_cache_size = 256 
ft_min_word_len = 4 
default-storage-engine = INNODB
thread_stack = 192K 
transaction_isolation = REPEATABLE-READ
tmp_table_size = 1024M 
open_files_limit = 65536 
key_buffer_size = 32M 
bulk_insert_buffer_size = 64M 
myisam_sort_buffer_size = 128M 
myisam_max_sort_file_size = 1G 
max_allowed_packet = 64M
max_prepared_stmt_count = 1000000
#-----------------------------------------------------------------
#replication
server-id = 4545454545
binlog_rows_query_log_events = on 
log-slave-updates = 1
relay-log = relay-bin
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
#auto_increment_offset = 1           
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
# auto_increment_increment = 1   
#####################必须集群内互相兼容，建议全都保持一致并为 row
binlog_format = row

# Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:Slave can not handle replication events with the checksum that master is configured to log; 
# the first event ‘mysql-bin.000001’ at 451, the last event read from ‘./mysql-bin.000001’ at 451, the last byte read from ‘./mysql-bin.000001’ at 120.’
# 这就是binlog_checksum变量设置成none的原因，以免因为主从数据库的版本不一致出现这个错误。
binlog_checksum = none 

binlog_cache_size = 16M 
max_binlog_cache_size = 2G
sync_binlog = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
sync_master_info = 10000
sync_relay_log_info = 10000
sync_relay_log = 0
#slave_net_timeout    = 4
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
slave_pending_jobs_size_max = 134217728
slave_preserve_commit_order = 0

#################################
## 下面这两个搞不好会导致启动失败，搞清楚原理再使用
#transaction_write_set_extraction = XXHASH64
#binlog_transaction_dependency_tracking = WRITESET
#####################MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。
#slave_net_timeout=8
###############mysql版本在5.6之后才有该选项。
gtid_mode = on
enforce-gtid-consistency = on
#
##############################################################################################半同步复制插件，需要安装。mysql5.7版本之后才有。
##########################################。安装插件在主库上：install plugin rpl_semi_sync_master soname 'semisync_master.so'; --安装 semisync_master.so插件 
##########################################。安装插件在从库上：install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_enabled = on
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_slave_enabled = on
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_wait_for_slave_count = 1
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_timeout = 1000
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_wait_point = after_sync

 
#undo
innodb_max_undo_log_size = 1024M 
innodb_undo_log_truncate = on 
innodb_undo_tablespaces = 3 #只能在初始化的时候设置
innodb_purge_rseg_truncate_frequency = 128

#innodb
innodb_autoextend_increment = 64
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_purge_batch_size = 300
innodb_stats_on_metadata = off
innodb_thread_sleep_delay = 10000 
innodb_adaptive_max_sleep_delay = 15000 
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
innodb_flush_neighbors = 1
############################nnoDB使用后台线程处理数据页上写 I/O（输入）请求的数量。
innodb_write_io_threads = 8 
############################InnoDB使用后台线程处理数据页上读 I/O（输出）请求的数量。这里输出是输入的两倍。
innodb_read_io_threads = 16
innodb_print_all_deadlocks = on
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8 
innodb_data_file_path = ibdata1:100M:autoextend # 这个参数在windows版本上没有这个参数
innodb_thread_concurrency = 48 
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
############################该值表示每个redo log文件大小，该值大小约为该mysql实例一个小时产生的日志大小，这里设置为4G，ib_logfile0和ib_logfile1文件都是4G，逻辑上ib_logfile被当成了一个文件。循环写入。
innodb_log_file_size = 4096M 
#############################配置redo log成多个文件，Redo log文件以ib_logfile[number]命名，日志目录通过参数innodb_log_group_home_dir控制，以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。
innodb_log_files_in_group = 3 
###############################控制了 Dirty Page 在 Buffer Pool 中所占的比率。
innodb_max_dirty_pages_pct = 75 
innodb_max_dirty_pages_pct_lwm = 10 
innodb_flush_method = O_DIRECT   # 在windows上不行
innodb_lock_wait_timeout = 10 
#注意：innodb_file_per_table参数必须设置为1，否则xtrabackup工具无法单独备份某一个数据库。
innodb_file_per_table = 1
innodb_purge_threads = 4 
#########################该参数表示缓冲区刷新到磁盘时，刷新脏页数量。普通单个SATA可以设置200，对于SSD可以设置3000到5000，而拥有5个磁盘组成的RAID5可以设置成2000。
innodb_io_capacity = 5000
innodb_open_files = 65535 
innodb_online_alter_log_max_size = 5120M
innodb_sort_buffer_size = 4M
innodb_adaptive_hash_index_parts = 8 
innodb_buffer_pool_chunk_size = 128 
innodb_buffer_pool_dump_pct = 80 
innodb_deadlock_detect = on 
innodb_default_row_format = DYNAMIC 
innodb_fill_factor = 100 
innodb_flush_sync = off 
innodb_log_checksums = on 
innodb_log_write_ahead_size = 8192 
innodb_max_undo_log_size = 1073741824 
innodb_page_cleaners = 4 
innodb_purge_rseg_truncate_frequency = 128 
innodb_temp_data_file_path = ibtmp1:12M:autoextend

安装validate_password 插件：

mysql> show variables like 'validate%';
Empty set (0.02 sec)

mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

Oracle版面本的MySQL8.0的配置文件 my.cnf_mysql_02

Oracle版面本的MySQL8.0的配置文件 my.cnf_同步复制_03

################################

标签：log,配置文件,home,work,cnf,innodb,mysql,3306,MySQL8.0
From： https://blog.51cto.com/u_13912516/6288016