MySQL 的安装
MySQL 的安装
This slide is based on Jeffrey D. Ullman's work, which can be download from his website.
and based on 刘增杰、张少军 《MySQL 5.5 从零开始学》
References: Michael Kofler, The Definitive Guide to MySQL 5
MySQL 5.1 Reference Manual
MySQL 有很多版本, 详见 https://downloads.mysql.com/archives/. 这里给出了 MySQL 5.5 安装相关的视频.
mysql 只是一个小程序, 它将命令传至 MySQL server, 并显示结果. 它是与 MySQL server 交互的客户端程序.
mysql -uodbc
mysql -u root -p -h localhost --default-character-set=utf8 test_bar
类似于
现在我们来建立一个普通帐户, 使具有有限的权限.
mysql> CREATE USER haifeng@localhost IDENTIFIED BY '123456';
上面这条语句创建了一个新的用户, 名为
mysql> GRANT ALL ON mydb.* to haifeng@localhost;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON mydb.*
-> TO 'haifeng'@'localhost'
-> IDENTIFIED BY '123456';
查看 MySQL 中的用户
mysql> use mysql mysql> select * from user\G;
mysql> INSERT INTO user
-> (host, user, password,
-> select_priv, insert_priv, update_priv)
-> VALUES ('localhost', 'haifeng',
-> PASSWORD('123456'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;
这里使用
MySQL 5.7.14 中还有下面的几项
root 用户的安全对于保证 MySQL 的安全非常重要. 修改 root 用户的密码有很多种方法.
mysqladmin -u username -h hostname -p password newpasswd
mysqladmin -u root -p password "654321" Enter password:
这里在
mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
使用 UPDATE 语句修改 user 表
使用 SET PASSWORD 语句
mysql>SET PASSWORD=PASSWORD("654321");
新密码必须用
SET PASSWORD FOR 'haifeng'@'localhost'=PASSWORD('newpasswd');
使用 SET PASSWORD 语句
mysql>SET PASSWORD=PASSWORD("654321");
我们以数据库
mysql> show databases;
mysql> use mysql;
mysql> show tables;
mysql> show columns from user; // 与 desc user 一致
mysql> show index from user;
mysql> SHOW TABLE STATUS LIKE '%user%'\G;
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 68
Data_length: 548
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2012-11-03 15:14:11
Update_time: 2014-11-11 10:09:47
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)
mysql 的一些命令在使用时要特别小心, 比如
mysql> DELETE FROM tablename
将删除表中的所有记录, 而且无法恢复.
如果希望能避免这样的误操作, 则可以在登录 mysql 时使用
mysql> delete from friends;
ERROR 1175 (HY000): You are using safe update mode and
you tried to update a table without a WHERE that uses a KEY column
此时如果要执行
| 简写 | 命令 | 解释 |
|---|---|---|
| 取消输入命令 | ||
| 显示常用的一些命令 | ||
| 退出 mysql, 在 Unix/Linux 中也可以使用 Ctrl+D | ||
| 显示 MySQL 服务器的状态信息 | ||
| 将所有的输入输出自动记录到指定的文件中 | ||
| 结束 |
||
| 指定哪个数据库为当前的数据库. | ||
| 执行保存在文件 filename 中的 SQL 命令. 命令之间必须使用分号隔开. |
注意
MySQL 会保存之前的命令, 可以使用上下光标键提取之前的历史命令. 这些命令即使在系统关闭之后仍会被保存.
目前大多 Linux 分发版默认使用的是 utf8 字符编码. 如果 mysql 与 MySQL 服务器之间的通讯并不是使用同一个字符集, 则 international special characters 将会被弄砸. 这个问题可以通过下面两个方式来解决.
可以使用
以下是在 wampserver 默认安装下, 启动其中的 mysql 后的状态.
mysql> \s -------------- Connection id: 19 Current database: mysql Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.14 MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 29 min 40 sec Threads: 1 Questions: 88 Slow queries: 0 Opens: 128 Flush tables: 1 Open tables: 121 Queries per second avg: 0.049 --------------
MySQL 是一个多用户数据库, 具有功能强大的访问控制系统, 可以为不同用户指定允许的权限.
MySQL 服务器通过权限表来控制用户对数据库的访问, 权限表存放在 mysql 数据库中, 有 mysql_install_db 脚本初始化.
存储账号权限的信息表主要有:
当用户与服务器之间建立连接时, 输入的帐户信息（包含主机名、用户名称和密码）必须与
修改用户密码时, 实际上就是修改
mysql> DESC user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.05 sec)
mysql> SELECT Host, User FROM user; +-----------+---------+ | Host | User | +-----------+---------+ | 127.0.0.1 | root | | ::1 | root | | localhost | | | localhost | haifeng | | localhost | root | +-----------+---------+ 5 rows in set (0.00 sec)
查询服务器是否支持
mysql> SHOW VARIABLES LIKE '%have_openssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | +---------------+----------+ 1 row in set (0.00 sec)
资源控制列的字段用来限制用户使用的资源:
mysql> SELECT max_questions, max_updates, max_connections, max_user_connections FROM user WHERE User='root'; +---------------+-------------+-----------------+----------------------+ | max_questions | max_updates | max_connections | max_user_connections | +---------------+-------------+-----------------+----------------------+ | 0 | 0 | 0 | 0 | | 0 | 0 | 0 | 0 | | 0 | 0 | 0 | 0 | +---------------+-------------+-----------------+----------------------+ 3 rows in set (0.00 sec) mysql> SELECT max_questions, max_updates, max_connections, max_user_connections FROM user WHERE User='haifeng'; +---------------+-------------+-----------------+----------------------+ | max_questions | max_updates | max_connections | max_user_connections | +---------------+-------------+-----------------+----------------------+ | 0 | 0 | 30 | 0 | +---------------+-------------+-----------------+----------------------+ 1 row in set (0.00 sec)
mysql> DESC db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.04 sec)
MySQL 5.6.17 版本中没有
mysql> SHOW TABLES LIKE '%host%'; Empty set (0.00 sec)
mysql> SELECT version(); +-----------+ | version() | +-----------+ | 5.6.17 | +-----------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM db WHERE 1\G;
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW TABLES LIKE "%tables%"; +----------------------------+ | Tables_in_mysql (%tables%) | +----------------------------+ | tables_priv | +----------------------------+ 1 row in set (0.01 sec)
mysql> DESC tables_priv\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Grantor
Type: char(77)
Null: NO
Key: MUL
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Table_priv
Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')
Null: NO
Key:
Default:
Extra:
*************************** 8. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
8 rows in set (0.03 sec)
mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
-> FROM information_schema.COLUMNS
-> WHERE table_name='tables_priv'
-> AND table_schema='mysql';
+------------------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME ) |
+------------------------------------------------------------------+
| Host,Db,User,Table_name,Grantor,Timestamp,Table_priv,Column_priv |
+------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> SHOW TABLES LIKE "%columns%"; +-----------------------------+ | Tables_in_mysql (%columns%) | +-----------------------------+ | columns_priv | +-----------------------------+ 1 row in set (0.01 sec)
mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
-> FROM information_schema.COLUMNS
-> WHERE table_name='columns_priv'
-> AND table_schema='mysql';
+-----------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME ) |
+-----------------------------------------------------------+
| Host,Db,User,Table_name,Column_name,Timestamp,Column_priv |
+-----------------------------------------------------------+
1 row in set (0.02 sec)
mysql> DESC columns_priv\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Column_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
7 rows in set (0.04 sec)
也可以使用
mysql> SHOW FULL COLUMNS FROM columns_priv\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Collation: utf8_bin
Null: NO
Key: PRI
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Collation: utf8_bin
Null: NO
Key: PRI
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 3. row ***************************
Field: User
Type: char(16)
Collation: utf8_bin
Null: NO
Key: PRI
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Collation: utf8_bin
Null: NO
Key: PRI
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 5. row ***************************
Field: Column_name
Type: char(64)
Collation: utf8_bin
Null: NO
Key: PRI
Default:
Extra:
Privileges: select,insert,update,references
Comment:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Collation: NULL
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
Privileges: select,insert,update,references
Comment:
*************************** 7. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Collation: utf8_general_ci
Null: NO
Key:
Default:
Extra:
Privileges: select,insert,update,references
Comment:
7 rows in set (0.01 sec)
mysql> SELECT GROUP_CONCAT( COLUMN_NAME )
-> FROM information_schema.COLUMNS
-> WHERE table_name='procs_priv'
-> AND table_schema='mysql';
+--------------------------------------------------------------------+
| GROUP_CONCAT( COLUMN_NAME ) |
+--------------------------------------------------------------------+
| Host,Db,User,Routine_name,Routine_type,Grantor,Proc_priv,Timestamp |
+--------------------------------------------------------------------+
1 row in set (0.07 sec)
mysql> DESC procs_priv\G;
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Routine_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Routine_type
Type: enum('FUNCTION','PROCEDURE')
Null: NO
Key: PRI
Default: NULL
Extra:
*************************** 6. row ***************************
Field: Grantor
Type: char(77)
Null: NO
Key: MUL
Default:
Extra:
*************************** 7. row ***************************
Field: Proc_priv
Type: set('Execute','Alter Routine','Grant')
Null: NO
Key:
Default:
Extra:
*************************** 8. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
8 rows in set (0.03 sec)
可以在登录系统时, 指定执行某个 SQL 语句, 使用 -e 选项, 比如
C:\Users\haifeng>mysql -u root -p test_zero -e "SHOW TABLES;" Enter password: ****** +---------------------+ | Tables_in_test_zero | +---------------------+ | account | | file_table | | tmp | | tmp11 | | tmp112 | | tmp12 | | tmp4 | | tmp5 | | tmp6 | | tmp7 | | tmp8 | | tmp9 | | worker | +---------------------+
创建新用户, 必须要有相应的权限.
有两种方式创建用户,
权限管理主要是指对于 MySQL 系统中的用户(即
所有用户的权限都存储在 MySQL 的权限表中. 权限表有
全局权限适用于一个给定服务器中的所有数据库, 这些权限存储在
数据库权限适用于一个给定数据库中的所有目标(表、存储过程、函数、触发器、视图等等), 这些权限存储在
表权限适用于一个给定表中的所有属性(列), 这些权限存储在
属性(列)权限适用于一个给定表中的某个属性(列), 这些权限存储在
在 MySQL 中, 必须是拥有
要使用
GRANT priv_type
使用
也可以直接在
mysql> SHOW GRANTS FOR 'root'@'localhost'; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW GRANTS FOR root@localhost; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ mysql> SHOW GRANTS FOR `root`@`localhost`; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'127.0.0.1'; +---------------------------------------------------------------------+ | Grants for root@127.0.0.1 | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR root@127.0.0.1; +---------------------------------------------------------------------+ | Grants for root@127.0.0.1 | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR `root`@`127.0.0.1`; +---------------------------------------------------------------------+ | Grants for root@127.0.0.1 | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR "root"@"127.0.0.1"; +---------------------------------------------------------------------+ | Grants for root@127.0.0.1 | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR haifeng@127.0.0.1; ERROR 1141 (42000): There is no such grant defined for user 'haifeng' on host '127.0.0.1' mysql> SHOW GRANTS FOR haifeng@localhost; +-------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for haifeng@localhost | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'haifeng'@'localhost' IDENTIFIED BY PASSWORD '*2A26DC91ED99AAF2AF0256BC32CDA93B8298724C' WITH MAX_CONNECTIONS_PER_HOUR 30 | | GRANT SELECT, UPDATE (name, id) ON `test`.`table1` TO 'haifeng'@'localhost' | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
User: haifeng
Password: *2A26DC91ED99AAF2AF0256BC32CDA93B8298724C
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 30
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='root'\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *2A26DC91ED99AAF2AF0256BC32CDA93B8298724C
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
*************************** 2. row ***************************
Host: 127.0.0.1
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
*************************** 3. row ***************************
Host: ::1
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
3 rows in set (0.00 sec)
MySQL 的访问控制分为两个阶段:
MySQL 使用
服务器只有在
建立连接后, 服务器进入访问控制的第二个阶段, 即请求核实阶段. 对此连接上进来的每个请求, 服务器检查用户要执行的操作, 然后检查是否有足够的权限来执行它. 此时, MySQL 会依次检查
MySQL 通过逐级向下检查权限表, 从
匿名帐户是指
mysql> SELECT * FROM mysql.user WHERE User=''\G;
*************************** 1. row ***************************
Host: localhost
User:
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
password_expired: N
1 row in set (0.00 sec)
这可以使得任何用户都可以登录到 MySQL 系统, 不过其权限有限, 可以对
C:\Users\haifeng>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 637 Server version: 5.6.17 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test_bar |
| test_db3 |
| test_fruits |
| test_my_first_db |
| test_products |
| test_transaction |
| test_zero |
+--------------------+
8 rows in set (0.01 sec)
mysql> SELECT * FROM worker;
+----+---------+
| id | Name |
+----+---------+
| 1 | Jimy |
| 2 | Tom |
| 3 | Kate |
| 4 | Kavin |
| 5 | Michael |
| 6 | Nick |
+----+---------+
6 rows in set (0.01 sec)
mysql> DESC worker;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)
mysql> INSERT INTO worker (Name) VALUES ("David");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM worker;
+----+---------+
| id | Name |
+----+---------+
| 1 | Jimy |
| 2 | Tom |
| 3 | Kate |
| 4 | Kavin |
| 5 | Michael |
| 6 | Nick |
| 7 | David |
+----+---------+
7 rows in set (0.00 sec)
mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
mysql>
在生产环境中, 匿名帐户一定要删除.
DELETE FROM mysql.user WHERE User='';
如果用户
C:\Users\haifeng>mysql -uhaifeng -p Enter password: ***** ERROR 1045 (28000): Access denied for user 'haifeng'@'localhost' (using password: YES)
使用
C:\Users\haifeng>mysql -uroot -p Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 642 Server version: 5.6.17 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> SELECT Host, User, Password, plugin FROM user WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
User: haifeng
Password: *9DF4837AD74328106EA4528EEA6DC38ED2CA2AD8
plugin: mysql_native_password
1 row in set (0.00 sec)
使用
mysql> SET PASSWORD FOR haifeng@localhost=PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host, User, Password, plugin FROM user WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
User: haifeng
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
plugin: mysql_native_password
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.00 sec)
通知用户
C:\Users\haifeng>mysql -uhaifeng -p Enter password: [input: 123456] Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 643 Server version: 5.6.17 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> SET PASSWORD=PASSWORD('NewPassword');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User, Host, Password FROM mysql.user WHERE User='haifeng'\G; ERROR 1142 (42000): SELECT command denied to user 'haifeng'@'localhost' for table 'user' ERROR: No query specified
mysql> SELECT User, Host, Password FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
User: haifeng
Host: localhost
Password: *EC760840CDAC6AF05321A7DDD05A84E18892AA97
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
User: haifeng
Password: *EC760840CDAC6AF05321A7DDD05A84E18892AA97
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 30
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.db WHERE User='haifeng'\G; Empty set (0.00 sec)
mysql> SELECT * FROM mysql.tables_priv WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
Db: test
User: haifeng
Table_name: table1
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv: Update
1 row in set (0.04 sec)
mysql> SELECT * FROM mysql.columns_priv WHERE User='haifeng'\G;
*************************** 1. row ***************************
Host: localhost
Db: test
User: haifeng
Table_name: table1
Column_name: id
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
*************************** 2. row ***************************
Host: localhost
Db: test
User: haifeng
Table_name: table1
Column_name: name
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.procs_priv WHERE User='haifeng'\G; Empty set (0.00 sec)
假设目前登录的是 root 用户, 连接的是 mysql 数据库.
mysql> select current_user; +----------------+ | current_user | +----------------+ | root@localhost | +----------------+ mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+
先创建用户, 比如
CREATE USER guest@localhost IDENTIFIED BY 'hello_world';
如果只写
mysql> CREATE USER guest IDENTIFIED BY 'hello_world'; Query OK, 0 rows affected (0.07 sec) mysql> select user, host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | guest | % | | haifeng | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 6 rows in set (0.00 sec)
当然, 可以更新 host 属性的值为 localhost.
mysql> UPDATE user SET host='localhost' WHERE user='guest'; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select user,host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | guest | localhost | | haifeng | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 6 rows in set (0.00 sec)
接着, 赋予其对数据库
GRANT SELECT, INSERT ON Blog.* TO 'guest'@'localhost' with grant option;
注意, 如果不带后面的
mysql> GRANT SELECT, INSERT ON Blog.* TO 'guest'@'localhost'; ERROR 1410 (42000): You are not allowed to create a user with GRANT 1 row in set (0.00 sec)
mysql> GRANT SELECT, INSERT ON Blog.* TO 'guest'@'localhost' with grant option; Query OK, 0 rows affected (0.02 sec)