从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件

04 - MYSQL运维常用基本语句

发布:蔺要红08-04分类: MYSQL

 
-- mysql 每秒钟查询次数、插入次数、删除次数、更新次数的统计
mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_delete    | 0       |
| Com_insert    | 1180202 |
| Com_select    | 717     |
| Com_update    | 0       |
+---------------+---------+
4 rows in set (0.00 sec)
 
# 命令使用帮助
help drop user
help grant
show variables like 'character%';   # 查看数据库编码
reset master;    # 清除binlog日志文件(如果有数据库主从,清除有风险)
flush logs;      # 刷新log日志,自此刻开始产生一个新编号的binlog日志文件,mysqld服务重启时,会自动执行此命令,刷新binlog日志


# 忘记root密码
mysqld --skip-grant-tables  # 配置文件里添加 启动免授权服务端


# 设置root密码,
update user set password=password(111111) where user='root';  
#  5.7方式不同
update mysql.user set authentication_string=password('Aa111111') where user='root' 


# 查看mysql所有的用户  
select distinct user,host from mysql.user;  


MySQL [mysql]> select Host,User,Password,Select_priv,Grant_priv from user;
+-------------+------------+-------------------------------------------+-------------+------------+
| Host        | User       | Password                                  | Select_priv | Grant_priv |
+-------------+------------+-------------------------------------------+-------------+------------+
| localhost   | root       | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y           | Y          |
| 127.0.0.1   | root       | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y           | N          |
| %           | root       | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | Y           | N          |
| %           | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N           | N          |
| localhost   | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N           | N          |
| 127.0.0.1   | linyaohong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | N           | N          |
+-------------+------------+-------------------------------------------+-------------+------------+
9 rows in set (0.00 sec)

# Select_priv  确定用户是否可以通过SELECT命令选择数据
# Grant_priv   确定用户是否可以将已经授予给该用户自己的权限再授予其他用户。
               例如,如果用户可以插入、选择和删除foo数据库中的信息,并且授予了GRANT权限,
               则该用户就可以将其任何或全部权限授予系统中的任何其他用户
   

# 删除用户
delete from mysql.user where user="root" and host="localhost";
delete from mysql.user where not (user='root');
delete from mysql.user where user='';
drop database test;
use mysql;
delete from db;  # 选择mysql数据库以后 清空db表

# 或者
use mysql;
drop user [email protected]'%';          # 删除用户任意IP链接的权限
drop user [email protected]'localhost';
drop user [email protected]'127.0.0.1';


show databases;          # 显示所有的数据库
select database();    # #查看当前所在的库
use day03;               # 进入(选择)day03数据库
show tables;             # 显示day03库里所有的表
SELECT * from auth_user  # 查看表里所有的数据,后面加 \G 代表分组

mysql> desc auth_user;   # 查看表结构
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| password     | varchar(128) | NO   |     | NULL    |                |
| last_login   | datetime     | YES  |     | NULL    |                |
| is_superuser | tinyint(1)   | NO   |     | NULL    |                |
| username     | varchar(150) | NO   | UNI | NULL    |                |
| is_active    | tinyint(1)   | NO   |     | NULL    |                |
| date_joined  | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

备注:
    Key -- RPI  primary key 主键 (一般情况下只有一个主键) 
                如果表中有两个主键则 两个字段作为联合主键
    Key -- UNI  唯一键
    key -- MUL  基本上是一个索引,它既不是主键,也不是唯一键
    

# 创建数据库    
create database wordpress default character SET utf8 COLLATE utf8_general_ci;
create database testdb charset "utf8";

create database linyao; 
mysql> show  create database linyao; #查看数据库创建的语句
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| linyao   | CREATE DATABASE `linyao` /*!40100 DEFAULT CHARACTER SET latin1 */ |   #默认编码不是utf8
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)


# 删除数据库
    
drop database 数据库名 


#清空数据库中的表 userinfo为表名

use 数据库名;
delete from userinfo;      # 清空userinfo表,插入新数据时,id从原来的基础上自增
truncate table  userinfo;  # 清空userinfo表,插入新数据时,id从1开始自增
  

# 删除表

drop table  userinfo


# 授权用户权限(如果用户不存在同时创建用户) #授权全部权限 ALL privileges或者只写ALL
grant ALL privileges on myweb.* to 'linyaohong'@'localhost' IDENTIFIED BY  '123456';
grant SELECT on myweb.* to 'linyaohong'@'127.0.0.1' IDENTIFIED BY  '123456';
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on myweb.* to 'linyaohong'@'%' IDENTIFIED BY  '123456';
# % 可以指定IP段  10.0.%    10.0.0.%   
  
# with grant option 代表用户有权限再授权给其他用户 
grant ALL privileges  on myweb.* to 'linyaohong'@'localhost' IDENTIFIED BY  '123456' with grant option;
grant SELECT on myweb.* to 'linyaohong'@'127.0.0.1' IDENTIFIED BY  '123456' with grant option;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on myweb.* to 'linyaohong'@'%' IDENTIFIED BY  '123456' with grant option;


# 刷新授权结果
flush privileges;


# 查看授权结果
show grants for [email protected]'localhost';                                                                         |
show grants for [email protected]'127.0.0.1'; 
MySQL [mysql]> show grants for [email protected]'%'; 
+-----------------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linyaohong'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `myweb`.* TO 'linyaohong'@'%'                       |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


# 删除用户的授权
revoke ALL privileges  ON myweb.* from 'linyaohong'@'localhost';

MySQL [mysql]> show grants for [email protected]'localhost'; 
+-------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'linyaohong'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站