MariaDB

MariaDB†[edit]

• MySQL の権利を　Oracle が取得したことにより，MySQLからスピンアウトした MySQL互換DB
• Maria は開発チームのリーダの娘の名前らしい．

• See also MySQL

操作[edit]

Backup & Restore (高速)[edit]

• mysqldump より高速？
• ディレクトリ構造をそのままバックアップ

Backup[edit]

mariadb-backup --backup --databases=jogrid_db --target-dir=/opt/Backup --user=root --password=****

Restore[edit]

systemctl stop mariadb
mariadb-backup --prepare --databases=jogrid_db --target-dir=/opt/Backup 
mariadb-backup --copy-back --databases=jogrid_db --target-dir=/opt/Backup 

Setup[edit]

設定ファイル[edit]

# vi /etc/my.cnf
# chmod a+r /etc/my.cnf

/etc/my.cnf[edit]

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

DBの初期化[edit]

# mysql_install_db  --user=mysql  --ldata=/var/lib/mysql  --basedir=/usr

パスワード設定[edit]

# systemctl start mariadb.service
# mariadb-admin -u root password

サーバへのアクセス制限がある場合[edit]

• -h オプションを指定する．

  # mariadb-admin -u root　-h localhost  password パスワード

匿名，パスワード無しユーザの削除[edit]

MariaDB [(none)]> use mysql;
MariaDB [mysql]> select Host,User,Password from user;
MariaDB [mysql]> set password for 'mariadb.sys'@localhost=password('set_password');    (set_password：パスワードは変える)
MariaDB [mysql]> select Host,User,Password from user;
MariaDB [mysql]> delete from user where password='';    （'' はシングルクォーテーション2個） 
MariaDB [mysql]> select Host,User,Password from user;
MariaDB [mysql]> flush privileges; 
MariaDB [mysql]> exit

データベース作成例[edit]

$ mariadb -u root -p
Enter password: パスワード
MariaDB [(none)]> create database db_name default character set utf8;       (または utf8mb4)
MariaDB [(none)]> grant all on db_name.* to db_name_user identified by 'db_name_pass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

• サーバへのアクセス制限がある場合は，ユーザ名を db_name_user@localhost などとする．

Install[edit]

Compile[edit]

• Compile and Setup

Rocky Linux 9 + 11.2[edit]

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=11.2
dnf -q module disable mariadb
dnf module reset mariadb
dnf rm mariadb
dnf install MariaDB-server MariaDB-client MariaDB-backup vim
mariadb -V
dnf install php-mysqlnd

Rocky Linux 8 + 10.5.22†[edit]

# vi /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/rhel8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
module_hotfixes=1

# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

# systemctl stop mariadb
# yum remove mariadb-server
# yum remove mariadb-10.3.35

# dnf install MariaDB-server MariaDB-client
# rpm -qa|grep MariaDB
# systemctl start mariadb

CentOS7 + MariaDB-10.6.7†[edit]

from Source Code†[edit]

• 先に openssl-1.1.1g をインストール

• コンパイル

  mkdir build
  cd build/
  cmake -DWITH_READLINE=true -DWITHOUT_TOKUDB_STORAGE_ENGINE=true -DHAVE_SYSTEMD=true -DOPENSSL_ROOT_DIR=/usr/local/ssl ..
  make
  systemctl stop mariadb
  make install
  systemctl start mariadb
  ps ax|grep maria
  ps ax|grep mysql

Trouble Shooting[edit]

Rocky Linux 9[edit]

mariadb-main[edit]

MariaDB Server                                                                                         14  B/s |  15  B     00:01
Errors during downloading metadata for repository 'mariadb-main':
 - Status code: 521 for https://downloads.mariadb.com/MaxScale/2.4/centos/9/x86_64/repodata/repomd.xml (IP: 104.17.191.14)
Error: Failed to download metadata for repo 'mariadb-main': Cannot download repomd.xml: Cannot download repodata/repomd.xml: All mirrors were tried

• https://mariadb.org/download/?t=repo-config&d=Red+Hat+Enterprise+Linux+9 を見て，/etc/yum.repos.d/mariadb.repo を修正

# MariaDB 11.8 RedHatEnterpriseLinux repository list - created 2026-03-22 10:55 UTC
# https://mariadb.org/download/
[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/11.8/rhel/$releasever/$basearch
baseurl = https://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/yum/11.8/rhel/$releasever/$basearch
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

パスワード無し[edit]

• root の場合, mariadb -u root でログインできるのだが....

Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.[edit]

• ログを確認する．

max_open_files to more than 32768 (request: 66655)[edit]

# vi /usr/lib/systemd/system/mariadb.service
 LimitNOFILE=100000 
 LimitMEMLOCK=100000
# systemctl daemon-reload
# systemctl start mariadb

Can't create test file '/var/lib/mysql/blackjack.lower-test'[edit]

• データベースディレクトリがリンボリックリンクだと v11 で発生？
• SELinux 関係か？

Rocky Linux 7/8[edit]

Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255)[edit]

• データベースをアップグレードする．

  # cd /var/log
  # ln -s mariadb mysql
  # mariadb-upgrade -u root -p
  # systemctl restart mariadb

最新の30件

[edit]