MariaDB management and basic configuration

The management environment described in this article is Ubuntu20.04 on WSL (Windows Subsystem for Linux). There may be differences in the native Ubuntu system, please note that readers.

1. Manage MariaDB service

Command (native)	Command (WSL)	illustrate
sudo systemctl restart mariadb	sudo service mysql restart	Restart MariaDB service
sudo systemctl start mariadb	sudo service mysql start	Start MariaDB service
sudo systemctl stop mariadb	sudo service mysql stop	Stop MariaDB service
sudo systemctl status mariadb	sudo service mysql status	View MariaDB service status

cannot be used in WSL, please refer to the reason: 161471131137ac https://segmentfault.com/a/1190000040670856

2. Modify MariaDB configuration

The configuration file of MariaDB is: /etc/mysql/mariadb.conf.d/50-server.cnf

2.1, set up remote access

If it is not set, it can only be accessed within the machine

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

Modify bind-address=XXX to the following, the table allows all addresses

bind-address            = 0.0.0.0

After the modification is completed, the mysql service needs to be restarted

sudo service mysql restart

2.2, modify the port

If you need to modify the port number, you can modify it as follows. If you don’t need to modify it, pass this link directly:

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

Uncomment and modify 3306 to the port you want:

#port                   = 3306

After the modification is completed, the mysql service needs to be restarted

sudo service mysql restart

3. Basic query and use

Order	effect
SHOW DATABASES	View all databases
CREATE DATABASE [IF NOT EXISTS] testdb	Create database testdb
USE testdb	Switch to testdb database
SHOW TABLES	View the tables in the database
CREATE TABLE [IF NOT EXISTS] students (...)	Create data table students
DESC students	View the students table structure
INSERT INTO students (...) VALUES (...)	Insert data into the data table students
SELECT * FROM students	Query all contents in the user table
SELECT student_id FROM students	Query the contents of the student_id field in the students table
DELETE FROM students WHERE student_id='1'	Delete the record with ID 1 in the students table
DROP TABLE [IF EXISTS] students	Delete table students
DROP DATABASE [IF EXISTS] testdb	Delete the database testdb

usage example is as follows

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> SHOW TABLES;
Empty set (0.000 sec)

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students(
  studen    ->     student_id INT NOT NULL AUTO_INCREMENT,
    ->     student_name VARCHAR(100) NOT NULL,
    ->     student_address VARCHAR(40) NOT NULL,
    ->     admission_date DATE,
    ->     PRIMARY KEY ( student_id )
    -> );
Query OK, 0 rows affected (0.021 sec)

MariaDB [testdb]> DESC students;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| student_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| student_name    | varchar(100) | NO   |     | NULL    |                |
| student_address | varchar(40)  | NO   |     | NULL    |                |
| admission_date  | date         | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB [testdb]> INSERT INTO students (student_name, student_address, admission_date) VALUES ('zhangsan', 'shanghai', '1990-10-01');
e, student_address, admission_date) VALUES ('lisi', 'beijing', '1996-08-21');

Query OK, 1 row affected (0.012 sec)

MariaDB [testdb]> INSERT INTO students (student_name, student_address, admission_date) VALUES ('lisi', 'beijing', '1996-08-21');
Query OK, 1 row affected (0.004 sec)

MariaDB [testdb]>
MariaDB [testdb]> SELECT * FROM students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | zhangsan     | shanghai        | 1990-10-01     |
|          2 | lisi         | beijing         | 1996-08-21     |
+------------+--------------+-----------------+----------------+
2 rows in set (0.000 sec)

MariaDB [testdb]> SELECT student_id FROM students;
+------------+
| student_id |
+------------+
|          1 |
|          2 |
+------------+
2 rows in set (0.000 sec)

MariaDB [testdb]> DELETE FROM students WHERE student_id = 1;
Query OK, 1 row affected (0.005 sec)

MariaDB [testdb]> SELECT * FROM students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          2 | lisi         | beijing         | 1996-08-21     |
+------------+--------------+-----------------+----------------+
1 row in set (0.000 sec)

MariaDB [testdb]> DROP TABLE IF EXISTS students;
Query OK, 0 rows affected (0.024 sec)

MariaDB [testdb]> DROP DATABASE IF EXISTS testdb;
Query OK, 0 rows affected (0.000 sec)

3. Data export and recovery

$ sudo mysqldump -u admin -p --all-database ## Back up all data in all tables
$ sudo mysqldump -u admin -p --all-database --no-data ## Back up all tables, but no data
$ sudo mysqldump -u admin -p westos ## Back up the westos database
$ sudo mysqldump -u admin -p westos user ##Back up the user table in the westos database
$ sudo mysqldump -u admin -p westos> mysql.sql ## Back up the westos database
$ sudo mysql -u admin -p -e "create database westos;" ## Create westos library
$ sudo mysql -u admin -p westos </mnt/westos.sql ## Import data into westos database

4. Other related references

• Database configuration notes (essence)
  https://www.cnblogs.com/LyShark/category/1231445.html
• Mariadb database configuration and management
  https://blog.csdn.net/a18829898663/article/details/71910231