Mysql基础操作

数据库必须具有UTF8字符集

create database jiradb character set utf8 collate utf8_bin;

  GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;


# 给用户设定密码的两种方式
1.  UPDATE user SET password = PASSWORD('your_password') WHERE user = 'username';
    FLUSH PRIVILEGES;
2.  set password for root@localhost = password('your_password');

# 创建用户并授权用户
create user 'username'@'localhost' identified by 'your_password';
create database database_name default character set = 'utf8' default collate = 'utf8_general_ci';
grant all privileges on database_name.* to 'username'@'localhost';
flush privileges;

查看mysql数据库中的所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看某个用户的权限

show grants for 'nextcloud'@'%'; 

or

select * from mysql.user where user='root' \G;

查看当前用户

select user();

修改用户密码

use mysql;
UPDATE user SET password=PASSWORD('新密码') WHERE user='用户';
flush privileges;

修改用户权限及密码

grant 权限 on 库名.表名 to '用户名'@’网段‘  identified by  "该用户的密码"；

grant all privileges on nextcloud.* to 'nextcloud'@'%' identified by 'du..olctx..entest.1';

删除用户

drop user 'nextcloud'@'%';

root更改密码

set password for root@localhost = password("Songhaofeng@123");



mysql> create database confluence default character set utf8 collate utf8_bin;
mysql> grant all on confluence.* to 'confluence'@'%' identified by 'confluence';
mysql> flush privileges;
如出现报错信息：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
有时候，只是为了自己测试，不想密码设置得那么复杂，譬如说，我只想设置root的密码为123456。
必须修改两个全局参数：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
    
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

修改MySQL初始密码

[root@localhost ~]# mysqladmin -uroot -p'/DBpfulrh8kl' password 'Song@123'