MySQL8.0单实例部署

 

目 录

 

一、服务器初始化

1. 配置时区

2. 安装基础软件包

3. 关闭防火墙 iptables

4. 配置历史命令格式

5. 优化系统参数配置

6. 关闭透明大页

7. 关闭NUMA

8. 配置MySQL用户limits资源限制

9. 卸载系统自带日版本MySQL相关RPM包

二、手动部署单实例

1. 创建MySQL用户和组

2. 创建MySQL运行需要的目录

3. 解压MySQL软件包

4. 配置文件模板

5. 设置MySQL软件及数据目录权限

6. 初始化MySQL数据库文件

7. 创建MySQL启停脚本

8. 创建用户

三、MySQL参数优化建议

1. 查询缓存

2. InnoDB buffer pool缓冲池优化

3. InnoDB存储引擎相关优化

4. 其他参数

 

 

一、服务器初始化

1. 配置时区

l 检查时区

shell> date -R

shell> timedatectl set-timezone Asia/Shanghai

l 如果不是东八区，设置软链调整正确的时区

shell> ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

shell> ll /etc|grep localtime

## 调整后重启crontab

shell> systemctl restart crond

2. 安装基础软件包

shell> yum install --quiet -y screen sudo vi vim less sshpass which wget net-tools net-snmp fontconfig libaio libaio-devel lvm2 hwloc logrotate crontabs libpciaccess lsof perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey libev perl-Data-Dumper perl lz4 libunwind gdb sysstat expect lrzsz unzip dstat glances > /dev/null

3. 关闭防火墙 iptables

l 建议关闭 iptables，否则可能会对数据库服务造成影响

shell> systemctl stop firewalld.service

shell> systemctl disable firewalld.service

l 检查selinux是否关闭

shell> getenforce

l 如果未关闭，配置关闭selinux

shell> setenforce 0

shell> vi /etc/selinux/config

 

SELINUX=disabled

SELINUXTYPE=targeted

4. 配置历史命令格式

l 使history记录更多历史操作记录，并记录时间戳和执行用户

shell> vi /etc/bashrc

 

export HISTFILESIZE=500000000

export HISTSIZE=1000000

export PROMPT_COMMAND="history -a"

export HISTTIMEFORMAT='%F %T '

export HISTTIMEFORMAT="%Y-%m-%d %H:%M:%S `whoami` "

l 使配置的环境变量生效

shell> source /etc/bashrc

5. 优化系统参数配置

shell> vi /etc/sysctl.conf

 

vm.overcommit_memory = 1

vm.swappiness=1

fs.file-max = 1024000

## 生效

shell> sysctl -p

6. 关闭透明大页

l 查看透明大页是否开启

shell> cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never ## always表示开启了

 

shell> cat /sys/kernel/mm/transparent_hugepage/defrag

[always] madvise never ## always表示开启了

l 永久关闭

## 将关闭THP的内容写入rc.local

shell> vi /etc/rc.d/rc.local

 

echo never >> /sys/kernel/mm/transparent_hugepage/enabled

echo never >> /sys/kernel/mm/transparent_hugepage/defrag

## 给rc.local加上x权限

shell> chmod +x /etc/rc.d/rc.local

7. 关闭NUMA

l 使用 MySQL5.7.9 之后的版本，不用关闭服务器的 numa

8. 配置MySQL用户limits资源限制

shell> vi /etc/security/limits.d/mysql.conf

mysql soft nofile 65535

mysql hard nofile 65535

mysql soft nproc 65535

mysql hard nproc 65535

 

9. 卸载系统自带版本MySQL相关RPM包

l 某些服务系预装了 MySQL，但是版本是5.1，避免客户端版本差异带来的影响，应该卸载这类rpm包

shell> rpm -qa|grep mysql -i

shell> rpm -e --nodeps mysql-xxx-5.1..x86_64

shell> mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d`

 

二、手动部署单实例

1. 创建MySQL用户和组

shell> groupadd mysql

shell> useradd -r -g mysql -s /bin/false mysql

2. 创建MySQL运行需要的目录

shell> mkdir -p /data/mysql/base

shell> mkdir -p /data/mysql/data/3306

shell> mkdir -p /data/mysql/log/binlog/3306

shell> mkdir -p /data/mysql/log/relaylog/3306

shell> mkdir -p /data/mysql/log/redolog/3306

shell> mkdir -p /data/mysql/tmp/3306

shell> mdkir -p /data/mysql/etc/3306

shell> chmod -R 750 /data/mysql

 

3. 解压MySQL软件包

l 解压8.0.x版本MySQL到指定目录

shell> wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.44-linux-glibc2.28-x86_64.tar.xz

shell> tar xzf mysql-8.0.44-linux-glibc2.28-x86_64.tar.xz

shell> mv mysql-8.0.44-linux-glibc2.28-x86_64 /data/mysql/base/8.0.44

 

4. 配置文件模板

shell> vi /data/mysql/etc/3306/my.cnf

l 注意其中有些参数根据实际情况修改

 

l 8.0.x

[mysqld]

create_admin_listener_thread = ON

admin_address = 127.0.0.1

 

# 管理员登录端口,自定义

admin_port = 6306

 

# 数据库端口,自定义

port = 3306

 

# server_id建议配置成ip+数据库端口

server_id = 628569637

 

# MySQL二进制包的路径

basedir = /data/mysql/base/8.0.44

 

datadir = /data/mysql/data/3306

log_bin = /data/mysql/log/binlog/3306/mysql-bin

relay_log = /data/mysql/log/relaylog/3306/mysql-relay

innodb_log_group_home_dir = /data/mysql/log/redolog/3306

tmpdir = /data/mysql/tmp/3306

log_error = /data/mysql/data/3306/mysql-error.log

 

# 数据库实例ip

report_host = 10.186.61.16

 

default_authentication_plugin=mysql_native_password

mysqlx=0

# admin_address=127.0.0.1

# admin_port=33062

# create_admin_listener_thread=ON

 

# BINLOG

binlog_error_action = ABORT_SERVER

binlog_format = row

binlog_checksum = NONE

binlog_rows_query_log_events = 1

log_replica_updates = 1

max_binlog_size = 250M

relay_log_info_repository = TABLE

relay_log_recovery = 1

sync_binlog = 1

log_bin_trust_function_creators = 1

binlog_encryption = OFF  # can not set the variable to ON, because we can not handle encrypted binlog so far.

 

 

# GTID #

gtid_mode = ON

enforce_gtid_consistency = 1

binlog_gtid_simple_recovery = 1

 

# ENGINE

default_storage_engine = InnoDB

 

# 物理内存大于32G时buffer pool配置到50-70%左右，物理内存小于32G时buffer pool配置60%左右即可。

innodb_buffer_pool_size = 1G        

 

innodb_data_file_path = ibdata1:1G:autoextend

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit=1

innodb_flush_method = O_DIRECT

innodb_log_buffer_size = 64M

innodb_log_file_size = 2G

innodb_log_files_in_group = 2

innodb_max_dirty_pages_pct = 75

innodb_print_all_deadlocks=1

innodb_stats_on_metadata = 0

innodb_strict_mode = 1

innodb_io_capacity_max=6000

innodb_io_capacity = 3000

innodb_write_io_threads=8

innodb_read_io_threads=8

innodb_buffer_pool_instances=8

innodb_max_undo_log_size=4G

innodb_undo_log_truncate=1

innodb_purge_threads = 4

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_dump_pct=25

innodb_sort_buffer_size = 8M

innodb_page_cleaners = 8

innodb_lock_wait_timeout = 10

innodb_flush_neighbors = 1

innodb_thread_concurrency = 8

innodb_stats_persistent_sample_pages = 64

innodb_autoinc_lock_mode = 2

innodb_online_alter_log_max_size = 1G

innodb_open_files = 4096

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:50G

innodb_rollback_segments = 128

innodb_numa_interleave = 1

innodb_monitor_enable = log_lsn_checkpoint_age,log_max_modified_age_async,log_max_modified_age_sync

 

# CACHE

key_buffer_size = 16M

tmp_table_size = 64M

max_heap_table_size = 64M

 

# 数据库连接数,自定义

max_connections = 10000

 

max_connect_errors = 1000

thread_cache_size = 200

open_files_limit = 65535

binlog_cache_size = 1M

join_buffer_size = 8M

sort_buffer_size = 2M

read_buffer_size = 8M

read_rnd_buffer_size = 8M

table_definition_cache = 2000

table_open_cache_instances = 8

table_open_cache = 2000

 

# SLOW LOG

slow_query_log = 1

slow_query_log_file = /data/mysql/data/3306/mysql-slow.log

log_slow_admin_statements = 1

log_slow_replica_statements = 1

long_query_time  = 1

 

# PLUGINS

plugin_load = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"

 

# SEMISYNC #

loose_rpl_semi_sync_source_enabled = 1

loose_rpl_semi_sync_replica_enabled = 0

loose_rpl_semi_sync_source_wait_for_replica_count = 1

loose_rpl_semi_sync_source_wait_no_replica = 0

loose_rpl_semi_sync_source_timeout = 30000 # 30s

 

# CLIENT_DEPRECATE_EOF

session_track_schema = 1

session_track_state_change = 1

session_track_system_variables = '*'

 

# MISC

character_set_server = utf8mb4

collation_server = utf8mb4_0900_ai_ci

log_timestamps=SYSTEM

lower_case_table_names = 1

max_allowed_packet = 64M

read_only = 0

super_read_only = 0

skip_external_locking = 1

skip_name_resolve = 1

skip_replica_start = 1

socket = /data/mysql/data/3306/mysqld.sock

pid_file = /data/mysql/data/3306/mysqld.pid

disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB

log-output = FILE

binlog_expire_logs_seconds = 2592000

 

#

transaction_isolation=READ-COMMITTED

replica_parallel_type = LOGICAL_CLOCK

replica_parallel_workers=8

replica_preserve_commit_order=on       # replica bear read flow

replica_max_allowed_packet = 1073741824

log_error_verbosity = 3

binlog_transaction_dependency_tracking = WRITESET

transaction_write_set_extraction = XXHASH64

secure_file_priv = ""

interactive_timeout = 1800

wait_timeout = 1800

explicit_defaults_for_timestamp = 1

performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'

performance-schema-instrument = 'memory/% = COUNTED'

performance-schema-instrument = 'stage/innodb/alter%=ON'

performance-schema-consumer-events-stages-current=ON

performance-schema-consumer-events-stages-history=ON

performance-schema-consumer-events-stages-history-long=ON

 

5. 设置MySQL软件及数据目录权限

shell> chown -R mysql:mysql /data/mysql

6. 初始化MySQL数据库文件

#根据数据库版本，指定路径

shell> /data/mysql/base/8.0.44/bin/mysqld --defaults-file=/data/mysql/etc/3306/my.cnf --initialize-insecure --user=mysql

7. 创建MySQL启停脚本

shell> vi /etc/systemd/system/mysql_3306.service

[Unit]

Description=MySQL Server

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

Before=uagent.service

 

[Install]

WantedBy=multi-user.target

 

[Service]

User=mysql

Group=mysql

 

# 启动结束后会发出通知信号，systemd 接下来可以启动其他服务，在容器中可能需要设置为forking（启动一个子进程，并且启动后父进程会退出）

#Type=notify

Type=forking

PIDFile=/data/mysql/data/3306/mysqld.pid

 

# Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

 

# 根据数据库版本，指定路径

ExecStart=/data/mysql/base/8.0.44/bin/mysqld --defaults-file=/data/mysql/etc/3306/my.cnf --daemonize $MYSQLD_OPTS

 

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

 

# Sets open_files_limit

LimitNOFILE = 65535

 

# 守护，意外的停止会被重启

Restart=on-failure

 

#设置安全退出码，不会被自动重启

RestartPreventExitStatus=1

 

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.

Environment=MYSQLD_PARENT_PID=1

 

# 设置私有的 tmpdir，建议设置为 false 关闭默认

PrivateTmp=false

## 重载进程服务文件

shell> systemctl daemon-reload

## 启动MySQL数据库

shell> systemctl start mysql_3306

shell> systemctl enabled mysql_3306

 

8. 创建用户

l 登录MySQL数据库

shell> /data/mysql/base/8.0.44/bin/mysql  -S /data/mysql/data/3306/mysqld.sock

l 创建用户

mysql> CREATE USER 'develop'@'%' IDENTIFIED WITH mysql_native_password BY 'Password_123';GRANT ALL ON *.* TO 'develop'@'%';

mysql> create user repl@'%' identified by 'repl';

mysql> grant replication slave on *.* to repl@'%';

mysql> flush privileges;

 

三、MySQL参数优化建议

1. 查询缓存

参数	参数建议	说明
query_cache_type	0	表结构或数据结构发生改变时，会导致查询缓存失效。 经常做cache失效操作，开销大。 5.7建议关闭，8.0已经弃用。
query_cache_size	\

2. InnoDB buffer pool缓冲池优化

参数	参数建议	说明
innodb_buffer_pool_size	物理内存的50%~70%	InnoDB引擎的缓冲池大小 大于32G时配置到50-75%左右，小于32G配置60%左右即可。 调整方式：按照innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size 的整数倍调整大小，可以在线修改。
innodb_buffer_pool_instances	8	InnoDB缓冲池被分成的实例数量，多个缓冲池可以减少内部对缓冲池数据结构的争用。 可以提高MySQL的并发性，减少不同线程读写页面的竞争。 设置太小锁冲突集中，设置太大，维护成本高。 默认值是8，采用默认值即可。
innodb_io_capacity	\	每秒可以读写磁盘的次数，会直接决定MySQL的TPS。（对读无效，对写有决定意义） 一般是innodb_io_capacity_max的一半。 根据服务器IOPS能力适当调整： SATA盘，15000 rpms，200 iops能力。 Sas raid10:2000 iops。 一般配普通SSD盘，可以调整到 8000 - 20000。 配置高端PCIe SSD卡，则可以调整的更高，比如 50000 - 80000。 Fusion-io（闪存卡）：25000 - 50000。 可以用户sysbench或其他工具来测试MySQL的磁盘吞吐量。
innodb_flush_neighbors	\	机械盘推荐设置为1 ，固态盘设置为0。 作用：1表示将临近数据脏页一块刷新，针对机械盘增加磁盘IO刷写性能。 此参数控制刷脏时，对旁边数据页“连坐”（一起刷掉）的行为。 1是开启一同刷脏，0是不开启。再机械硬盘中，“连坐”的行为可以减少随机IO。机械硬盘的IOPS容易成为瓶颈，而固态硬盘的IOPS有了很大提升，取消临近数据页一同刷脏的行为将可以提高查询语句的响应时间。
innodb_max_dirty_pages_pct	60	InnoDB刷新脏页的比例（也是最大脏页比例，innodb会尝试刷新刷脏，以使得buffer pool中的脏页不高于这个比例），基数是缓冲池。 如果业务并发量比较高，可以根据内存空间使用情况，将参数调低，保证有足够的内存空间写入新的数据。 5.7默认值75，8.0默认值90，建议设置为60。
innodb_buffer_pool_dump_pct	25	记录buffer pool中缓存页的比例。 例如，如果有4个缓冲池，每个缓冲池有100个页面，并且innodb_buffer_pool_dump_pct设置为25，那么每个缓冲池中最近使用的25个页面将被转储 范围是1-100，默认值是25，建议采用默认值即可。
innodb_buffer_pool_dump_at_shutdown	启用	指定在MySQL服务器关闭时，是否记录InnoDB缓存池中的缓存页，以缩短下次重启时的预热过程，默认启用。
innodb_buffer_pool_load_at_startup	启用	指定MySQL服务在启动时，InnoDB缓冲池通过加载之前的缓存页数据来启动预热，默认启用。

3. InnoDB存储引擎相关优化

参数	参数建议	说明
innodb_file_per_table	1	启用独立表空间，每张表数据独立存放在单独的文件中，提高数据库读写性能。
innodb_log_file_size	2-4G	redo log重做日志文件大小 重做日志文件不能太大或太小，太大数据库crash safe时，启动恢复会很慢，太小容易频繁刷脏页。 一般建议将其设置为2-4GB大小。
innodb_log_files_in_group	2	重做日志个数 InnoDB以循环方式写入文件，默认值为2。
innodb_log_buffer_size	\	redo log缓冲区大小(以字节为单位) 主要用于在InnoDB执行事务期间，保存数据页的变化。 16G内存建议设置为64M
innodb_flush_method	O_DIRECT	InnoDB数据刷盘策略 建议设置为O_DIRECT模式，在此模式下，数据文件的写入操作直接从buffer pool中刷新到磁盘上，而redo log使用操作系统缓存，避免Innodb和OS的双重缓冲。

4. 其他参数

参数	参数建议	说明
sync_binlog	1	binlog刷盘策略 生产主库没有特殊情况，一定配置为1。 设置写了多少个binlog events之后把binlog日志刷新到磁盘。 默认为0，表示二进制日志的落盘操作由操作系统刷新，性能最好，但是也最不安全，发生意外崩溃时没有落盘的binlog日志将丢失。 为了确保binlog日志的安全，建议设置为1，这样在每次事务提交时就会调用fdatasync()实时把binlog日志刷新到磁盘。
innodb_read_io_threads	核数*2	默认是4，范围1-64（5.7） MySQL性能调优-参数配置建议： 配置MySQL的IO线程与CPU核数一致。
innodb_write_io_threads	核数*2
innodb_flush_log_at_trx_commit	1	redo log刷写日志策略：控制redo buffer何时刷新到磁盘日志文件的参数。 0：在事务提交时，不会立即触发将缓存日志写入到磁盘日志文件中，而是大约每秒一次由主线程刷新到磁盘文件的操作，并调用操作系统的fsync刷新IO缓存。最不安全，但是性能最好。 在数据库挂了或者宕机的时候可能会有一秒数据的丢失。
		1：在事务提交时，innodb立即触发将缓存中的redo日志写回到磁盘日志文件中，并调用系统fsync刷新IO缓存，最安全，但是性能最差。 只要事务提交就不会有数据丢失。MySQL挂了或者宕机时，因为事务没有提交，所以日志丢了也不会有损失。 生产主库没有特殊情况，一定配置为1。
		2：在事务提交时，innodb立即将缓存中的redo日志写回到日志文件，但是并不会马上调用fsync来刷新IO缓存，而是大约每秒一次磁盘IO缓存刷新操作或者交由操作系统来决定合适刷新磁盘IO缓存，安全性和性能介于1和0之间。 只要事务提交成功，redo log buffer只写入文件系统缓存（page cache）。MySQL挂了不会有数据丢失，但是宕机可能会有一秒的数据损失。
tmp_table_size	\	规定了内存临时表的最大值 实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。 当内存临时表超出限制时，MySQL会自动将内存中的数据写入到磁盘临时表中，写的位置由tmpdir变量决定。如果大量存在使用磁盘临时表的情况，会影响MySQL的性能。 16G内存建议设置为64M大小。
max_heap_table_size	\	定义了用户可以创建的内存表(memory table)的大小，这个值用来计算内存表的最大行数值 与tmp_table_size设置一致即可。