MySQL8.0自动部署、配置，shell代码 MySQL8.0自动部署配置shell代码编辑shell文件mysql8.0-auto-install.sh复制下面代码使用方法:直接执行./mysql8.0-auto-install.sh安装mysql密码会自动生成;指定密码./mysql8.0-auto-install.sh -p安装成功后会在本目录生成.dbconfig.txt文件,记录了mysql的安装信息;使用cat .dbconfig.txt 来查看#!/bin/bashdisable_selinux(){/usr/sbin/setenforce 0 sed-ri/^SELINUX/s/^(SELINUX).*$/\1disabled/g/etc/selinux/config}config_firewalld(){if!firewall-cmd--list-port|grep-qw 3306;then systemctl restart firewalld systemctl enable firewalld firewall-cmd--zonepublic--add-port3306/tcp--permanent firewall-cmd--reload fi}disable_selinux config_firewalld uninstall(){systemctl stop mysqld /dev/null||service mysqld stop /dev/null pkill-9 mysqld 2/dev/nullrm-rf/var/lib/mysql 2/dev/nullrm-f/var/log/mysqld.log 2/dev/null}if[$# -ne 0 ];thencase$1in uninstall)uninstallechouninstall success.exit0;;-p)if[$# -ne 2 ];thenechoerror param number $#exit1elseshift init_password$1fi;;esac fi if_mysql_running(){local flag0if[$(ps-ef|grep mysqld|grep-v grep|wc-l)-ne0];then local flag1 fiif[-d/var/lib/mysql]||[-d/data/mysql];then local flag1 fi[$flag-eq1]echomysql exist.exit0}if_mysql_running#定义颜色BLUE\033[1;34mGREEN\033[1;32mRED\033[1;31mEND\033[0m# 定义日志输出格式函数log(){local logtype local text logtype$1text$2now_time$(date%Y-%m-%d %H:%M:%S)case$logtypein INFO)echo-e\033[34;1m[$now_time] [$logtype]$text\033[0m;;WARN)echo-e\033[33;1m[$now_time] [$logtype]$text\033[0m;;ERROR)echo-e\033[31;1m[$now_time] [$logtype]$text\033[0m;;esac}# 判断操作系统os_release$(sed-nr/^NAME/s/^NAME\(.*)\$/\1/p/etc/os-release)hostnamectlset-hostnamemysql memory_byte$(cat/proc/meminfo|grep-wMemTotal:|awk{print $2*1024})create_user(){ifgrep-qw mysql/etc/shadow;then log WARNMySQL用户mysql已经创建完成继续执行else/usr/sbin/groupadd mysql /dev/null/usr/sbin/useradd-r-g mysql-s/sbin/nologin mysql /dev/null log INFOMySQL用户mysql创建成功fi}create_user TOTALMEMORY_MBcat/proc/meminfo|grep-wMemTotal:|awk{print $2/1024}#计算内存的70%TOTALMEMORY_MB_70echo${TOTALMEMORY_MB}|awk{print $1*0.7}#内存的70%取整INNODB_BPSIZE_MBecho${TOTALMEMORY_MB_70}|awk-F.{print $1}cat/etc/sysctl.conf EOF# 增加监听队列上限net.core.somaxconn 65535 net.core.netdev_max_backlog 65535 net.ipv4.tcp_max_syn_backlog 65535# 加快TCP连接的回收net.ipv4.tcp_fin_timeout 10 net.ipv4.tcp_tw_reuse 1# TCP连接接收和发送缓冲区大小的默认值和最大值:net.core.wmem_default 87380 net.core.wmem_max 16777216 net.core.rmem_default 87380 net.core.rmem_max 16777216# 减少失效连接所占用的TCP资源的数量加快资源回收的效率net.ipv4.tcp_keepalive_time 120 net.ipv4.tcp_keepalive_intvl 30 net.ipv4.tcp_keepalive_probes 3# 单个共享内存段的最大值 这个参数应该设置的足够大以便能在一个共享内存段下容纳整个的Innodb缓冲池的大小。# 这个值的大小对于64位linux系统可取的最大值为(物理内存值-1)byte建议值为大于物理内存的一半一般取值大于Innodb缓冲池的大小即可。以下是64G的一半 32Gkernel.shmmax $(expr$memory_byte/10 \*8)# 表示最大限度使用物理内存然后才是 swap空间vm.swappiness 0 EOF sysctl-p /dev/null sed-is/4096/90000/g/etc/security/limits.d/20-nproc.conf 2/dev/nullcat/etc/security/limits.conf EOF*soft nofile 655360*hard nofile 655360*soft core unlimited*hard core unlimited*soft sigpending 90000*hard sigpending 90000*soft nproc 90000*hard nproc 90000 EOFifrpm-qa|grep-iq mariadb;then yum remove-y mariadb-libs.x86_64 /dev/null 21 fiif[$os_releaseAnolis OS];thenifdnf install-y mysql-server /dev/null;thenechoinstall mysql success!elseechoinstall mysql failed!exit1 fielseifyum localinstall-y/opt/software/mysql80-community-release-el7-5.noarch.rpm /dev/null;thenechoinstall mysql repo success.ifyum install-y mysql-community-server--nogpgcheck /dev/null;thenechoinstall mysql success!elseechoinstall mysql failed!exit1 fielseechoinstall mysql repo failed.exit1 fi fi sed-is/LimitNOFILE 10000/LimitNOFILE 65535/g/usr/lib/systemd/system/mysqld.servicecp-pn/etc/my.cnf/etc/my.cnf.bak mkdir-p/var/lib/mysql/{data,logs,tmp}/dev/null touch/var/log/mysqld.log chown mysql:mysql/var/log/mysqld.log chown-R mysql:mysql/var/lib/mysql /dev/nullcat/etc/my.cnf EOF[client]port 3306 socket/var/lib/mysql/mysql.sock[mysql]#mysql终端提醒配置prompt \u\h:\p [\d] #关闭自动补全功能no_auto_rehash[mysqld]####################general####################user mysql port 3306 datadir/var/lib/mysql/datasocket/var/lib/mysql/mysql.sock log-error/var/log/mysqld.log pid-file/var/run/mysqld/mysqld.pid tmpdir /var/lib/mysql/tmp#服务编号,主从同步时需要,不能重复,一般设置为末位ip端口server_id 1 mysqlx_port 33060#管理员用来连接的端口号,注意如果admin_address没有设置的话,这个端口号是无效的admin_port 33062#用于指定管理员发起tcp连接的主机地址admin_address 127.0.0.1#是否创建一个单独的listener线程来监听admin的链接请求,默认值是关闭的create_admin_listener_thread on#禁用dns解析skip_name_resolve 1#设置默认时区default_time_zone 8:00#设置默认的字符集character_set_server utf8mb4#设置表名不区分大小写,默认值为0,表名是严格区分大小写的lower_case_table_names 1#是否信任存储函数创建者log_bin_trust_function_creators 1#设置mysql支持的sql语法sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION####################max connection#################对整个服务器的用户限制,设置允许的最大连接数max_connections 3000#限制每个用户的session连接个数max_user_connections 500#客户端连接失败以下次数,MySQL不再响应客户端连接max_connect_errors 3000 mysqlx_max_connections 300####################binlog#########################设置binlog日志(主从同步和数据恢复需要)log_bin /var/lib/mysql/logs/mysql-bin#设置主从复制的模式STATEMENT模式SBR、ROW模式RBR、MIXED模式MBRbinlog_format row#开启该参数,从库从主库同步的数据也会更新到从库的binlog文件,默认为on;8.0.26版本之前使用log_slave_updates,默认为off#log_replica_updates on#开启全局事务标识模式,gtid用于在binlog中唯一标识一个事务gtid_mode on#当启用enforce_gtid_consistency功能的时候,MySQL只允许能够保障事务安全,并且能够被日志记录的SQL语句被执行#像create table … select 和 create temporary table语句,以及同时更新事务表和非事务表的SQL语句或事务都不允许执行enforce_gtid_consistency on#为每个session分配的内存,在事务过程中用来存储二进制日志的缓存binlog_cache_size 1M#binlog文件的大小,超过该大小会自动创建新的binlog文件max_binlog_size 512M#在row模式下开启该参数,将把sql语句打印到binlog日志里面,默认值为offbinlog_rows_query_log_events on#设置每次事务提交都将数据同步到磁盘sync_binlog 1#提交的事务是否按照写入二进制日志binlog的顺序提交,在一些情况下关闭这个参数,可以获得性能上的一点提升,默认值为onbinlog_order_commits off#设置binlog日志的保留时间,超过保留时间的日志会被自动删除,单位为s,默认值为30天;8.0.12之前使用expire_logs_days,默认为0binlog_expire_logs_seconds 604800##################Parallel replication###########控制检测事务依赖关系时采用的HASH算法,有三个取值OFF|XXHASH64|MURMUR32,默认值为XXHASH64,8.0.26以后废弃#transaction_write_set_extraction XXHASH64#5.7.29版本有下面2个参数,低于该版本的请关闭下面配置#控制事务依赖模式,让从库根据主库写入binlog中的commit timestamps或者write sets并行回放事务#有三个取值COMMIT_ORDERE|WRITESET|WRITESET_SESSIONbinlog_transaction_dependency_tracking writeset#取值范围为1-1000000,初始默认值为25000binlog_transaction_dependency_history_size 25000####################slow log#####################开启慢查询slow_query_log 1#SQL语句运行时间阈值,执行时间大于参数值的语句才会被记录下来long_query_time 10#设置慢查询日志文件的路径和名称slow_query_log_file slow.log#将没有使用索引的语句记录到慢查询日志#log_queries_not_using_indexes 1#设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间#log_throttle_queries_not_using_indexes 60#SQL扫描行数大于等于100行才会被记录#min_examined_row_limit 100####################error log#####################控制错误日志、慢查询日志等日志中的显示时间,在5.7.2之后该参数默认为UTC,会导致日志中记录的时间比中国这边的慢,导致查看日志不方便log_timestamps SYSTEM#1-错误信息;2-错误信息和告警信息;3-错误信息、告警信息和通知信息log_error_verbosity 3####################session######################sort_buffer_size 2M join_buffer_size 2M thread_cache_size 1500 thread_stack 256K tmp_table_size 96M read_buffer_size 2M read_rnd_buffer_size 16M bulk_insert_buffer_size 32M####################timeout######################interactive_timeout 300 wait_timeout 300 innodb_rollback_on_timeout on#8.0.26之前使用slave_net_timeoutreplica_net_timeout 30#8.0.26之前使用rpl_stop_slave_timeoutrpl_stop_replica_timeout 180 lock_wait_timeout 300####################relay_log####################relay_log relay-bin relay_log_index relay-bin.index#指定master复制相关信息的存储方式,默认值为table,8.0.23之后废弃#master_info_repository table#指定中继日志Relay Log的信息存储方式,默认值为table,8.0.23之后废弃#relay_log_info_repository table#控制中继日志Relay Log的清理行为,默认值为onrelay_log_purge on####################sql_thread#####################用来控制在数据库重新启动时,如何对Relay日志进行恢复和重放,默认为off,不执行Relay日志的恢复和重放relay_log_recovery on#8.0.26之前使用slave_preserve_commit_order,默认值为off,8.0.27及以后默认值为onreplica_preserve_commit_order on#并行复制模式:DATABASE,基于库的并行复制方式,默认值;LOGICAL_CLOCK,基于组提交的并行复制方式#8.0.26之前使用slave_parallel_type,8.0.27及以后默认值为LOGICAL_CLOCK,8.0.29将废弃#replica_parallel_type LOGICAL_CLOCK#主从复制时,设置并行复制的工作线程数,默认值为4,8.0.26之前使用slave_parallel_workers,默认值为0replica_parallel_workers 16####################innodb########################内存的50%-70%innodb_buffer_pool_size ${INNODB_BPSIZE_MB}M#2个G一个instance,一般小于32G配置为4,大于32G配置为8innodb_buffer_pool_instances 4#默认启用,指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热,通常与innodb_buffer_pool_dump_at_shutdown结合使用.innodb_buffer_pool_load_at_startup 1#默认启用,指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面,以便在下次重新启动时缩短预热过程.innodb_buffer_pool_dump_at_shutdown 1#指定innodb tablespace表空间的大小,默认: ibdata1:12M:autoextendinnodb_data_file_path ibdata1:1024M:autoextend#默认值为1,在每个事务提交时,InnoDB立即将缓存中的redo日志回写到日志文件,并调用操作系统fsync刷新IO缓存,保证完整的ACID.innodb_flush_log_at_trx_commit 1#日志缓冲区大小innodb_log_buffer_size 64M#redo日志大小innodb_log_file_size 1024M#redo日志组数,默认为2innodb_log_files_in_group 3#用来控制buffer pool中脏页的百分比,当脏页数量占比超过这个参数设置的值时,InnoDB会启动刷脏页的操作.innodb_max_dirty_pages_pct 90#开启独立表空间,默认为开启innodb_file_per_table 1#开始事务超时回滚整个事务,默认不开启,超时回滚最后一次提交记录innodb_rollback_on_timeout on#根据您的服务器IOPS能力适当调整,一般配普通SSD盘的话,可以调整到10000-20000#配置高端PCIe SSD卡的话,则可以调整的更高,比如50000-80000innodb_io_capacity 10000 innodb_io_capacity_max 20000#设置事务的隔离级别为读已提交transaction_isolation READ-COMMITTED#控制mysql将数据刷到inndodb的数据文件和日志文件的动作,linux上有fsync(默认值)、O_DSYNC、O_DIRECT、O_DIRECT_NO_FSYNC四个选项innodb_flush_method O_DIRECT#开启保存死锁日志,死锁日志存放到log_error配置的文件里innodb_print_all_deadlocks 1#禁用线程并发检查,使InnoDB按照请求的需求,创造尽可能多的线程innodb_thread_concurrency 0#设置IO读写的线程数(默认4),一般CPU多少核就设置多少innodb_read_io_threads 4 innodb_write_io_threads 4#开启死锁检测,默认开启innodb_deadlock_detect on#设置锁等待超时时间,默认为50sinnodb_lock_wait_timeout 20#设置持久化统计信息收集的采样页面数量innodb_stats_persistent_sample_pages 500####################undo#########################设置undo log的最大值,默认值为1G.当超过设置的阈值,会触发truncate回收(收缩)动作.innodb_max_undo_log_size 4G#undo文件存放的位置innodb_undo_directory /var/lib/mysql/undolog#从8.0.14开始废弃该参数,默认表空间数量为2#innodb_undo_tablespaces 4#开启自动清理undo log的功能innodb_undo_log_truncate 1####################performance_schema#####################MySQL的performance schema用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况performance_schema on performance_schema_consumer_global_instrumentation on performance_schema_consumer_thread_instrumentation on performance_schema_consumer_events_stages_current on performance_schema_consumer_events_stages_history on performance_schema_consumer_events_stages_history_long off performance_schema_consumer_statements_digest on performance_schema_consumer_events_statements_current on performance_schema_consumer_events_statements_history on performance_schema_consumer_events_statements_history_long off performance_schema_consumer_events_waits_current on performance_schema_consumer_events_waits_history on performance_schema_consumer_events_waits_history_long off#key-value格式,支持使用通配符,匹配memory/开头的performance_schema_instrument memory/%COUNTED[mysqldump]quick max_allowed_packet 32M EOF#配置mysql环境变量config_mysqlenv(){ifgrep-q mysql/etc/profile;then log WARNMySQL environment variables has been configured! Continue!elseecho#Use for mysql install added by tools/etc/profileechoexport MYSQL_HOME/usr/local/mysql/etc/profileechoexport PATH$MYSQL_HOME/bin:$PATH/etc/profileechoexport MYSQL_PS1\u\h:\p [\d] /etc/profile source/etc/profile /dev/null log INFOMySQL environment variables is successfully configured!fi}config_mysqlenvif[-n$init_password];then ROOT_PWD$init_passwordelseROOT_PWD$(cat/dev/urandom|tr-dc A-Za-z0-9|head-c 8).$(date%m%d%H%M)ficat/opt/software/.dbconfig.txt EOF#MySQL数据库的配置信息为:#数据库用户名/密码root/${ROOT_PWD}test/${ROOT_PWD}#数据库字符集utf8mb4#数据库配置文件/etc/my.cnf#数据库内存分配${INNODB_BPSIZE_MB}M#数据库自启动服务mysqld#数据文件路径/var/lib/mysql#mysqldump备份任务计划为每天夜晚1点备份,备份保留7天#mysqldump备份路径为/databack/mysqldata/sql_packageEOFecho-e$GREEN开始MySQL备份配置................................................................$ENDmkdir-p/databack/mysqldata/sql_packagecat/databack/mysqldata/mysql_backup.sh EOF#!/bin/bashusernamerootEOFechomypasswd$ROOT_PWD/databack/mysqldata/mysql_backup.shcat/databack/mysqldata/mysql_backup.sh EOFbase_dir/databack/mysqldata#日志地址mysql_backup_log${base_dir}/mysql_backup.log#备份地址backup_path${base_dir}/sql_package#当前日期 date$(date %Y-%m-%d) #备份的文件名filename$backup_path/$(date%Y%m%d_%H%M%S).sql#打印日志echo开始备份备份日期:$(date%Y-%m-%d %H:%M:%S)$mysql_backup_log#back up 备份全部/usr/bin/mysqldump-u${username}-p${mypasswd}--quick--events--all-databases--master-data2--single-transaction--set-gtid-purgedOFF $filename#打印日志echo备份成功备份路径$filename$mysql_backup_logechoDelete the file bakup before 6 days...$mysql_backup_logif[$(ls/databack/mysqldata/sql_package/*.sql|wc-l)-gt6];then find/databack/mysqldata/sql_package-mtime6-name*.sql-execrm-rf{}\;echoDelete the file bakup successfully. $mysql_backup_logfi EOF chmodx/databack/mysqldata/mysql_backup.shif!grep-q/databack/mysqldata/mysql_backup.sh/var/spool/cron/root;thenecho0 1 * * * /databack/mysqldata/mysql_backup.sh/var/spool/cron/root fi#初始化mysqlinitialize_mysql(){log INFOStart MySQL database initialization ... Please wait .../usr/sbin/mysqld--defaults-file/etc/my.cnf--initialize-insecure/dev/nullif[$?-eq0];then log INFOMySQL initialize successfully!elselog ERRORMySQL initialize unsuccessfully! Now exit procedure!exit1 fi}initialize_mysql start_mysql(){systemctl daemon-reload systemctl enable mysqldifsystemctl restart mysqld /dev/null 21;thenecho-e${GREEN}start mysqld success!${END}elseecho-e${RED}start mysqld failed!${END}exit1 fi}start_mysql#修改root管理员密码并授权modify_password(){#定义root密码mysql-uroot--database mysql EEE update mysql.usersethost%where userroot;flush privileges;alter userroot%identified with mysql_native_password by${ROOT_PWD};grant all privileges on*.*toroot%with grant option;EEEif[$?-eq0];then log INFOThe password for the database root user is successfully changed!elselog WARNThe password for the database root user is failed change! Continue!exit1 fi mysql-uroot-p${ROOT_PWD}--database mysql EOFcreate database test DEFAULT CHARSET utf8mb4;EOFif[$?-eq0];then log INFOCreate databases successfully!elselog WARNCreate databases failed!exit1 fi mysql-uroot-p${ROOT_PWD}--database mysql EOF create usertest%identified with mysql_native_password by${ROOT_PWD};EOFif[$?-eq0];then log INFOcreate users successfully!elselog WARNcreate users failed!exit1 fi mysql-uroot-p${ROOT_PWD}--database mysql EOFgrant all privileges on test.*totest%with grant option;flush privileges;exitEOFif[$?-eq0];then log INFOGrant privileges successfully!elselog WARNGrant privileges failed!exit1 fi}modify_password可能会用到的两个rpm包mysql80-community-release-el7-5.noarch.rpmmysql80-community-release-el8-8.noarch.rpm下载地址https://download.csdn.net/download/qq_45748758/92885063