常配置MySQL服务的一些设置记录

[client]

port = 3306 default-character-set = utf8

[mysqld]

port = 3306 user = mysql character-set-server = utf8 skip-external-locking skip_name_resolve = ON bind_address=

max_connections = 5000

max_connect_errors = 10 max_allowed_packet = 64M

default-storage-engine = INNODB

innodb_file_per_table = 1 innodb_buffer_pool_size = 16G innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 60 innodb_flush_method = O_DIRECT

default-time-zone = '+8:00'

event_scheduler = ON open_files_limit = 51200 read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 16M

thread_cache_size = 64

query_cache_type = 1 query_cache_size = 256M query_cache_limit = 10M

transaction_isolation = READ-COMMITTED

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

server-id = 1

log_bin = mysql-bin binlog_format = mixed binlog_ignore_db = mysql expire_logs_days = 90

log_error =/var/log/mysql-error.log

innodb_print_all_deadlocks = 1 slow_query_log = 1 long_query_time = 5 log_queries_not_using_indexes = 1 slow_query_log_file =/var/log/mysql-slow.log

[mysqldump]

quick max_allowed_packet = 500M

 

systemctl enable mysqld

systemctl start mysqld systemctl status mysqld grep "temporary password" /var/log/mysqld.log mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'Uiop!789';

grant replication slave on *.* to 'repuser'@'slaveip' identified by 'Ei:saH0u9ys<';

flush privileges; show master status;

slave的配置：

server-id=18 log-bin = mysql-bin sync_binlog=1 binlog_format=row binlog_ignore_db=mysql expire_logs_days = 30

change master to master_host='masterip',master_port=3306,master_user='repuser',master_password='Ei:saH0u9ys<',master_log_file='bin-log.000005',master_log_pos=120;

start slave; show slave status\G

主库设置binlog_group_commit_sync_delay=1

在MySQL5.7中，开启enhanced multi-threaded slave，从库具体配置参数如下：

slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=8 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON