ubuntu22.04安装Mariadb 10.6过程记录

默认安装10.3已经没有：

# apt install mariadb-server-10.3
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Package mariadb-server-10.3 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
However the following packages replace it:
  mariadb-server-core-10.6 mariadb-server-10.6

# apt install mariadb-server-10.6 mariadb-client
The following additional packages will be installed:
  galera-4 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdaxctl1 libdbd-mysql-perl libdbi-perl libfcgi-bin libfcgi-perl libfcgi0ldbl
  libhtml-template-perl libmariadb3 libmysqlclient21 libndctl6 libpmem1 libsigsegv2 libsnappy1v5 libterm-readkey-perl liburing2 mariadb-client-10.6
  mariadb-client-core-10.6 mariadb-common mariadb-server-core-10.6 mysql-common socat
Suggested packages:
  gawk-doc libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx mariadb-test
The following NEW packages will be installed:
  galera-4 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdaxctl1 libdbd-mysql-perl libdbi-perl libfcgi-bin libfcgi-perl libfcgi0ldbl
  libhtml-template-perl libmariadb3 libmysqlclient21 libndctl6 libpmem1 libsigsegv2 libsnappy1v5 libterm-readkey-perl liburing2 mariadb-client
  mariadb-client-10.6 mariadb-client-core-10.6 mariadb-common mariadb-server-10.6 mariadb-server-core-10.6 mysql-common socat
0 upgraded, 28 newly installed, 0 to remove and 134 not upgraded.
Need to get 18.8 MB of archives.
After this operation, 166 MB of additional disk space will be used.

# cp -r /var/lib/mysql /xxx/
# chown -R mysql.mysql mysql

修改# vi /etc/my.cnf没生效：

修改apparmor：AppArmor是一种用于Ubuntu的基于路径的访问控制系统

vim /etc/apparmor.d/tunables/alias
在文档最后面加入：alias /var/lib/mysql -> /xxx/mysql,

修改配置文件：
缺省路径为/etc/mysql/mariadb.cnf，里面包含一些子目录，简要说明如下，本次使用/etc/mysql/mariadb.conf.d/50-server.cnf

root@ceshr-hbdb01-lh:/etc/mysql# more mariadb.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.

修改如下：

# vim /etc/mysql/mariadb.conf.d/50-server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#

#user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
#datadir                 = /var/lib/mysql
#tmpdir                  = /tmp
# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip-name-resolve
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
#
# * Fine Tuning
#
#key_buffer_size        = 128M
#max_allowed_packet     = 1G
#thread_stack           = 192K
#thread_cache_size      = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections        = 100
#table_cache            = 64

#
# * Logging and Replication
#

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 10
#log_slow_verbosity     = query_plan,explain
#log-queries-not-using-indexes
#min_examined_row_limit = 1000

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
server-id=1
user=mysql
datadir=/xxx/mysql
port=3306
binlog-format=row
log-bin=/xxx/mysql/mysql-bin.log
log-bin-index=/xxx/mysql/mysql-bin.index
expire_logs_days        = 20
log_error = /xxx/mysql/mariadb_error.log
slow_query_log = 1
slow_query_log_file = /xxx/mysql/mariadb_slow.log
performance_schema=ON
default_storage_engine=InnoDB
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 8G
innodb_log_file_size = 2048M
max_connections = 500
connect_timeout=10
skip-name-resolve
#character-set-server=utf8
bind-address=0.0.0.0
socket=/xxx/mysql/mysql.sock
#max_binlog_size        = 100M

#
# * SSL/TLS
#

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G

# this is only for embedded server

[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.6 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.6]
[client]
socket=/xxx/mysql/mysql.sock
port=3306

修改mysql.service和mysqld.service文件：
ProtectHome 是一个 systemd 服务配置项，定义在 service unit 文件（如 /lib/systemd/system/mysql.service）中。它用于限制服务进程访问用户的家目录（/home，/root 和 /run/user）。

sudo vim /lib/systemd/system/mysql.service
sudo vim /lib/systemd/system/mysqld.service
将ProtectHome修改为False
# Prevent accessing /home, /root and /run/user
ProtectHome=False

Ubuntu22.04使用sftp指定加密算法

root@ceshr-hbdb01-lh:~/run# sftp -oHostKeyAlgorithms=+ssh-rsa sftpuser@ip

监控使用snmp v3方式：

root@ceshr-hbdb01-lh:/etc# atp-get install snmp snmpd snmp-mibs-downloader
root@ceshr-hbdb01-lh:/etc# systemctl stop snmpd
root@ceshr-hbdb01-lh:/etc# net-snmp-create-v3-user -ro -A 密码 -a SHA -X 密码 -x AES 用户名
adding the following line to /var/lib/snmp/snmpd.conf:
   createUser 用户 SHA "密码" AES "密码"
adding the following line to /usr/share/snmp/snmpd.conf:
   rouser 用户

root@ceshr-hbdb01-lh:/etc# snmpdf -v 3  -a SHA -A 密码-x AES -X 密码 -u 用户 -l authpriv    127.0.0.1
Description              Size (kB)            Used       Available Used%
Physical memory           16367688        15646484          721204   95%
Virtual memory            18464836        16444528         2020308   89%
Memory buffers            16367688          251564        16116124    1%
Cached memory              3255768         3255768               0  100%
Shared memory                 1840            1840               0  100%
Swap space                 2097148          798044         1299104   38%
Available memory           3889264               0         3889264    0%
/run                       1636772            2240         1634532    0%
...