1.基本命令

启动

systemctl start mariadb.service

停止

systemctl stop mariadb.service

查看状态

systemctl status mariadb.service

重启

systemctl restart mariadb

2.修改密码

systemctl stop mariadb              # 停止MariaDB数据库
mysqld_safe --skip-grant-tables     # 安全模式进行MariaDB,并且跳过授权表
此时要打开另一个ssh窗口
mysql -uroot -p                 # 连接数据库
Enter password:                                     # 不要输入密码，直接回车

MariaDB [(none)]> use mysql;
update user set password=PASSWORD("admin@1234") where user='root';         # 先重置数据库root密码
flush privileges;                              # 必须要刷新权限才能生效
systemctl restart mariadb                   # 重启MariaDB数据库，使用密码进入MariaDB数据库

3.创建新用户

MariaDB [(none)]> create user newuser@localhost identified by '123456';
Query OK, 0 rows affected (0.22 sec)   #创建新用户

任意用户localhost连接

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

任意用户host连接

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

带密码的localhost连接

8.0以前的版本
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'your_password' WITH GRANT OPTION;

8.0版本分两步
# 创建账号密码
CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';

# 授予权限
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;

# 删除权限
REVOKE all privileges ON databasename.tablename FROM 'username'@'host';

# 修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';  </pre>

刷新

FLUSH   PRIVILEGES;

MariaDB [(none)]> grant all privileges on *.* to my@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)   #新用户授权，要在none中执行此命令

MariaDB [(none)]> show grants #查看权限是否生成
MariaDB [(none)]> select user,host from mysql.user; #验证全向是否生成

4.mysqld_safe A mysqld process already exists的问题解决

执行 ps aux | grep mysqld
kill -9 进程号 杀掉进程后重新执行 安全模式

5.如果是阿里云，去创建一个0.0.0.0/0 端口为3306的安全组

删除mysql账号

delete from user where user='XXX' and host='localhost';其中XXX为用户名，localhost为主机名。

delete from user where user='newbeidou' and host='%';

FLUSH PRIVILEGES;

授予权限

grant create on testdb.* to developer@'192.168.0.%'; 
revoke all on *.* from dba@localhost;
grant select,delete,update,create on bigloans.* to 'yu2036'@'%';