文章目录
- 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、硬件准备
| 参数 | 要求 |
|---|---|
| 内存 | >=8G |
| swap | 8G |
| 磁盘 | / : 50G；/mysqldata : 100G |
| CPU | >=4C |
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.loglog_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 = ONconnect_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、完成
部署完成了，开始测试吧。
方法的使用)
函数无法计算形参指针指向的字符串大小)
