Mariadb 二进制版安装与配置

slug

mariadb-setup-from-binary

一、MariaDB 准备

1.1. 下载

根据系统选择相应版本版本，然后下载解压。

下载地址：https://downloads.mariadb.org/


mariadb-11.3.2-linux-systemd-x86_64.tar.gz

1.2. 安装依赖


# On Ubuntu/Debian:
sudo apt -y install build-essential libaio-dev
sudo apt -y install libncurses5

# On Fedora/RHEL/CentOS:
yum -y install libaio-devel numactl-libs (centos)
dnf -y install ncurses-devel (centos8)
ln -s /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5
ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5

二、安装 MariaDB

2.1. 添加用户组


# On Ubuntu/Debian:
sudo adduser \
   --system \
   --shell /bin/false \
   --gecos 'MariaDB server' \
   --group \
   --disabled-password \
   --home /var/lib/mysql \
   mysql

# On Fedora/RHEL/CentOS:
sudo groupadd --system mysql
sudo adduser \
   --system \
   --shell /bin/bash \
   --comment 'MariaDB server' \
   --gid mysql \
   --home-dir /var/lib/mysql \
   --create-home \
   mysql

2.2. 配置文件

Configuring MariaDB with Option Files

创建工作路目录：


sudo mkdir -p /var/log/mariadb  /etc/my.cnf.d  && \
sudo chown mysql:mysql /var/log/mariadb -R

主配置文件： /etc/my.cnf


sudo sh -c "cat > /etc/my.cnf" <<EOF
#
# This group is read both by the client and the server
#
[client-server]
port=3306
socket=/dev/shm/mysql.sock

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
EOF

客户端配置文件： /etc/my.cnf.d/client.cnf


sudo sh -c "cat > /etc/my.cnf.d/client.cnf" <<EOF
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#
[client]
default-character-set = utf8mb4

#
# This group is not read by mysql client library,
#
[client-mariadb]
EOF

服务端配置文件： /etc/my.cnf.d/server.cnf


sudo sh -c "cat > /etc/my.cnf.d/server.cnf" <<EOF
#
# This groups are read by MariaDB server.
# Use it for options that only the server (but not clients)
#
[server]

#
# This group is read both by the MariaDB Server and MySQL Server.
#
[mysqld]
datadir=/var/lib/mysql
bind-address=0.0.0.0
pid-file=/var/lib/mysql/mariadb.pid

character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci

#
# This group is only read by MariaDB servers, not by MySQL.
#
[mariadb]
log_error=/var/log/mariadb/mariadb.err
aria_log_dir_path=/var/log/mariadb

## Binary Log
log_bin=/var/log/mariadb/mariadb-bin
log_bin_index=/var/log/mariadb/mariadb-bin.index
binlog_format=mixed
expire-logs-days=7

## Slow Query Log
slow-query-log=1
slow_query_log_file=/var/log/mariadb/mariadb-slow
long_query_time=10

## MariaDB Audit Plugin
#<https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables>
plugin-load=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
server_audit_events=CONNECT,QUERY,TABLE
server_audit_logging=ON
server_audit_file_rotate_size=20M
server_audit_file_rotations=10
server_audit_file_path=/var/log/mariadb/mariadb-audit.log
EOF

2.3. 安装


sudo mkdir -p /usr/local/mariadb/ && \
sudo tar -zxf mariadb-11.*.*-linux-systemd-x86_64.tar.gz -C /usr/local/mariadb && \
sudo ln -s /usr/local/mariadb/mariadb-11.3.2-linux-systemd-x86_64 /usr/local/mysql && \
cd /usr/local/mysql
sudo ./scripts/mysql_install_db --user=mysql

2.4. 开机自启动

方法1：systemd

systemd


sudo cp support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service
sudo systemctl enable --now mariadb.service
# sudo systemctl disable mariadb.service

方法2：不推荐


sudo cp support-files/mysql.server /etc/init.d/mysql
sudo chkconfig mysql on
sudo service mysql start

方法3：手动启动


export PATH=$PATH:/usr/local/mysql/bin/
echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> ~/.bashrc
mysqld_safe --user=mysql &

三、设置 MariaDB

3.1. 初始化设置

MariaDB 10.4+ 以后，root 帐号认证机制已经变更，详见官网说明：

Authentication in MariaDB 10.4 — Understanding the Changes

mysql 帐号登录：


# root 和 mysql 均是系统自动创建的账号
sudo mysql

如果报错 sudo: mysql: command not found 请检查 sudo 配置是否开启了 secure_path


sudo visudo


# 注释以下内容
Defaults secure_path="......"

不想使用 unix_socke 方式登陆？设置 root 密码：


ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret");

MariaDB 10.4 以前版本继续使用以下方法设置root密码：


/usr/local/mysql/bin/mysqladmin -u root -h 'hosts-name' password 'verysecret'
/usr/local/mysql/bin/mysql_secure_installation

3.2. 开启远程登录


sudo mysql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'verysecret' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3.3. 解决 MariaDB 中文乱码

Setting Character Sets and Collations


vim /etc/my.cnf

[client]
...
default-character-set = utf8mb4
...
[mysql]
...
default-character-set = utf8mb4

...
[mysqld]
...
character-set-server  = utf8mb4
character-set-filesystem = utf8mb4
collation-server      = utf8mb4_general_ci
...

查看效果


SHOW VARIABLES WHERE Variable_name LIKE 'character\\_set\\_%' OR Variable_name LIKE 'collation%';

3.4. 数据备份

导出为SQL文件


mysqldump -u root -p --opt [database name] > [database name].sql

迁移至另一台主机


mysqldump -u root -pPassword --all-databases | ssh user@new_host.host.com 'cat - | mysql -u root -pPassword'

加密sql文件


zip --encrypt dump.zip db.sql
unzip -P your-password dump.zip

导入SQL文件


mysql -u root -p newdatabase < /path/to/newdatabase.sql

3.5. 其他

错误修复


mysqlcheck --all-databases --check-upgrade --auto-repair
mysql_upgrade -uroot -p --force

官方文档：

Installing MariaDB Binary Tarballs

Server System Variables

Configuring MariaDB with Option Files