MariaDB management and basic configuration

Time:2022-8-7

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 pay attention to it.

1. Manage MariaDB services

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

You cannot use systemctrl in WSL, please refer to:https://segmentfault.com/a/11…

2. Modify MariaDB configuration

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

2.1. Set up remote access

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

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

Change 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, just pass this link directly:

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

Uncomment and change 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 the testdb database
SHOW TABLES View tables in database
CREATE TABLE [IF NOT EXISTS] students (…) Create data table students
DESC students View students table structure
INSERT INTO students (…) VALUES (…) Insert data into the data table students
SELECT * FROM students Query all content in the user table
SELECT student_id FROM students Query the content 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 database testdb

The 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 ## Backup all data in all tables
$ sudo mysqldump -u admin -p –all-database –no-data ## Backup all tables but not data
$ sudo mysqldump -u admin -p westos ## backup 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 ## Backup westos database
$ sudo mysql -u admin -p -e "create database westos;" ## create westos library
$ sudo mysql -u admin -p westos < /mnt/westos.sql ## 将数据导入westos数据库

4. Other related references