MariaDB管理和基础配置

本文所描述的管理所在的环境为Ubuntu20.04 on WSL(Windows Subsystem for Linux)，原生Ubuntu系统可能会存在差异，请读者注意。

1、管理MariaDB服务

命令（原生）	命令（WSL）	说明
sudo systemctl restart mariadb	sudo service mysql restart	重启MariaDB服务
sudo systemctl start mariadb	sudo service mysql start	启动MariaDB服务
sudo systemctl stop mariadb	sudo service mysql stop	停止MariaDB服务
sudo systemctl status mariadb	sudo service mysql status	查看MariaDB服务状态

在WSL不能使用systemctrl，原因请参考：https://segmentfault.com/a/11...

2、修改MariaDB配置

MariaDB的配置文件为：/etc/mysql/mariadb.conf.d/50-server.cnf

2.1、设置远程访问

如果不设置，只能本机内访问

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

把 bind-address=XXX 修改为 如下，表允许所有地址

bind-address            = 0.0.0.0

修改完成后需要重启mysql服务

sudo service mysql restart

2.2、修改端口

如果需要修改端口号的同学可以按如下修改，如不需要修改，直接PASS此环节：

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

取消注释，并把3306修改成你所想要的端口：

#port                   = 3306

修改完成后需要重启mysql服务

sudo service mysql restart

3、基本查询和使用

命令	作用
SHOW DATABASES	查看所有数据库
CREATE DATABASE [IF NOT EXISTS] testdb	创建数据库testdb
USE testdb	切换至testdb数据库
SHOW TABLES	查看数据库中的表
CREATE TABLE [IF NOT EXISTS] students (...)	创建数据表students
DESC students	查看students表结构
INSERT INTO students (...) VALUES (...)	向数据表students里面插入数据
SELECT * FROM students	查询user表中的所有内容
SELECT student_id FROM students	查询students表中的student_id字段内容
DELETE FROM students WHERE student_id='1'	删除students表中ID为1的记录
DROP TABLE [IF EXISTS] students	删除表students
DROP DATABASE [IF EXISTS] testdb	删除数据库testdb

使用示例如下

$ 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、数据的导出和恢复

$ sudo mysqldump -u admin -p --all-database ## 备份所有表中的所有数据
$ sudo mysqldump -u admin -p --all-database --no-data ## 备份所有表，但不备份数据
$ sudo mysqldump -u admin -p westos ## 备份westos数据库
$ sudo mysqldump -u admin -p westos user ##备份westos数据库中的user表
$ sudo mysqldump -u admin -p westos > mysql.sql ## 备份westos数据库
$ sudo mysql -u admin -p -e "create database westos;" ## 创建westos库
$ sudo mysql -u admin -p westos < /mnt/westos.sql ## 将数据导入westos数据库

4、其他相关参考

• 数据库配置笔记（精华）
  https://www.cnblogs.com/LySha...
• Mariadb数据库配置及管理
  https://blog.csdn.net/a188298...