MariaDB安装与配置 #

版本类型
├── LTS（长期支持）
│   ├── 10.6 LTS（支持到2026年）
│   └── 10.11 LTS（支持到2028年）
├── Stable（稳定版）
│   └── 当前稳定版本
└── Development（开发版）
    └── 最新特性版本

# 更新软件源
sudo apt update

# 安装MariaDB
sudo apt install mariadb-server mariadb-client

# 启动服务
sudo systemctl start mariadb

# 设置开机自启
sudo systemctl enable mariadb

# 检查状态
sudo systemctl status mariadb

# 安装依赖
sudo apt install curl software-properties-common

# 添加MariaDB仓库
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

# 安装MariaDB
sudo apt update
sudo apt install mariadb-server

# 启动服务
sudo systemctl start mariadb
sudo systemctl enable mariadb

# CentOS 7
sudo yum install mariadb-server mariadb

# CentOS 8 / Rocky Linux / AlmaLinux
sudo dnf install mariadb-server mariadb

# 启动服务
sudo systemctl start mariadb
sudo systemctl enable mariadb

# 检查状态
sudo systemctl status mariadb

# 创建MariaDB仓库配置
sudo cat > /etc/yum.repos.d/mariadb.repo << EOF
[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.11/centos7-amd64
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1
EOF

# 安装
sudo yum install MariaDB-server MariaDB-client

# 启动
sudo systemctl start mariadb
sudo systemctl enable mariadb

# 运行安全配置脚本
sudo mariadb-secure-installation

# 交互式配置
# 1. 设置root密码
# 2. 移除匿名用户
# 3. 禁止root远程登录
# 4. 移除测试数据库
# 5. 重新加载权限表

下载地址
├── 官方下载
│   └── https://mariadb.org/download/
├── 版本选择
│   ├── ZIP Archive（免安装版）
│   └── MSI Package（安装版）
└── 架构选择
    ├── 64-bit（推荐）
    └── 32-bit

# 1. 双击MSI文件启动安装向导

# 2. 选择安装类型
#    - Typical：典型安装
#    - Custom：自定义安装
#    - Complete：完整安装

# 3. 设置root密码

# 4. 配置服务
#    - 服务名称：MySQL（兼容MySQL）
#    - 启动类型：自动

# 5. 完成安装

# 解压到目标目录
# 例如：C:\mariadb-10.11.0-winx64

# 创建配置文件
# C:\mariadb-10.11.0-winx64\my.ini

[mysqld]
basedir=C:/mariadb-10.11.0-winx64
datadir=C:/mariadb-10.11.0-winx64/data
port=3306
character-set-server=utf8mb4

[client]
port=3306
default-character-set=utf8mb4

# 初始化数据目录
cd C:\mariadb-10.11.0-winx64\bin
.\mariadb-install-db.exe --service=MariaDB --password=your_password

# 启动服务
net start MariaDB

# 或手动启动
.\mysqld.exe --console

# 启动服务
net start MariaDB
net start MySQL

# 停止服务
net stop MariaDB
net stop MySQL

# 查看服务状态
sc query MariaDB

# 删除服务
.\mariadb-install-db.exe --remove

# 安装Homebrew（如未安装）
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# 安装MariaDB
brew install mariadb

# 启动服务
brew services start mariadb

# 停止服务
brew services stop mariadb

# 重启服务
brew services restart mariadb

# 下载DMG或TAR包
# 解压并移动到/usr/local/

# 初始化
cd /usr/local/mariadb
./scripts/mysql_install_db --user=$(whoami)

# 启动
./bin/mysqld_safe &

# 连接
./bin/mysql -u root

# 运行安全脚本
/usr/local/bin/mariadb-secure-installation

# 或使用mysql_secure_installation
mysql_secure_installation

# 拉取镜像
docker pull mariadb:10.11

# 运行容器
docker run -d \
    --name mariadb \
    -e MYSQL_ROOT_PASSWORD=your_password \
    -e MYSQL_DATABASE=mydb \
    -p 3306:3306 \
    -v mariadb_data:/var/lib/mysql \
    mariadb:10.11

# 查看日志
docker logs mariadb

# 进入容器
docker exec -it mariadb mariadb -u root -p

# docker-compose.yml
version: '3.8'

services:
  mariadb:
    image: mariadb:10.11
    container_name: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: your_root_password
      MYSQL_DATABASE: mydb
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci

volumes:
  mariadb_data:

# 启动
docker-compose up -d

# 停止
docker-compose down

# 查看日志
docker-compose logs -f mariadb

[mysqld]
# 基本设置
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mariadb/mariadb.pid

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接设置
max_connections = 200
max_connect_errors = 100
wait_timeout = 28800
interactive_timeout = 28800

# 缓冲区设置
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 4000
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M

# 日志设置
log_error = /var/log/mariadb/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
long_query_time = 2

# InnoDB设置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
no-auto-rehash
default-character-set = utf8mb4

[mysqld]
# 内存配置（根据服务器内存调整）
# 假设服务器有8GB内存

# InnoDB缓冲池（物理内存的50-70%）
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# 日志配置
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2

# 查询缓存（MariaDB 10.5+已移除）
# query_cache_type = 1
# query_cache_size = 128M

# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序和连接缓冲
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 16M

# 线程缓存
thread_cache_size = 64
thread_concurrency = 16

# 表缓存
table_open_cache = 8000
table_definition_cache = 2000

[mysqld]
# 禁用本地文件加载
local_infile = 0

# 禁用符号链接
symbolic-links = 0

# 跳过DNS解析
skip-name-resolve

# 只允许本地连接
bind-address = 127.0.0.1

# 禁用匿名用户
skip-grant-tables = 0

# SSL配置
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

# 密码验证插件
plugin_load_add = simple_password_check
simple_password_check_minimal_length = 8

# 本地连接
mariadb -u root -p

# 或使用mysql命令（兼容）
mysql -u root -p

# 指定主机和端口
mariadb -h 127.0.0.1 -P 3306 -u root -p

# 指定数据库
mariadb -u root -p mydb

# 执行SQL文件
mariadb -u root -p mydb < script.sql

# 执行SQL命令
mariadb -u root -p -e "SELECT VERSION();"

# ~/.my.cnf
[client]
user = root
password = your_password
host = localhost
port = 3306

# 使用
mariadb

# systemctl管理
sudo systemctl start mariadb
sudo systemctl stop mariadb
sudo systemctl restart mariadb
sudo systemctl status mariadb
sudo systemctl enable mariadb
sudo systemctl disable mariadb

# 查看日志
sudo journalctl -u mariadb

# 重新加载配置
sudo systemctl reload mariadb

# 启动
mysqld_safe --user=mysql &

# 停止
mysqladmin -u root -p shutdown

# 检查进程
ps aux | grep mysql

# 检查端口
netstat -tlnp | grep 3306

# 配置多实例
# /etc/my.cnf
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass

[mysqld1]
port = 3306
datadir = /var/lib/mysql1
socket = /var/lib/mysql1/mysql.sock

[mysqld2]
port = 3307
datadir = /var/lib/mysql2
socket = /var/lib/mysql2/mysql.sock

# 启动多实例
mysqld_multi start
mysqld_multi start 1
mysqld_multi start 2

# 查看状态
mysqld_multi report

-- 连接后执行
SELECT VERSION();

-- 结果示例
+-----------------+
| VERSION()       |
+-----------------+
| 10.11.2-MariaDB |
+-----------------+

-- 查看详细信息
SHOW VARIABLES LIKE '%version%';

-- 查看状态
SHOW STATUS;

-- 查看引擎
SHOW ENGINES;

-- 查看变量
SHOW VARIABLES;

-- 查看字符集
SHOW VARIABLES LIKE 'character%';

-- 查看连接数
SHOW STATUS LIKE 'Threads_connected';

# 测试本地连接
mariadb -u root -p -e "SELECT 1;"

# 测试远程连接
mariadb -h remote_host -u user -p -e "SELECT 1;"

# 测试性能
mariadb -u root -p -e "SELECT BENCHMARK(1000000, 1+1);"

# 检查错误日志
tail -f /var/log/mariadb/error.log

# 检查权限
ls -la /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql

# 检查配置
mariadbd --validate-config

# 初始化数据目录
mariadb-install-db --user=mysql

# 检查服务状态
sudo systemctl status mariadb

# 检查端口
sudo netstat -tlnp | grep 3306

# 检查防火墙
sudo firewall-cmd --list-ports
sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

# 检查用户权限
mariadb -u root -p -e "SELECT user, host FROM mysql.user;"

# 停止服务
sudo systemctl stop mariadb

# 跳过权限启动
mysqld_safe --skip-grant-tables &

# 连接并修改密码
mariadb -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

# 重启服务
sudo systemctl restart mariadb