一、安装环境

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

数据库版本: mariadb-10.5.17-linux-systemd-x86_64.tar.gz

二、上传安装文件至/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 $PKGBASEtar -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 mysqlecho 'mysql'|passwd --stdin mysqlmkdir -p /data/mysql/{data,socket,log,tmp}chown -R mysql:mysql /usr/local/mysqlchown -R mysql:mysql /data/mysql#4、初始化数据库echo "********************4、初始化数据库**************************"\cp -f $PKGBASE/my.cnf /etc/my.cnfsed -i "s/report_host = 8.8.8.8/report_host = $IP_ADDR/g" /etc/my.cnfcd /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-reloadsystemctl start mariadbsystemctl status mariadbsystemctl enable mariadbecho "export PATH=/usr/local/mysql/bin:$PATH">>/etc/profileexport 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 = 3306socket = /data/mysql/socket/mysqld.sockdefault-character-set = utf8mb4# The MySQL server#---Basic---#[mysqld]read-only=0server-id = 1report_host = 8.8.8.8 #让master自动发现slave,方便mysqlfailover工具发现.report-port=3306  extra_port = 3106 #管理员专用通道extra_max_connections=3 #管理员专用通道最大数量port = 3306user = mysqlbasedir = /usr/local/mysqldatadir = /data/mysql/datatmpdir = /data/mysql/tmpsocket = /data/mysql/socket/mysqld.socklog_bin = /data/mysql/log/mysql-bin.log pid-file = /data/mysql/socket/mysql.pidskip-external-lockingskip-name-resolvelower_case_table_names = 1default-time_zone = '+8:00'default-storage-engine = INNODBcharacter-set-server = utf8mb4connect_timeout = 10wait_timeout = 28800interactive_timeout = 28800back_log = 1024event_scheduler = OFFopen_files_limit = 65535sql_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 = 1024query_cache_type = 0query_cache_size = 0#init-connect = 'insert into auditdb.accesslog (connectionid,conn rname,privmatchname,logintime) values(connection_id(),user(),current_user(),now());'#log_warnings = 2log_warnings = 1#---binlog---#log-bin = /data/mysql/log/mysql-binbinlog_format = rowmax_binlog_size = 1024Mbinlog_cache_size = 24M #之前是64M ， 这是一次性分配的会话级别变量，正确情况下10M足够。expire-logs-days = 7sync_binlog = 1#sync_relay_log=1log-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 = 1relay_log_recovery = 1relay_log = mysqld-relay-bin#GTID#gtid_mode=on#enforce-gtid-consistency=on#gtid_domain_id = 0 针对mariadb可用#---slow log---#slow_query_log = 1slow_query_log_file = /data/mysql/log/mysql-slow.loglong_query_time = 0.5#---error log---#log-error = /data/mysql/log/error.log#---per_thread_buffers---#max_connections=8000max_user_connections=5000max_connect_errors=1000000key_buffer_size = 64Mmax_allowed_packet = 1024Mtable_open_cache = 6144table_definition_cache = 4096sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mjoin_buffer_size = 8M #之前是2M，本参数和BNL优化器有关系，适当调高tmp_table_size = 256Mmax_heap_table_size = 256Mquery_cache_type=0query_cache_size = 0query_cache_strip_comments = 1bulk_insert_buffer_size = 32Mthread_cache_size = 1024#thread_concurrency = 32 主要针对solaris8之前的系统thread_stack = 512K#--- InnoDB ---#innodb_data_home_dir = /data/mysql/datainnodb_data_file_path=ibdata1:10M:autoextendinnodb_buffer_pool_size = 512Minnodb_buffer_pool_instances = 21 #通常1.3G一个pool,最大64.innodb_log_file_size = 1024Minnodb_log_buffer_size = 64Minnodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 20innodb_sync_spin_loops = 40innodb_max_dirty_pages_pct = 75#innodb_support_xa = 1innodb_thread_concurrency = 0#innodb_thread_sleep_delay = 500#innodb_concurrency_tickets = 1000log_bin_trust_function_creators = 1innodb_flush_method = O_DIRECTinnodb_file_per_table = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 2000innodb_purge_threads=4 #适当跳大此线程数,加速DML,硬件能够支持的住。 innodb_purge_batch_size = 300 # 5.6.3以后默认就是300.#innodb_old_blocks_pct=75innodb_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=1auto_increment_offset=1[mysqldump]quickmax_allowed_packet = 1024Mmyisam_max_sort_file_size = 10G[mysql]no-auto-rehash[myisamchk]key_buffer_size = 64Msort_buffer_size = 256kread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 68192#flush_caches = 1#numa_interleave = 1