独角兽企业重金招聘Python工程师标准>>>
1、Mysql用户管理;
场景,为了安全,新建的站点,创建新的用户,或者给已有用户授权,对某个库或者某个表有权限;
语法: grant all on *.* to 'user'@'127.0.0.1' identified by 'password';
grant 后 表示授权: all 表示所有权限( inser t delete update select );
on 后 表示给那个库,那个表授权( *.* )表示所有库下面的所有表;
to 后 表示给那个用户授予权限;
@ 后 表示来源IP,只有来源IP是这个时才允许访问;
by 后 表示密码;
注释:在mysql界面下,如果输错了字符,并且按了回车键,只需再次输入分号(;),则会退出到登录界面;
注释:退出mysql的命令: exit quit ctrl+d
1:创建普通用户user1;
grant all on *.* to 'user'@'127.0.0.1' identified by '123456';
注释:'user'@'127.0.0.1' 表示指定用户@来源IP(指定IP时可以写%,表示指定所有IP),如果指定来源IP,只能通过来源IP来登录了;
注释*.* 表示所有库.所有表(第一个*表示所有库), mysql.* 表示mysql下的所有表;
注释identified by '123456' 表示指定mysql的密码;
注释: grant的语句是不会记录到命令历史里的,因为不安全;
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';Query OK, 0 rows affected (0.00 sec)
2:退出mysql,使用用户user1登录;
[root@localhost_001 ~]# mysql -uuser1 -p123456Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
3:使用user1会看到登录失败,因为它默认是sock,需要指定-h指定IP来登录;会看到以user1用户登录数据库;
[root@localhost_001 ~]# mysql -uuser1 -p123456 -h127.0.0.1Welcome to the MySQL monitor. Commands end with ; or \g.mysql>
注释:那如何不指定-h来登录,需要给localhost授权本地登录,然后用sock去连接;
mysql> grant all on *.* to 'user1'@'localhost' identified by '123456';Query OK, 0 rows affected (0.00 sec)
4:现在不知道-h也可以登录了,因为现在授权是针对的localhost;
[root@localhost_001 ~]# mysql -uuser1 -p123456Welcome to the MySQL monitor. Commands end with ; or \g.mysql>
2、针对具体的权限去授权;
格式:grant select update insert on db.* to 'user2'@'192.168.149.130' idenfified by '123456';
针对来源IP是192.168.149.130的user2用户,给它授予db库的(select update intsert)的权限;
注释:show grants; 查看当前用户的权限(登录某一个用户的情况下);
1:运行第一条命令,创建user2用户及来源IP192.168.149.130,不给删除的权限;如下;
mysql> grant select,update,insert on db.* to 'user3'@'192.168.149.130' identified by '123456';Query OK, 0 rows affected (0.00 sec)
2:然后使用来源IP192.168.149.130登录user3用户,并执行删除操作(以上并没有给删除的权限);
mysql> drop table db1.t1;ERROR 1142 (42000): DROP command denied to user 'user3'@'localhost' for table 't1'而插入操作就可以执行;mysql> insert into db1.t1 values (1, 'abc');Query OK, 1 row affected (0.00 sec)
3:show grants; 查看当前用户的权限;
[root@localhost_001 ~]# mysql -uuser3 -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.mysql> show grants;+--------------------------------------------------------------------------------------------+| Grants for user3@localhost|+--------------------------------------------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE ON *.* TO 'user3'@'localhost' IDENTIFIED BY PASSWORD <secret> |+--------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
注释:show grants for user3@'192.168.148.130'; 查看指定用户user3的权限;
mysql> show grants for user3@'192.168.149.130';+-------------------------------------------------------------------------------------------------------------------------------------+| Grants for user3@192.168.149.130|+-------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE ON *.* TO 'user3'@'192.168.149.130' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
有一种情况,比如用user3给192.168.149.130做了授权,发现不够,还需要给192.168.149.132做授权,也就是说user3不仅要使用192.168.149,130连接,也需要使用192.168.149,132连接,这时候需要把授权的命令都执行一遍;
这时候就可以通过show grants for user3@'192.168.149.130'把指定用户额权限打印出来,然后直接复制在运行(修改IP地址)参数即可;
mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO 'user'@'192.168.149.130' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)
查看user3及来源IP是192.168.149.132;如下;
mysql> show grants for user3@'192.168.149.132';+-------------------------------------------------------------------------------------------------------------------------------------+| Grants for user3@192.168.149.132|+-------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, INSERT, UPDATE ON *.* TO 'user3'@'192.168.149.132' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
注释:在使用show grant来查看时,除了IP地址不一样,其他都相同;
注释:有时候在不知mysql的密码时,在创建用户时也可以这样来指定密码;
常用sql语句,增 删 改 查;
select count(*) from mysql.user;select * from mysql.db;select db from mysql.db;select db,user from mysql.db;select * from mysql.db where host like '192.168.%';insert into db1.t1 values (1, 'abc');update db1.t1 set name='aaa' where id=1;truncate table db1.t1;drop table db1.t1;drop database db1;
1:查看数据的所有表; show tables; 查看表的行数; show count(*) from mysql.user;
mysql> select count(*) from mysql.user;+----------+| count(*) |+----------+| 18 |+----------+1 row in set (0.00 sec)
注释:查看所有的内容 select * from mysql.db;(这样看起来会很乱) ——>可以在后面加上\G,如select * from mysql.db\G;
不建议使用上面命令select *命令,会比较耗费资源和内存;
myisam引擎,能够自动统计行,select会比较快;
innodb引擎,不会自动统计行数,每次查询每次统计,比较耗费资源;
3:查看db库的所有内容(第一个db是字段);
mysql> select db from mysql.db;+---------+| db|+---------+| test || test\_% || db|| db|+---------+4 rows in set (0.01 sec)
4:查看db字段和user字段; select db,user from mysql.db;
mysql> select db,user from mysql.db;+---------+-------+| db| user |+---------+-------+| test | || test\_% | || db| user2 || db| user2 |+---------+-------+4 rows in set (0.00 sec)
注释:查询 select * from mysql.db where host like '192.168.%'; like 就是模糊匹配;
2、插入语句;
1:查看创建的表; desc db1.t1; 发现内容为空:select * from db1.t1;
mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id | int(4) | YES || NULL | || name | char(40) | YES || NULL | |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)
2:插入一条数据; insert into db1.t1 values (2,'def');
mysql> insert into db1.t1 values (2, 'def');Query OK, 1 row affected (0.00 sec)mysql> insert into db1.t1 values (2, 234);Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 1 | abc || 2 | def || 1 | 234 || 1 | 234 || 3 | 256 || 2 | 234 |+------+------+6 rows in set (0.00 sec)
注释:在插入一条数据时,name字段是一个字符串,字符串需要加上一个双引号,而数字则不需要加双引号;
3:update操作;
删除db1.t1表中id为1的的数据; delete from db1.t1 where id=1;
mysql> delete from db1.t1 where id=1;Query OK, 3 rows affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id | name |+------+------+| 2 | def || 3 | 256 || 2 | 234 |+------+------+3 rows in set (0.00 sec)
truncate 清空一个表; truncate db1.t1;
mysql> truncate db1.t1;Query OK, 0 rows affected (0.18 sec)mysql> select * from db1.t1;Empty set (0.00 sec)mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id | int(4) | YES || NULL | || name | char(40) | YES || NULL | |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)
注释:即使表清空了,但是字段内容还是在的;
truncate只是清空表的内容,而drop会删除表的内容并把表的框架也删除;
mysql> drop table db1.t1;#删除表;Query OK, 0 rows affected (0.01 sec)mysql> select * from db1.t1;ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
truncate db1.t1;清空一个表;
drop tables db1.t1; 删除表;
drop databases db1;删除数据库;
注释:以上操作尽量少用,要是数据库没了就玩完了;
4、mysql的数据库的备份和恢复; mysqldump
注释:在执行mysqldump -uroot -pnihao123! 直接回车时候会看到显示好多信息,屏幕上显示的这些就是备份的数据;
备份库;备份mysql的库到/tmp/mysqbak.sql
[root@localhost_001 ~]# mysqldump -uroot -pnihao123! mysql > /tmp/mysqlbak.sqlWarning: Using a password on the command line interface can be insecure.
恢复库;可以先手动创建一个mysql2库用来恢复;
[root@localhost_001 ~]# mysql -uroot -pnihao123! -e "create database mysql2"Warning: Using a password on the command line interface can be insecure.[root@localhost_001 ~]# mysql -uroot -pnihao123! mysql2 < /tmp/mysqlbak.sql Warning: Using a password on the command line interface can be insecure.
注释:在后面加一个mysql2 就会进入到mysql2数据库里面;
[root@localhost_001 ~]# mysql -uroot -pnihao123! mysql2
查看当前所在的库; select database();
mysql> select database();+------------+| database() |+------------+| mysql2|+------------+1 row in set (0.00 sec)
备份表:针对数据库里的一个表备份,只需要在库后面加上表的名字即可备份;
注释:先库再表,中间是空格;
备份的时候,库存在的话,先把库drop掉,然后创建库,表存在的话,先把表drop掉,然后创建表,然后在一步一步的插入每一行数据;
备份表: mysqldump -uroot -pnihao123! mysql user > /tmp/user.sql
[root@localhost_001 ~]# mysqldump -uroot -pnihao123! mysql user >/tmp/user.sqlWarning: Using a password on the command line interface can be insecure.
恢复表: mysql -uroot -pnihao123! mysql2 < /tmp/user.sql
[root@localhost_001 ~]# mysql -uroot -pnihao123! mysql2 < /tmp/user.sql Warning: Using a password on the command line interface can be insecure.
备份所有库; -A 是所有库的意思;
[root@localhost_001 ~]# mysqldump -uroot -pnihao123 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.
也可以只备份表结构,不会备份数据; -d
[root@localhost_001 ~]# mysqldump -uroot -pnihao123 -d > /tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.
示例:两个机器的库备份,一个库备份到另一台机器;
首先两台机器能够通信;
然后mysqldump -h远程mysql的IP -uuser -ppassword dbname > /本地bakup.sql
这样既可备份;