---
title: 'Ubuntu 22.04 Multi Mariadb install'
tags: ubuntu,Mariadb
description: Ubuntu 22.04 Multi Mariadb install
---

# Ubuntu 22.04 Multi Mariadb install

---

[TOC]

---

## Install Mariadb

sudo apt update && sudo apt upgrade -y
sudo apt install mariadb-client mariadb-server

Check status

sudo systemctl status mysql


sudo systemctl stop mysql


sudo systemctl disable mysql


---

## Configuring MariaDB for Running Multiple Database Instances

cd /etc/mysql/mariadb.conf.d/
sudo mv -v 50-server.cnf 50-server.cnf.backup
sudo vim 50-server-multi.cnf

50-server-multi.cnf content

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log    = /var/log/mysql/mysqld_multi.log
user=multi_admin
password=******

sudo vim 50-server1.cnf

50-server1.cnf content

[mysqld1]
user                    = mysql
pid-file                = /run/mysqld/mysqld1.pid
socket                  = /run/mysqld/mysqld1.sock
basedir                 = /usr
datadir                 = /var/lib/mysql1
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 0.0.0.0
query_cache_size        = 100M
log_error               = /var/log/mysql/mysqld1_error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci
port                    = 20001
max_connections         = 2000

sudo vim 50-server2.cnf

50-server2.cnf content

[mysqld2]
user                    = mysql
pid-file                = /run/mysqld/mysqld2.pid
socket                  = /run/mysqld/mysqld2.sock
basedir                 = /usr
datadir                 = /var/lib/mysql2
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 0.0.0.0
query_cache_size        = 100M
log_error               = /var/log/mysql/mysqld2_error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci
port                    = 20002
max_connections         = 2000

sudo vim 50-server3.cnf

50-server3.cnf content

[mysqld3]
user                    = mysql
pid-file                = /run/mysqld/mysqld3.pid
socket                  = /run/mysqld/mysqld3.sock
basedir                 = /usr
datadir                 = /var/lib/mysql3
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
bind-address            = 0.0.0.0
query_cache_size        = 100M
log_error               = /var/log/mysql/mysqld3_error.log
expire_logs_days        = 10
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci
port                    = 20003
max_connections         = 2000

---

## Configuration

sudo touch /var/log/mysql/mysqld_multi.log
sudo chown -v mysql:adm /var/log/mysql/mysqld_multi.log
sudo chmod -v u=rw,g=rw,o= /var/log/mysql/mysqld_multi.log
sudo touch /var/log/mysql/mysqld{1..3}_error.log
sudo chown -v mysql:adm /var/log/mysql/mysqld{1..3}_error.log
sudo chmod -v u=rw,g=rw,o= /var/log/mysql/mysqld{1..3}_error.log
sudo mkdir -v /var/lib/mysql{1..3}
sudo chown -v mysql:mysql /var/lib/mysql{1..3}
sudo chmod -v u=rwx,g=rx,o=rx /var/lib/mysql{1..3}

ls -lhd /var/lib/mysql*


sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql1
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql3

---

## Running Multiple MariaDB

sudo mysqld_multi report


sudo mysqld_multi start
sudo mysqld_multi report


這邊重複做三次

sudo mysql -S /run/mysqld/mysqld1.sock -u root

MySQL script

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'secret';
GRANT ALL ON *.* to 'root'@'%' IDENTIFIED BY '!QAZ2wsx' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

sudo mysqld_multi stop

---

## 開機自動啟動

sudo crontab -e


請按 1 後， Enter

新增

@reboot    /usr/bin/mysqld_multi start

ctrl + x後，在按Y

---

## 參考鏈結

https://linuxhint.com/running-multiple-mariadb-instances-on-ubuntu-20-04-lts/