Ubuntu 24.04安装mysql8.0最新版本

关于在阿里云上的ubuntu24版本上搭建mysql8.0主备数据库
一、操作系统配置
1.修改主机名
hostnamectl hostname jpyzdb

2.配置hosts
echo "ip  jpyzdb" >> /etc/hosts

3. 配置英文环境
echo "export LANG=en_US.UTF-8" >> ~/.bash_profile
cat ~/.bash_profile

4.修改资源限制参数
vi /etc/security/limits.conf
#限制分为软限制和硬限制，软限制在达到数目后会警告。硬链接达到限制收将不可再使用。
#*代表是每一个用户都这么限制
#每一个用户创建进程数的限制
* soft nproc 65535
* hard nproc 65535
#文件描述符，每个进程能打开文件的次数
* soft nofile 65535
* hard nofile 65535
#堆大小
* soft stack 65535
* hart stack 65535

#使用ulimit -a 查看是生效

5.虚拟内存，保留内存
vi /etc/sysctl.conf
#交换内存，物理内存剩余60%时，开始使用交换内存，因此需要将交换内存改为10，默认是60
vm.swappiness=10

#保留内存，物理内存使用完了，将无法操作，因此设置一个保留内存来面对内存耗尽的情况
vm.min_free_kbytes=51200  --测试环境保留50M
#vm.min_free_kbytes=512000  --生产环境最少500M,操作系统没那么容易宕机
#使配置生效
sysctl -p

设置内核参数

cat >> /etc/sysctl.conf  <<EOF

# file
fs.aio-max-nr = 2097152
fs.file-max = 76724600
fs.nr_open= 20480000

# vm
vm.swappiness=1
vm.min_free_kbytes = 204800
vm.overcommit_memory = 0    
vm.overcommit_ratio = 90     
vm.dirty_background_bytes = 409600000       
vm.dirty_expire_centisecs = 3000             
vm.dirty_ratio = 95                          
vm.dirty_writeback_centisecs = 100            
vm.mmap_min_addr = 65536
vm.zone_reclaim_mode = 0     
vm.nr_hugepages = 750    

#kernel
kernel.shmmax = 3221225472   
kernel.shmall = 786432     
kernel.shmmni = 4096      
kernel.sem = 4096 2048000 200 32768
kernel.numa_balancing = 0

# tcp-part1-load-balancer
net.ipv4.ip_forward = 1
net.ipv4.ip_nonlocal_bind = 1
net.netfilter.nf_conntrack_max = 1048576 
net.ipv4.ip_local_port_range = 40000 65535
net.ipv4.tcp_max_tw_buckets = 262144
net.core.somaxconn = 16384
net.ipv4.tcp_max_syn_backlog = 8192
net.core.netdev_max_backlog = 10000

#tcp-part2-buffer
net.ipv4.tcp_rmem = 8192 65536 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304

# tcp-part3-keepalive
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60

# tcp-part4-port resure
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_timestamps = 1

# tcp-part4-anti-flood
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 1
EOF
sysctl -p

6.准备目录
mkdir -p  /mysql/app
mkdir -p  /mysql/data
mkdir -p  /mysql/log
mkdir -p  /mysql/backup
mkdir -p  /mysql/data/3306/data
mkdir -p  /mysql/log/3306
mkdir -p  /mysql/log/3306/relaylog/
mkdir -p  /mysql/log/3306/binlog/
chown mysql:mysql /mysql -R

7.创建用户和组
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

8.上传软件解压
tar -xvf mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz
ln -s mysql-8.0.42-linux-glibc2.17-x86_64 mysql

二、安装数据库
1.配置环境变量
vi .bash_profile
export PATH=$PATH:/mysql/app/mysql/bin

2.my.cnf

[client]
port=3306
socket    = /mysql/data/3306/mysql.sock

[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8mb4

[mysqld]
########basic settings########
server-id=3306
port=330638
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket    = /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8mb4
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M