mariadb(mysql) Linux 二進制版快速安裝指南

一、安裝環境

作業系統版本: CentOS Linux release 7.9.2009 (Core)

資料庫版本: mariadb-10.5.17-linux-systemd-x86_64.tar.gzhttp://ftp.cc.uoc.gr/mirrors/mariadb/mariadb-10.5.17/bintar-linux-systemd-x86_64/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 $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