This document is free text: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version.
This document is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see
Mariadb Installation, configuration, simple user and DB management, and Primary - Replica (Master - Slave) Replication on Debian 12 (also 11) and Ubuntu 24.04 (also 22.04) Server.
Based on the book Mastering Ubuntu Server 2nd Ed. by Jay LaCroix. This book has introduced me to Ubuntu Server and I have to thank him for this excellent book.
Almost (if not all) everything on this tutorial can be applied to Mysql.
Mariadb is a fork or Mysql, and I prefer using it, besides a lot of other reasons, I just don't like Or*cle
Do not ever install Mariadb and Mysql on the same server
sudo apt update
sudo apt install --yes mariadb-server
systemctl status mariadb
The following command makes some fine tunes regarding Mariadb security.
sudo mysql_secure_installation
You will be asked some questions.
Enter current password for root (enter for none):
There is no password yet, so press enter.
The next 2 questions
Switch to unix_socket authentication [Y/n]
and
Change the root password? [Y/n]
are about securing root account. In Ubuntu and Debian root account is already protected, so you can answer n.
For the next questions you can select default answers.
EXIT; to exit
sudo mariadb
All commands must be run on Mariadb shell
sudo mariadb
For administrating the db, it is best to create an admin user on mariadb shell. admin can only login from localhost
Remember to change password to a good one.
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Can do anything but grant
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
GRANT SELECT ON *.* TO 'readonlyuser'@'localhost' IDENTIFIED BY 'password';
SELECT HOST, USER, PASSWORD FROM mysql.user;
GRANT SELECT ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password';
SHOW GRANTS FOR 'appuser'@'localhost';
DELETE FROM mysql.user WHERE user='myuser' AND host='localhost';
All commands must be run on Mariadb shell
CREATE DATABASE mysampledb;
SHOW DATABASES;
USE mysampledb;
CREATE TABLE Employees (Name char(15), Age int(3), Occupation char(15));
SHOW COLUMNS IN Employees;
INSERT INTO Employees VALUES ('Joe Smith', '26', 'Ninja');
SELECT * FROM Employees;
DELETE FROM Employees WHERE Name = 'Joe Smith';
DROP TABLE Employees;
DROP DATABASE mysampledb;
sudo mysqldump --databases mysampledb > mysampledb.sql
sudo mariadb < mysampledb.sql
Primary Server : 192.168.1.216
Replica Server : 192.168.1.221
Replication User : 'replicate'@'192.168.1.221'
Rep. User Password : Pass1234
Database instance to replicate: mysampledb
As in the following link, Mariadb Knowledge Base says that; primary and replica server do not need to have the same version of Mariadb, although it is preferred to have the primary an older version.
https://mariadb.com/kb/en/database-version-on-master-slave-replication/
Mariadb versions on Debian and Ubuntu Servers:
Debian 11 : 10.5.19
Ubuntu 22.04 : 10.6.12
Debian 12 : 10.11.3
Ubuntu 24.04 : 10.11.7
I made the tests with the following pairs.
Ubuntu 22.04 Primary - Debian 12 Replica
Install mariadb on both servers,
Please Remember:
Replication doesn't mean that you don't have to backup. If you delete something accidentally, it is automatically deleted at slave too. So if you are running a production server, backup (at least) daily and weekly.
sudo nano /etc/mysql/conf.d/mysql.cnf
Change as below:
[mysql]
[mysqld]
log-bin
binlog-do-db=mysampledb
server-id=1
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Change following line (Around lines 27-30)
bind-address = 127.0.0.1
to
bind-address = 0.0.0.0
Run following command on primary mariadb shell
GRANT REPLICATION SLAVE ON *.* to 'replicate'@'192.168.1.221' identified by 'Pass1234';
EXIT;
sudo systemctl restart mariadb
Run on Primary server Mariadb shell
FLUSH TABLES WITH READ LOCK;
EXIT;
sudo mysqldump --databases mysampledb > mysampledb.sql
At this step, you need to copy the backup file (mysampledb.sql) to the replica server.
sudo mariadb < mysampledb.sql
sudo nano /etc/mysql/conf.d/mysql.cnf
Change as below:
[mysql]
[mysqld]
server-id=2
For more than 1 replicas, give different server-id numbers
sudo systemctl restart mariadb
CHANGE MASTER TO MASTER_HOST="192.168.1.216", MASTER_USER='replicate', MASTER_PASSWORD='Pass1234';
Check to see if replica is running (on mariadb shell)
SHOW SLAVE STATUS;
If Slave_IO_State is empty, run (on mariadb shell)
START SLAVE;
Run on master mariadb shell
UNLOCK TABLES;
You can try manipulating the DB on the primary, changes will be applied on the replica slave in a few seconds.