前提：mysql忘记密码，进行修改密码操作

修改配置文件

vim /etc/my.cnf

在 [mysqld] 小节下添加一行：（这一行配置让 mysqld 启动时不对密码进行验证）

skip-grant-tables=1

重启 mysqld 服务：

systemctl restart mysqld

无需密码就可以登录到 mysql：

mysql -u root

修改用户密码遇到的问题（ERROR 1064、ERROR 1290）

set password for root@localhost = password('ctsi@Passw0rd');

ALTER USER 'root'@'localhost' IDENTIFIED BY 'ctsi@Passw0rd';

这两种命令均出现报错，

mysql> set password for root@localhost = password('ctsi@Passw0rd');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for root@localhost = password('ctsi@Passw0rd')' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ctsi@Passw0rd';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决办法

执行//刷新系统权限表命令（flush privileges;）即可正常修改；

mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ctsi@Passw0rd';
Query OK, 0 rows affected (0.02 sec)

创建用户遇到的问题（ERROR 1396）

mysql> CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'ctsi@Passw0rd';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'127.0.0.1'

解决办法

多半是因为之前创建过，有残留，删除痕迹即可

mysql> drop user 'root'@'127.0.0.1';
Query OK, 0 rows affected (0.02 sec)

常用创建用户及赋权命令

允许本地访问的用户（127.0.0.1）

mysql>CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '123456';

允许外网IP访问的用户

mysql>CREATE USER 'root'@'%' IDENTIFIED BY '123456';

 授予用户在本地服务器对所有数据库的全部权限

mysql>grant all privileges on *.* to 'root'@'127.0.0.1';
mysql>alter user 'root'@'127.0.0.1' identified with mysql_native_password by '123456';

刷新授权

mysql>FLUSH PRIVILEGES;

创建一个专门用于主从复制的用户

mysql> create user 'repl'@'%' identified by '123456';

mysql> grant replication slave on *.* to 'repl'@'%';

mysql> flush privileges;