一.OS环境准备 echo "127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4"> /etc/hosts echo "::1 localhost localhost.localdomain localhost6 localhost6.localdomain6">> /etc/hosts echo "192.168.18.80 fgedu80">> /etc/hosts cat /etc/hosts echo "export.UTF8" >> ~/.bash_profile cat ~/.bash_profile mkdir -p /mysql/app mkdir -p /mysql/data mkdir -p /mysql/log mkdir /backup pvcreate /dev/sdb /dev/sdc /dev/sdd /dev/sde pvs vgcreate appvg /dev/sdb vgcreate datavg /dev/sdc vgcreate logvg /dev/sdd vgcreate bakvg /dev/sde vgdisplay vgs lvcreate -n mysqllv -L 50G appvg lvcreate -n datalv -L 50G datavg lvcreate -n loglv -L 50G logvg lvcreate -n baklv -L 50G bakvg lvdisplay lvs mkfs.xfs /dev/appvg/mysqllv mkfs.xfs /dev/datavg/datalv mkfs.xfs /dev/logvg/loglv mkfs.xfs /dev/bakvg/baklv mkdir -p /mysql/app mkdir -p /mysql/data mkdir -p /mysql/log mkdir -p /mysql/backup mkdir -p /soft echo "/dev/appvg/mysqllv /mysql/app xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab echo "/dev/datavg/datalv /mysql/data xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab echo "/dev/logvg/loglv /mysql/log xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab echo "/dev/bakvg/baklv /mysql/backup xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab cat /etc/fstab systemctl daemon-reload mount /mysql/app mount /mysql/data mount /mysql/log mount /mysql/backup mount /dev/cdrom /mnt cd /etc/yum.repos.d mkdir bk mv *.repo bk/ echo "[EL9-1]" >> /etc/yum.repos.d/fgedu.repo echo "name =Linux-9-DVD1" >> /etc/yum.repos.d/fgedu.repo echo "baseurl=File:///mnt/AppStream" >> /etc/yum.repos.d/fgedu.repo echo "gpgcheck=0" >> /etc/yum.repos.d/fgedu.repo echo "enabled=1" >> /etc/yum.repos.d/fgedu.repo echo "[EL9-2]" >> /etc/yum.repos.d/fgedu.repo echo "name =Linux-9-DVD2" >> /etc/yum.repos.d/fgedu.repo echo "baseurl=File:///mnt/BaseOS" >> /etc/yum.repos.d/fgedu.repo echo "gpgcheck=0" >> /etc/yum.repos.d/fgedu.repo echo "enabled=1" >> /etc/yum.repos.d/fgedu.repo mount /dev/cdrom /mnt dnf install -y gcc gcc-c++ make ncurses-devel.x86_64 lrzsz.x86_64 cmake.x86_64 libaio.x86_64 bison.x86_64 dnf install -y gcc-c++.x86_64 bind-utils wget curl curl-devel perl openssh-clients sysstat make -y systemctl set-default multi-user.target systemctl get-default cat >> /etc/security/limits.conf << EOF * soft nofile 1024000 * hard nofile 1024000 * soft nproc 1024000 * hard nproc 1024000 * soft core unlimited * hard core unlimited * soft memlock unlimited * hard memlock unlimited EOF cat /etc/security/limits.conf echo "* - nproc 16384" > /etc/security/limits.d/90-nproc.con 内核参数配置 cat >> /etc/sysctl.conf <<EOF # file fs.aio-max-nr = 2097152 fs.file-max = 76724600 fs.nr_open= 20480000 # vm vm.swappiness=10 vm.min_free_kbytes = 188743 vm.overcommit_memory = 0 vm.overcommit_ratio = 90 vm.zone_reclaim_mode = 0 vm.nr_hugepages = 2458 vm.hugetlb_shm_group=666 #kernel kernel.shmmax = 5153960756 kernel.shmall = 1258291 kernel.shmmni = 4096 kernel.sem = 4096 2048000 200 32768 kernel.numa_balancing = 0 net.ipv4.ip_forward = 1 net.ipv4.ip_nonlocal_bind = 1 net.ipv4.ip_local_port_range = 40000 65535 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 net.ipv4.tcp_tw_reuse = 1 EOF sysctl -p #file: fs.aio-max-nr = 1048576 --# 限制并发未完成的异步请求数目，不应小于1M fs.file-max = 76724600 --# 最大打开XXX个文件 fs.nr_open=20480000 --#单个进程打开文件句柄数上限 #kernel kernel.shmmax = 3221225472 --#shmmax是最大的共享内存段的大小，单位是字节 --保守计算：echo $(expr $(getconf _PHYS_PAGES) / 2 \* $(getconf PAGE_SIZE))， --我一般为：> DB内存mysql实例的内存 < 物理内存(建议不超过内存的80%) mysql实例的内存=（innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+tmp_table_size） tmp_table_size与max_heap_table_size 取最大值 kernel.shmall = 786432 --整个系统的内存segment的总个数 --保守建议设置为：系统可用的内存页总量的一半，可以用getconf _PHYS_PAGES查看系统可用的内存页总量 --echo $(expr $(getconf _PHYS_PAGES) / 2)， --我一般为：kernel.shmmax/PAGE_SIZE kernel.shmmni = 4096 一共能生成多少共享内存段,.系统范围内共享内存段的最大数量 kernel.sem = 4096 134217728 200 32768 SEMMSL含义：每个信号量set中信号量最大个数 设置：最小250；对于processes参数设置较大的系统建议设置为processes+10 SEMMNS含义：linux系统中信号量最大个数 设置：至少32000；SEMMSL * SEMMNI SEMOPM含义：semop系统调用允许的信号量最大个数设置：至少100；或者等于SEMMSL SEMMNI含义：linux系统信号量set最大个数 设置：最少128 kernel.numa_balancing = 0 --是否启用NUMA平衡功能，将任务移动到最近的内存或移动内存到任务执行最近的地方，0表上关闭； net.ipv4.ip_forward = 1 --# IP转发 net.ipv4.ip_nonlocal_bind = 1 --# 绑定非本机地址 net.netfilter.nf_conntrack_max = 1048576 -- # 最大跟踪连接数 net.ipv4.ip_local_port_range = 10000 65535 --# 端口范围 net.ipv4.tcp_rmem = 8192 65536 16777216 --# tcp读buffer net.ipv4.tcp_wmem = 8192 65536 16777216 -- # tcp写buffer net.ipv4.tcp_mem = 131072 262144 16777216 --# tcp 内存使用 net.core.rmem_default = 262144 --# 接受缓冲区默认大小 net.core.rmem_max = 4194304 --# 接受缓冲区最大大小 net.core.wmem_default = 262144 -- # 发送缓冲区默认大小: 256K net.core.wmem_max = 4194304 -- # 发送缓冲区最大大小: 4M net.ipv4.tcp_tw_reuse = 1 -- # 允许将TIME_WAIT socket用于新的TCP连接。默认为0 vm.overcommit_memory = 0 内存分配策略 0：表示内核将检查是否有足够的可用内存供应用进程使用；如果有足够的可用内存，内存申请允许；否则，内存申请失败，并把错误返回给应用进程。 1：表示内核允许分配所有的物理内存，而不管当前的内存状态如何。它根据某种算法决定是否可以overcommit 2： 表示内核允许分配超过所有物理内存和交换空间总和的内存，禁止超过阈值的overcommit。 vm.overcommit_ratio = 90 值为0到100，表示允许内核分配的内存超过实际物理内存的百分比。 当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。 vm.zone_reclaim_mode = 0 --# 禁用 numa, 或者在vmlinux中禁止 vm.hugetlb_shm_group=666 # 大内存页使用的group id vm.swappiness=10 #内存剩余10%的时候开始使用swap vm.min_free_kbytes = 188743 #设置为内存的3% vm.nr_hugepages = 2458 #大内存页数量 * 2m mysql实例的内存=（innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+tmp_table_size） tmp_table_size与max_heap_table_size 取最大值 https://dev.mysql.com/doc/refman/8.4/en/large-page-support.html 关闭防火墙 echo "SELINUX=disable" >/etc/selinux/configecho "#SELINUXTYPE=trageted" >> /etc/selinux/configcat /etc/selinux/configsetenforce 0systemctl stop firewalld.servicesystemctl disable firewalld.service 关闭NUMA和透明大页cp /etc/default/grub /etc/default/grub_bakline_num=`cat -n /etc/default/grub | grep 'GRUB_CMDLINE_LINUX' |awk '{print $1}'|head -n 1`sed -i --follow-symlinks 's/numa=off//g' /etc/default/grubsed -i --follow-symlinks 's/transparent_hugepage=never elevator=deadline//g' /etc/default/grubsed -i --follow-symlinks ""${line_num}" s/\"$/ numa=off\"/g" /etc/default/grubsed -i --follow-symlinks ""${line_num}" s/\"$/ transparent_hugepage=never elevator=deadline\"/g" /etc/default/grub#IO调度参数说明
cfq：适合桌面系统和多任务环境
deadline：适合数据库、Web服务器等对延迟敏感的应用
noop：适合虚拟化环境、SSD、高速存储设备
cat /etc/default/grubgrub2-mkconfig -o /boot/grub2/grub.cfg 关闭avahisystemctl stop avahi-daemon.socket avahi-daemon.servicesystemctl disable avahi-daemon.socket avahi-daemon.service 配置rc localchmod +x /etc/rc.d/rc.localcat >> /usr/lib/systemd/system/rc-local.service << EOF[Install]WantedBy=multi-user.targetEOFsystemctl enable rc-local 修改系统时区timedatectl set-timezone Asia/Shanghaitimedatectltimedatectl list-timezones echo "RemoveIPC=no" >> /etc/systemd/logind.conf 二.数据库安装1.确认OS glibc版本[root@fgedu80 backup]# ldd --versionldd (GNU libc) 2.34Copyright (C) 2021 Free Software Foundation, Inc.This is free software; see the source for copying conditions. There is NOwarranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.Written by Roland McGrath and Ulrich Drepper. 2.检查早期版本的MYSQLrpm -qa |grep -i mysqlrpm -qa |grep -i maria*rm -f /etc/my.cnfrm -rf /etc/my.cnf.d 3.创建用户和组groupadd -g 666 mysqluseradd -u 666 -r -g mysql -s /bin/false mysql 4.解压缩cd /mysql/apptar -Jxf /soft/mysql-8.0.35-linux-glibc2.28-x86_64.tar.xzln -s mysql-8.0.35-linux-glibc2.28-x86_64 mysqlecho "export PATH=\$PATH:\$HOME/bin:/mysql/app/mysql/bin">>~/.bash_profilesource ~/.bash_profile 5.创建目录 mkdir -p /mysql/data/3306/datamkdir -p /mysql/log/3306/binlogmkdir -p /mysql/log/3306/relaylogmkdir -p /mysql/backup/backup-dbmkdir -p /mysql/backup/backup-tmpmkdir -p /mysql/backup/backup-binlogchown -R mysql:mysql /mysql/*chmod -R 755 /mysql/* 6.建立参数文件cd /mysql/data/3306vi my.cnf
mycnf.txt
[client] port=3306 socket = /mysql/data/3306/mysql.sock default-character-set=utf8mb4 [mysql] no-beep prompt="\u@mysqldb \R:\m:\s [\d]> " #no-auto-rehash auto-rehash skip-binary-as-hex default-character-set=utf8mb4 [mysqld] ########basic settings######## server-id=3306 port=3306 user = mysql bind_address= 192.168.1.80 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 = 1 lower-case-table-names=1 #skip_name_resolve = 1 max_connections = 1000 max_connect_errors = 5000 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 #sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" interactive_timeout = 600 wait_timeout = 600 read_buffer_size = 16M read_rnd_buffer_size = 32M large-pages=ON back_log=1024 table_open_cache=2000 thread_cache_size=768 open_files_limit=65536 table_definition_cache=1400 #lock_wait_timeout = 3600 #query_cache_type = 1 #query_cache_size=1M #flush_time=0 #binlog_row_event_max_size=8K #sync_master_info=10000 #sync_relay_log=10000 #sync_relay_log_info=10000 ##myisam settings myisam_max_sort_file_size=10G myisam_sort_buffer_size=135M key_buffer_size=32M default_time_zone = "+8:00" admin_address = '127.0.0.1' admin_port = 33066 ########log settings######## log-output=FILE general_log = 0 general_log_file=/mysql/log/3306/fgedudb-general.err slow_query_log = ON slow_query_log_file=/mysql/log/3306/fgedudb-query.err long_query_time=10 log-error=/mysql/log/3306/fgedudb-error.err log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_replica_statements = 1 log_throttle_queries_not_using_indexes = 10 binlog_expire_logs_seconds=604800 min_examined_row_limit = 100 log_bin=/mysql/log/3306/binlog/fgedudb-binlog binlog_rows_query_log_events=on binlog_gtid_simple_recovery=1 log_timestamps=system #deprecated:log_slow_slave_statements = 1 #deprecated:expire_logs_days = 90 #deprecated:binlog_format='ROW' ########replication settings######## #master_info_repository = TABLE #relay_log_info_repository = TABLE #log_bin = bin.log #sync_binlog = 1 #gtid_mode = on #enforce_gtid_consistency = 1 #log_slave_updates #binlog_format = row #relay_log = relay.log #relay_log_recovery = 1 #binlog_gtid_simple_recovery = 1 #slave_skip_errors = ddl_exist_errors #slave_parallel_type = LOGICAL_CLOCK #slave_parallel_workers = 32 #binlog_transaction_dependency_tracking = WRITESET #slave_preserve_commit_order = 1 #slave_checkpoint_period = 2 #replication_optimize_for_static_plugin_config = ON #replication_sender_observe_commit_only = ON ########mgr settings######## #loose-plugin_load_add = 'mysql_clone.so' #loose-plugin_load_add = 'group_replication.so' #loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1" #loose-group_replication_local_address = "192.168.1.51:33061" #loose-group_replication_group_seeds = "192.168.1.51:33061,192.168.1.52:33061,192.168.1.53:33061" #loose-group_replication_start_on_boot = OFF #loose-group_replication_bootstrap_group = OFF #loose-group_replication_exit_state_action = READ_ONLY #loose-group_replication_flow_control_mode = "DISABLED" #loose-group_replication_single_primary_mode = ON #loose-group_replication_autorejoin_tries = 288 #loose-group_replication_member_expel_timeout = 5 #loose-group_replication_unreachable_majority_timeout = 30 #loose-group_replication_communication_max_message_size = 10M ########innodb settings######## # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 innodb_buffer_pool_size = 500M innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 4000 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 32M innodb_redo_log_capacity = 3G innodb_max_undo_log_size = 4G innodb_online_alter_log_max_size = 4G innodb_print_ddl_logs = 1 innodb_status_file = 1 innodb_status_output = 0 innodb_status_output_locks = 1 innodb_stats_persistent_sample_pages = 500 innodb_adaptive_hash_index = 0 sql_generate_invisible_primary_key = ON innodb_flush_neighbors = 1 innodb_purge_threads = 4 #innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_strict_mode = 1 innodb_sort_buffer_size = 64M innodb_flush_log_at_trx_commit=1 innodb_autoextend_increment=64 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=65536 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 4 innodb_purge_rseg_truncate_frequency = 128 #transaction_write_set_extraction=MURMUR32 #default_authentication_plugin=mysql_native_password #default_authentication_plugin=caching_sha2_password #innodb monitor settings innodb_monitor_enable = "module_innodb" innodb_monitor_enable = "module_server" innodb_monitor_enable = "module_dml" innodb_monitor_enable = "module_ddl" innodb_monitor_enable = "module_trx" innodb_monitor_enable = "module_os" innodb_monitor_enable = "module_purge" innodb_monitor_enable = "module_log" innodb_monitor_enable = "module_lock" innodb_monitor_enable = "module_buffer" innodb_monitor_enable = "module_index" innodb_monitor_enable = "module_ibuf_system" innodb_monitor_enable = "module_buffer_page" #innodb_monitor_enable = "module_adaptive_hash" #pfs settings performance_schema = 1 #performance_schema_instrument = '%memory%=on' performance_schema_instrument = '%lock%=on' [mysqldump] quick
7.初始化&启动 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data --initialize/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf &tail -f fgedudb-error.err #获取密码ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sockmysql> alter user 'root'@'localhost' identified by 'rootroot'; 8.配置mysql启停脚本cd /mysql/app/mysql/support-files/cp mysql.server mysql.server.bakcp /tmp/mysql.server /mysql/app/mysql/support-files/chown mysql:mysql /mysql/app/mysql/support-files/mysql.serverchmod +x /mysql/app/mysql/support-files/mysql.server/mysql/app/mysql/support-files/mysql.server stop/mysql/app/mysql/support-files/mysql.server start/mysql/app/mysql/support-files/mysql.server status#加入系统启动 cp /mysql/app/mysql/support-files/mysql.server /usr/lib/systemd/system/ cat > /usr/lib/systemd/system/mysqld.service << EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql Type=forking PIDFile=/mysql/data/3306/mysql.pid TimeoutSec=0 # Execute pre and post scripts as root PermissionsStartOnly=true # Start main service ExecStart=/mysql/app/mysql/support-files/mysql.server start # Use this to switch malloc implementation EnvironmentFile=-/etc/sysconfig/mysql # Sets open_files_limit LimitNOFILE = 65536 LimitNPROC = 65536 Restart=on-failure RestartPreventExitStatus=1 PrivateTmp=false EOF systemctl daemon-reload systemctl stop mysqld systemctl start mysqld 三.测试与远程连接1.创建远程连接的root用户 create USER 'root'@'%' IDENTIFIED BY 'rootroot';grant all privileges on *.* to 'root'@'%' with grant option; 2.创建数据库 create database fgedudb default character set utf8mb4 collate utf8mb4_unicode_ci; CREATE USER 'fgedu'@'%' IDENTIFIED BY 'fgedu'; GRANT ALL PRIVILEGES ON fgedudb.* TO 'fgedu'@'%'; GRANT ALL PRIVILEGES ON fgedudb.* TO 'fgedu'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;
