MariaDB 常用 SQL 语句

一、MariaDB 账户管理

1.1. 查询在线用户

SHOW processlist;
mysqladmin -u admin -p processlist

1.2. 查询全部用户

SELECT DISTINCT User,Host,Password,plugin FROM mysql.user;

1.3. 创建普通用户

CREATE USER 'wangyan'@'%' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON wangyan\\_%.* TO 'wangyan'@'%' IDENTIFIED BY 'xxx';

1.4. 删除普通用户

DROP USER 'wangyan'@'%';
DELETE FROM mysql.user WHERE host = '%' AND user = 'wangyan';

1.5. 修改用户密码

SET PASSWORD=PASSWORD("xxx");
SET PASSWORD for "wangyan"@"%"=PASSWORD("xxx");
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxx';

1.6. ROOT密码找回

vim /etc/my.cnf.d/server.cnf
在Mysqld区域添加skip-grant-table语句，重启
sudo mysql -u root -p #免密码登陆数据库
UPDATE mysql.user SET password = password('123') WHERE user = 'root';
修改完成后将skip-grant-table属性去掉

二、MariaDB 权限管理

2.1. 查询所有权限

SHOW GRANTS FOR wangyan; #查询指定用户权限
SHOW GRANTS FOR 'wangyan'@'%';
FLUSH PRIVILEGES;

2.2. 收回用户授权

REVOKE ALL ON . FROM 'wangyan'@'%';
FLUSH PRIVILEGES;

2.3. 快速创建数据库和用户

DROP USER IF EXISTS 'wangyan'@'%';
DROP DATABASE IF EXISTS wangyan_gitea;
CREATE DATABASE wangyan_gitea CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

三、MariaDB 数据操作

3.1. 导入导出

mysqldump  -u wangyan -p databasename > databasename.sql
mysql -u wangyan -p databasename < /var/lib/mysql-files/databasename.sql