- 浏览: 89061 次
- 性别:
- 来自: 深圳
-
文章分类
最新评论
MySQL常规管理
本文包括如下内容：
匿名访问test:
修改root密码:
创建一个新用户:
限制用户权限到表和列：
一个用户将自己所拥有的权限赋给另一个用户：
取消用户权限：
删除一个用户：
使用mysqldump备份一个数据库：
使用mysqldump备份一个或多个表：
从备份文件恢复一个数据库：
匿名访问test:
C:\>mysql -h localhost test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1 |
+----------------+
1 row in set (0.00 sec)
修改root密码:
C:\>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update user set password=password('0000') where user=root;
ERROR 1046: No Database Selected
mysql> create database mydata;
Query OK, 1 row affected (0.00 sec)
mysql> use mydata;
Database changed
mysql> update user set password=password('0000') where user=root;
ERROR 1146: Table 'mydata.user' doesn't exist
mysql> use mysql;
Database changed
mysql> update user set password=password('0000') where user=root;
ERROR 1054: Unknown column 'root' in 'where clause'
mysql> update user set password=password('0000') where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
C:\>mysqladmin -u root status
Uptime: 1045 Threads: 1 Questions: 32 Slow queries: 0 Opens: 9 Flush tables: 1 Open table
s: 2 Queries per second avg: 0.031 Memory in use: 8332K Max memory used: 8372K
C:\>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
C:\>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
C:\>mysqladmin -u root reload
C:\>mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
C:\>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
创建一个新用户:
mysql> show databases;
+----------+
| Database |
+----------+
| mydata |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
mysql> use mydata
Database changed
mysql> create table tab1(col1 int(4) not null primary key auto_increme
-> col2 char(10) not null,
-> col3 char(20) not null);
Query OK, 0 rows affected (0.05 sec)
mysql> grant all on mydata.* to bitan@127.0.0.1 identified by 'bitan';
Query OK, 0 rows affected (0.02 sec)
mysql> use mysql;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)
mysql> select host,name,password from user;
ERROR 1054: Unknown column 'name' in 'field list'
mysql> select host,user,password from user;
+-----------+-------+------------------+
| host | user | password |
+-----------+-------+------------------+
| localhost | root | |
| build | root | |
| localhost | | |
| build | | |
| localhost | bitan | 000864941a82a3c6 |
+-----------+-------+------------------+
5 rows in set (0.00 sec)
限制用户权限到表和列：
mysql> grant select on mydata.tab1 to tab1@localhost identified by 'tab1'
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
C:\>mysql -u tab1 -p tab1
Enter password: ****
ERROR 1044: Access denied for user: 'tab1@localhost' to database 'tab1'
C:\>mysql -u tab1 -p mydata
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from tab1;
+------+-------+--------------+
| col1 | col2 | col3 |
+------+-------+--------------+
| 1 | aaaaa | aaaaaaaaaaaa |
| 2 | bbbbb | bbbbbbbbbbbb |
+------+-------+--------------+
2 rows in set (0.00 sec)
mysql> insert tab1 (col2,col3) values('bbbbb', 'bbbbbbbbbbbb');
ERROR 1142: insert command denied to user: 'tab1@localhost' for table 'tab1'
mysql> exit
Bye
C:\>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql
Database changed
mysql> grant insert on mydata.tab1 to tab1@localhost
-> ;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
C:\>mysql -u tab1 -p mydata
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from tab1
-> ;
+------+-------+--------------+
| col1 | col2 | col3 |
+------+-------+--------------+
| 1 | aaaaa | aaaaaaaaaaaa |
| 2 | bbbbb | bbbbbbbbbbbb |
+------+-------+--------------+
2 rows in set (0.00 sec)
mysql> insert tab1 (col2,col3) values('bbbbb', 'bbbbbbbbbbbb');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab1;
+------+-------+--------------+
| col1 | col2 | col3 |
+------+-------+--------------+
| 1 | aaaaa | aaaaaaaaaaaa |
| 2 | bbbbb | bbbbbbbbbbbb |
| 3 | bbbbb | bbbbbbbbbbbb |
+------+-------+--------------+
3 rows in set (0.00 sec)
mysql> update tab1 set col2='cccc',col3='cccccccccccc' where col1=3
-> ;
ERROR 1142: update command denied to user: 'tab1@localhost' for table 'tab1'
mysql> exit
Bye
C:\>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant update(col2) on mydata.tab1 to tab1@localhost;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
C:\>mysql -u tab1 -p mydata
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update tab1 set col2='cccc',col3='cccccccccccc' where col1=3
-> ;
ERROR 1143: UPDATE command denied to user: 'tab1@localhost' for column 'col3' in table 'tab1'
mysql> update tab1 set col2='cccc' where col1=3
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tab1;
+------+-------+--------------+
| col1 | col2 | col3 |
+------+-------+--------------+
| 1 | aaaaa | aaaaaaaaaaaa |
| 2 | bbbbb | bbbbbbbbbbbb |
| 3 | cccc | bbbbbbbbbbbb |
+------+-------+--------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
C:\>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant update on mydata.tab1 to tab1@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
C:\>mysql -u tab1 -p mydata
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> update tab1 set col2='cccc',col3='cccccccccccc' where col1=3
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tab1;
+------+-------+--------------+
| col1 | col2 | col3 |
+------+-------+--------------+
| 1 | aaaaa | aaaaaaaaaaaa |
| 2 | bbbbb | bbbbbbbbbbbb |
| 3 | cccc | cccccccccccc |
+------+-------+--------------+
3 rows in set (0.00 sec)
mysql>
一个用户将自己所拥有的权限赋给另一个用户：
C:\>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant select on mydata.* to user6@localhost identified by 'x' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> grant insert on mydata.* to user7@localhost identified by 'x' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
C:\>mysql -u user6 -p
Enter password: *
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant select on mydata.* to user7@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> grant update on mydata.* to user8@localhost identified by 'x';
ERROR 1044: Access denied for user: 'user6@localhost' to database 'mydata'
mysql> grant update on mydata.* to user7@localhost;
ERROR 1044: Access denied for user: 'user6@localhost' to database 'mydata'
mysql> grant all on *.* to user7@localhost;
ERROR 1045: Access denied for user: 'user6@localhost' (Using password: YES)
mysql> grant all on mydata.* to user7@localhost;
ERROR 1044: Access denied for user: 'user6@localhost' to database 'mydata'
mysql>
取消用户权限：
mysql> grant select,insert,update on mydata.tab1 to user6@localhost identified by 'x';
Query OK, 0 rows affected (0.06 sec)
mysql> revoke update on mydata.tab1 from user6@localhost;
Query OK, 0 rows affected (0.03 sec)
mysql> revoke insert on mydata.tab1 from user6@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> revoke all on mydata.tab1 from user6@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
C:\>mysql -u user6 -p
Enter password: *
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mydata;
ERROR 1044: Access denied for user: 'user6@localhost' to database 'mydata'
mysql> exit
Bye
C:\>mysql -u user6 -p mydata;
Enter password: *
ERROR 1044: Access denied for user: 'user6@localhost' to database 'mydata;'
删除一个用户：
C:\>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select host,user from user;
+-----------+----------------+
| host | user |
+-----------+----------------+
| % | user2localhost |
| build | |
| build | root |
| localhost | |
| localhost | root |
| localhost | user1 |
| localhost | user3 |
| localhost | user4 |
| localhost | user5 |
| localhost | user6 |
| localhost | user7 |
+-----------+----------------+
11 rows in set (0.02 sec)
mysql> delete from user where user='user7';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='user6';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='user5';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='user4';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='user3';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='user2';
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from user;
+-----------+----------------+
| host | user |
+-----------+----------------+
| % | user2localhost |
| build | |
| build | root |
| localhost | |
| localhost | root |
| localhost | user1 |
+-----------+----------------+
6 rows in set (0.00 sec)
mysql>
使用mysqldump备份一个数据库：
mysql> grant all on mydata.tab1 to user2@localhost identified by 'x';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
C:\>mysqldump -u user2 mydata > .\mydata.sql
mysqldump: Got error: 1045: Access denied for user: 'user2@localhost' (Using password: NO) when
trying to connect
C:\>mysqldump -u user2 -p mydata > .\mydata.sql
Enter password: *
C:\>mysql -u root mydata
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 68 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table tab2 (col1 int not null primary key auto_increment,
-> col2 char(20) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> exit
Bye
C:\>mysqldump -u user2 -p mydata > .\mydata.sql
Enter password: *
C:\>mysql -u root mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 70 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant all on mydata.tab2 to user2@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
C:\>mysqldump -u user2 -p mydata > .\mydata.sql
Enter password: *
C:\>
////////////////////////////////////////////////
C:\mydata.sql:
-- MySQL dump 9.11
--
-- Host: localhost Database: mydata
-- ------------------------------------------------------
-- Server version4.0.20a-debug
--
-- Table structure for table `tab1`
--
CREATE TABLE tab1 (
col1 int(4) NOT NULL auto_increment,
col2 char(10) NOT NULL default '',
col3 char(20) NOT NULL default '',
PRIMARY KEY (col1)
) TYPE=MyISAM;
--
-- Dumping data for table `tab1`
--
INSERT INTO tab1 VALUES (1,'aaaaa','aaaaaaaaaaaa');
INSERT INTO tab1 VALUES (2,'bbbbb','bbbbbbbbbbbb');
INSERT INTO tab1 VALUES (3,'cccc','cccccccccccc');
INSERT INTO tab1 VALUES (4,'ccccc','cccccccccccc');
--
-- Table structure for table `tab2`
--
CREATE TABLE tab2 (
col1 int(11) NOT NULL auto_increment,
col2 char(20) NOT NULL default '',
PRIMARY KEY (col1)
) TYPE=MyISAM;
--
-- Dumping data for table `tab2`
--
////////////////////////////////////////////////
使用mysqldump备份一个或多个表：
C:\>mysqldump -u user2 mydata tab1 -p > .\mydata.sql
Enter password: *
C:\>mysqldump -u user2 mydata tab1 tab2 -p > .\mydata.sql
Enter password: *
C:\>
从备份文件恢复一个数据库：
mysql> drop table tab1,tab2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> exit
Bye
C:\>mysql -u root mydata < .\mydata.sql
C:\>mysql -u root mydata
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 91 to server version: 4.0.20a-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| tab1 |
| tab2 |
+------------------+
2 rows in set (0.00 sec)
mysql>
发表评论
相关推荐
MySQL是一种广泛使用的开源关系型数据库管理系统，其丰富的命令行接口为数据库管理提供了极大的便利。以下是一些常用的MySQL命令，它们涵盖了基本的查询、操作和控制功能。 1. **显示当前数据库版本和日期**： ...
常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急...
一、MySQL常规使用 1. 安装与配置：MySQL的安装通常包括下载适合操作系统（如Windows、Linux或macOS）的安装包，按照向导进行安装，然后配置服务器参数，如设置root用户的密码、端口、数据存储位置等。 2. 数据库...
此外，项目包含的文件如`build.php`、`admin.php`、`index.php`是TP5框架的常规入口文件，分别用于后台管理、前台展示和整个应用的启动。`jhems.sql`是数据库的结构和初始数据文件，可以通过导入到MySQL服务器来创建...
常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急物资管理系统-常规应急...
《基于JSP与MySQL的图书馆管理系统详解》 图书馆管理系统是一个典型的Web应用，它结合了数据库技术与...通过深入研究和实践，不仅可以掌握JSP编程和MySQL数据库的使用，还能了解到Web应用开发的常规流程和设计原则。
MySQL数据库管理系统是全球最受欢迎的关系型数据库之一，广泛应用于各种规模的企业和项目中。为了方便管理和维护MySQL数据库，有许多工具被开发出来，其中“mysql数据库管理工具”就是一款实用的绿色英文版工具，专...
标题中的“MySQL 纯绿色免安装版本”指的是这个版本的MySQL不需要通过常规的安装程序进行安装，而是可以下载后直接解压到指定位置。这样的设计使得用户无需担心系统注册表的修改，避免了可能产生的系统冲突问题，...
毕业设计，基于SpringBoot+Vue+MySQL开发的常规应急物资管理系统，源码+数据库+开题报告+毕业论文+视频演示 当下，正处于信息化的时代，许多行业顺应时代的变化，结合使用计算机技术向数字化、信息化建设迈进。以前...
通过Navicat for MySQL，用户不仅可以对MySQL数据库进行常规管理，还可以对特定应用程序如ConvnetServer的配置进行深入操作，提高工作效率并保证数据的安全性。对于初学者和专业开发者来说，这都是一个非常有价值的...
│ 3_MySQL8非常规安装.avi │ 4_MySQL8常见客户端和启动相关参数.avi │ ├─新版MySQL DBA综合实战班 第02天 │ 10_MySQL Update课堂练习.mp4 │ 1_课后作业讲解.mp4 │ 2_MySQL权限系统介绍.mp4 │ 3_MySQL授权...
本常规应急物资管理系统是建立在B/S模式的基础之上，通过使用idea平台上编写相关的Java代码，实现对系统的功能模块的设计，使用MySQL数据库设计相关的数据表，实现对系统数据资源的管理和控制，使用SSM框架创建相关...
- **多版本支持**：学会如何使用Navicat管理不同版本的数据库，包括MySQL、MariaDB、Oracle、PostgreSQL和SQL Server等。 ### 总结 本文介绍了Navicat for MySQL的基本使用方法，包括系统需求、安装、注册授权、...
常规应急物资-常规应急物资系统-常规应急物资系统源码-常规应急物资管理系统-常规应急物资管理系统java代码-常规应急物资系统设计与实现-基于springboot的常规应急物资系统-基于Web的常规应急物资系统设计与实现-...
MySQL是世界上最受欢迎的关系型数据库管理系统之一，用于存储和管理数据。在这个名为"MySql练习5：模拟常规业务"的练习中，我们很显然会探讨如何利用MySQL来处理日常业务场景中的数据操作。通过这个练习，你可以深入...
毕业设计，基于SpringBoot+Vue+MySql开发的前后端分离的常规应急物资管理系统，内含完整源代码，数据库脚本，开题报告，视频教程 当下，正处于信息化的时代，许多行业顺应时代的变化，结合使用计算机技术向数字化、...
常规应急物资-常规应急物资系统-常规应急物资系统源码-常规应急物资管理系统-常规应急物资管理系统java代码-常规应急物资系统设计与实现-基于springboot的常规应急物资系统-基于Web的常规应急物资系统设计与实现-...