最後更新: 2022-05-06

介紹

mariadb - MariaDB is a community developed branch of MySQL.

https://mariadb.org/

 

---

Centos7 Package

 

mariadb

/usr/bin/aria_chk
/usr/bin/aria_dump_log
/usr/bin/aria_ftdump
/usr/bin/aria_pack
/usr/bin/aria_read_log
/usr/bin/msql2mysql
/usr/bin/my_print_defaults
/usr/bin/mysql
/usr/bin/mysql_find_rows
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqlimport
/usr/bin/mysqlshow
/usr/bin/mysqlslap

mariadb-server

/usr/bin/innochecksum
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql_convert_table_format
/usr/bin/mysql_fix_extensions
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_setpermission
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysql_zap
/usr/bin/mysqlbug
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqldumpslow
/usr/bin/mysqlhotcopy
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip

 

---

BASIC Install & Setup

 

yum -y install mariadb-server mariadb

systemctl start mariadb.service

systemctl enable mariadb.service

mysql_secure_installation

 

---

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]

 

---

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.