MYSQL运维常用基本语句

SELECT table_schema, table_name,TABLE_ROWS
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

-p  --password      连接数据库密码
-P  --port          连接数据库端口号
-u  --user          指定连接的用户名
-h  --host          指定主机信息
-S  指定mysql.sock
-e ""  执行SQL语句

mysql -uroot -p111111  -S /data/3306/mysql.sock  testdb < 123.sql  可以指定库执行SQL语句

mysql -uroot -p'111111'  -h 10.10.10.190 -P 3309  # 远程登录mysql

-- 不跳出mysql命令行执行系统命令
mysql> system cd  /root
mysql> system pwd
/data/3307

-- 清除mysql操作历史记录
> /root/.mysql_history

# 命令使用帮助
help drop user
help grant
select version();             # 查看数据库版本
select user();                # 查看当前登陆用户
select now();                 # 查看当前时间
show master status;           # 查看当前binlog
show engine innodb status\G   # 来查看死锁日志

reset master;    # 清除binlog日志文件(如果有数据库主从,一定不能执行此命令*****************)
flush logs;      # 刷新log日志，自此刻开始产生一个新编号的binlog日志文件,mysqld服务重启时，会自动执行此命令，刷新binlog日志,刷新不影响数据库主从,备份的时候也可以加-F 参数,

grep  -E  -v "#|\/|^$|--" t483_shangtua_c_20190827_171324.sql   # 筛选SQL语句

mysql -uroot -p111111 -e "show full processlist;"|egrep -vi "sleep"  # 不进入数据库查看慢语句
show full processlist # 进入数据库查看慢语句,可以通过kill id 杀死语句

SELECT SQL_NO_CACHE * FROM TEST WHERE NUM  =7645;  测试sql语句的效率,不用缓存来查询

mysql -uroot -p111111 -U  # 在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
flush tables with read lock;  # 数据库只读锁定命令，防止导出数据库的时候有数据写入
unlock tables                 # 数据库解除锁定

mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -E -F -R --single-transaction --master-data=2 --triggers --ignore-table=linyaohong.test linyaohong|gzip >/backup/all.sql.gz

#  恢复的时候首先确定备份的时候有没有加-B参数
gunzip < /backup/all.sql.gz|mysql -uroot  -p111111 -S /data/3306/mysql.sock 

mysql  -uroot  -p111111 wordpress2018</home/bak_wordpress2018-12-23.sql

# 设置root密码,
update user set password=password(111111) where user='root';  

# 无密码设置密码
mysqladmin -u root password 111111 -S /data/3309/mysql.sock 
# 有密码设置密码
 mysqladmin -u root -p123123 password 111111 -S /data/3309/mysql.sock

#  5.7方式不同
update mysql.user set authentication_string=password('Aa111111') where user='root' 

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

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

select * from mysql.user \G #比较详细(包含各种权限)

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 linyaohong@'%';          # 删除用户任意IP链接的权限
drop user linyaohong@'localhost';
drop user linyaohong@'127.0.0.1';


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

mysql> desc userinfo; # 查看表结构 看索引不是很详细/可使用show index from userinfo;

# 备注：
   #  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; 
show create table userinfo\G  # 查看创建表的语句
show  create database linyao; #查看数据库创建的语句(包含创建的编码等)

drop table if exists student;  # 删除钱判断数据库是否存在
drop database student   # 删除数据库

#清空数据库中的表 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;

# 或者分成两步
mysql> create user linyaohong@'10.10.10.%' identified by '111111';
mysql> grant select on *.* to linyaohong@'10.10.10.%';

# 刷新授权结果
flush privileges;

# 回收权限
revoke all privileges on *.*  from 'yearning'@'%';
revoke all privileges on *.*  from 'yearning'@'127.0.0.1';
revoke all privileges on *.*  from 'yearning'@'localhost';


# 查看授权结果
# ALL: 允许做任何事(和root一样)
# USAGE: 只允许登录--其它什么也不允许做
show grants for linyaohong@'localhost';                                                 
show grants for linyaohong@'127.0.0.1'; 
show grants for linyaohong@'%'; 

select * from mysql.user\G

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

mysqladmin -u root password 111111 -S /data/3307/mysql.sock                     # 无密码设置密码
mysqladmin -uroot -p111111 password 111111 -S /data/3306/mysql.sock             # 有修改密码
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock shutdown                   # 关闭mysql
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock flush-logs                 # 重新生成二进制日志,或者重新生成慢日志
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock status                     # 查看MYSQL少量状态
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock -i 1 status                # 没1秒刷新一次状态显示
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock extended-status            # 查看MYSQL全部状态
watch -d -n1  mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock processlist  # 没1秒查看一次慢日志

mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock processlist -i 1  # 没1秒查看一次慢日志
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock variables|grep query_log   #  show variables

#  mysql 每秒钟查询次数、插入次数、删除次数、更新次数的统计 (可以做定时任务按天取出,然后对比)
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock extended-status|grep -Ew "Com_select|Com_insert|Com_update|Com_update|Com_delete"

# 只取查询次数
mysqladmin -uroot  -p111111 -S /data/3306/mysql.sock extended-status|grep -w Com_select|awk '{print $4}'

mysql -uroot -p111111 -S /data/3306/mysql.sock  -e "show variables"
mysql -uroot -p111111 -S /data/3306/mysql.sock  -e "show variables like 'server_id%'"
mysql -uroot -p111111 -S /data/3306/mysql.sock  -e "show variables like '%slow%'"
mysql -uroot -p111111 -S /data/3306/mysql.sock  -e "show variables like 'log_bin%'"  # 查看当前是否开启binlog
show variables like 'character%';             # 查看数据库编码
             
mysql> show global variables like "key_buffer_size";
mysql> set global key_buffer_size=1024*1024*64;  # 动态调整,不重启mysql生效
mysql> show global variables like "key_buffer_size";