mysql查看用户名_Mysql数据库04MySQL数据库管理

本文详细介绍了MySQL数据库管理,包括用户账号的创建、删除、重命名和密码设置,以及如何在忘记root密码时进行恢复。此外,还讲解了用户授权操作,如授予、查看和撤销权限,并提到了常见的权限类型。最后,文章提及了MySQL的日志管理和数据乱码的解决方案。
摘要由CSDN通过智能技术生成

Mysql数据库04-MySQL数据库管理

MySQL用户账号管理

1、新建用户

格式:CREATE USER ‘用户名’@’来源地址’ [IDENTIFIED BY [PASSWORD] ‘密码’];

示例:

1448b25078b28d7f9441443fd2edae69.png

mysql> create user 'admin'@'%' identified by '123123';

e715f1e5ecbccaeefbb60622568e2081.png

2、删除用户

格式:DROP USER ‘用户名’@’来源地址’;

示例:

mysql> drop user 'admin'@'%';

80a620277292b340c5ca6f0be073227c.png

3、重命名及修改主机

格式:RENAME USER ‘原用户名’@’原主机’ to ‘新用户名’@’新主机’;       //可以只修改用户名或只修改主机

示例:

mysql> create user 'amber'@'192.168.2.%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> rename user 'amber'@'192.168.2.%' to 'admin'@'192.168.2.%';       //修改用户名

Query OK, 0 rows affected (0.03 sec)

mysql> rename user 'admin'@'192.168.2.%' to 'admin'@'%';       //修改来源地址

Query OK, 0 rows affected (0.00 sec)

mysql> rename user 'admin'@'%' to 'amber'@'192.168.2.%';       //修改用户名及来源地址

Query OK, 0 rows affected (0.00 sec)

4、给用户设置密码

格式:SET PASSWORD=PASSWORD(‘密码’);              //为当前登录的用户修改密码

         SET PASSWORD FOR ‘用户名’@’来源地址’ =PASSWORD(‘密码’);       //为其他用户设置面

示例:

mysql> select user();       //查看当前所登录的用户

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

| user()         |

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

| root@localhost |

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

1 row in set (0.00 sec)

mysql> set password=password('123456');              //修改root@localhost的密码

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

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

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

Your MySQL connection id is 2

Server version: 5.5.22-log Source distribution

……

d95336ce42f96de85c6cf48790f9752b.png

mysql> set password for 'amber'@'192.168.2.%' =password('123456');

Query OK, 0 rows affected (0.00 sec)

5c29f4fdacfe05750f40ce9c744af30f.png

[root@mysql ~]# ifconfig eth1|grep "inet addr"

          inet addr:192.168.2.16  Bcast:192.168.2.255  Mask:255.255.255.0

[root@mysql ~]# mysql -uamber -p123456 -h192.168.2.16

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

Your MySQL connection id is 6

Server version: 5.5.22-log Source distribution

……

mysql> select user();

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

| user()             |

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

| amber@192.168.2.16 |

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

1 row in set (0.00 sec)

root密码忘记时的解决方法

方法一:修改配置文件

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

skip-grant-tables

……

[root@mysql ~]# /etc/init.d/mysqld restart

[root@mysql ~]# mysql

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

Your MySQL connection id is 2

Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, 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 mysql.user set password=password('123123') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

再修改配置文件将刚才加入的skip-grant-tables行删掉,重启

[root@mysql ~]# vim /etc/my.cnf

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

[root@mysql ~]# mysql -uroot -p123123

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

Your MySQL connection id is 1

Server version: 5.5.22-log Source distribution

方法二:直接跳过授权表登录

1、停止mysqld服务

[root@mysql ~]# /etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

2、直接跳过授权表

[root@mysql ~]# mysqld_safe --skip-grant-tables &

[1] 9426

[root@mysql ~]# 160428 16:03:37 mysqld_safe Logging to '/usr/local/mysql/data/mysql.err'.

160428 16:03:37 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

3、登录mysql修改mysql.user表内root用户的密码

[root@mysql ~]# mysql

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

Your MySQL connection id is 1

Server version: 5.5.22-log Source distribution

Copyright (c) 2000, 2011, 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 mysql.user set password=password('123456') where user='root';

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL.160428 16:04:47 mysqld_safe mysqld from pid file /usr/local/mysql/data/mysql.pid ended

 SUCCESS!

Starting MySQL.. SUCCESS!

[1]+  Done                    mysqld_safe --skip-grant-tables

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

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

Your MySQL connection id is 1

Server version: 5.5.22-log Source distribution

MySQL用户授权操作

       在MySQL系统中,要对用户进行权限控制,才能保证系统数据的安全。

1、授予权限

格式:GRANT 权限列表 ON 库名.表名 TO ‘用户名’@’来源地址’ [IDENTIFIED BY ‘密码’];

示例:

3a1736d292f310d243dba4f9506bfa3e.png

mysql> grant select on mysql.user to 'amber'@'192.168.2.%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

fd4f9c6754bf92ce37a03d19a0359eb3.png

mysql> grant all on *.* to 'amber'@'%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'admin'@'%' identified by '123123' with grant option;

       //没有admin用户,系统自动创建此用户,赋予所有权限及授权的权限

Query OK, 0 rows affected (0.00 sec)

7344a5819c24e5b1317d2867fb184d70.png

2、查看授权

格式:SHOW GRANTS;      //查看当前登录用户的授权信息

         SHOW GRANTS FOR ‘用户名’@’来源地址’;

示例:

mysql> show grants;

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

| Grants for root@localhost                                                                                                              |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

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

2 rows in set (0.00 sec)

mysql> show grants for 'amber'@'%';

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

| Grants for amber@%                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO 'amber'@'%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |

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

1 row in set (0.00 sec)

mysql> show grants for 'admin'@'%';

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

| Grants for admin@%                                                                                                              |

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

| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' WITH GRANT OPTION |

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

1 row in set (0.00 sec)

3、撤销权限

格式:REVOKE 权限列表 ON 库名.表名 FROM ‘用户名’@’来源地址’;

示例:

mysql> show grants for 'amber'@'%';

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

| Grants for amber@%                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO 'amber'@'%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |

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

1 row in set (0.00 sec)

mysql> revoke drop,create on *.* from 'amber'@'%';         //仅撤销amber的drop和create权限

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'amber'@'%';

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

| Grants for amber@%                                                                                                                                                                                                                                                                                                                                                                                           |

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

| GRANT SELECT, INSERT, UPDATE, DELETE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'amber'@'%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |

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

1 row in set (0.00 sec)

mysql> revoke all on *.* from 'amber'@'%';              //把剩下的所有权限都撤销

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'amber'@'%';

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

| Grants for amber@%                                                                                   |

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

| GRANT USAGE ON *.* TO 'amber'@'%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |

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

1 row in set (0.00 sec)

4、常见的权限列表:

ALL:设置除GRANT OPTION之外的所有权限,如需设置,授权时加上WITH GRANT OPTION

ALTER:允许使用ALTER TABLE,修改表

CREATE:允许使用CREATE TABLE,创建表

CREATE USER:用户管理权限

DELETE:允许使用DELETE,删除数据

DROP:允许使用DROP TABLE,删除表

INSERT:允许使用INSERT,在表中插入信息

REPICATION SLAVE:从主服务器中读取二进制日志事件

SELECT:允许使用SELECT,查看表内数据

SHOW DATABASES:允许使用SHOW DATABASES显示所有数据库

UPDATE:允许使用UPDATE修改表内的数据

四、MySQL日志管理

MySQL日志包括:

       ·错误日志

       ·通用查询日志

       ·二进制日志

       ·慢速查询日志

1、错误日志

       包含了当MySQL启动和停止时,以及服务器在运行过程中发生的任何错误时的相关信息,默认在安装目录/usr/local/mysql/data/下的“主机名.err”。

       可在登录时使用“--log-error=文件路径及文件名”的方式来指定mysqld保存错误日志的位置;或者修改主配置文件/etc/my.cnf,在[mysqld]下方添加“log-error = 文件路径及文件名”,重启mysqld服务。

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

……

log-error = mysql_error.log       //默认路径在/usr/local/mysql/data/下

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

b8ac4baacf11411d7d7fd8303ef1df13.png

[root@mysql ~]# cat /usr/local/mysql/data/mysql_error.log

160428 16:40:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

160428 16:40:45 InnoDB: The InnoDB memory heap is disabled

160428 16:40:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins

160428 16:40:45 InnoDB: Compressed tables use zlib 1.2.3

160428 16:40:45 InnoDB: Initializing buffer pool, size = 128.0M

160428 16:40:45 InnoDB: Completed initialization of buffer pool

160428 16:40:45 InnoDB: highest supported file format is Barracuda.

160428 16:40:45  InnoDB: Waiting for the background threads to start

160428 16:40:46 InnoDB: 1.1.8 started; log sequence number 1643899

160428 16:40:46 [Note] Event Scheduler: Loaded 0 events

160428 16:40:46 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.5.22-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

2、通用查询日志

       MySQL所有连接和语句都将会被记录。默认关闭此项日志记录,一般用作调试用,平时开启会记录大量数据占用磁盘空间。

       可在登录时使用“--log=文件路径及文件名”或“-l 文件路及文件名”选项指定;或修改主配置文件/etc/my.cnf,在[mysqld]下添加“log = ON”和“log = 文件路径及文件名”,重启mysqld服务。

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

log = ON

log = /usr/local/mysql/data/mysql_general.log       //绝对路径可以省略,保证mysql对该目录具有写入权限!

[root@mysql ~]# tail -f /usr/local/mysql/data/mysql_general.log

/usr/local/mysql/bin/mysqld, Version: 5.5.22-log (Source distribution). started with:

Tcp port: 3306  Unix socket: /tmp/mysql.sock

Time                 Id Command    Argument

[root@mysql ~]# mysql -uroot -p

Enter password:

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

Your MySQL connection id is 1

Server version: 5.5.22-log Source distribution

……

mysql> show databases;

……

mysql> use auth;

Database changed

mysql> show tables;

……

发现日志全部记录了所有执行过的命令

f0125a3a48b61932f6c9472fbad70b02.png

3、二进制日志

       包含了所有更新了的数据或者已经潜在更新了数据的所有语句,记录了数据的更改。主要目的是在恢复时能够最大可能地恢复数据库。默认是开启的,默认路径在/usr/local/mysql/data/下的,以“mysql-bin”开头的二进制日志。

       可在登录时使用“--log-bin=文件路径及文件命前缀”选项指定;或修改主配置文件/etc/my.cnf中[mysqld]下的“log-bin = 文件路径及文件名”修改存放路径。

       可用mysqlbinlog命令查看二进制日志文件。

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

……

log-bin=mysql-bin              //默认就有,无需修改即可

[root@mysql ~]# cd /usr/local/mysql/data/

[root@mysql data]# mysqlbinlog mysql-bin.000001

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

[root@mysql data]# mysqlbinlog --no-defaults mysql-bin.000001

4、慢速查询日志

       记录所有执行时间超过long_query_time秒的SQL语句,可用于找到执行时间长的查询,以用于优化。默认未开启。

       可在登录时使用“--log-slow-queries[=文件路径及文件名]”选项开启,若为指定文件路径及文件会,会在/usr/local/mysql/data下生成“主机名-slow.log”;或修改主配置文件/etc/my.cnf,在[mysqld]下添加“long_query_time”和“log-slow-queries = 文件路径及文件名”,重启mysqld服务。

[root@mysql ~]# vim /etc/my.cnf

[mysqld]

long_query_time = 5       //单位秒

log-slow-queries = mysql_slow.log

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

[root@mysql ~]# ll /usr/local/mysql/data/mysql_slow.log

-rw-rw---- 1 mysql mysql 175 4月  28 16:59 /usr/local/mysql/data/mysql_slow.log

MySQL数据乱码的解决方法

1、MySQL数据乱码的可能原因

       (1)服务器系统字符设置问题

       (2)数据表羽西设置问题

       (3)客户端连接语系的问题

2、解决方法

       (1)在创建数据库时设定

       格式:CREATE DATABASE 库名 CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’;

       示例:

mysql> create database aaa character set 'utf8' collate 'utf8_general_ci';

Query OK, 1 row affected (0.05 sec)

       (2)在创建表时设定字符集

       格式:CREATE TABLE 表名(字段 格式) DEFAULT CHARSET=utf8;

       示例:

mysql> use aaa;

Database changed

mysql> create table bbb(name char(40),tel int(20)) default charset=utf8;

Query OK, 0 rows affected (0.01 sec)

       (3)使用SET NAMES设置默认字符集

       格式:SET NAMES utf8;                     //或 CHARSET utf8;

       示例:

mysql> set names utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> charset utf8;

Charset changed

       (4)永久修改,修改主配置文件在[mysql]字段中加入:default_character_set=utf8;

       示例:

[root@mysql ~]# vim /etc/my.cnf

[mysql]              //注意,不是[mysqld]!!!否则启动时报错!!!(若为yum安装的不会报错)

default-character-set = utf8

[root@mysql ~]# /etc/init.d/mysqld restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值