快速上手MySQL8.0.28：一站式安装指南与性能优化技巧

yum -y install make gcc-c++ cmake bison-devel ncurses-devel libaio perl-Data-Dumper net-tools libaio-devel perl zlib*
复制

yum -y install unzip* gzip* zip* tar* lrzsz* twm* xterm* pixman* pixman-devel* fio* ntp* telnet* bc* dos2unix* net-tools* iperf* iftop* iotop* psmisc* lsof* nethogs* htop*
复制

echo "net.ipv4.tcp_syncookies = 1" >> etc/sysctl.conf
echo "fs.file-max = 1000000" >> etc/sysctl.conf
echo "vm.swappiness = 0" >> etc/sysctl.conf
echo "net.core.somaxconn = 26214" >> etc/sysctl.conf
echo "net.ipv4.tcp_fin_timeout = 30" >> etc/sysctl.conf
echo "net.ipv4.tcp_keepalive_time = 120" >> etc/sysctl.conf
echo "net.ipv4.tcp_tw_reuse = 1" >> etc/sysctl.conf
echo "net.ipv4.tcp_timestamps = 1" >> etc/sysctl.conf
echo "net.ipv4.tcp_tw_recycle = 0" >> etc/sysctl.conf
echo "net.ipv4.tcp_max_tw_buckets = 20000" >> etc/sysctl.conf
echo "net.ipv4.tcp_max_syn_backlog = 65535" >> etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 10240 65000" >> etc/sysctl.conf
复制

echo "www-data soft noproc 65535>> /etc/security/limits.conf
echo "www-data hard noproc 65535>> /etc/security/limits.conf
echo "www-data soft nofile 65535>> /etc/security/limits.conf
echo "www-data hard nofile 65535>> /etc/security/limits.conf
echo "www-data soft stack 65535>> /etc/security/limits.conf
echo "www-data hard stack 65535>> /etc/security/limits.conf
复制

setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux    
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
systemctl stop firewalld    
systemctl disable firewalld
复制

#首先移除服务器自带的mariadb
yum -y  remove mariadb*


mkdir -p /data/mysql/data
groupadd mysql
useradd -g mysql -r -s /sbin/nologin -M -d /usr/local/mysql mysql
复制

/etc/profile.d/mysql.sh 
export PATH=$PATH:/usr/local/mysql/bin
复制

解压软件    
tar -xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
mkdir /data/mysql
mv mysql-8.0.28-linux-glibc2.12-x86_64 /data/mysql/mysql-8.0.28
ln -s /data/mysql/mysql-8.0.28 /usr/local/mysql
            
 
chown -R mysql:mysql /data/mysql /usr/local/mysql
复制

[mysqld]
bind-address=0.0.0.0
port= 3306
user= mysql
basedir=/data/mysql/mysql-8.0.28
datadir=/data/mysql/mysql-8.0.28/data
socket=/data/mysql/mysql-8.0.28/data/mysql.sock
            
 
#tmpdir=/data/mysql/mysql-8.0.28/data/mysql-tmp/
log-error=/data/mysql/mysql-8.0.28/data/mysql3306err.log
pid-file=/data/mysql/mysql-8.0.28/data/mysql3306.pid
            
 
authentication_policy= mysql_native_password   # MySQL 5.7 无此参数
            
 
###  Server Config  ###
server-id= 1
autocommit = 1      
character-set-server=utf8
collation-server = utf8_general_ci
init_connect='SET NAMES utf8'
skip_name_resolve = 1  
open_files_limit = 65535
max_connections = 30000  
max_connect_errors = 100  
interactive_timeout = 18800  
wait_timeout = 18800 
transaction_isolation = READ-COMMITTED  
explicit_defaults_for_timestamp = 1  
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO" 
table_open_cache = 4000
max_allowed_packet = 16M
join_buffer_size = 128M  
tmp_table_size = 128M
max_heap_table_size = 128M  
read_buffer_size = 16M  
read_rnd_buffer_size = 32M  
sort_buffer_size = 32M
lower_case_table_names= 1
explicit_defaults_for_timestamp=true
            
 
### slow query config ###
slow_query_log = on
long_query_time = 600
slow_query_log_file =/data/mysql/mysql-8.0.28/data/mysql-log/slowquery.log
            
 
###  binlog config ###
#gtid_mode = on
#enforce_gtid_consistency = on
innodb-file-per-table = 1
#sync_binlog = 1
            
 
log-bin=/data/mysql/mysql-8.0.28/data/mysql-bin
binlog_format = row
binlog_expire_logs_seconds = 172800
max_binlog_size = 1G
binlog_ignore_db = mysql    
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
            
 
### innodb config ###
innodb_log_files_in_group = 5
#innodb_log_group_home_dir= /data/mysql/mysql-8.0.28/data/redo-log/
#innodb_undo_directory =  /data/mysql/mysql-8.0.28/data/undo-log/
#innodb_page_size = 16K
            
 
# 0.8*Physical Memory for the dedicated MySQL Server，be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
innodb_buffer_pool_size = 13455346892
innodb_buffer_pool_instances = 20 
innodb_log_file_size = 1G
innodb_log_buffer_size = 100M
#innodb_thread_concurrency = 64
            
 
[mysql]
default-character-set=utf8
socket=/data/mysql/mysql-8.0.28/data/mysql.sock
            
 
[client]
default-character-set=utf8
socket=/data/mysql/mysql-8.0.28/data/mysql.sock
[mysqld_safe]  
socket=/data/mysql/mysql-8.0.28/data/mysql.sock
log-error=/data/mysql/mysql-8.0.28/data/mysql3306err.log
pid-file=/data/mysql/mysql-8.0.28/data/mysql3306.pid
复制

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/mysql-8.0.28 --datadir=/data/mysql/mysql-8.0.28/data --initialize-insecure
复制

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
chkconfig --level 2345 mysql on
            
#启动数据库 
service mysql start
复制

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123tst';
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123tst';


#授权          
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
flush privileges;
#PS:本次为演示操作，所以设置密码比较简单，生产环境建议复杂度设置更高一些。
复制