MariadbOnUbuntu: MariaDB Tutorial on Ubuntu 20.04
Copyright (C) 2020 Exforge exforge@x386.xyz
# 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 <https://www.gnu.org/licenses/>.
# Mariadb 2 Installation, configuration, simple user and DB management
# on Ubuntu 20.04 Server
# Based on the book Mastering Ubuntu Server 2nd Ed. by Jay LaCroix
# This book hes introduced me to Ubuntu Server and I have to thank him for this
# excellent book:
https://www.packtpub.com/networking-and-servers/mastering-ubuntu-server-second-edition
# 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 Oracle
# !!! Do not ever install Mariadb and Mysql on the same server !!!
# 1.1. Install MariaDB
sudo apt install mariadb-server
# 1.2. Check if installation is OK
systemctl status mariadb
#
# 1.3. Secure MariaDB,
# select Default for all questions, give a good password
sudo mysql_secure_installation
#
# 1.4. Enter Mariadb shell
# EXIT; to exit
sudo mariadb
# 2.1. Config files are in /etc/mysql
#_______________________________
debian.cnf
debian-start
mariadb.cnf
my.cnf
my.cnf.fallback
# and these directories
conf.d
mariadb.conf.d
#____________________________
#
# 2.2. Sample /etc/mysql/debian.cnf file
#____________________________
[client]
host = localhost
user= root
password =
socket= /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = root
password =
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
#____________________________
# 3.1. Mariadb shell can be run in two ways:
sudo mariadb
# or
mariadb -u root -p
# 3.2. To use the latter, we first enter with sudo mariadb and enter following
# commands on Mariadb shell
UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE USER='root';
FLUSH PRIVILEGES;
EXIT;
# Now we can use it
mariadb -u root -p
# !!! All commands must be run on Mariadb shell !!!
# 4.1. For administrating the db, it is best to create an admin user
# on mariadb shell. admin can only login from localhost
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
# 4.2. To let admin login from anywhere use:
CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
#
# 4.3. Give admin full access DB server. Can do anything but grant
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
#
# 4.4. Following command makes a full admin, with grant permissions
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
#
# 4.5. Create a readonly user for DB
GRANT SELECT ON *.* TO 'readonlyuser'@'localhost' IDENTIFIED BY 'password';
#
# 4.6. List database users
SELECT HOST, USER, PASSWORD FROM mysql.user;
#
# 4.7. Grant a user readonly access for one database
GRANT SELECT ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password';
#
# 4.8. Grant a user full access for a database
GRANT ALL ON mysampledb.* TO 'appuser'@'localhost' IDENTIFIED BY 'password';
#
# 4.9. Show the grants for a particular user:
SHOW GRANTS FOR 'appuser'@'localhost';
#
# 4.10. Remove a user
DELETE FROM mysql.user WHERE user='myuser' AND host='localhost';
# !!! All commands must be run on Mariadb shell !!!
# 5.1. Create a database
CREATE DATABASE mysampledb;
#
# 5.2. List databases
SHOW DATABASES;
#
# 5.3. Enter the workspace of a database
USE mysampledb;
#
# 5.4. Create a table
CREATE TABLE Employees (Name char(15), Age int(3), Occupation char(15));
#
# 5.5. List columns of a table
SHOW COLUMNS IN Employees;
#
# 5.6. Insert a row into a table
INSERT INTO Employees VALUES ('Joe Smith', '26', 'Ninja');
#
# 5.7. List contents of a table
SELECT * FROM Employees;
#
# 5.8. Remove an entry from a database
DELETE FROM Employees WHERE Name = 'Joe Smith';
#
# 5.9. Drop a table
DROP TABLE Employees;
#
# 5.10. Drop an entire database:
DROP DATABASE mysampledb;
# 6.1. Backup a database
mysqldump -u root -p --databases mysampledb > mysampledb.sql
#
# 6.2. Restore it
mariadb -u root -p < mysampledb.sql
7. Master-Slave Replication Configuration
# 7.1. Specs and Preliminary Tasks
# Master Server: 192.168.0.161
# Slave Server: 192.168.0.162
# Replication User: 'replicate'@'192.168.0.162'
# Rep. User Password: Pass1234
# Database instance to replicate: mysampledb
#
# Install mariadb on both servers,
# Apply steps 1 and 3 on both servers
# Apply step 5.1 to 5.7 on master server
#
# !!! 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
#
# 7.2. Master Server Configuration
# 7.2.1. Configure master for bin log
sudo nano /etc/mysql/conf.d/mysql.cnf
# Change as below:
#__________________
[mysql]
[mysqld]
log-bin
binlog-do-db=mysampledb
server-id=1
#__________________
# 7.2.2. Change bind address to outside
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
# Change following line (Around line 28)
#_______________________
bind-address = 127.0.0.1
#_______________________
# to
#_______________________
bind-address = 0.0.0.0
#_______________________
# 7.2.3. Create replication user
# Run following command on master mariadb shell
GRANT REPLICATION SLAVE ON *.* to 'replicate'@'192.168.0.162' identified by 'Pass1234';
#
# 7.2.4. Restart master mariadb server
sudo systemctl restart mariadb
#
# 7.2.5. Lock Master server for initial full replication
# Run on Master server Mariadb shell
FLUSH TABLES WITH READ LOCK;
#
# 7.2.6. Backup the database at master server
mysqldump -u root -p --databases mysampledb > mysampledb.sql
# Move backup file to the slave server for restoring later
#
# 7.3. Slave Server Config
# 7.3.1. Restore database backed up at master
mariadb -u root -p < mysampledb.sql
#
# 7.3.2. Update slave server conf file
sudo nano /etc/mysql/conf.d/mysql.cnf
#__________________
[mysql]
[mysqld]
server-id=2
#__________________
# For more than 1 slaves, give different server-id numbers
#
# 7.3.3. Restart slave mariadb
sudo systemctl restart mariadb
#
# 7.3.4. Run the commands on slave mariadb shell
CHANGE MASTER TO MASTER_HOST="192.168.0.161", MASTER_USER='replicate', MASTER_PASSWORD='Pass1234';
# Check to see if slave running (mariadb shell)
SHOW SLAVE STATUS;
# If Slave_IO_State is empty run (mariadb shell)
START SLAVE;
#
# 7.4. Unlock Master Mariadb
# Run on master mariadb shell
UNLOCK TABLES;
# 7.5. All set. You can try manipulating the DB on master, changes will be applied on slave
# in a few seconds.