登录mysql并修改用户密码和权限

==========================mysql服务安装完成启=============================

进入数据库

mysql -hlocalhost -uroot -p

设置 基础root密码 ,密码自定义可以，root账户只需要修改密码其他不用，root账户也只能localhost使用其他网络地址是不能远程访问的。

mysql> set password = password('123456');

创建数据库用户并且赋权 执行如下sql：

CREATE USER 'pt_user'@'' IDENTIFIED BY 'pt_user_2018';

GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON *.* TO 'pt_user'@'';

CREATE USER 'pt_dba'@'' IDENTIFIED BY 'pt_dba_2018';

GRANT GRANT OPTION ON *.* TO 'pt_dba'@'';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'pt_dba'@'';

 

---------------------------------------------------------------------------------------------------------------------------

备注 :如果 新增的用不能通过 navicat或其他工具远程连接的时候需要执行授权语句给pt_user,pt_dba

---------------------------------------------------------------------------------------------------------------------------

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pt_user'@'%' IDENTIFIED BY 'pt_user_2018' WITH GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pt_dba'@'%' IDENTIFIED BY 'pt_dba_2018' WITH GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)