最後更新: 2022-05-06
介紹
mariadb - MariaDB is a community developed branch of MySQL.
https://mariadb.org/
目錄
Version
MariaDB 10.11
- long-term maintenance release series (It will be maintained until February 2028)
Basic Install & Setup
yum -y install mariadb-server mariadb
systemctl start mariadb.service
systemctl enable mariadb.service
mysql_secure_installation
Manual Install
# 安裝 10.11
rpm --import https://supplychain.mariadb.com/MariaDB-Server-GPG-KEY
cat > /etc/yum.repos.d/MariaDB.repo <<'EOF'
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.11/rhel/$releasever/$basearch
baseurl = https://mirrors.xtom.com.hk/mariadb/yum/10.11/rhel/$releasever/$basearch
module_hotfixes = 1
gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck = 1
EOF
yum install MariaDB-server MariaDB-client
Server Usage
/etc/my.cnf.d/server.cnf
# this is read by the "standalone daemon" and "embedded" servers [server] # this is only for the mysqld standalone daemon [mysqld] # These two groups are only read by MariaDB servers, not by MySQL. [mariadb]
常用 Settings
[mysqld] disable-log-bin=1 bind-address=0.0.0.0
User Account
# LXC USER
CREATE USER 'root'@'192.168.200.%' IDENTIFIED BY '????';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.200.%';
GRANT GRANT OPTION ON *.* TO 'root'@'192.168.200.%';
FLUSH PRIVILEGES;
# Local User
ALTER USER 'root'@'localhost' IDENTIFIED BY '????';
FLUSH PRIVILEGES;
EXIT
Character Set
In MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci.
* down to the column level
SHOW COLLATION LIKE 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | ...
Setting
[mysqld] ... collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8 ...
Doc
https://mariadb.com/kb/en/mariadb/setting-character-sets-and-collations/
mariadb memory usage
performance_schema - a feature for monitoring server performance
SHOW ENGINES\G;
Or
SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
performance_schema = 0
SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+
minimal memory
/etc/my.cnf:
# the memory area where InnoDB caches table and index data. (Default: 128M)
# SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
innodb_buffer_pool_size=5M
# The MySQL InnoDB log buffer allows transactions to run
# without having to write the log to disk before the transactions commit.
# if you have big transactions, making the log buffer larger saves disk I/O.
# SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';
# Default: 8M
innodb_log_buffer_size=256K
#
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000
# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K
#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K
Cache
Cache - stores results of SELECT queries
* Prepared statements are always considered as different to non-prepared statements
* Queries are examined in a case-sensitive
SELECT * FROM t select * from t
* Comments are also considered and can make the queries differ
/* retry */SELECT * FROM t
* Each time changes are made to the data in a table, all affected results in the query cache are cleared.
# setting
SHOW VARIABLES LIKE '%cache%';
# built with the query cache
| have_query_cache | YES |
# cache is enabled
| query_cache_type | ON |
# Default: 0 bytes ( multiple of 1024 )
| query_cache_size | 0 |
# status
show status like "%cache%";
mariadb reset password
systemctl stop mariadb.service
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
use mysql;
# update user set password=PASSWORD("new-password") where User='root';
update user set password=PASSWORD("??????") where User='root';
flush privileges;
killall mysql
systemctl start mariadb.service
# testing
mysql -u root -p
Check Engine
SELECT engine,GROUP_CONCAT(DISTINCT TABLE_SCHEMA) Table_Schema_List,COUNT(*)
FROM information_schema.tables GROUP BY engine;
+--------------------+--------------------+----------+ | engine | Table_Schema_List | COUNT(*) | +--------------------+--------------------+----------+ | Aria | information_schema | 9 | | CSV | mysql | 2 | | MEMORY | information_schema | 53 | | MyISAM | mysql | 22 | | PERFORMANCE_SCHEMA | performance_schema | 17 | +--------------------+--------------------+----------+
aria_log
aria_read_log is a tool for displaying and applying log records from an Aria transaction log.