系统：centos7
数据库版本：Percona-Server-8
安装包：二进制包
系统优化：
- 关闭selinux
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
- 关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
- 安装依赖包
yum -y install libevent
- sysctl.conf 优化：
cat >> /etc/sysctl.conf << EOF
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF
sysctl -p
- limit 优化：
cat >> /etc/security/limits.conf << EOF
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
EOF
cat >> /etc/security/limits.d/20-nproc.conf << EOF
* soft nproc unlimited
* hard nproc unlimited
EOF
cat >> /etc/pam.d/login << EOF
session required /lib/security/pam_limits.so
session required pam_limits.so
EOF
cat >> /etc/profile << EOF
ulimit -HSn 65535
EOF
source /etc/profile
安装mysql8
- 新建系统用户及创建所需文件夹
useradd -M mysql -s /sbin/nologin
mkdir -p /data/mysql/{data,log} /usr/local/mysql/etc
chown -R mysql:mysql /data/mysql
- 下载mysql8安装包
wget https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.15-5/binary/tarball/Percona-Server-8.0.15-5-Linux.x86_64.ssl101.tar.gz
- 安装
tar zxvf Percona-Server-8.0.15-5-Linux.x86_64.ssl101.tar.gz
mv Percona-Server-8.0.15-5-Linux.x86_64.ssl101/* /usr/local/mysql/
ln -s /usr/local/mysql /usr/local/Percona-Server-8.0.15-5-Linux.x86_64.ssl101
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -i 's|conf=/etc/my.cnf|conf=/usr/local/mysql/etc/my.cnf|g' /etc/init.d/mysqld
sed -i "s|^mysqld_pid_file_path=.*$|mysqld_pid_file_path='/data/mysql/mysql.pid'|g" /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
- 修改配置
cat > /etc/my.cnf << EOF
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
prompt=\u@\d \R:\m>
auto_rehash
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld]
user = mysql
port = 3306
wait_timeout = 31536000
interactive_timeout = 31536000
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid_file = /data/mysql/mysql.pid
datadir = /data/mysql/data
log_bin = /data/mysql/data/bin
skip_name_resolve
skip_external_locking
default_storage_engine = InnoDB
slow_query_log = 1
long_query_time = 3
slow_query_log_file = /data/mysql/log/slow.log
log_error = /data/mysql/log/mysql_error.log
relay_log = /data/mysql/log/slave-relay.log
server_id = 8
gtid_mode = on
enforce_gtid_consistency = 1
EOF
cat > /usr/local/mysql/etc/my.cnf << EOF
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
prompt=\u@\d \R:\m>
auto_rehash
[mysqldump]
quick
max_allowed_packet = 64M
[mysqld]
user = mysql
server_id = 8
port = 3306
wait_timeout = 31536000
interactive_timeout = 31536000
socket = /tmp/mysql.sock
explicit_defaults_for_timestamp = 1
basedir = /usr/local/mysql
datadir = /data/mysql/data
pid_file = /data/mysql/mysql.pid
performance_schema = 0
log_bin_trust_function_creators = 1
lower_case_table_names = 1
init_connect = 'SET NAMES utf8mb4'
#character_set_server = utf8mb4
skip_name_resolve
skip_external_locking
max_connections = 4000
max_connect_errors = 10000
open_files_limit = 65535
max_allowed_packet = 64M
binlog_cache_size = 64K
max_heap_table_size = 16M
tmp_table_size = 16M
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
join_buffer_size = 256K
key_buffer_size = 8M
thread_cache_size = 9
####binlog
log_bin = /data/mysql/data/bin
binlog_format = row
log_slave_updates = 1
sync_binlog = 1
expire_logs_days = 5
binlog_checksum = CRC32
slave_allow_batching = 1
master_verify_checksum = 1
slave_sql_verify_checksum = 1
master_info_repository = table
relay_log_info_repository = table
relay_log_purge = 1
relay_log_recovery = 1
relay_log = /data/mysql/log/slave-relay.log
binlog_row_image = minimal
binlog_rows_query_log_events = 1
####other logs
long_query_time = 3
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
log_error = /data/mysql/log/mysql_error.log
####master&slave
gtid_mode = on
enforce_gtid_consistency = 1
####innodb
default_storage_engine = InnoDB
innodb_open_files = 4000
innodb_buffer_pool_size = 128m
innodb_purge_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_log_file_size = 48M
innodb_flush_method = O_DIRECT
innodb_data_file_path = ibdata1:12M:autoextend
innodb_file_per_table = 1
EOF
- 初始化
/usr/local/mysql/bin/mysqld --initialize-insecure
- 启动、关闭mysql
/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &
service mysqld stop
- 登录服务器并增加用户
/usr/local/mysql/bin/mysql -S /tmp/mysql.sock
> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
> CREATE USER 'root'@'%' IDENTIFIED BY '123456';
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
> FLUSH PRIVILEGES;
- 修改mysql启动文件并设置开机启动
echo '/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &' >> /etc/rc.d/rc.local