# yum groupinstall mariadb mariadb-client -y
# systemctl start mariadb
# systemctl enable mariadb
MariaDB
MariaDB 安装
# ss -tulpn | grep mysql
# vim /etc/my.cnf
skip-networking=1
# systemctl restart mariadb
# ss -tulpn | grep mysql
Note
|
skip-networking 配置将导致 MariaDB 只能在本地连接,不监听端口接收远程连接。skip-networking=1 配置需添加在 [mysqld] 下面。
|
# mysql_secure_installation
# mysql -uroot -predhat
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
基本管理
管理相关的元数据
MariaDB 默认安装会有三个模型:information_schema,performance_schema,mysql。它们保存着和管理相关的元数据。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(mysql)]> DESCRIBE servers;
MariaDB [(mysql)]> SELECT * FROM servers;
SQL 语法
CREATE DATABASE DBNAME;
运算符 | 描述 |
---|---|
= |
等于 |
<> |
不等于,有些版本下的 SQL 也支持 |
> |
大于 |
< |
小于 |
>= |
大于等于 |
⇐ |
小于等于 |
BETWEEN |
在两者之间 |
LIKE |
使用范式查询 |
IN |
在指定的可能的值之中 |
创建用户
CREATE USER USERNAME@HOSTNAME IDENTIFIED BY 'PASSWORD'
Account | 描述 |
---|---|
test_user@localhost |
test_user 只可从本地 localhost 连接 |
test_user@192.168.1.5 |
test_user 只可从 192.168.1.5 连接 |
test_user@'192.168.1.%' |
test_user 只可从 192.168.1.0 网段服务器连接 |
test_user@'%' |
test_user 可从任意主机连接 |
test_user@'2000:472:18:b51:c32:a21' |
test_user 只可从 2000:472:18:b51:c32:a21 连接 |
权限管理
GRANT SELECT, UPDATE, DELETE, INSERT on inventory.category to test_user@localhost;
FLUSH PRIVILEGES;
REVOKE SELECT, UPDATE, DELETE, INSERT test_user@localhost;
FLUSH PRIVILEGES;
分配权限示例 | 说明 |
---|---|
GRANT SELECT ON database.table TO username@hostname |
分配 SELECT 某一数据库中的一张表的权限 |
GRANT SELECT ON database.* TO username@hostname |
分配 SELECT 某一数据库中的所有表的权限 |
GRANT SELECT ON . TO username@hostname |
分配 SELECT 所有数据中所有表的权限 |
GRANT CREATE, ALTER, DROP ON database.* to username@hostname |
分配 CREATE, ALTER, DROP 某一数据库中的所有表的权限 |
GRANT ALL PRIVILEGES ON . to username@hostname |
分配所有权限,相当于root 用户 |
SHOW GRANTS FOR username
创建用户分配权限示例
CREATE USER john@localhost identified by 'john_password';
CREATE USER steve@'%' identified by 'steve_password';
GRANT INSERT, UPDATE, DELETE, SELECT on inventory.* to john@localhost;
GRANT SELECT on inventory.* to steve@'%';
FLUSH PRIVILEGES;
$ mysql -u john -p
USE inventory;
SELECT * FROM category;
INSERT INTO category(name) VALUES('Memory');
UPDATE category SET name='Solid State Drive' where id = 3;
DELETE FROM category WHERE name LIKE 'Memory';
$ mysql -u steve -h server -p
USE inventory;
SELECT * FROM category;
INSERT INTO category(name) VALUES('Memory');
备份和恢复数据库数据
两种备份方式比较
逻辑备份 | 物理(原始)备份 |
---|---|
|
|
备份一个数据库中的数据
备份所有数据库中的数据
|
物理备份一般是通过如下工具:
|
恢复备份
|
恢复备份
|
逻辑备份恢复示例
mysqldump -u root -p inventory > /backup/inventory.dump
# mysql -u root
MariaDB [(none)]> create database inventory;
MariaDB [(none)]> exit
# mysql -u root inventory < inventory.dump
# mysql -u root
MariaDB [(none)]> use inventory;
MariaDB [(inventory)]> SELECT * FROM category;