mysql用户权限管理和增删改查操作

最新推荐文章于 2024-03-20 23:36:26 发布

weixin_33763244

最新推荐文章于 2024-03-20 23:36:26 发布

阅读量940

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/angus717/974835

版权

mysql使用上一篇的安装的mysql。启动mysql服务后直接就能连到mysql服务器上。

# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>

1>mysql用户
此时你会发现mysql用户密码为空。（一个完整的mysql用户是由俩部分组成用户名+主机名）；

这样会给数据库带来很大的危害，所以装完一个数据，什么都不做，先加密码并把不使用的账户都删掉。对mysql用户操作命令如下：
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.04 sec)

ysql> drop user 'root'@'::1';
Query OK, 0 rows affected (0.02 sec)

mysql> delete from mysql.user where user='root' and host='localhost.localdomain';
Query OK, 1 row affected (0.00 sec)

进行以上操作后再次查看：
mysql> select Host,User,Password from user;
+-----------+------+----------+
| Host      | User | Password |
+-----------+------+----------+
| localhost | root |          |
| 127.0.0.1 | root |          |
+-----------+------+----------+
2 rows in set (0.00 sec)

下面就要为需要的用户添加密码。首先介绍下服务器端的管理工具mysqladmin。

服务器端的管理工具
  --mysqladmin非交互式工具，用于创建，删除数据库
    --mysqladmin create mydb2
    --mysqldamin drop mydb2
    --mysqladmin status
    --mysqladmin extended-status显服务器扩展状态信息
    --mysqladmin flush-hosts    清空dns等状态信息
    --mysqladmin flush-logs 做日志滚动的
    --mysqladmin flush-privileges 重读数据文件
    --mysqladmin flush-status 重置服务器状态信息
    --mysqladmin flush-tables 关闭当前打开的表（等待正在使用表操作完后关闭）
    --mysqladmin flush-threads 重置线程的
    --mysqladmin kill
    --mysqladmin processlist 显示主机上处于活动状态的线程
    -- mysqladmin refresh 相当于执行mysqladmin flush-hosts 和mysqladmin flush-logs
    -- mysqladmin shutdown关闭mysql服务器
    --mysqladmin start-slave启动从服务器的
    --mysqladmin stop-slave 关闭重服务器
    --mysqladmin variables 显示服务器全局变量及其值

使用mysqladmin给mysql用户添加密码。
# mysqladmin -uroot password 'redhat'
此刻再执行
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -predhat
mysql> use mysql
Database changed
mysql> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root |                                           |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)
发现只为root@localhost用户添加密码，如果想为127.0.0.1添加密码使用刚才那种方式，是会报错的。所以使用mysql交互式命令修改添加密码是个不错的选择。
mysql> set password for 'root'@'127.0.0.1' = password('redhat');
Query OK, 0 rows affected (0.00 sec)

mysql> update user set password=password('') where host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改用户权限后需要把权限刷新到内存中。
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql还有一种特殊的添加密码方式--授权

>GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO BACKUSER@192.168.2.106 IDENTIFIED BY 'redhat';
授与admin用户从任何其它主机发起的访问

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'angus' WITH GRANT OPTION;

给用户赋权可以分为以下几种：

select,insert,update,delete,create,drop,index,alter,GRANT,references,reload,shutdown,process,file等14个权限
grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权限
grant select, insert, update, delete on db1.* to user_name@’%’ ;
grant 数据库开发人员，创建表、索引、视图、存储过程、函数...等权限
grant 创建、修改、删除 MySQL 数据表结构权限
grant create, alter, drop on db1.* to user_name@’%’;
grant 操作 MySQL 外键,索引权限
grant index,references on db1.* to user_name@’%’;
grant 操作 MySQL 临时表权限
grant create temporary tables on db1.* to user_name@’%’;
grant 高级 DBA 管理 MySQL 中所有数据库的权限，并能把权限赋予给别人
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'angus' WITH GRANT OPTION;

找mysql的root用户密码，关闭mysql，在启动mysqld的时候加上--skip-grant-tables，为了安全也要加上--skip-network
#service mysql start --skip-grant-tables
mysql> update mysql.user set password=password('redhat') where user= 'root';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2 Changed: 0 Warnings: 0
(在这只能用update命令，不能使用insert等其他命令修改)

2>mysql 数据库与表操作
创建数据库，表并查看表结构。

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> create table mydb.user (Name char(20),Age tinyint unsigned);
Query OK, 0 rows affected (0.09 sec)
mysql> desc mydb.user;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | char(20) | YES | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+

创建表时候指定默认存储引擎

mysql> create table mydb.user (Name char(20),Age tinyint unsigned) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

数据库：mydb
同学表：
ID 姓名性别年龄课程代码老师编号

老师表：
ID 姓名性别年龄职称课程代码

课程表：
ID 课程名称

mysql> use mydb;
Database changed
mysql> create table stu (
-> SID int unsigned not null auto_increment primary key,
-> Name varchar(50) not null,
-> Gender enum('F','M') not null default 'M',
-> Age tinyint unsigned,
-> CID int unsigned,
-> TID int unsigned );
Query OK, 0 rows affected (0.01 sec)
mysql> desc stu;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Gender | enum('F','M') | NO | | M | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| CID | int(10) unsigned | YES | | NULL | |
| TID | int(10) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+
mysql> create table teacher ( TID int unsigned not null auto_increment primary key, Name varchar(50) not null, Gender enum('F','M'), CID int unsigned);
Query OK, 0 rows affected (0.03 sec)
mysql> desc teacher;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| TID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Gender | enum('F','M') | YES | | NULL | |
| CID | int(10) unsigned | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table course(
-> CID int unsigned not null auto_increment primary key,
-> Course varchar(100) not null );
Query OK, 0 rows affected (1.27 sec)
mysql> desc course;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| CID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Course | varchar(100) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

向表中添加数据

mysql> insert into course (course) values ('kuihuabaodian'),('xianglongshibazhang'),('qiankundaluoyi'),('hamagong');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> insert into course set course='jiuyangshenggong';
Query OK, 1 row affected (0.01 sec)
mysql> select * from course;
+-----+---------------------+
| CID | Course              |
+-----+---------------------+
|   1 | kuihuabaodian       |
|   2 | xianglongshibazhang |
|   3 | qiankundaluoyi      |
|   4 | hamagong            |
|   5 | jiuyangshenggong    |
+-----+---------------------+
5 rows in set (0.00 sec)

复制表结构

mysql> create table tutor like teacher;
Query OK, 0 rows affected (0.05 sec)
mysql> desc tutor;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| TID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(50)      | NO   |     | NULL    |                |
| Gender | enum('F','M')    | YES  |     | NULL    |                |
| CID    | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

复制表结构加数据

mysql> create table kecheng select * from course;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from kecheng;
+-----+---------------------+
| CID | Course              |
+-----+---------------------+
|   1 | kuihuabaodian       |
|   2 | xianglongshibazhang |
|   3 | qiankundaluoyi      |
|   4 | hamagong            |
+-----+---------------------+
4 rows in set (0.00 sec)
mysql> desc kecheng;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| CID    | int(10) unsigned | NO   |     | 0       |       |
| Course | varchar(100)     | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> desc course;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| CID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Course | varchar(100)     | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

数据库修改表操作

mysql> alter table tutor add Title varchar(100) after name;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tutor;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| TID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(50)      | NO   |     | NULL    |                |
| Title  | varchar(100)     | YES  |     | NULL    |                |
| Gender | enum('F','M')    | YES  |     | NULL    |                |
| CID    | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
######只改字段定义不改名字######
mysql> alter table tutor modify gender enum('F','M') default 'M';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tutor;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| TID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(50)      | NO   |     | NULL    |                |
| Title  | varchar(100)     | YES  |     | NULL    |                |
| gender | enum('F','M')    | YES  |     | M       |                |
| CID    | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
######改名字要用change######
mysql> alter table tutor change gender Sex enum('F','M') default 'M';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc tutor;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| TID   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(50)      | NO   |     | NULL    |                |
| Title | varchar(100)     | YES  |     | NULL    |                |
| Sex   | enum('F','M')    | YES  |     | M       |                |
| CID   | int(10) unsigned | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
######修改表名######
mysql> alter table tutor rename to teacher;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| course         |
| stu            |
| teacher        |
| user           |
+----------------+
4 rows in set (0.00 sec)
mysql> rename table teacher to tutor;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| course         |
| stu            |
| tutor          |
| user           |
+----------------+
4 rows in set (0.00 sec)

3>mysql数据库的备份和恢复

数据库备份和恢复
mysql数据库的备份和还原
备份指定数据库
mysqldump -hhostname -uusername -ppassword databasename >backupfile.sql
压缩备份指定数据库
mysqldump -hhostname -uusername -ppassword databasename |gzip>backupfile.sql.gz
备份指定数据库指定的表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 >backupfile.sql
备份多个数据库
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3>multibackupfile.sql
备份数据库结构
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
备份所以数据库
mysqldump –all-databases>allbackupfile.sql
备份所以数据库并标记binlog位置
mysqldump -hlocalhost -uroot -p --all-databases --single-transaction --master-data=2 > /root/all.sql
mysql恢复
mysql -hhostname -uusername -ppassword databasename <backupfile.sql
gunzip<backupfile.sql.gz|mysql -uusername -ppassword databasename
mysqldump -uusername -ppassword databasename|mysql –host=*.*.*.* -C databasename

>mysql 日志

错误日志:服务器启动和关闭时产生的信息；服务器运行中产生的错误的信息；事件调度运行一个事件时产生的信息；在从服务器上启动从服务器进程产生的信息.
(不把日志放在数据目录下，可以在配置文件中指定错误日志的位置 log-error=/var/log/mysqld_err,但是这个文件对与mysql用户有读写权限)

查看当前的错误日志的位置可以使用mysql> show global variables like '%log_error%';

mysql> show global variables like '%log_error%';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| log_error     | /var/lib/mysql/localhost.localdomain.err |
+---------------+------------------------------------------+

二进制日志:是以二进制格式记录的对mysql服务器发起的能够改变（写入，删除，修改）数据库内容的操作的.常用作做即时点恢复和从服务器复制.
在配置文件中可以定义二进制日志文件存放的位置log-bin=/path/filename;二进制日志格式有--基于行的--基于语句（如果基于语句执行的结果在不同的时间不一样的情况下要基于行的格式)和混合模式（mysql自动会识别）
"--max_binlog_size"设置binlog 的最大存储上限,当日志达到该上限时,MySQL 会重新创建一个日志开始继续记录.
--binlog-do-db=db_name参数明确告诉MySQL,需要对某个（db_name）数据库记录binlog,如果有了"--binlog-do-db=db_name"参数的显式指定,MySQL 会忽略针对其他
数据库执行的query,而仅仅记录针对指定数据库执行的query.
"--binlog-ignore-db=db_name"与"--binlog-do-db=db_name"完全相反，它显式指定忽略某个（db_name）数据库的binlog 记录.
mysql-bin.index 文件（binary log index）的功能是记录所有Binary Log 的绝对路径,保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件.
删除二进制日志：purge binary logs (慎重)
mysql>show master status;用于查看当前系统上正在使用的二进制文件；
mysql>flush logs;手动滚动使用命令

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
mysql> purge binary logs to 'mysql-bin.000001';
Query OK, 0 rows affected (0.02 sec)
mysql>purge binary logs before '2013-3-5 23:00:00';

事务日志：innodB的事务日志，是一种循环日志，主要用于将innodB引擎的随机IO转换成顺序IO，提高innodB的并发性能的重要手段.

慢查询日志：查询超时的记录，默认没有开启，慢查日志是我们地位分析服务器性能的重要手段
慢查询日志slow_query_log和慢查询日志存放的位置slow_query_log_file=/path/fielname,慢查超时时间long_query_time=num

中继日志：在复制中用到。
默认日志都是记录在文件中，也可以记录在数据库中-->需要修改log_output=[file,table]

".ibd"文件和ibdata 文件这两种文件都是存放Innodb 数据的文件，之所以有两种文件来存放Innodb 的数据（包括索引），是因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且
每个表一个“.ibd”文件，文件存放在和MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配
置）ibdata 文件。ibdata 文件可以通过innodb_data_home_dir 和innodb_data_file_path两个参数共同配置组成， innodb_data_home_dir 配置数据存放的总目录，而
innodb_data_file_path 配置每一个文件的名称。当然，也可以不配置innodb_data_home_dir 而直接在innodb_data_file_path 参数配置的时候使用绝对路径来
完成配置。innodb_data_file_path 中可以一次配置多个ibdata 文件。文件可以是指定大小，也可以是自动扩展的，但是Innodb 限制了仅仅只有最后一个ibdata 文件能够配置成自
动扩展类型。当我们需要添加新的ibdata 文件的时候，只能添加在innodb_data_file_path配置的最后，而且必须重启MySQL 才能完成ibdata 的添加工作。不过如果我们使用独享表
空间存储方式的话，就不会有这样的问题，但是如果要使用裸设备的话，每个表一个裸设备，可能造成裸设备数量非常大，而且不太容易控制大小，实现比较困难，而共享表空间却不会
有这个问题，容易控制裸设备数量。我个人还是更倾向于使用独享表空间存储方式。当然，两种方式各有利弊，看大家各自应用环境的侧重点在那里了。

转载于:https://blog.51cto.com/angus717/974835

weixin_33763244

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql用户权限管理和增删改查操作

mysql使用上一篇的安装的mysql。启动mysql服务后直接就能连到mysql服务器上。#mysqlWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.25-logSourcedistributionCo...
复制链接

扫一扫