1. 操作系统实施方案设计
1.1. 操作系统环境设计
Mariadb软件安装在x86 64位及以上版本，满足Mariadb软件对操作系统的要求。
1.2.操作系统配置
1.2.1. 检查硬件配置
1. 架构：uname -m
x86_64 ##表示x86 64为架构
2. 内存：grep MemTotal /proc/meminfo
当前系统识别到内存大小
3. CPU：lscpu
当前系统识别到CPU信息
4. swap：grep SwapTotal /proc/meminfo
当前系统识别到交换区大小
5. 文件系统：df -h
当前系统识别到已挂载文件系统信息
1.2.2. 其他服务配置
1) chrony配置(推荐采用)：
节点1添加以下内容到/etc/chrony.conf
server xxxx
server xxxx
具体以实际环境为准
注释外网时钟同步
#server 0.pool.ntp.org iburst
#server 1.pool.ntp.org iburst
#server 2.pool.ntp.org iburst
#server 3.pool.ntp.org iburst
重启chrony服务
systemctl stop chronyd.service
systemctl start chronyd.service
查询服务是否启动
ss -tunlp|grep chronyd
systemctl status chronyd.service
设置开机自启动
systemctl enable chronyd.service
检查chrony源时钟服务器状态
watch chronyc sources
检查chrony每个时钟源的偏移评估值
chronyc sourcestats
检查chrony详细同步状态
chronyc sources -v
2) ntp配置：
添加以下内容到/etc/ntp.conf
server xxxx
server xxxx
具体以实际环境为准
添加启动项-x到/etc/sysconfig/ntpd，配置如下
Command line options for ntpd
OPTIONS=“-g -x”
重启ntp服务
systemctl stop ntpd
systemctl start ntpd
节点2添加以下内容到/etc/ntp.conf
server 10.1.80.252
server 10.1.80.253
server 10.8.52.252
server 10.8.52.253
3) iptables：
建议停止iptables服务
systemctl disable firewalld.service
systemctl status firewalld.service
[root@tshddb ~]# systemctl status firewalld.service
firewalld.service - firewalld - dynamic firewall
daemon
Loaded:
loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset:
enabled)
Active:
inactive (dead)
Docs:
man:firewalld(1)4) 操作系统参数配置
cat <<EOF >>/etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
EOF
cat <<EOF >>/etc/sysctl.conf
vm.swappiness=1
EOF
echo "使内核配置信息生效"
sysctl -p /etc/sysctl.conf
安装依赖包
yum install bison bison-devel zlib-devel
libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel
gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel.x86_641.3. 配置参数文件
将/app/maridb/maridb3306/etc/my.cnf替换为如下内容(假设Mariadb安装在/app/mariadb目录下，注意若干参数需要根据配置调整)。
The following options
will be passed to all MariaDB clients
[client]
port =
3306
socket =
/tmp/mysql.sock
The MariaDB server
[mysqld]
#***********************************common
parameters******************************
user = mysql
port =
3306
socket =
/tmp/mysql.sock
basedir = /app/maridb/maridb10
datadir =
/app/maridb/maridb3306/data
log-error =
/app/maridb/maridb3306/log/mysql_err.log
pid-file =
/app/maridb/maridb3306/etc/mysql.pid
log-bin = /app/maridb/maridb3306/log/bin_log
innodb_log_group_home_dir = /app/maridb/maridb3306/data
innodb_data_home_dir = /app/maridb/maridb3306/data
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = 'SET
collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
transaction_isolation = READ-COMMITTED
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
table_open_cache = 64
table_definition_cache = 400
sort_buffer_size = 8M
max_length_for_sort_data = 16k
##buffer for each session
read_buffer_size = 1M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
binlog_cache_size = 2M
thread_cache_size = 128
tmp_table_size = 8M
query_cache_size = 0
query_cache_type = OFF
#####the MariaDB server
listens for TCP/IP connections
#bind-address=0.0.0.0
max_connections = 2000
max_user_connections = 1500
wait_timeout =172800
interactive_timeout =172800
net_buffer_length = 8K
binary logging format -
mixed recommended
binlog_format =Row
#
***************************** INNODB Specific options
---
server-id = 710 ##id需唯一
#default_storage_engine = InnoDB
#del 30day before binlog
expire_logs_days = 7
back_log = 500
Try number of CPU's*2 for
thread_concurrency
#thread_concurrency = 40
Set .._log_file_size to
25 % of buffer pool size
####time before deadlock
rollbak
innodb_lock_wait_timeout = 120
innodb_autoinc_lock_mode = 2
lower_case_table_names = 1
innodb_autoextend_increment = 1
##*************** Data
options
innodb_data_file_path = ibdata1:128M:autoextend
#innodb_file_format_max = barracuda
#innodb_file_format_check = ON
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_checksum_algorithm = crc32
sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
###************************Buffer
Pool options
#innodb_additional_mem_pool_size
= 40M
innodb_buffer_pool_size = 16G ##物理内存的50%
innodb_max_dirty_pages_pct = 75
innodb_adaptive_flushing = ON
innodb_old_blocks_time = 1000
####*****************Redo
options
innodb_log_buffer_size = 128M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown = 1
####*****************Transaction
options
innodb_rollback_on_timeout = 1
####*****************IO
options
performance_schema = on
innodb_read_io_threads = 4
innodb_write_io_threads = 12
innodb_io_capacity = 2000
innodb_use_native_aio = 1
####***********slow log
slow_query_log=on
slow_query_log_file =
/app/maridb/maridb3306/log/slow_log/mysql_slow.log
long_query_time = 3
min_examined_row_limit = 1024
####*************gtid
gtid-domain-id = 1
#gtid_mode=on
#enforce_gtid_consistency=true
log-slave-updates=true
relay-log=mysql-relay-bin
[mysqldump]
quick
max_allowed_packet = 1G
default-character-set = utf8
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 4M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysql_safe]
user=mysql
open-files-limit = 65535
log-error =
/app/maridb/maridb3306/log/mysql_safe.log
pid-file =
/app/maridb/maridb3306/etc/mysql_safe.pid2. Mariadb数据库单机安装部署
创建用户和组
if
id -u mysql >/dev/null 2>&1; then
echo "INFO" "MySQL user
exists."
userdel mysql
groupadd -g 10011 mysql
useradd -d /home/mysql -u 12001 -g mysql -G
mysql mysql
echo "配置用户密码"
echo "password" | passwd --stdin
mysql
else
echo "INFO" "Create MySQL user."
groupadd -g 10011 mysql
useradd -d /home/mysql -u 12001 -g mysql -G
mysql mysql
echo "配置用户密码"
echo "Xpassword | passwd --stdin
mysql
fi建立/app/soft目录，上传Mariadb安装介质到该目录
该目录需要Mariadb用户有读写执行的权限
mkdir -p /app/soft
mkdir -p /app/maridb/maridb3306/etc
mkdir -p /app/maridb/maridb3306/data
mkdir -p /app/maridb/maridb3306/tmp
mkdir -p /app/maridb/maridb3306/share
mkdir -p /app/maridb/maridb3306/log/bin_log
mkdir -p /app/maridb/maridb3306/log/slow_log
ln -s /app/maridb/mariadb-10.11.5-linux-systemd-x86_64 /app/maridb/maridb10
chown -R mysql.mysql /app2.1. 安装包解压缩
上传Mariadb.zip压缩包至：
mariadb-10.11.5-linux-systemd-x86_64.tar.gz
tar -zxvf mariadb-10.11.5-linux-systemd-x86_64 .tar.gz2.2. 初始化数据库
/app/maridb/maridb10/scripts/mysql_install_db
--defaults-file=/app/maridb/maridb3306/etc/my.cnf --user=mysql
cp /app/maridb/maridb3306/etc/my.cnf /etc/my.cnf2.3. 配置服务
cp /app/maridb/maridb10/support-files/mysql.server /etc/init.d/mysql
修改以下配置:
vi /etc/init.d/mysql
basedir=/app/maridb/maridb10
datadir=/app/maridb/maridb3306/data
chkconfig --add mysql
chkconfig --level 2345 mysql on
visudo
mysql ALL=(ALL) NOPASSWD:/bin/systemctl * mysql
启动数据库
MySQL用户
sudo systemctl start mysql
sudo systemctl status mysql
root用户登陆,修改密码
mysql -uroot -p -S /tmp/mysql.socket
alter user root@'localhost' identified by "password";
flush privileges;3. 安装配置主从架构Mariadb
3.1. 安装从库Mariadb实例
参考2章节创建Mariadb实例，注意配置文件server-id、report_host不能和主库一致。
3.2. 配置主从复制关系
登录主库后，在主库创建复制账号：（在主库进行操作！）
create
user 'repl'@'%' identified by "password";
grant
replication slave on *.* to 'repl'@'%';
CREATE
USER `prom`@`%` IDENTIFIED BY "Prom@2023" with MAX_USER_CONNECTIONS 5
;
GRANT
SELECT, PROCESS, REPLICATION CLIENT ON *.* TO `prom`@`%`;
flush privileges;- 登录从库后，进行主从复制配置：（在从库进行操作！）
Mariadb>
stop REPLICA；
Mariadb>
reset REPLICA；
Mariadb>
reset master;
Mariadb>
change master to
master_host = '10.10.6.48',
master_port = 3306,
master_user = 'repl',
master_password = 'password';
Mariadb>
start REPLICA;
Mariadb>
show REPLICA status\G
set
global read_only=on;3.登录主库后，删除匿名用户：（在主库进行操作！）
#删除匿名用户
mysql>delete
from user where user = '';
mysql>delete
from user where user = 'root' and host <> 'localhost';
mysql>delete
from proxies_priv where user = 'root' and host <> 'localhost';
#立刻生效
mysql>flush
privileges;5.配置环境变量
为了使MYSQL数据库能正常的启动，需要配置环境变量。
在/etc/profile中加上如下内容(假设数据库安装在/app/maridb/maridb10目录下)：
export PATH=$PATH:/app/maridb/maridb10/bin然后执行source /etc/profile使其生效
