一、安装环境

操作系统版本: CentOS Linux release 7.9.2009 (Core)

二、上传安装文件至/opt/my_install目录，

my.cnf

mysql-install.sh

mariadb-10.5.17-linux-systemd-x86_64.tar.gz

三、开始安装

执行以下命令开始安装

cd /opt/my_install

sh ./mysql-install.sh

四、mariadb安装脚本

mysql-install.sh

#!/bin/bash
#mariadb二进制包安装脚本
PKGBASE=`pwd`
DBPASSWORD='数据库密码'
##1、基础环境初始化
### slb-install.sh 脚本中已经完成基础环境初始化
#2、解压安装包
echo "********************2、解压安装包************************"
cd $PKGBASE
tar -zxvf mariadb-10.5.17-linux-systemd-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s /usr/local/mariadb-10.5.17-linux-systemd-x86_64 /usr/local/mysql
#3、创建用户及目录
echo "********************3、创建用户及目录**************************"
useradd mysql
echo 'mysql'|passwd --stdin mysql
mkdir -p /data/mysql/{data,socket,log,tmp}
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysql
#4、初始化数据库
echo "********************4、初始化数据库**************************"
\cp -f $PKGBASE/my.cnf /etc/my.cnf
sed -i "s/report_host = 8.8.8.8/report_host = $IP_ADDR/g" /etc/my.cnf
cd /usr/local/mysql
./scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql
#5、配置自启动服务
echo "********************5、配置自启动服务**************************"
cp support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service; 
systemctl daemon-reload
systemctl start mariadb
systemctl status mariadb
systemctl enable mariadb
echo "export PATH=/usr/local/mysql/bin:$PATH">>/etc/profile
export PATH=/usr/local/mysql/bin:$PATH
\cp -f /usr/local/mysql/bin/mysql /usr/local/bin
#6、创建用户及授权
echo "********************6、创建用户及授权**************************"
DBPASSWORD=`ls -l  /dev/disk/by-uuid|grep -v sr0|awk NR==2'{print}'|awk '{print $9}'|awk -F\- '{print $1}'`
mysql -e"create database easytong;"
mysql -e"CREATE USER 'easytong'@'%' IDENTIFIED BY '$DBPASSWORD';"
mysql -e"grant all on easytong.* to 'easytong'@'%';"
mysqladmin -u root password "$DBPASSWORD"
#7、安装完成
echo "********************7、安装完成,Good!**************************"
#echo -e "\033[46;30m查看数据库初始密码：ls -l  /dev/disk/by-uuid|grep -v sr0|awk NR==2'{print}'|awk '{print \$9}'|awk -F\- '{print \$1}'\033[0m"

五、数据库配置文件

my.cnf

# mariadb10.5.8 configuration 
[client]
port = 3306
socket = /data/mysql/socket/mysqld.sock
default-character-set = utf8mb4
# The MySQL server
#---Basic---#
[mysqld]
read-only=0
server-id = 1
report_host = 8.8.8.8 #让master自动发现slave,方便mysqlfailover工具发现.
report-port=3306  
extra_port = 3106 #管理员专用通道
extra_max_connections=3 #管理员专用通道最大数量
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
socket = /data/mysql/socket/mysqld.sock
log_bin = /data/mysql/log/mysql-bin.log 
pid-file = /data/mysql/socket/mysql.pid
skip-external-locking
skip-name-resolve
lower_case_table_names = 1
default-time_zone = '+8:00'
default-storage-engine = INNODB
character-set-server = utf8mb4
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
back_log = 1024
event_scheduler = OFF
open_files_limit = 65535
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#innodb_flush_neighbors=0 sas盘建议开启. ssd pci-e 等存储设备建议关闭
thread_cache_size = 1024
query_cache_type = 0
query_cache_size = 0
#init-connect = 'insert into auditdb.accesslog (connectionid,conn rname,privmatchname,logintime) values(connection_id(),user(),current_user(),now());'
#log_warnings = 2
log_warnings = 1
#---binlog---#
log-bin = /data/mysql/log/mysql-bin
binlog_format = row
max_binlog_size = 1024M
binlog_cache_size = 24M #之前是64M ， 这是一次性分配的会话级别变量，正确情况下10M足够。
expire-logs-days = 7
sync_binlog = 1
#sync_relay_log=1
log-slave-updates=1 #级联复制需要开启
#master_info_repository=table #之前是关闭的，开启有利于数据安全，主要针对slave掉电挂掉的情况
#relay_log_info_repository=table #之前是关闭的，开启有利于数据安全,主要针对slave掉电挂掉的情况
#---replication---#
slave-net-timeout = 10
#半同步复制
#rpl_semi_sync_master_enabled = 1 #master开启半同步复制
#rpl_semi_sync_master_wait_no_slave = 1 #是否允许master 每个事物提交后都要等待slave的receipt信号。默认为on ，每一个事务都会等待，如果slave宕掉后，当slave追赶上master的日志时，可以自动的切换为半同步方式，如果为off,则slave追赶上后，也不会采用半同步的方式复制了，需要手工配置。
#rpl_semi_sync_master_timeout = 1000 #主库在某次事务中，如果等待时间超过1000毫秒，那么则降级为普通模式，不再等待备库
#rpl_semi_sync_slave_enabled = 1 #slave 开启半同步复制
skip-slave-start
#log_slave_updates = 1
relay_log_recovery = 1
relay_log = mysqld-relay-bin
#GTID
#gtid_mode=on
#enforce-gtid-consistency=on
#gtid_domain_id = 0 针对mariadb可用
#---slow log---#
slow_query_log = 1
slow_query_log_file = /data/mysql/log/mysql-slow.log
long_query_time = 0.5
#---error log---#
log-error = /data/mysql/log/error.log
#---per_thread_buffers---#
max_connections=8000
max_user_connections=5000
max_connect_errors=1000000
key_buffer_size = 64M
max_allowed_packet = 1024M
table_open_cache = 6144
table_definition_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
join_buffer_size = 8M #之前是2M，本参数和BNL优化器有关系，适当调高
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_type=0
query_cache_size = 0
query_cache_strip_comments = 1
bulk_insert_buffer_size = 32M
thread_cache_size = 1024
#thread_concurrency = 32 主要针对solaris8之前的系统
thread_stack = 512K
#--- InnoDB ---#
innodb_data_home_dir = /data/mysql/data
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 21 #通常1.3G一个pool,最大64.
innodb_log_file_size = 1024M
innodb_log_buffer_size = 64M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 75
#innodb_support_xa = 1
innodb_thread_concurrency = 0
#innodb_thread_sleep_delay = 500
#innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
innodb_purge_threads=4 #适当跳大此线程数,加速DML,硬件能够支持的住。 
innodb_purge_batch_size = 300 # 5.6.3以后默认就是300.
#innodb_old_blocks_pct=75
innodb_change_buffering=all #之前是insert，建议使用默认设置，应对更多场景。 上次的qc，update操作很多，修改这个效果很明显。具体值可以根据实际情况动态修改。
innodb_stats_on_metadata=0
#optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on' # 新添加项，目的是利用5.6的MRR，ICP，BKA 优化器
#transaction_isolation = READ-COMMITTED
#skip-innodb_adaptive_hash_index此参数在聚合支付中还是注释掉，加快基于索引数据量比较大的在内存中开辟一块hash存入索引hash
#skip-innodb_adaptive_hash_index #5.6或者更高的版本，通常禁用hash-index 适应性更好， SHOW ENGINE INNODB STATUS command. If you see many threads waiting on an RW-latch created in btr0sea.c。 需要关闭hash——index,2019-03-01但是聚合支付要求启用hash-index,也就要把这个参数注释
#---new---#
eq_range_index_dive_limit =10000 #优化 where 条件中 in 语句
#innodb_log_block_size = 512 这是默认值
#numa
#thread pool
# thread_handling=pool-of-threads
# thread_pool_oversubscribe=15 #默认是3，在一主多从中有风险
# thread_pool_stall_limit = 50 #默认是500毫秒
#
#innodb_corrupt_table_action = warn
#innodb_buffer_pool_dump_at_shutdown = 1 #在shutdown时把热数据dump到本地磁盘
#innodb_buffer_pool_load_at_startup = 1 #在启动时把热数据加载到内存
#
#innodb_kill_idle_transaction = 5 #类似于pt-kill
#innodb_fake_changes = 1 #重启备库时对其进行预热，以加快复制的速度
auto_increment_increment=1
auto_increment_offset=1
[mysqldump]
quick
max_allowed_packet = 1024M
myisam_max_sort_file_size = 10G
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 68192
#flush_caches = 1
#numa_interleave = 1