Installation and use of mariadb in Linux

1, the relationship between mariadb and MySQL:

? Both MySQL and MariaDB are open source database technologies. MySQL is the most widely adopted open source database. It is the primary relational database for many applications and commercial products. MariaDB is a modified version of MySQL. After MySQL was acquired by Oracle Corporation, the original MySQL development team made MariaDB due to licensing and distribution issues. Since the acquisition, MySQL and MariaDB have experienced different developments. However, MariaDB uses MySQL’s data and table definition files, and also uses the same client protocol, client API, ports, and sockets.

1.1. Similarities:

? Since MariaDB is a fork of MySQL, the two relational database management systems have many similarities. For example, MariaDB preserves MySQL’s structure, naming conventions, and data definition files. Additionally, it supports all MySQL connectors, connections and ports. So the MySQL client package works fine with MariaDB.

ACID Compliance	Atomicity, Consistency, Isolation, and Durability ACID (ACID) are the four core principles that ensure the reliability of database transactions. Both MySQL and MariaDB follow these principles. Both databases maintain data accuracy and integrity by following ACID.
SQL Compatibility	MySQL and MariaDB are both relational databases that organize data into tables. Both MariaDB and MySQL use SQL to manage and query data. You can use many of the same commands across these systems.
Open source software	As open source relational database management systems, MySQL and MariaDB It’s all the result of a joint effort by the developer community. The source code for both is available to the public. There is a fully open source version of the MySQL database, released under the General Public License (GPL). It also has a paid enterprise version that comes with additional features and support. MariaDB is fully open source on GitHub.
Security	MySQL and MariaDB provide similar basic security features. They provide encryption functions, access control mechanisms, user authentication and authorization functions, and SSL/TLS support. They also allow for fine-grained access control, allowing you to provide different levels of permissions to different users.

1.2. Main differences:

	MySQL	MariaDB
JSON	MySQL stores JSON reports as binary objects.	MariaDB stores JSON reports in strings. MariaDB’s JSON data type is an alias for LONGTEXT .
Oracle database compatibility	MySQL has high compatibility, But does not support PL/SQL.	MariaDB has high compatibility and supports PL/SQL since version 10.3.
Speed and performance	In terms of replication and query, MySQL is faster than MariaDB Slow down.	MariaDB is slightly faster than MySQL for replication and querying.
Features	MySQL supports super read-only functions, dynamic columns and data mask.	MariaDB supports invisible columns and temporary tablespaces.
Authentication	MySQL has validate_password components.	MariaDB has three password validator components.
Encryption	MySQL database uses InnoDB and AES to encrypt data at rest .	MariaDB supports temporary log encryption and binary log encryption.
Storage Engines	MySQL has fewer storage engines than MariaDB.	MariaDB has more storage engines than MySQL, and you can use multiple engines in a table.
License	MySQL is available in two editions: MySQL Enterprise Edition and GPL version.	MariaDB is fully GPL-ed.
Thread Pool	MySQL Enterprise Edition comes with a thread pool.	MariaDB can manage over 200,000 simultaneous connections, more than MySQL.

2. Install Mariadb

2.1, use the command to install mariadb

yum -y install mariadb-server mariadb

When complete appears, the download is complete

Start the mariadb service and make it start automatically at boot

systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb

2.2, mariadb initialization

Enter the command to initialize mariadb

mysql_secure_installation

Enter the current password, there is no password for the first installation, just press Enter

Whether to set a new password for root, select y

Enter new password and confirm

Whether to remove anonymous users, feel free, suggest y

Whether to refuse remote login, it is recommended to choose n

Whether to delete the test library, choose at will

Reload permissions table: y

3. Basic operation of mariadb database

mysql -uroot -p

Enter the password you just set to enter the database

3.1, users

Create a local user named admin with a password of 123

create user shihk@localhost identified by '123';

Grant all privileges to this user

GRANT ALL PRIVILEGES ON my_database.* TO 'shihk'@'localhost';

view all users

SELECT host,user,select_priv FROM mysql.user; ----select_priv is whether there is a queryable permission

[External link picture transfer failed, the source site may have an anti-leeching mechanism, it is recommended to save the picture and upload it directly (img-5NGIGp59-1692667071699)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230815142431.png)]

delete users

DROP USER 'username'@'host'; ----username is the user name,---host is local access, which can be changed to %, which means arbitrary access

3.2, database

Create test database

CREATE DATABASE test;

Enter the test database

use test;

delete database

DROP DATABASE IF EXISTS 'database'; ---database is the name of the database, 'IF EXISTS' refers to checking whether the database exists before deleting

3.3, data table

Create data tables s001 and s002, and set the id as primary field and auto-increment (AUTO_INCREMENT PRIMARY KEY).

CREATE TABLE s001 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

CREATE TABLE s002 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Query all tables that exist in the database

show tables;

delete data table

DROP TABLE 'data table';

3.3.1, Basic operation of data table

#insert data ---- INSERT INTO data table (field name, field name...) VALUES( ),( ),( )...;
INSERT INTO s002 (name, age) VALUES ('Dlice', 30), ('Eob', 22), ('Farol', 28);
#Query data ---- SELECT field FROM data table; query all fields with '*'
SELECT * FROM s002;

[External link picture transfer failed, the source site may have an anti-theft link mechanism, it is recommended to save the picture and upload it directly (img-splitelh-1692667071699)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230816142419.png)]

Query the content of multiple tables by associating

#SELECT column1, column2, ... FROM table1
 ->UNION
 ->SELECT column1, column2, ... FROM table2;
#column1, column2, ... indicate the columns to be selected, table1 and table2 are the tables to be queried, and all queries use '*'.

#Query the contents of the name and age columns in the table s001 and s002
SELECT name,age FROM s001 UNION SELECT name,age FROM s002;

[External link picture transfer failed, the source site may have an anti-leeching mechanism, it is recommended to save the picture and upload it directly (img-kiO4nG8I-1692667071699)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230816144956.png)]

#Delete data ---- DELETE FROM database WHERE field = ' ';
DELETE FROM s002 WHERE id = 3;

[External link picture transfer failed, the source site may have an anti-theft link mechanism, it is recommended to save the picture and upload it directly (img-0GW0MKMS-1692667071699)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230816142653.png)]

3.3.2, primary and foreign key settings

? Create table s003 and set the int field id as the auto-increment primary field; the int field s002_id is the foreign key, bind the id in s002, so that s002 deletes data, and s003 is also deleted synchronously

CREATE TABLE s003 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    s002_id INT,
    other_column VARCHAR(50),
    FOREIGN KEY (s002_id) REFERENCES s002(id) ON DELETE CASCADE
);

[External link picture transfer failed, the source site may have an anti-theft link mechanism, it is recommended to save the picture and upload it directly (img-xIqsZCcX-1692667071700)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230817085648.png)]

3.4, stored procedures and triggers

3.4.1, stored procedure settings

Create a stored procedure so that when data is inserted in s002, s003 is inserted at the same time

#The premise of the stored procedure is that there is a primary foreign key relationship between the two tables
DELIMITER //
CREATE PROCEDURE InsertIntoS002AndS003(IN name_val VARCHAR(50), IN age_val INT)
BEGIN
    DECLARE new_id INT;

    INSERT INTO s002 (name, age) VALUES (name_val, age_val);

    SET new_id = LAST_INSERT_ID();

    INSERT INTO s003 (s002_id, other_column) VALUES (new_id, CONCAT('Data for s002_id ', new_id));
END;
//

use stored procedure

CALL InsertIntoS002AndS003('name', age);

[External link picture transfer failed, the source site may have an anti-theft link mechanism, it is recommended to save the picture and upload it directly (img-QwlBM9FB-1692667071700)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230817113350.png)]

ps: Stored procedures are similar to functions in programming languages and need to be called actively

3.4.2, trigger settings

Create a trigger, when data is inserted in s004, s005 is inserted at the same time

DELIMITER //
CREATE TRIGGER InsertIntoS005OnS004Insert

#insert means to trigger after table s004 is inserted, modify it to updata, and delete it to delete
AFTER INSERT ON s004
#Set the trigger range (trigger every time a row is inserted)
FOR EACH ROW
BEGIN
    INSERT INTO s005 (s004_id, other_info)
    VALUES (NEW.id, CONCAT('Other info for Item ', NEW.id));
END;
//
DELIMITER;

insert data test

INSERT INTO s004 (name, description) VALUES ('Item 1', 'Description for Item 1');

[External link picture transfer failed, the source site may have an anti-leeching mechanism, it is recommended to save the picture and upload it directly (img-ZqX6lQd7-1692667071700)(https://yw-linux-1318457786.cos.ap-nanjing.myqcloud.com /QQ screenshot 20230817113559.png)]

3.4.3 Summary

The difference between triggers and stored procedures:

When to execute:

Trigger: It is automatically executed before and after specific database operations, without manual calls, and is usually used for data integrity, auditing, and other requirements.

Stored procedure: Manual call execution, executed at any time as needed, usually used to encapsulate business logic and complex data operations.
Purpose:

Triggers: Used to handle constraints at the data level, associated operations, audit records, etc.

Stored procedure: used to execute custom business logic, data manipulation, data conversion, etc.
Reusability:

Triggers: Usually for specific tables and operations, it is difficult to reuse in multiple places.

Stored procedure: can be called in multiple places, providing better reusability.

? Generally speaking, triggers are suitable for maintaining integrity and triggering operations at the data level. Stored procedures can refer to functions in programming languages, and are more suitable for executing business logic and encapsulating reusable operations.

4. Backup database

4.1, write the script back-up-db.sh for database backup

#!/bin/bash


#Backup address (you need to create the specified directory first)
backupdir=/root/sql/crontab/db/logs
#Backup file suffix time
time=_`date + %Y_%m_%d_%H_%M_%S`
dbfile=db${time}.sql
dbuser=root
dbpassword=123


#-h followed by the ip address of the mariadb server
mysql -e "show databases;" -h192.168.18.150 -u${dbuser} -p${dbpassword}| grep -Ev "Database|information_schema|performance_schema|mysql|test" | xargs mysqldump -h192.168.18.150 -u${dbuser} -p${dbpassword} --databases > ${backupdir}/${dbfile}

#The following is a separate manual backup
#mysqldump -h192.168.18.150 --port=3306 -uroot -p123 --lock-tables=0 --all-databases > /root/db-`date " + %Y-%m-%d-%H -%M-%S"`.sql

#Delete backup files older than 7 days
find $backupdir -name "db*.sql" -type f -mtime + 7 -exec rm -rf {} \; > /dev/null 2> & amp;1

echo ${dbfile} "ok"

Grant executable permissions to the script

chmod +x back-up-db.sh

4.2. Grant database user permissions

Enter the mariadb database (select the user to enter after -u)

mysql -uroot -p

Grant this user access on 192.168.18.150

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.18.150' IDENTIFIED BY '123' WITH GRANT OPTION;

Exit the database after refreshing permissions

FLUSH PRIVILEGES;
exit

Execute the back-up-db.sh script

./back-up-db.sh

Shows that the database backup has been created successfully

5. Set timed tasks

change vim default editor

export VISUAL=vim

Edit timed tasks

#crontab -e

# Execute at 15:45 every day
45 15 * * * /bin/bash /root/sql/crontab/db/back-up-db.sh

# Execute after startup
@reboot /bin/bash /root/sql/crontab/db/back-up-db.sh

The operation effect is as follows

6. Use backup to restore database content

6.1, delete the users table after entering the database

#mysql -uroot -p
#use Test;
#DROP TABLE users
exit database
#exit

6.2. Restoring the database with backup data

Root is the account, 123 is the account password, and Test is the database to be restored

/root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql is the backup file to be imported

mysql -uroot -p123 Test < /root/sql/crontab/db/logs/db_2023_07_19_15_45_01.sql

The effect is as follows

The image is being transferred…(img-Kqpszazg-1692667071700)]