MariaDB 常用 SQL 语句

SHOW processlist;
mysqladmin -u admin -p processlist

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

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

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

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

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属性去掉

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

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

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

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