Mariadb 二进制版安装与配置

slug
mariadb-setup-from-binary
tags
mariadb
binary
date
Aug 8, 2024
summary
MariaDB是MySQL关系数据库管理系统的一个分叉,由社区开发,有商业支持。
status
Published
type
Post
MariaDB是MySQL关系数据库管理系统的一个分叉,由社区开发,有商业支持,旨在继续保持在GNU GPL下开源。MariaDB的开发是由MySQL的一些原始开发者领导的,他们担心甲骨文公司收购MySQL后变成更加封闭。
从2012年11月12日起发布的10.0.0版开始,不再依照MySQL的版号。10.0.x版以5.5版为基础,加上移植自MySQL 5.6版的功能和自行开发的新功能。

一、MariaDB 准备

1.1. 下载

根据系统选择相应版本版本,然后下载解压。
notion imagenotion image
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. 配置文件

创建工作路目录:
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
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 帐号认证机制已经变更,详见官网说明:
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 中文乱码

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
官方文档:
If you have any questions, please contact me.