13.4 MySQL用户管理
Mysql默认有一个root用户,权限很高
可以给某个用户授权使用户的权限在合理范围内(仅对某个数据库有权限或仅对某个表有权限):
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'hyc940421';
授予所有权限给user1,指定user1只能通过ip地址127.0.0.1登录,user1认证密码为hyc940421
*.* 表示所有的库中所有的表,第一个*表示所有的库,第二个*表示所有的表
@后面127.0.0.1可以写成%,此时%表示所有的ip
为了安全,grant的命令语句不会被记录到命令历史中
Query OK, 0 rows affected (0.03 sec)
登录测试:
[root@hyc-01-01 ~]# mysql -uuser1 -phyc940421
不指定登录地址时默认会从socket登录
由于先前为user1用户指定的登录地址为ip地址127.0.0.1,所以此处登录失败
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
[root@hyc-01-01 etc]# mysql -uuser1 -phyc940421 -h127.0.0.1
指定登录的源ip
对user1重新授权:
mysql> grant all on *.* to `user1`@`localhost` identified by 'hyc940421';
执行grant必须使用root权限
localhost即针对socket
Query OK, 0 rows affected (0.02 sec)
再测试:
[root@hyc-01-01 etc]# mysql -uuser1 -phyc940421
此时从socket登录成功(不加-h指定地址)
…
mysql>
登出mysql时可使用quit、exit或ctrl+d
有针对的授权:
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.31.129' identified by 'hyc940421';
授权用户user2从192.168.31.129登录,登录密码hyc940421,对库db1下的所有表拥有SELECT,UPDATE,INSERT权限
Query OK, 0 rows affected (0.01 sec)
查看授权:
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 user1@`127.0.0.1`; 查看指定用户user1的授权
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1 |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
加\G:
mysql> show grants\G;
*************************** 1. row ***************************
Grants for user1@localhost: GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE'
1 row in set (0.00 sec)
ERROR:
No query specified
user1用户在127.0.0.1登录时有以下权限,在socket登录时也想拥有相同的权限:
mysql> show grants for user1@127.0.0.1;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1 |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE' |
| GRANT SELECT, INSERT ON `db1`.* TO 'user1'@'127.0.0.1' |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE'
此时可以将上面user1@127.0.0.1的grant信息复制粘贴过来并将127.0.0.1替换为localhost
-> ;
需要注意,当执行一组grant命令时,若后面还有grant命令,则后面不跟;直接回车,在换行后再敲;并回车,然后执行本组的下一个grant命令,直到敲完本组最后一个grant命令时直接在后面跟;然后回车(一般针对同一个用户和地址的grant命令为同一组grant命令)
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT ON `db1`.* TO 'user1'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)
此时即使不知道user1的密码,只要将show grants for user1@127.0.0.1命令显示的grant命令复制,用户名不变,修改地址为localhost后执行就可以让user1@localhost拥有和user1@127.0.0.1相同的密码和权限
即使不知道user1'@'localhost的密码,也可以用grant为user1'@'localhost添加权限:
mysql> show grants for user1@192.168.100.1;
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.100.1 |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.100.1' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user1'@'192.168.100.1' |
| GRANT ALL PRIVILEGES ON `test1`.* TO 'user1'@'192.168.100.1' |
+---------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> grant all on test.* to 'user1'@'192.168.100.1';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user1@192.168.100.1;
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.100.1 |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.100.1' IDENTIFIED BY PASSWORD '*A2862550F7B805609ACB48DA00EDF4642255A5DE' |
| GRANT ALL PRIVILEGES ON `test1`.* TO 'user1'@'192.168.100.1' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user1'@'192.168.100.1' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'192.168.100.1' |
+---------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
所有grant命令的行为均需要在root下完成
13.5 常用sql语句
数据库常用引擎
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
…
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
该查询结果显示数据库引擎为MyISAM,默认mysql数据库中所有的表使用该引擎
Myisam引擎会自动统计表的行数,所以在用户查询表的行数时执行较快
InnoDB在不会自动统计表的行数,每次用户查询表的行数时执行较慢
在使用数据库时应尽量避免select *…或select count(*)…之类的操作,因为这类操作比较消耗资源
SELECT 查询
mysql> select count(*) from mysql.db; 查看db表的行数
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
即使切换了数据库也可以查看另一个库的某个表的信息
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from mysql.db; 在test1数据库下查看
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
查看mysql库下的db表中的所有数据
mysql> select * from mysql.db\G; 加\G是为了显示的更规整
查询某个表中某个字段的值
mysql> select db from mysql.db; 查询db表中db字段的值
+---------+
| db |
+---------+
| test |
| test\_% |
| db1 |
| db1 |
| test |
| test1 |
| db1 |
| db1 |
| db1 |
| test1 |
+---------+
10 rows in set (0.00 sec)
mysql> select db,user from mysql.db; 查询一张表中多个字段的值
+---------+-------+
| db | user |
+---------+-------+
| test | |
| test\_% | |
| db1 | user1 |
| db1 | user1 |
| test | user1 |
| test1 | user1 |
| db1 | user2 |
| db1 | user1 |
| db1 | user3 |
| test1 | user3 |
+---------+-------+
10 rows in set (0.01 sec)
在db表中模糊查询host字段匹配192.168.*的行,显示匹配行的所有内容
mysql> select * from mysql.db where host like '192.168.%'\G;
like代表模糊匹配
INSERT 插入
在表中插入一行数据
mysql> desc test1.test1; 查看表结构
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from test1.test1; 插入前表为空
Empty set (0.01 sec)
mysql> insert into test1.test1 values(1,'lly'); 在表test1中插入数据(1,lly)
mysql中对字符串的操作一般需要加单引号
Query OK, 1 row affected (0.02 sec)
mysql> select * from test1.test1; 插入后
+------+------+
| id | name |
+------+------+
| 1 | lly |
+------+------+
1 row in set (0.00 sec)
Update 更新(修改)
将匹配条件(id=1)的行中的name字段都修改为aaa
mysql> update test1.test1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test1.test1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
+------+------+
2 rows in set (0.00 sec)
Delete 删除
将test1表中id=1的行删除
mysql> delete from test1.test1 where id=1;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from test1.test1;
Empty set (0.00 sec)
truncate 清空
mysql> truncate test1.test1; 直接清空test1库中test1表的全部数据,但表结构仍然保留
Query OK, 0 rows affected (0.04 sec)
mysql> select * from test1.test1;
Empty set (0.00 sec)
Drop 销毁
mysql> drop table test1; 执行drop后表中的数据和表本身都会消失
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test1.test1; 再查询该表时表已不存在
ERROR 1146 (42S02): Table 'test1.test1' doesn't exist
mysql> show databases; 执行前
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; 执行后,数据库test消失
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
+--------------------+
4 rows in set (0.01 sec)
13.6 MySQL数据库备份恢复
备份库:
[root@hyc-01-01 ~]# mysqldump -uroot -p123456 mysql > /tmp/mysqlbak.sql
使用root用户通过socket登录备份数据库中的mysql库,将备份的内容重定向到/tmp/mysqlbak.sql中
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 ~]# ls /tmp/mysqlbak.sql
/tmp/mysqlbak.sql
若不将内容重定向到某个文件则备份的内容会被打印在屏幕上
恢复库:
[root@hyc-01-01 ~]# mysql -uroot -p123456 -e "create database mysql2"
新建数据库mysql2
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 ~]# mysql -uroot -p123456 mysql2 < /tmp/mysqlbak.sql
将mysql备份文件恢复到库mysql2中
Warning: Using a password on the command line interface can be insecure.
[root@hyc-01-01 ~]# mysql -uroot -p123456 mysql2
后跟库名称mysql2,则用户登录mysql数据库后直接进入库mysql2
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59
Server version: 5.6.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> select database();
查看当前所在的库
+------------+
| database() |
+------------+
| mysql2 |
+------------+
1 row in set (0.00 sec)
mysql> show tables; 查看mysql2数据库的表
+---------------------------+
| Tables_in_mysql2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables; 查看mysql库中的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
两个库的表信息和数量完全一样
备份表:
[root@hyc-01-01 ~]# mysqldump -uroot -p123456 mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
备份mysql库中的user表到/tmp/user.sql
恢复表:
[root@hyc-01-01 ~]# mysql -uroot -p123456 mysql2 < /tmp/user.sql
备份使用mysqldump,恢复使用mysql
Warning: Using a password on the command line interface can be insecure.
将/tmp/user.sql中备份的mysql下的user表恢复到库mysql2下的user表
恢复时,若需要恢复的库或表已存在则会先执行drop,再重新创建库或表,然后再一步步插入每一行的数据
备份所有库:
[root@hyc-01-01 ~]# mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql
-A表示所有库
Warning: Using a password on the command line interface can be insecure.
查看备份所有库的文件:
[root@hyc-01-01 ~]# less /tmp/mysql_all.sql
…
-- Current Database: `mysql` 备份了三个库
…
-- Current Database: `mysql2`
…
-- Current Database: `test1`
…
仅备份表结构:
[root@hyc-01-01 ~]# mysqldump -uroot -p123456 -d mysql2 > /tmp/mysql2.sql
-d 指定只备份表结构,不备份数据
Warning: Using a password on the command line interface can be insecure.
查看备份表结构的文件:
[root@hyc-01-01 ~]# less /tmp/mysql2.sql
查看后发现文件中包含了drop、create等对表或库的操作,但没有insert等插入数据的操作
Mysqldump适用于备份量较小时使用,当数据量较大(G级或以上)时可能会备份的很慢
此时建议使用其他备份工具