mysql运维命令_运维常用 MySQL 命令

设置用户密码

方法一

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPassword');

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> \q

Bye

[root@node ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

方法二

UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';

mysql> UPDATE mysql.user SET Password = PASSWORD('234567') WHERE user = 'root';

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> \q

Bye

# 这里使用新密码登陆

[root@node ~]# mysql -uroot -p234567

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 16

Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

丢失root密码找回

两种方式：

在启动的时候增加 --skip-grant-tables 参数启动

在my.cnf配置文件中的mysql模块中添加下面参数 skip-grant-tables = 1

上面两种方式效果相同

使用后，启动mysql，就可以使直接使用mysql 命令登陆，无需密码，进入后直接修改root密码即可，最后去掉参数或者配置文件中的参数重启数据库即可；

添加用户