Mariadb 二进制版安装与配置

wget https://mirrors.xtom.com.hk/mariadb//mariadb-11.4.5/bintar-linux-systemd-x86_64/mariadb-11.4.5-linux-systemd-x86_64.tar.gz
sudo tar zxf mariadb-11.4.5-linux-systemd-x86_64.tar.gz -C /usr/local/src
sudo ln -s /usr/local/src/mariadb-11.4.5-linux-systemd-x86_64 /usr/local/mysql

# 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

# On Ubuntu/Debian:
sudo addgroup --system --gid 36 mysql
sudo adduser \
    --system \
    --home /var/lib/mysql \
    --shell /bin/false \
    --uid 36 \
    --gecos 'MariaDB Server' \
    --gid 36 \
    --disabled-password \
    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

# 日志目录
sudo mkdir /var/log/mariadb && \
sudo chown mysql:mysql /var/log/mariadb

# 安全目录
sudo mkdir /var/lib/mysql-files && \
sudo chown mysql:mysql /var/lib/mysql-files && \
sudo chmod 750 /var/lib/mysql-files

sudo mkdir -p /etc/mysql/conf.d
sudo vim /etc/mysql/my.cnf

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

#
# include all files from the config directory
#
!includedir /etc/mysql/conf.d/
EOF

sudo vim /etc/mysql/conf.d/client.cnf

sudo sh -c "cat > /etc/mysql/conf.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

sudo vim /etc/mysql/conf.d/server.cnf

sudo sh -c "cat > /etc/mysql/conf.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]

# Basic Settings
datadir=/var/lib/mysql
bind-address=0.0.0.0
pid-file=/var/lib/mysql/mariadb.pid

# Character Settings
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci

# InnoDB Settings
# innodb_buffer_pool_size=6G

# Connection settings
# max_connections=500

#
# This group is only read by MariaDB servers, not by MySQL.
#
[mariadb]
server_id=1

# Error Log
log_error=/var/log/mariadb/error.log
aria_log_dir_path=/var/log/mariadb

# General Query Log
general_log=0
general_log_file=/var/log/mariadb/general_query.log

# Binary Log

log-bin=/var/log/mariadb/master-bin
log-bin-index=/var/log/mariadb/master-bin.index
binlog-format=mixed
#binlog-do-db=mydb
#binlog-ignore-db=test
expire-logs-days=3

# Slow Query Log
slow_query_log=1
slow_query_log_file=/var/log/mariadb/slow_query.log
long_query_time=5.0
log_queries_not_using_indexes=0

# 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/audit.log
EOF

cd /usr/local/mysql
sudo ./scripts/mariadb-install-db --user=mysql

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

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

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

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

sudo visudo

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

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

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

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

# 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# 创建用户
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypasswd';

# 授权
GRANT ALL ON mydb.* TO 'myuser'@'localhost';

# 刷新生效
FLUSH PRIVILEGES;

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'

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

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

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