mysql各版本配置文件

版本：8.0.30 

my.cnf

[client]
port            = 3306
socket          = /tmp/mysqld.sock
default-character-set = utf8mb4
[mysqld]
########basic settings########
server-id = 33066771
port = 3306
socket          = /tmp/mysqld.sock
pid-file        = /tmp/mysqld.pid
user = mysql
default_authentication_plugin=mysql_native_password
character_set_server=utf8mb4
default-time-zone='+08:00'
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/zbdb/mysqldata
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 2M
tmp_table_size = 512M
tmpdir = /tmp
max_allowed_packet = 16M
sql_mode = ""
#interactive_timeout = 28800
#wait_timeout = 28800
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 256M
lower_case_table_names=1
########log settings########
log_error = /data/zbdb/mysqllog/wlyy6771.err
slow_query_log = 1
slow_query_log_file = /data/zbdb/mysqllog/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds = 7776000
long_query_time = 2
min_examined_row_limit = 100
########replication settings########
log_bin = /data/zbdb/mysqllog/wlyy6771-bin
sync_binlog = 100
binlog_format = row 
relay_log = /data/zbdb/mysqllog/relay.log
relay_log_recovery = 1

########innodb settings########
innodb_page_size = 16384
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir =  /data/zbdb/mysqllog/redolog/
innodb_undo_directory =  /data/zbdb/mysqllog/undolog/
innodb_flush_neighbors = 1
#innodb_log_file_size = 300M
innodb_log_buffer_size = 16M
innodb_purge_threads = 4
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
innodb_log_file_size = 1024M
innodb_strict_mode = 0
innodb_sort_buffer_size = 64M
########semi sync replication settings########
#plugin_dir=/usr/local/mysql/lib/plugin
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
prompt=\\u@\\d \\r:\\m:\\s>


[mysqlhotcopy]
interactive-timeout

#添加用户和组
#groupadd mysql
#useradd -g mysql mysql

#改变目录属主

#chown -R mysql.mysql /home/mysql-8.0.30

# bin/mysqld --initialize --user=mysql --basedir=/home/mysql-8.0.30 --datadir=/home/mysql-8.0.30/data

#复制配置文件到默认位置（没有就新建/etc/my.cnf）
#cp my_default.cnf /etc/my.cnf

#修改 mysql.server

#拉到最上面修改
#basedir=/home/mysql-8.0.30
#datadir=/home/mysql-8.0.30/data

#开机启动脚本
#cp mysql.server /etc/init.d/mysqld

#启动
#service mysqld start

vim /etc/profile
export MYSQL_HOME="/home/mysql-8.0.30"
export PATH="$PATH:$MYSQL_HOME/bin"

alter user root@localhost identified WITH 'mysql_native_password' by '8aH8x*M6Zv';
create user 'root'@'%' identified by '8aH8x*M6Zv';
grant all privileges on *.* to 'root'@'%' with grant option;
create database xx default character set utf8mb4 collate utf8mb4_0900_ai_ci;
create user 'nacos'@'%' identified by 'Vk$rk8CCMe';
grant all privileges on nacos.* to 'nacos'@'%' with grant option;

版本：5.6.30

my.cnf

[mysql]
default-character-set=utf8mb4
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
########basic settings########
port=3306
user=mysql
socket=/tmp/mysql.sock
pid-file=/tmp/mysqld.pid
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
basedir=/home/mysql-8.0.30/mysql-8.0
datadir=/home/mysql-8.0.30/data
log-bin=/home/mysql-8.0.30/logs/mysql-bin
character-set-server=utf8mb4   #数据库默认编码
autocommit=1  #自动提交事务
symbolic-links=0  #禁用符号连接以防止各类安全风险安全
tmp_table_size = 512M
tmpdir = /tmp
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 256M  
lower_case_table_names=1 #表名不区分大小写

########innodb settings########
innodb_page_size = 8192
innodb_buffer_pool_size = 10G   #根据主机内存大小的60%-80%设置
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_undo_directory =  /home/mysql-8.0.30/logs/undolog/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 300M
innodb_log_buffer_size = 16M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 256M

########log settings########
log-error=/home/mysql-8.0.30/logs/mysql.err
slow_query_log = 1
slow_query_log_file = /home/mysql-8.0.30/logs/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1  ##记录管理语句
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
long_query_time = 2   #记录执行时间超过参数long_query_time 设置值的SQL语句，默认不记录。
min_examined_row_limit = 100


[mysqldump]
quick
max_allowed_packet = 16M


[mysqlhotcopy]
interactive-timeout

mysqldump -uroot -p --single-transaction --master-data=2 --all-databases > /data/alldatabases.sql    #导出数据

cd /usr/local && wget https://file.zjwlyy.cn/mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz
tar -zxvf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz && mv mysql-5.6.30-linux-glibc2.5-x86_64 mysql

groupadd mysql
useradd -g mysql mysql

rm -rf /data/zbdb/*

mkdir -p /data/zbdb/mysqllog/redolog
mkdir -p /data/zbdb/mysqldata/

chown -R mysql:mysql /data/zbdb/mysqllog
chown -R mysql:mysql /data/zbdb/mysqldata

./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/zbdb/mysqldata


vim /etc/profile
export MYSQL_HOME="/usr/local/mysql"
export PATH="$PATH:$MYSQL_HOME/bin"

#修改密码
mysqladmin -u root password '5e0e5a0daa' -p


#创建同步用户
grant replication slave on *.* to "rep"@"%" identified by "wlyy@0628";

#导入数据
source /data/alldatabases.sql

#配置同步
CHANGE MASTER TO MASTER_HOST='10.8.67.71', MASTER_USER='rep', MASTER_PASSWORD='wlyy@0628', MASTER_LOG_FILE='wlyy6771-bin.000002',MASTER_LOG_POS=1307;

#开启同步
start slave

#如果有问题
reset slave