Mariadb的简单使用

简介

Mariadb数据库管理系统是mysql的一个分支，原因：甲骨文公司收购mysql后，有将mysq闭源的潜在风险，因此社区采用分支的方式来避开这个风险。

源配置

[root@localhost ~]# cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# vim mariadb.repo
[mariadb]
name=MariaDB
baseurl=https://mirrors.ustc.edu.cn/mariadb/yum/10.4/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@localhost yum.repos.d]# yum clean all
已加载插件：fastestmirror
正在清理软件源： base extras mariadb updates
Cleaning up list of fastest mirrors
[root@localhost yum.repos.d]# yum makecache

安装mariadb

[root@localhost yum.repos.d]# yum install -y mariadb-server mariadb-client

开启mariadb并设置开机自启

[root@localhost yum.repos.d]# systemctl start mariadb
[root@localhost yum.repos.d]# systemctl enable mariadb
查看端口开放
[root@localhost ~]# netstat -ntlp |grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      41821/mysqld   

初始化数据库

[root@localhost yum.repos.d]# mysql_secure_installation
直接回车
y 设置root密码
y 删除匿名用户
n 禁用root远程登录
y 删除test数据库
y 重新加载权限表
[root@localhost yum.repos.d]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.4.21-MariaDB MariaDB Server

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)]> 

修改数据库的密码

[root@localhost yum.repos.d]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.4.21-MariaDB MariaDB Server

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)]> set password = PASSWORD('newpassword');

简单了解数据库

创建数据库mytestdb
MariaDB [(none)]> create database mytestdb;
Query OK, 1 row affected (0.000 sec)

进入数据库
MariaDB [(none)]> use mytestdb;
Database changed

创建一个带字段的表
MariaDB [mytestdb]> create table mytable(id int,name char(32));
Query OK, 0 rows affected (0.007 sec)

查看表结构
MariaDB [mytestdb]> desc mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(32) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.001 sec)

表内插入值
MariaDB [mytestdb]> insert into mytable(id,name) values(1,"zero"),(2,"one");
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

筛选查看表数据
MariaDB [mytestdb]> select id,name from mytable;
+------+------+
| id   | name |
+------+------+
|    1 | zero |
|    2 | one  |
+------+------+
2 rows in set (0.001 sec)

根据id删除表内的值
MariaDB [mytestdb]> delete from mytable where id=2;
Query OK, 1 row affected (0.002 sec)

MariaDB [mytestdb]> select id,name from mytable;
+------+------+
| id   | name |
+------+------+
|    1 | zero |
+------+------+
1 row in set (0.001 sec)

根据id更新表内的值
MariaDB [mytestdb]> update mytable set name="ten" where id=1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

数据库的备份与恢复

新建数据库测试
MariaDB [mytestdb]> create database testdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [mytestdb]> create database webdb;
Query OK, 1 row affected (0.000 sec)

查看所有数据库
MariaDB [mytestdb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytestdb           |
| performance_schema |
| testdb             |
| webdb              |
+--------------------+
7 rows in set (0.000 sec)

MariaDB [mytestdb]> exit
Bye

备份

单个数据库备份
[root@localhost ~]# mysqldump -u root -p testdb > /tmp/db.dump
Enter password: 

全部数据库备份
[root@localhost ~]# mysqldump -u root -p --all-databases > /tmp/db.dump

进入数据库将数据库删除

MariaDB [(none)]> drop database mytestdb;
Query OK, 1 row affected (0.008 sec)

MariaDB [(none)]> drop database webdb;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> drop database testdb;
Query OK, 1 row affected (0.004 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

恢复

[root@localhost ~]# mysql -uroot -p < /tmp/db.dump
Enter password: 

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.21-MariaDB MariaDB Server

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              |
| mytestdb           |
| mytestdb1          |
| performance_schema |
| testdb             |
| webdb              |
+--------------------+
7 rows in set (0.001 sec)

作者：月光染衣袂
转发请加上该地址 ： https://blog.youkuaiyun.com/weixin_46860149/article/details/119841594
如果笔记对您有用，请帮忙点个赞！