用户相关最佳实践-亲试ok

方法1： 用SET PASSWORD命令

mysql -u root

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

方法2：用mysqladmin

mysqladmin -u root password "newpass"

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

mysql -u root

mysql> use mysql;

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

mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

mysqld_safe --skip-grant-tables&

mysql -u root mysql

mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

mysql> FLUSH PRIVILEGES;

[root@myown_test binlog]# mysql -uroot -h127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql -uroot -h 127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h 127.0.0.1 -u root  -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h127.0.0.1 -uroot  -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h127.0.0.1 -uroot 

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

Your MySQL connection id is 13

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

mysql> show variables like 'log%';

+----------------------------------------+-----------------------------------------+

| Variable_name                          | Value                                   |

+----------------------------------------+-----------------------------------------+

| log_bin                                | ON                                      |

| log_bin_basename                       | /data/mysql/log/binlog/master-bin       |

| log_bin_index                          | /data/mysql/log/binlog/master-bin.index |

| log_bin_trust_function_creators        | OFF                                     |

| log_bin_use_v1_row_events              | OFF                                     |

| log_error                              | /data/mysql/log/alert.log               |

| log_output                             | FILE                                    |

| log_queries_not_using_indexes          | ON                                      |

| log_slave_updates                      | ON                                      |

| log_slow_admin_statements              | ON                                      |

| log_slow_slave_statements              | OFF                                     |

| log_throttle_queries_not_using_indexes | 0                                       |

| log_warnings                           | 1                                       |

+----------------------------------------+-----------------------------------------+

13 rows in set (0.00 sec)

[root@myown_test binlog]# mysqlbinlog master-bin.000005

mysqlbinlog: unknown variable 'default-character-set=gbk'

修改如下：

[root@myown_test binlog]# mysqlbinlog --no-defaults master-bin.000005

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=gbk这个指令。

      两个方法可以解决这个问题

一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用mysqlbinlog --no-defaults  master-bin.000005  命令打开

[root@myown_test mysql]# mysql -h 127.0.0.1 -u root

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

Your MySQL connection id is 10

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

mysql> use information_schema;

Database changed

mysql> show tables;

+---------------------------------------+

| Tables_in_information_schema          |

+---------------------------------------+

| CHARACTER_SETS                        |

| COLLATIONS                            |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS                               |

| COLUMN_PRIVILEGES                     |

| ENGINES                               |

| EVENTS                                |

|                。。。。。。                                            |

| INNODB_BUFFER_POOL_STATS              |

| INNODB_FT_CONFIG                      |

+---------------------------------------+

59 rows in set (0.00 sec)

mysql> connect mysql;         mysql环境直接切换数据库

Connection id:    11

Current database: mysql

mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| event                     |

|                。。。。。。                 |

| user                      |

+---------------------------+

+---------------------------+

28 rows in set (0.00 sec)

1、创建用户

mysql> create user 'test'@'localhost' identified by 'test';

Query OK, 0 rows affected (0.00 sec)

mysql> create user 'test01'@'192.168.7.195' identified by 'test';

Query OK, 0 rows affected (0.00 sec)

PS：username - 你将创建的用户名,

host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

2、授权

mysql> grant select,update,delete on test.testlog03 to test;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,delete on test.test03 to 'test01'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

3、直接以授权的方式创建用户

mysql> grant all privileges on mysql.testlog03 to test01;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,delete on test.test03 to 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

PS:必须执行flush privileges; 

否则登录时提示：ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES ) 

请看以下case

4、修改密码

mysql> set password for 'test01'@'%'=password('123456');

Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'test02'@'192.168.7.195' =password('123456');

Query OK, 0 rows affected (0.00 sec)

5、撤销用户权限

mysql> select host,user,password,select_priv,update_priv,delete_priv from mysql.user;

mysql> revoke select on test.test03  from 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

6、删除用户

mysql> drop user 'test01'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'test01'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

Grant all privileges on *.* to 'rot'@'192.168.168.168' identified by password 'haha';

注意  db表哦

方法1： 用SET PASSWORD命令

mysql -u root

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

方法2：用mysqladmin

mysqladmin -u root password "newpass"

如果root已经设置过密码，采用如下方法

mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

mysql -u root

mysql> use mysql;

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

mysql> FLUSH PRIVILEGES;

在丢失root密码的时候，可以这样

mysqld_safe --skip-grant-tables&

mysql -u root mysql

mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';

mysql> FLUSH PRIVILEGES;

[root@myown_test binlog]# mysql -uroot -h127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql -uroot -h 127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h 127.0.0.1 -u root  -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h127.0.0.1 -uroot  -p

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[root@myown_test binlog]# mysql  -h127.0.0.1 -uroot 

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

Your MySQL connection id is 13

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

mysql> show variables like 'log%';

+----------------------------------------+-----------------------------------------+

| Variable_name                          | Value                                   |

+----------------------------------------+-----------------------------------------+

| log_bin                                | ON                                      |

| log_bin_basename                       | /data/mysql/log/binlog/master-bin       |

| log_bin_index                          | /data/mysql/log/binlog/master-bin.index |

| log_bin_trust_function_creators        | OFF                                     |

| log_bin_use_v1_row_events              | OFF                                     |

| log_error                              | /data/mysql/log/alert.log               |

| log_output                             | FILE                                    |

| log_queries_not_using_indexes          | ON                                      |

| log_slave_updates                      | ON                                      |

| log_slow_admin_statements              | ON                                      |

| log_slow_slave_statements              | OFF                                     |

| log_throttle_queries_not_using_indexes | 0                                       |

| log_warnings                           | 1                                       |

+----------------------------------------+-----------------------------------------+

13 rows in set (0.00 sec)

[root@myown_test binlog]# mysqlbinlog master-bin.000005

mysqlbinlog: unknown variable 'default-character-set=gbk'

修改如下：

[root@myown_test binlog]# mysqlbinlog --no-defaults master-bin.000005

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=gbk这个指令。

      两个方法可以解决这个问题

一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。

二是用mysqlbinlog --no-defaults  master-bin.000005  命令打开

[root@myown_test mysql]# mysql -h 127.0.0.1 -u root

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

Your MySQL connection id is 10

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

mysql> use information_schema;

Database changed

mysql> show tables;

+---------------------------------------+

| Tables_in_information_schema          |

+---------------------------------------+

| CHARACTER_SETS                        |

| COLLATIONS                            |

| COLLATION_CHARACTER_SET_APPLICABILITY |

| COLUMNS                               |

| COLUMN_PRIVILEGES                     |

| ENGINES                               |

| EVENTS                                |

|                。。。。。。                                            |

| INNODB_BUFFER_POOL_STATS              |

| INNODB_FT_CONFIG                      |

+---------------------------------------+

59 rows in set (0.00 sec)

mysql> connect mysql;         mysql环境直接切换数据库

Connection id:    11

Current database: mysql

mysql> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| event                     |

|                。。。。。。                 |

| user                      |

+---------------------------+

+---------------------------+

28 rows in set (0.00 sec)

1、创建用户

mysql> create user 'test'@'localhost' identified by 'test';

Query OK, 0 rows affected (0.00 sec)

mysql> create user 'test01'@'192.168.7.195' identified by 'test';

Query OK, 0 rows affected (0.00 sec)

PS：username - 你将创建的用户名,

host - 指定该用户在哪个主机上可以登陆，此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录，如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录;也可以指定某台机器可以远程登录;

password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器。

2、授权

mysql> grant select,update,delete on test.testlog03 to test;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,delete on test.test03 to 'test01'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

3、直接以授权的方式创建用户

mysql> grant all privileges on mysql.testlog03 to test01;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,delete on test.test03 to 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

PS:必须执行flush privileges; 

否则登录时提示：ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES ) 

请看以下case

4、修改密码

mysql> set password for 'test01'@'%'=password('123456');

Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'test02'@'192.168.7.195' =password('123456');

Query OK, 0 rows affected (0.00 sec)

5、撤销用户权限

mysql> select host,user,password,select_priv,update_priv,delete_priv from mysql.user;

mysql> revoke select on test.test03  from 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

6、删除用户

mysql> drop user 'test01'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'test02'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'test01'@'192.168.7.195';

Query OK, 0 rows affected (0.00 sec)

Grant all privileges on *.* to 'rot'@'192.168.168.168' identified by password 'haha';

注意  db表哦