MySQL部署

Docker安装Mysql

docker pull mysql:8
docker run --name mysql8 -v /opt/data/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 --restart=always -d mysql:8
# 5.7
docker run --name mysql -v /opt/docker/mysql/conf:/etc/mysql -v /opt/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=dfemall@2022 -p 23306:3306 --restart=always -d mysql:5.7.34

1.CentOS7上安装MySQL 5.7

参考文档：https://blog.csdn.net/m0_51510236/article/details/113791490
官网下载地址：https://downloads.mysql.com/archives/community/
下载列表：

• MySQL Server
• Client Utilities
• MySQL Configuration
• Shared Library
• Compatibility Libraries

安裝过程

yum install -y mysql-community-*-5.7.35-1.el7.x86_64.rpm
# 若安装出现mariadb-libs依赖错误，执行卸载后再次安装
yum remove mariadb-libs
# 开启MySQL服务器
systemctl start mysqld
# 查看默认生成的密码
cat /var/log/mysqld.log | grep password
# 登录mysql进行账号初始化
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dfemall@2112.MySQL';

MySQL优化

优化参考
AliSQL-8C-16G.cnf：<https://github.com/alibaba/AliSQL/wiki/AliSQL-8C-16G.cnf>
AliSQL-small.cnf：<https://github.com/alibaba/AliSQL/wiki/AliSQL-small.cnf>
AliSQL-middle.cnf：<https://github.com/alibaba/AliSQL/wiki/AliSQL-middle.cnf>
AliSQL-large.cnf：<https://github.com/alibaba/AliSQL/wiki/AliSQL-large.cnf>
# 8C 16G标准配置
vim /etc/my.cnf

# 默认编码修改
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

back_log = 3000
max_connect_errors = 100
max_connections = 4050
max_user_connections = 4050
max_heap_table_size = 64M
max_allowed_packet = 1024M
max_binlog_size = 500M
thread_stack = 256K
interactive_timeout = 7200
wait_timeout = 86400
sort_buffer_size = 848KB
read_buffer_size = 848KB
read_rnd_buffer_size = 432KB
join_buffer_size = 432KB
net_buffer_length = 16K
thread_cache_size = 100
ft_min_word_len = 4
transaction_isolation = READ-COMMITTED
tmp_table_size = 2097152
table_open_cache = 2000
skip_name_resolve
core-file
lower_case_table_names = 1
log-slave-updates = 1
binlog_cache_size = 2048KB
sync_binlog = 1
slow_query_log = 1
log_output = TABLE
long_query_time = 1
binlog_format = ROW
auto_increment_increment = 1
auto_increment_offset = 1
slave_net_timeout = 60
key_buffer_size = 16M
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 262144
myisam_max_sort_file_size = 2048K
myisam_repair_threads = 1
myisam_recover_options = FORCE
innodb_buffer_pool_size = 13108M
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1500M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_doublewrite = 1
innodb_rollback_on_timeout = OFF
innodb_autoinc_lock_mode = 1
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 2000
innodb_purge_threads = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
query_cache_type = 0
concurrent_insert = 1
query_cache_limit = 1048576
query_cache_min_res_unit = 1K
log-slow-admin-statements
innodb_stats_on_metadata = OFF
innodb_file_format = Barracuda
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
default_storage_engine = InnoDB
innodb_stats_sample_pages = 8
open_files_limit = 65535
gtid_mode = ON
loose_rds-anonymous-in-gtid-out-enable = 1
enforce-gtid-consistency = 1
loose_performance_schema = off
loose_binlog_order_commits = OFF
innodb_ft_max_token_size = 84
loose_tokudb_cache_size = 13108M
loose_opt_rds_enable_set_default_engine = OFF
log_bin_use_v1_row_events = 1
loose_innodb_rds_autoinc_persistent_interval = 1
delay_key_write = ON
key_cache_division_limit = 100
innodb_old_blocks_pct = 37
loose_rds_gtid_precommit = ON
loose_implicit_primary_key = 1
ft_query_expansion_limit = 20
loose_tokudb_checkpointing_period = 60
innodb_thread_sleep_delay = 10000
loose_innodb_log_compressed_pages = OFF
init_connect = ''
loose_rds_sql_select_filter = ''
innodb_print_all_deadlocks = OFF
delayed_insert_timeout = 300
loose_thread_pool_stall_limit = 30
loose_thread_pool_oversubscribe = 10
loose_max_statement_time = 0
loose_tokudb_commit_sync = ON
binlog_stmt_cache_size = 32768
net_retry_count = 10
binlog_checksum = CRC32
low_priority_updates = 0
loose_tokudb_support_xa = ON
autocommit = 1
loose_rds_set_connection_id_enabled = ON
key_cache_age_threshold = 300
innodb_concurrency_tickets = 5000
loose_innodb_rds_log_checksum_algorithm = INNODB
table_definition_cache = 512
loose_rds_check_core_file_enabled = ON
loose_tokudb_rpl_lookup_rows = OFF
innodb_use_native_aio = 0
net_write_timeout = 60
loose_rds_threads_running_high_watermark = 50000
slave_exec_mode = strict
query_alloc_block_size = 8192
loose_tokudb_fs_reserve_percent = 5
max_prepared_stmt_count = 16382
net_read_timeout = 30
loose_innodb_rds_min_concurrency_tickets = 50
loose_rds_ic_reduce_hint_enable = OFF
max_write_lock_count = 102400
innodb_old_blocks_time = 1000
innodb_stats_method = nulls_equal
max_length_for_sort_data = 1024
query_prealloc_size = 8192
innodb_large_prefix = ON
delayed_insert_limit = 100
group_concat_max_len = 1024
innodb_disable_sort_file_cache = ON
loose_tokudb_buffer_pool_ratio = 0
loose_innodb_rds_autoinc_persistent = ON
innodb_ft_min_token_size = 3
loose_rds_enable_log_global_var_update = ON
loose_opt_rds_last_error_gtid = ON
loose_skip_symbolic_links = ON
loose_tokudb_rpl_unique_checks = OFF
key_cache_block_size = 1024
loose_tokudb_directio = OFF
slow_launch_time = 2
loose_tokudb_fsync_log_period = 0
loose_thread_handling = "one-thread-per-connection"
loose_rds_allow_unsafe_stmt_with_gtid = ON
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 3000
eq_range_index_dive_limit = 10
loose_rds_reset_all_filter = 0
loose_innodb_adaptive_hash_index_parts = 8
div_precision_increment = 4
binlog_row_image = full
loose_tokudb_row_format = tokudb_zlib
innodb_strict_mode = OFF
delayed_queue_size = 1000
default_week_format = 0
loose_rds_expand_fast_index_creation = ON
log_queries_not_using_indexes = OFF
innodb_read_ahead_threshold = 56
loose_slave_parallel_workers = 8
connect_timeout = 10
default_time_zone = SYSTEM
loose_rds_slave_read_no_lock = ON
sql_mode = ''
innodb_table_locks = ON
query_cache_size = 3145728
innodb_adaptive_hash_index = ON
innodb_purge_batch_size = 300
loose_innodb_rds_adaptive_tickets_algo = ON
loose_rpl_semi_sync_slave_trace_level = 1
loose_rpl_semi_sync_master_timeout = 1000
loose_rpl_semi_sync_master_trace_level = 1
loose_rpl_semi_sync_slave_enabled = OFF
loose_rpl_semi_sync_master_enabled = OFF
loose_rpl_semi_sync_master_wait_no_slave = ON

# 重启mysql服务
systemctl restart mysqld

# 查询错误日志
cat /var/log/mysqld.log

配置文件模板

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

back_log = 3000
max_connect_errors = 100
max_connections = 4050
max_user_connections = 4050
max_heap_table_size = 64M
max_allowed_packet = 1024M
max_binlog_size = 500M
thread_stack = 256K
interactive_timeout = 7200
wait_timeout = 86400
sort_buffer_size = 848KB
read_buffer_size = 848KB
read_rnd_buffer_size = 432KB
join_buffer_size = 432KB
net_buffer_length = 16K
thread_cache_size = 100
ft_min_word_len = 4
transaction_isolation = READ-COMMITTED
tmp_table_size = 2097152
table_open_cache = 2000
skip_name_resolve
core-file
lower_case_table_names = 1
log-slave-updates = 1
binlog_cache_size = 2048KB
sync_binlog = 1
slow_query_log = 1
log_output = TABLE
long_query_time = 1
binlog_format = ROW
auto_increment_increment = 1
auto_increment_offset = 1
slave_net_timeout = 60
key_buffer_size = 16M
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 262144
myisam_max_sort_file_size = 2048K
myisam_repair_threads = 1
myisam_recover_options = FORCE
innodb_buffer_pool_size = 13108M
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1500M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_doublewrite = 1
innodb_rollback_on_timeout = OFF
innodb_autoinc_lock_mode = 1
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 2000
innodb_purge_threads = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
query_cache_type = 0
concurrent_insert = 1
query_cache_limit = 1048576
query_cache_min_res_unit = 1K
log-slow-admin-statements
innodb_stats_on_metadata = OFF
innodb_file_format = Barracuda
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
default_storage_engine = InnoDB
innodb_stats_sample_pages = 8
open_files_limit = 65535
gtid_mode = ON
loose_rds-anonymous-in-gtid-out-enable = 1
enforce-gtid-consistency = 1
loose_performance_schema = off
loose_binlog_order_commits = OFF
innodb_ft_max_token_size = 84
loose_tokudb_cache_size = 13108M
loose_opt_rds_enable_set_default_engine = OFF
log_bin_use_v1_row_events = 1
loose_innodb_rds_autoinc_persistent_interval = 1
delay_key_write = ON
key_cache_division_limit = 100
innodb_old_blocks_pct = 37
loose_rds_gtid_precommit = ON
loose_implicit_primary_key = 1
ft_query_expansion_limit = 20
loose_tokudb_checkpointing_period = 60
innodb_thread_sleep_delay = 10000
loose_innodb_log_compressed_pages = OFF
init_connect = ''
loose_rds_sql_select_filter = ''
innodb_print_all_deadlocks = OFF
delayed_insert_timeout = 300
loose_thread_pool_stall_limit = 30
loose_thread_pool_oversubscribe = 10
loose_max_statement_time = 0
loose_tokudb_commit_sync = ON
binlog_stmt_cache_size = 32768
net_retry_count = 10
binlog_checksum = CRC32
low_priority_updates = 0
loose_tokudb_support_xa = ON
autocommit = 1
loose_rds_set_connection_id_enabled = ON
key_cache_age_threshold = 300
innodb_concurrency_tickets = 5000
loose_innodb_rds_log_checksum_algorithm = INNODB
table_definition_cache = 512
loose_rds_check_core_file_enabled = ON
loose_tokudb_rpl_lookup_rows = OFF
innodb_use_native_aio = 0
net_write_timeout = 60
loose_rds_threads_running_high_watermark = 50000
slave_exec_mode = strict
query_alloc_block_size = 8192
loose_tokudb_fs_reserve_percent = 5
max_prepared_stmt_count = 16382
net_read_timeout = 30
loose_innodb_rds_min_concurrency_tickets = 50
loose_rds_ic_reduce_hint_enable = OFF
max_write_lock_count = 102400
innodb_old_blocks_time = 1000
innodb_stats_method = nulls_equal
max_length_for_sort_data = 1024
query_prealloc_size = 8192
innodb_large_prefix = OFF
delayed_insert_limit = 100
group_concat_max_len = 1024
innodb_disable_sort_file_cache = ON
loose_tokudb_buffer_pool_ratio = 0
loose_innodb_rds_autoinc_persistent = ON
innodb_ft_min_token_size = 3
loose_rds_enable_log_global_var_update = ON
loose_opt_rds_last_error_gtid = ON
loose_skip_symbolic_links = ON
loose_tokudb_rpl_unique_checks = OFF
key_cache_block_size = 1024
loose_tokudb_directio = OFF
slow_launch_time = 2
loose_tokudb_fsync_log_period = 0
loose_thread_handling = "one-thread-per-connection"
loose_rds_allow_unsafe_stmt_with_gtid = ON
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 3000
eq_range_index_dive_limit = 10
loose_rds_reset_all_filter = 0
loose_innodb_adaptive_hash_index_parts = 8
div_precision_increment = 4
binlog_row_image = full
loose_tokudb_row_format = tokudb_zlib
innodb_strict_mode = OFF
delayed_queue_size = 1000
default_week_format = 0
loose_rds_expand_fast_index_creation = ON
log_queries_not_using_indexes = OFF
innodb_read_ahead_threshold = 56
loose_slave_parallel_workers = 8
connect_timeout = 10
default_time_zone = SYSTEM
loose_rds_slave_read_no_lock = ON
sql_mode = ''
innodb_table_locks = ON
query_cache_size = 3145728
innodb_adaptive_hash_index = ON
innodb_purge_batch_size = 300
loose_innodb_rds_adaptive_tickets_algo = ON
loose_rpl_semi_sync_slave_trace_level = 1
loose_rpl_semi_sync_master_timeout = 1000
loose_rpl_semi_sync_master_trace_level = 1
loose_rpl_semi_sync_slave_enabled = OFF
loose_rpl_semi_sync_master_enabled = OFF
loose_rpl_semi_sync_master_wait_no_slave = ON