Table of Contents

• 1、准备阶段
• 1.1、部署规划
• 1.2、硬件准备
• 1.3、软件准备
• 1.4、环境清理
• 2、实施阶段
• 2.1、操作系统实施
• 2.2、数据库部署实施
• 3、完成

1、准备阶段

1.1、部署规划

本次部署用于测试环境，单机模式，不需要主备；MySQL数据库版本要MySQL5.7.26社区版；操作系统为CentOS7。（生产环境强烈建议不要装单机，风险太高，单机只适合测试环境或者自己学习装来用）

1.2、硬件准备

1.3、软件准备

下方链接是MySQL社区版下载官网地址，访问后，根据部署规划选择需要的MySQL软件包。

https://downloads.mysql.com/archives/community/

1.4、环境清理

rpm -qa | grep -i 'mysql\|mariadb'

yum remove mariadb-server mariadb-client mariadb-libs
yum remove mysql-community-server 
yum remove mysql-community-client mysql-common

2、实施阶段

2.1、操作系统实施

2.1.1、修改主机名（若已完成，则忽略）

hostnamectl set-hostname mysql1

2.1.2、关闭防火墙与SELINUX（若已完成，则忽略）

#关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

#关闭selinux

setenforce 0
sed -i 's/^SELINUX=.*$/SELINUX=disabled/g' /etc/selinux/config

2.1.3、检查时区、时间（若已完成，则忽略）

#需要为东八区，时间正确

# date -R
Thu, 23 Apr 2020 04:50:47 -0400
# cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? yes
# date -R
Thu, 23 Apr 2020 16:51:02 +0800

2.1.4、调整系统限制参数

cat >> /etc/security/limits.conf << EOF
########## mysql limit ########## 
mysql   soft    nofile   65535
mysql   hard    nofile   65535
mysql   soft    nproc    65535
mysql   hard    nproc    65535
EOF

2.1.5、关闭大透明页

#1、查看系统有没有开启透明大页

#cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never
#cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
#方括号[]中的值代表当前有效值，如果其中是never，代表透明大页被禁用，如果是always，代表透明大页已启用。

#2、直接禁用透明大页（临时）

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

#3、永久禁用透明大页

#编辑rc.local文件
vim /etc/rc.d/rc.local
#将下面内容写入rc.local文件
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
#给rc.local文件增加执行权限
chmod +x /etc/rc.d/rc.local

2.1.6、关闭NUMA

#编辑GRUB文件
vim /etc/default/grub
#在GRUB_CMDLINE_LINUX参数末尾添加numa=off（保留原有参数）
#GRUB_CMDLINE_LINUX="...原有参数... numa=off"
#例如：
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"

#重新生成GRUB配置
grub2-mkconfig -o /etc/grub2.cfg

2.1.7、安装系统包

yum install cmake -y
yum install make -y
yum install gcc -y
yum install gcc-c++ -y
yum install bison -y
yum install ncurses -y 
yum install ncurses-devel -y 
yum install libaio* -y
yum install net-tools -y
yum install iotop -y
yum install telnet -y
yum install sysstat -y

2.2、数据库部署实施

2.2.1、创建MySQL用户、组

groupadd mysql
useradd -g mysql mysql
#查看mysql用户信息
id mysql

2.2.2、创建安装目录，并授权

mkdir -p /mysqldata/my3306/{data,etc,pid,socket,tmp,login,log}
chown -R mysql:mysql /mysqldata

2.2.3、上传MySQL软件压缩包

#上传压缩包至tmp命令
tar -zxvf /tmp/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
#创建软链接
ln -s /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/ /usr/local/mysql
#修改权限
chown -R mysql:mysql /usr/local/mysql/

2.2.4、添加MySQL可执行路径

#编辑配置文件
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
#加载使其生效
source /etc/profile

2.2.5、配置my.cnf参数文件
my.cnf是MySQL的核心文件，其使用方括号[]来定义不同的模块，每个模块包含一组相关配置选项。这些模块及其配置选项会对MySQL的运行与性能有直接影响，如果部署后要修改my.cnf，需要重启mysql数据库服务。请注意，本次的my.cnf是以8G内存、单机为基础进行配置的。

mv /etc/my.cnf /etc/my.cnf.bak #/etc下没有这个文件就忽略这条命令
vim /mysqldata/my3306/etc/my.cnf

#MySQL configuration for 8G memory
[client]
############# CLIENT #############
port    = 3306
socket                             = /mysqldata/my3306/socket/mysql.sock
default-character-set = utf8

[mysqld]
############# GENERAL #############
server-id                          = 1
autocommit                         = ON
character_set_server               = utf8
collation_server                   = utf8_general_ci
explicit_defaults_for_timestamp    = ON
lower_case_table_names             = 1
port                               = 3306
transaction_isolation              = READ-COMMITTED
default_time_zone                  = "+8:00"
log_timestamps                     =system
############### PATH ##############
basedir                            = /usr/local/mysql
datadir                            = /mysqldata/my3306/data
tmpdir                             = /mysqldata/my3306/tmp
socket                             = /mysqldata/my3306/socket/mysql.sock
pid_file                           = /mysqldata/my3306/pid/mysql.pid
innodb_data_file_path              = ibdata1:1G:autoextend
innodb_data_home_dir               = /mysqldata/my3306/data
innodb_temp_data_file_path         = ibtmp1:12M:autoextend
log_error                          = /mysqldata/my3306/log/error.log
general_log_file                   = /mysqldata/my3306/log/general.log
slow_query_log_file                = /mysqldata/my3306/log/slow.log

log_bin                            = /mysqldata/my3306/log/mysql-bin
log_bin_index                      = /mysqldata/my3306/log/mysql-bin.index
relay_log                          = /mysqldata/my3306/log/relay-log
relay_log_index                    = /mysqldata/my3306/log/relay-log.index
relay_log_purge                    = 1
innodb_log_group_home_dir          = /mysqldata/my3306/data 
innodb_undo_directory              = /mysqldata/my3306/data
innodb_undo_tablespaces            = 3
log_bin_trust_function_creators    = on
############# INNODB #############
innodb_buffer_pool_size  = 4G         ##根据内存调整
innodb_buffer_pool_instances = 4      ##根据内存调整
innodb_log_file_size      = 1G
innodb_log_files_in_group = 3
innodb_log_buffer_size    = 64M
innodb_undo_log_truncate  = 1
innodb_max_undo_log_size  = 1024M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table    = 1
innodb_flush_method      = O_DIRECT
innodb_io_capacity        = 50000      ###根据磁盘io调整
innodb_io_capacity_max    = 50000    ###根据磁盘io调整
innodb_thread_concurrency = 0        ###默认值为0
innodb_autoinc_lock_mode  = 2
innodb_lock_wait_timeout  = 60
innodb_read_io_threads    = 4
innodb_write_io_threads   = 4
innodb_max_dirty_pages_pct = 80
innodb_autoextend_increment = 512
innodb_checksum_algorithm = CRC32
innodb_doublewrite        = 1
innodb_use_native_aio    = 1
innodb_open_files        = 8192
innodb_flush_neighbors  = 0  ### SSD 设置为0，HDD 设置为1
innodb_print_all_deadlocks  = 1 
innodb_online_alter_log_max_size  = 4G
innodb_undo_logs = 128
back_log = 2048

################ P_S ##################
performance-schema=1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
performance_schema_instrument = '%wait%=on' 
performance-schema-instrument='wait/lock/metadata/sql/mdl=on'

####### CACHES AND LIMITS #########
interactive_timeout                = 600
lock_wait_timeout                  = 60    ###MDL锁超时
max_connect_errors                 = 10000
max_connections                    = 2048
max_user_connections               = 1024
thread_stack                       = 256K
thread_cache_size        = 768
key_buffer_size          = 32M   
tmp_table_size            = 64M
max_heap_table_size      = 64M
read_buffer_size          = 2M
read_rnd_buffer_size      = 8M
sort_buffer_size          = 2M
join_buffer_size          = 2M
binlog_cache_size         = 2M
table_open_cache          = 8192
table_definition_cache             = 8192
table_open_cache_instances = 16
bulk_insert_buffer_size  = 64M

############# LOGGING #############
general_log                        = 0
log_queries_not_using_indexes      =ON 
log_throttle_queries_not_using_indexes   = 60  
min_examined_row_limit           = 1000
log_slow_admin_statements          = ON
log_error_verbosity               =2
long_query_time                    = 1
slow_query_log                     = ON

connect_timeout          = 300
wait_timeout              = 86400
open_files_limit          = 65535 
skip_name_resolve
skip_ssl
skip_external_locking
explicit_defaults_for_timestamp = TRUE
############# relay_log #############
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=1

############### mysqld_safe ###############
[mysqld_safe]
open_files_limit                    = 65535

2.2.6、mysql初始化安装

 /usr/local/mysql/bin/mysqld --defaults-file=/mysqldata/my3306/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/mysqldata/my3306/data --explicit_defaults_for_timestamp --user=mysql

–initialize：该选项初始化时会在错误日志中写一个随机root密码，初始化完成之后在错误日志中搜索password，紧跟其后的一串字符串就是这个随机密码，在初始化完成并启动mysqld之后，初次登录需要使用这个随机密码才能够登录。

2.2.7、启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/mysqldata/my3306/etc/my.cnf &

2.2.8、初始化mysql root用户密码

#操作系统执行
mysql -uroot -p'e*0PiBj4!;;C' -S /mysqldata/my3306/socket/mysql.sock
#mysql 命令行执行
alter user root@'localhost' identified by 'Zqd@123.+';
flush privileges;
#操作系统执行
mysql -uroot -pZqd@123.+ -S /mysqldata/my3306/socket/mysql.sock

2.2.9、关闭数据库命令

mysqladmin -uroot -p -S /mysqldata/my3306/socket/mysql.sock shutdown

3、完成

部署完成了，开始测试吧。