12月6日任务

13.4 mysql用户管理 (创建用户以及授权)
create user 'test'@'%' identified by '123456';    这种方法可以用来创建用户
查询mysql当前有多少个用户 
use mysql;
select user,host from user;
 
 
grant all(所有的权限) on *.* to 'user1' identified by 'passwd'; 
grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; 
grant all on db1.* to 'user3'@'%' identified by 'passwd'; 
show grants; 
show grants for user2@192.168.133.1;
 
创建用户
创建授权权限最高的用户
mysql> grant all on *.* to 'user1' identified by 'passwd';
grant授权  
all所有的权限(查看、创建、删除等)
创建的新用户user1
*.*表示所有 前面的*表示库名 后面的*表示表  可以写成:mysql.*表示mysql库中所有的表
 
创建用户指定登陆客户端IP
mysql> grant all on *.* to 'user1'@'192.168.1.3' identified by 'passwd';
 
%表示通配 这个用户可以所有的IP登陆
mysql> grant all on *.* to 'user1'@'%' identified by 'passwd';
 
创建用户指定本地socket连接
mysql> grant all on *.* to 'user1'@'localhost' identified by 'passwd';
 
针对具体权限(语句、库、表)创建用户
mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.1.3' identified by 'passwd';
 
针对所有的IP授权( 可执行db1库所有的表用户user3所有的IP登陆)
mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
 
查看root用户的授权
mysql> show grants\G;
*************************** 1. row ***************************
Grants for root @localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root @localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
3 rows in set (0.00 sec)
 
ERROR:
No query specified
 
查看指定用户的授权
mysql> show grants for user1@'127.0.0.1';
 
复制用户,多IP登陆
mysql> show grants for  txc2@'183.131.3.206';
2d3eec3f521dd358f7142fa2c2d97798b06.jpg
假设此时183.131.3.207也需要同样的授权  直接复制前面的命令更换IP 就可以
 
针对一个段去授权
grant all on *.* to 'user1'@'192.168.130.%' identified by '密码';
 
 
13.5 常用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;
 
select查看的   insert插入  update更改
 
mysql> use mysql;
Database changed
count(*)查找表的行数  mysql.user mysql数据库的user表
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
 
查看内容
mysql> select * from mysql.db\G;  (*很耗资源)
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: sys
                 User: mysql.sys
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
2 rows in set (0.00 sec)
 
ERROR:
No query specified
 
注:主流的搜索引擎 myisam(自动统计行,搜索速度很快)   innodb(不会自动统计行,搜索很耗时间)
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Password_reuse_history` smallint(5) unsigned DEFAULT NULL,
  `Password_reuse_time` smallint(5) unsigned DEFAULT NULL,
  `Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`Host`,`User`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges'
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
指定一个字段搜索
mysql> select db from mysql.db;
+--------------------+
| db                 |
+--------------------+
| performance_schema |
| sys                |
+--------------------+
2 rows in set (0.00 sec)
 
指定两个字段
mysql> select db,user from mysql.db;
+--------------------+---------------+
| db                 | user          |
+--------------------+---------------+
| performance_schema | mysql.session |
| sys                | mysql.sys     |
+--------------------+---------------+
2 rows in set (0.00 sec)
 
模糊查询  like模糊匹配
mysql> select * from mysql.db where host like 'local%'\G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: sys
                 User: mysql.sys
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
2 rows in set (0.01 sec)
 
ERROR:
No query specified
 
mysql> select * from mysql.db where host like '183.131.3.%'\G
635a36ae02300da4b3890017ef03519f2c3.jpg
 
插入语句
5bf7a4137b0f768c062d5e0ece942bfc93d.jpg
mysql> select * from db1.t1;
Empty set (0.02 sec)
 
插入数据
mysql> insert into db1.t1 values (1, 'abc');   
Query OK, 1 row affected (0.11 sec)
 
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)
字符串加'' 不加则表示是数字
mysql> insert into db1.t1 values (1, 234);
Query OK, 1 row affected (0.06 sec)
 
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
|    1 | 234  |
+------+------+
2 rows in set (0.00 sec)
 
更改
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    1 | aaa  |
+------+------+
2 rows in set (0.00 sec)
 
清除
mysql> delete from db1.t1 where id=1;
Query OK, 2 rows affected (0.11 sec)
 
清空表(清空表内容,不对表结构做任何操作)
mysql> truncate db1.t1;
Query OK, 0 rows affected (0.40 sec)
 
mysql> drop table t1;(将整个表删除)
 
13.6 mysql数据库备份恢复
库的备份可以跨机器, 将一个库备份到另一台机器上   首先这两个机器能够通信   mysqldump -h 远程mysql-ip -uuser-ppassword dbname  >  /本地backup.sql
mysqldump  -uroot -p --database test mysql wordpress>        --database 参数 加与不加有啥区别   不加 --database 就不支持同时备份多个库。
 
备份库  mysqldump -uroot -p123456 mysql > /tmp/mysql.sql 
恢复库 mysql -uroot -p123456 mysql < /tmp/mysql.sql 
备份表 mysqldump -uroot -p123456 mysql user > /tmp/user.sql 
恢复表 mysql -uroot -p123456 mysql < /tmp/user.sql 
备份所有库 mysqldump -uroot -p -A >/tmp/123.sql 
只备份表结构 mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
 
备份指定库至指定位置
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 mysql > /tmp/mysqlbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
备份的库恢复至指定的库
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 -e "create database mysql2"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 mysql2 < /tmp/mysqlbak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
[root@txc208 mysql]#/usr/local/mysql/bin/mysql -uroot -p123 mysql2(直接进入mysql2)
mysql> select database();
+------------+
| database() |
+------------+
| mysql2     |
+------------+
1 row in set (0.00 sec)
 
指定表备份 
mysql库的user表
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 mysql user > /tmp/user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
恢复表
指定要恢复的库名
[root@txc208 mysql]# /usr/local/mysql/bin/mysql -uroot -p123 mysql2 < /tmp/user.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
备份所有库 -A(ALL)
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 -A >/tmp/MYSQL_ALL.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
 
备份表结构
[root@txc208 mysql]# /usr/local/mysql/bin/mysqldump -uroot -p123 -d mysql > /tmp/mysql_jg.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

转载于:https://my.oschina.net/u/3803396/blog/2994621

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值