--- 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 ``` terminal sudo apt update && sudo apt upgrade -y sudo apt install mariadb-client mariadb-server ``` Check status ``` terminal sudo systemctl status mysql ``` ![](https://i.imgur.com/1q7mzGm.png) ``` terminal sudo systemctl stop mysql ``` ![](https://i.imgur.com/x5vTIdv.png) ``` terminal sudo systemctl disable mysql ``` ![](https://i.imgur.com/8Nxujrb.png) --- ## Configuring MariaDB for Running Multiple Database Instances ``` terminal 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 ``` configure [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/log/mysql/mysqld_multi.log user=multi_admin password=****** ``` ``` terminal sudo vim 50-server1.cnf ``` 50-server1.cnf content ``` configure [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 ``` ``` terminal sudo vim 50-server2.cnf ``` 50-server2.cnf content ``` configure [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 ``` ``` terminal sudo vim 50-server3.cnf ``` 50-server3.cnf content ``` configure [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 ``` terminal 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} ``` ``` terminal ls -lhd /var/lib/mysql* ``` ![](https://i.imgur.com/91QnVrH.png) ``` terminal 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 ``` terminal sudo mysqld_multi report ``` ![](https://i.imgur.com/OQ9a2Su.png) ``` terminal sudo mysqld_multi start sudo mysqld_multi report ``` ![](https://i.imgur.com/iyCWxPV.png) 這邊重複做三次 ``` terminal sudo mysql -S /run/mysqld/mysqld1.sock -u root ``` MySQL script ``` terminal GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'secret'; GRANT ALL ON *.* to 'root'@'%' IDENTIFIED BY '!QAZ2wsx' WITH GRANT OPTION; FLUSH PRIVILEGES; EXIT; ``` ``` terminal sudo mysqld_multi stop ``` --- ## 開機自動啟動 ``` terminal sudo crontab -e ``` ![](https://i.imgur.com/xy3eqP5.png) 請按 1 後， Enter 新增 ``` text @reboot /usr/bin/mysqld_multi start ``` ctrl + x後，在按Y ![](https://i.imgur.com/vPYyeGz.png) --- ## 參考鏈結 https://linuxhint.com/running-multiple-mariadb-instances-on-ubuntu-20-04-lts/