8.23 13.4-13.6

最新推荐文章于 2020-03-01 19:27:04 发布

weixin_34323858

最新推荐文章于 2020-03-01 19:27:04 发布

阅读量76

点赞数

原文链接：http://blog.51cto.com/12216458/2164421

版权

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 |

| test |

| test1 |

| db1 |

| test1 |

+---------+

10 rows in set (0.00 sec)

mysql> select db,user from mysql.db; 查询一张表中多个字段的值

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

| db | user |

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

| test | |

| test\_% | |

| 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; 查看表结构

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

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

| id | int(4) | 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 |

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

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)

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级或以上）时可能会备份的很慢

此时建议使用其他备份工具

转载于:https://blog.51cto.com/12216458/2164421

weixin_34323858

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
8.23 13.4-13.6

13.4 MySQL用户管理Mysql默认有一个root用户，权限很高可以给某个用户授权使用户的权限在合理范围内（仅对某个数据库有权限或仅对某个表有权限）：mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'hyc940421';授予所有权限给user1，指定user1只能通过ip地址127.0....
复制链接

扫一扫